From: Tom Lane <tgl@sss.pgh.pa.us>
To: borz_off@cs.msu.su
cc: pgsql-bugs@lists.postgresql.org, Alvaro Herrera <alvherre@alvh.no-ip.org>
Subject: Re: BUG #17579: 15beta2: strange error when trying to use MERGE
 statement as a CTE
In-reply-to: <17579-82482cd7b267b862@postgresql.org>
References: <17579-82482cd7b267b862@postgresql.org>
Comments: In-reply-to PG Bug reporting form <noreply@postgresql.org>
	message dated "Sun, 07 Aug 2022 21:44:25 -0000"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <304360.1659925267.1@sss.pgh.pa.us>
Content-Transfer-Encoding: quoted-printable
Date: Sun, 07 Aug 2022 22:21:07 -0400
Message-ID: <304361.1659925267@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/304361.1659925267%40sss.pgh.pa.us>
Precedence: bulk

PG Bug reporting form <noreply@postgresql.org> writes:
> create table foo (id int);

> with cte_failure as (
>     merge into foo as target
>     using foo as source
>     on target.id =3D source.id
>     when matched then do nothing
> )
> select 'fail!';

> When executing the above code I get the following error:

> ERROR:  DO INSTEAD NOTIFY rules are not supported for data-modifying
> statements in WITH

With asserts on, it fails in the parser:

TRAP: FailedAssertion("IsA(cte->ctequery, InsertStmt) || IsA(cte->ctequery=
, UpdateStmt) || IsA(cte->ctequery, DeleteStmt)", File: "parse_cte.c", Lin=
e: 149, PID: 303950)
postgres: postgres regression [local] SELECT(ExceptionalCondition+0x7c)[0x=
98013c]
postgres: postgres regression [local] SELECT(transformWithClause+0x66c)[0x=
6275ec]
postgres: postgres regression [local] SELECT(transformStmt+0x10f9)[0x60361=
9]

> I suspect that MERGE was never intended to work as a CTE, but right now =
the
> grammar allows any PreparableStmt in a common_table_expr and the above e=
rror
> is triggered a lot later due to implementation details.

It evidently wasn't ever *tested*, but in principle I think it ought
to work.  I'm not sure how much effort will be involved to make that
happen.  At this point we might have to disallow it for v15 and
come back to the problem later.

			regards, tom lane