Re: COPY (INSERT/UPDATE/DELETE .. RETURNING ..)

On Sun, Nov 1, 2015 at 2:49 AM, Marko Tiikkaja <marko(at)joh(dot)to> wrote:
> Attached is a patch for being able to do $SUBJECT without a CTE. The
> reasons this is better than a CTE version are:
>
> 1) It's not obvious why a CTE version works but a plain one doesn't
> 2) This one has less overhead (I measured a ~12% improvement on a
> not-too-unreasonable test case)

Would you mind sharing this test case as well as numbers?

> With regard to RULEs, similar restrictions apply as the ones on
> data-modifying statements in WITH.

OK, so with this patch BeginCopy holds a light copy of
RewriteQuery(at)rewriteHandler(dot)c that rewrites queries with rules, and this
patch extends the error handling after calling pg_analyze_and_rewrite. This
looks like the right approach.

INSERT/UPDATE/DELETE ... RETURNING is a Postgres extension not part of the
SQL spec, so my first thought is that there is no reason to reject this
feature based on the grammar allowed.

Here are some comments about the patch.

Nice patch to begin with.

Regression tests are broken when copyselect is run in parallel because
test3 is a table used there as well. A simple idea to fix this is to rename
the table test3 to something else or to use a schema dedicated to this
test, I just renamed it to copydml_test in the patch attached.

- | COPY select_with_parens TO opt_program
copy_file_name opt_with copy_options
+ | COPY '(' PreparableStmt ')' TO opt_program
copy_file_name opt_with copy_options
This does not look right, PreparableStmt is used for statements that are
part of PREPARE queries, any modifications happening there would impact
COPY with this implementation. I think that we had better have a new option
query_with_parens. Please note that the patch attached has not changed that.
--
Michael