Re: COPY (INSERT/UPDATE/DELETE .. RETURNING ..) - Mailing list pgsql-hackers

On Sun, Nov 1, 2015 at 2:49 AM, Marko Tiikkaja <marko@joh.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@rewriteHandler.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

--
Michael