Month: June 2014

Waiting for 9.5 – Implement UPDATE tab SET (col1,col2,…) = (SELECT …), …

On 18th of June, Tom Lane committed patch:

Implement UPDATE tab SET (col1,col2,...) = (SELECT ...), ...
 
This SQL-standard feature allows a sub-SELECT yielding multiple columns
(but only one row) to be used to compute the new values of several columns
to be updated.  While the same results can be had with an independent
sub-SELECT per column, such a workaround can require a great deal of
duplicated computation.
 
The standard actually says that the source for a multi-column assignment
could be any row-valued expression.  The implementation used here is
tightly tied to our existing sub-SELECT support and can't handle other
cases; the Bison grammar would have some issues with them too.  However,
I don't feel too bad about this since other cases can be converted into
sub-SELECTs.  For instance, "SET (a,b,c) = row_valued_function(x)" could
be written "SET (a,b,c) = (SELECT * FROM row_valued_function(x))".

Continue reading Waiting for 9.5 – Implement UPDATE tab SET (col1,col2,…) = (SELECT …), …

Anonymize CTE names on explain.depesz.com

A colleague recently let me know that anonymization in explain.depesz.com doesn't handle CTE names. For example, in plan:

                                            QUERY PLAN                                             
---------------------------------------------------------------------------------------------------
 CTE Scan ON some_name  (cost=0.01..0.03 ROWS=1 width=8) (actual TIME=0.027..0.028 ROWS=1 loops=1)
   CTE some_name
     ->  RESULT  (cost=0.00..0.01 ROWS=1 width=0) (actual TIME=0.023..0.023 ROWS=1 loops=1)
 Planning TIME: 0.217 ms
 Execution TIME: 0.124 ms
(5 ROWS)

“some_name" was kept even if anonymization was turned on.

Now it's fixed, and new code should properly anonymize all CTE names. Of course the change is live on explain.depesz.com too.