pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedBUG #17579: 15beta2: strange error when trying to use MERGE statement as a CTE
10+ messages / 4 participants
[nested] [flat]
Commits from this thread:
2022-08-12 92af9143f1 455d254d22 Reject MERGE in CTEs and COPY
* BUG #17579: 15beta2: strange error when trying to use MERGE statement as a CTE
@ 2022-08-07 21:44 PG Bug reporting form <noreply@postgresql.org>
2022-08-08 02:21 ` Re: BUG #17579: 15beta2: strange error when trying to use MERGE statement as a CTE Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 10+ messages in thread
From: PG Bug reporting form @ 2022-08-07 21:44 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org; +Cc: borz_off@cs.msu.su
The following bug has been logged on the website:
Bug reference: 17579
Logged by: Alexey Borzov
Email address: borz_off@cs.msu.su
PostgreSQL version: Unsupported/Unknown
Operating system: Windows
Description:
create table foo (id int);
with cte_failure as (
merge into foo as target
using foo as source
on target.id = 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
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 error
is triggered a lot later due to implementation details.
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: BUG #17579: 15beta2: strange error when trying to use MERGE statement as a CTE
2022-08-07 21:44 BUG #17579: 15beta2: strange error when trying to use MERGE statement as a CTE PG Bug reporting form <noreply@postgresql.org>
@ 2022-08-08 02:21 ` Tom Lane <tgl@sss.pgh.pa.us>
2022-08-11 02:41 ` Re: BUG #17579: 15beta2: strange error when trying to use MERGE statement as a CTE Richard Guo <guofenglinux@gmail.com>
0 siblings, 1 reply; 10+ messages in thread
From: Tom Lane @ 2022-08-08 02:21 UTC (permalink / raw)
To: borz_off@cs.msu.su; +Cc: pgsql-bugs@lists.postgresql.org, Alvaro Herrera <alvherre@alvh.no-ip.org>
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 = 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", Line: 149, PID: 303950)
postgres: postgres regression [local] SELECT(ExceptionalCondition+0x7c)[0x98013c]
postgres: postgres regression [local] SELECT(transformWithClause+0x66c)[0x6275ec]
postgres: postgres regression [local] SELECT(transformStmt+0x10f9)[0x603619]
> 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 error
> 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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: BUG #17579: 15beta2: strange error when trying to use MERGE statement as a CTE
2022-08-07 21:44 BUG #17579: 15beta2: strange error when trying to use MERGE statement as a CTE PG Bug reporting form <noreply@postgresql.org>
2022-08-08 02:21 ` Re: BUG #17579: 15beta2: strange error when trying to use MERGE statement as a CTE Tom Lane <tgl@sss.pgh.pa.us>
@ 2022-08-11 02:41 ` Richard Guo <guofenglinux@gmail.com>
2022-08-11 11:40 ` Re: BUG #17579: 15beta2: strange error when trying to use MERGE statement as a CTE Alvaro Herrera <alvherre@alvh.no-ip.org>
0 siblings, 1 reply; 10+ messages in thread
From: Richard Guo @ 2022-08-11 02:41 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: borz_off@cs.msu.su, PostgreSQL mailing lists <pgsql-bugs@lists.postgresql.org>; Alvaro Herrera <alvherre@alvh.no-ip.org>
On Mon, Aug 8, 2022 at 10:21 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
> 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.
Seems we neglect to think of MERGE statements when we transform WITH
clauses and when we rewrite the query. If we add the check against MERGE
statement in parse_cte.c and in rewriteHandler.c, the query in problem
can work. But I'm not sure if that's enough.
+1 to disallow it for now.
Thanks
Richard
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: BUG #17579: 15beta2: strange error when trying to use MERGE statement as a CTE
2022-08-07 21:44 BUG #17579: 15beta2: strange error when trying to use MERGE statement as a CTE PG Bug reporting form <noreply@postgresql.org>
2022-08-08 02:21 ` Re: BUG #17579: 15beta2: strange error when trying to use MERGE statement as a CTE Tom Lane <tgl@sss.pgh.pa.us>
2022-08-11 02:41 ` Re: BUG #17579: 15beta2: strange error when trying to use MERGE statement as a CTE Richard Guo <guofenglinux@gmail.com>
@ 2022-08-11 11:40 ` Alvaro Herrera <alvherre@alvh.no-ip.org>
2022-08-11 13:37 ` Re: BUG #17579: 15beta2: strange error when trying to use MERGE statement as a CTE Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 10+ messages in thread
From: Alvaro Herrera @ 2022-08-11 11:40 UTC (permalink / raw)
To: Richard Guo <guofenglinux@gmail.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; borz_off@cs.msu.su, PostgreSQL mailing lists <pgsql-bugs@lists.postgresql.org>
On 2022-Aug-11, Richard Guo wrote:
> Seems we neglect to think of MERGE statements when we transform WITH
> clauses and when we rewrite the query. If we add the check against MERGE
> statement in parse_cte.c and in rewriteHandler.c, the query in problem
> can work. But I'm not sure if that's enough.
I would like to have MERGE within CTEs, but I think for it to be truly
useful we need a RETURNING clause, which is currently not implemented.
I don't think it's terribly difficult to implement ... AFAICS most of
the pieces are there ... but clearly out of scope for pg15.
> +1 to disallow it for now.
This patch does that.
--
Álvaro Herrera PostgreSQL Developer — https://www.EnterpriseDB.com/
Attachments:
0001-Reject-MERGE-in-CTE.patch (text/x-diff, 0B) download
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: BUG #17579: 15beta2: strange error when trying to use MERGE statement as a CTE
2022-08-07 21:44 BUG #17579: 15beta2: strange error when trying to use MERGE statement as a CTE PG Bug reporting form <noreply@postgresql.org>
2022-08-08 02:21 ` Re: BUG #17579: 15beta2: strange error when trying to use MERGE statement as a CTE Tom Lane <tgl@sss.pgh.pa.us>
2022-08-11 02:41 ` Re: BUG #17579: 15beta2: strange error when trying to use MERGE statement as a CTE Richard Guo <guofenglinux@gmail.com>
2022-08-11 11:40 ` Re: BUG #17579: 15beta2: strange error when trying to use MERGE statement as a CTE Alvaro Herrera <alvherre@alvh.no-ip.org>
@ 2022-08-11 13:37 ` Tom Lane <tgl@sss.pgh.pa.us>
2022-08-11 14:17 ` Re: BUG #17579: 15beta2: strange error when trying to use MERGE statement as a CTE Alvaro Herrera <alvherre@alvh.no-ip.org>
0 siblings, 1 reply; 10+ messages in thread
From: Tom Lane @ 2022-08-11 13:37 UTC (permalink / raw)
To: Alvaro Herrera <alvherre@alvh.no-ip.org>; +Cc: Richard Guo <guofenglinux@gmail.com>; borz_off@cs.msu.su, PostgreSQL mailing lists <pgsql-bugs@lists.postgresql.org>
Alvaro Herrera <alvherre@alvh.no-ip.org> writes:
> On 2022-Aug-11, Richard Guo wrote:
>> +1 to disallow it for now.
> This patch does that.
The parse error location seems quite oddly chosen. Can you make
it point at the MERGE instead? I think exprLocation(cte->ctequery)
might work, but not sure.
regards, tom lane
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: BUG #17579: 15beta2: strange error when trying to use MERGE statement as a CTE
2022-08-07 21:44 BUG #17579: 15beta2: strange error when trying to use MERGE statement as a CTE PG Bug reporting form <noreply@postgresql.org>
2022-08-08 02:21 ` Re: BUG #17579: 15beta2: strange error when trying to use MERGE statement as a CTE Tom Lane <tgl@sss.pgh.pa.us>
2022-08-11 02:41 ` Re: BUG #17579: 15beta2: strange error when trying to use MERGE statement as a CTE Richard Guo <guofenglinux@gmail.com>
2022-08-11 11:40 ` Re: BUG #17579: 15beta2: strange error when trying to use MERGE statement as a CTE Alvaro Herrera <alvherre@alvh.no-ip.org>
2022-08-11 13:37 ` Re: BUG #17579: 15beta2: strange error when trying to use MERGE statement as a CTE Tom Lane <tgl@sss.pgh.pa.us>
@ 2022-08-11 14:17 ` Alvaro Herrera <alvherre@alvh.no-ip.org>
2022-08-11 14:23 ` Re: BUG #17579: 15beta2: strange error when trying to use MERGE statement as a CTE Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 10+ messages in thread
From: Alvaro Herrera @ 2022-08-11 14:17 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Richard Guo <guofenglinux@gmail.com>; borz_off@cs.msu.su, PostgreSQL mailing lists <pgsql-bugs@lists.postgresql.org>
In the meantime I noticed that DoCopy is subject to the same problem,
since it also uses PreparableStmt. I'll post a patch in a bit.
On 2022-Aug-11, Tom Lane wrote:
> The parse error location seems quite oddly chosen. Can you make
> it point at the MERGE instead? I think exprLocation(cte->ctequery)
> might work, but not sure.
I tried, but couldn't find the location anywhere. None of the nodes for
ModifyTable statements have location :-( We could add one now -- while
it's a bit late for 15, we're likely going to have a catversion bump due
to the JSON revert, so it might be fine.
--
Álvaro Herrera 48°01'N 7°57'E — https://www.EnterpriseDB.com/
"Cuando mañana llegue pelearemos segun lo que mañana exija" (Mowgli)
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: BUG #17579: 15beta2: strange error when trying to use MERGE statement as a CTE
2022-08-07 21:44 BUG #17579: 15beta2: strange error when trying to use MERGE statement as a CTE PG Bug reporting form <noreply@postgresql.org>
2022-08-08 02:21 ` Re: BUG #17579: 15beta2: strange error when trying to use MERGE statement as a CTE Tom Lane <tgl@sss.pgh.pa.us>
2022-08-11 02:41 ` Re: BUG #17579: 15beta2: strange error when trying to use MERGE statement as a CTE Richard Guo <guofenglinux@gmail.com>
2022-08-11 11:40 ` Re: BUG #17579: 15beta2: strange error when trying to use MERGE statement as a CTE Alvaro Herrera <alvherre@alvh.no-ip.org>
2022-08-11 13:37 ` Re: BUG #17579: 15beta2: strange error when trying to use MERGE statement as a CTE Tom Lane <tgl@sss.pgh.pa.us>
2022-08-11 14:17 ` Re: BUG #17579: 15beta2: strange error when trying to use MERGE statement as a CTE Alvaro Herrera <alvherre@alvh.no-ip.org>
@ 2022-08-11 14:23 ` Tom Lane <tgl@sss.pgh.pa.us>
2022-08-11 16:27 ` Re: BUG #17579: 15beta2: strange error when trying to use MERGE statement as a CTE Alvaro Herrera <alvherre@alvh.no-ip.org>
0 siblings, 1 reply; 10+ messages in thread
From: Tom Lane @ 2022-08-11 14:23 UTC (permalink / raw)
To: Alvaro Herrera <alvherre@alvh.no-ip.org>; +Cc: Richard Guo <guofenglinux@gmail.com>; borz_off@cs.msu.su, PostgreSQL mailing lists <pgsql-bugs@lists.postgresql.org>
Alvaro Herrera <alvherre@alvh.no-ip.org> writes:
> On 2022-Aug-11, Tom Lane wrote:
>> The parse error location seems quite oddly chosen. Can you make
>> it point at the MERGE instead? I think exprLocation(cte->ctequery)
>> might work, but not sure.
> I tried, but couldn't find the location anywhere. None of the nodes for
> ModifyTable statements have location :-(
Ah, right, in particular MergeStmt doesn't.
> We could add one now -- while
> it's a bit late for 15, we're likely going to have a catversion bump due
> to the JSON revert, so it might be fine.
Nah, not worth it just for this.
regards, tom lane
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: BUG #17579: 15beta2: strange error when trying to use MERGE statement as a CTE
2022-08-07 21:44 BUG #17579: 15beta2: strange error when trying to use MERGE statement as a CTE PG Bug reporting form <noreply@postgresql.org>
2022-08-08 02:21 ` Re: BUG #17579: 15beta2: strange error when trying to use MERGE statement as a CTE Tom Lane <tgl@sss.pgh.pa.us>
2022-08-11 02:41 ` Re: BUG #17579: 15beta2: strange error when trying to use MERGE statement as a CTE Richard Guo <guofenglinux@gmail.com>
2022-08-11 11:40 ` Re: BUG #17579: 15beta2: strange error when trying to use MERGE statement as a CTE Alvaro Herrera <alvherre@alvh.no-ip.org>
2022-08-11 13:37 ` Re: BUG #17579: 15beta2: strange error when trying to use MERGE statement as a CTE Tom Lane <tgl@sss.pgh.pa.us>
2022-08-11 14:17 ` Re: BUG #17579: 15beta2: strange error when trying to use MERGE statement as a CTE Alvaro Herrera <alvherre@alvh.no-ip.org>
2022-08-11 14:23 ` Re: BUG #17579: 15beta2: strange error when trying to use MERGE statement as a CTE Tom Lane <tgl@sss.pgh.pa.us>
@ 2022-08-11 16:27 ` Alvaro Herrera <alvherre@alvh.no-ip.org>
2022-08-11 17:08 ` Re: BUG #17579: 15beta2: strange error when trying to use MERGE statement as a CTE Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 10+ messages in thread
From: Alvaro Herrera @ 2022-08-11 16:27 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Richard Guo <guofenglinux@gmail.com>; borz_off@cs.msu.su, PostgreSQL mailing lists <pgsql-bugs@lists.postgresql.org>
Here's a patch. I kept the location in the CTE bit, because it's better
than nothing. No location hint is possible for the COPY one, as far as
I can see.
--
Álvaro Herrera 48°01'N 7°57'E — https://www.EnterpriseDB.com/
Attachments:
v2-0001-Reject-MERGE-in-CTEs-and-COPY.patch (text/x-diff, 0B) download
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: BUG #17579: 15beta2: strange error when trying to use MERGE statement as a CTE
2022-08-07 21:44 BUG #17579: 15beta2: strange error when trying to use MERGE statement as a CTE PG Bug reporting form <noreply@postgresql.org>
2022-08-08 02:21 ` Re: BUG #17579: 15beta2: strange error when trying to use MERGE statement as a CTE Tom Lane <tgl@sss.pgh.pa.us>
2022-08-11 02:41 ` Re: BUG #17579: 15beta2: strange error when trying to use MERGE statement as a CTE Richard Guo <guofenglinux@gmail.com>
2022-08-11 11:40 ` Re: BUG #17579: 15beta2: strange error when trying to use MERGE statement as a CTE Alvaro Herrera <alvherre@alvh.no-ip.org>
2022-08-11 13:37 ` Re: BUG #17579: 15beta2: strange error when trying to use MERGE statement as a CTE Tom Lane <tgl@sss.pgh.pa.us>
2022-08-11 14:17 ` Re: BUG #17579: 15beta2: strange error when trying to use MERGE statement as a CTE Alvaro Herrera <alvherre@alvh.no-ip.org>
2022-08-11 14:23 ` Re: BUG #17579: 15beta2: strange error when trying to use MERGE statement as a CTE Tom Lane <tgl@sss.pgh.pa.us>
2022-08-11 16:27 ` Re: BUG #17579: 15beta2: strange error when trying to use MERGE statement as a CTE Alvaro Herrera <alvherre@alvh.no-ip.org>
@ 2022-08-11 17:08 ` Tom Lane <tgl@sss.pgh.pa.us>
2022-08-12 00:58 ` Re: BUG #17579: 15beta2: strange error when trying to use MERGE statement as a CTE Richard Guo <guofenglinux@gmail.com>
0 siblings, 1 reply; 10+ messages in thread
From: Tom Lane @ 2022-08-11 17:08 UTC (permalink / raw)
To: Alvaro Herrera <alvherre@alvh.no-ip.org>; +Cc: Richard Guo <guofenglinux@gmail.com>; borz_off@cs.msu.su, PostgreSQL mailing lists <pgsql-bugs@lists.postgresql.org>
Alvaro Herrera <alvherre@alvh.no-ip.org> writes:
> Here's a patch. I kept the location in the CTE bit, because it's better
> than nothing. No location hint is possible for the COPY one, as far as
> I can see.
OK by me.
regards, tom lane
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: BUG #17579: 15beta2: strange error when trying to use MERGE statement as a CTE
2022-08-07 21:44 BUG #17579: 15beta2: strange error when trying to use MERGE statement as a CTE PG Bug reporting form <noreply@postgresql.org>
2022-08-08 02:21 ` Re: BUG #17579: 15beta2: strange error when trying to use MERGE statement as a CTE Tom Lane <tgl@sss.pgh.pa.us>
2022-08-11 02:41 ` Re: BUG #17579: 15beta2: strange error when trying to use MERGE statement as a CTE Richard Guo <guofenglinux@gmail.com>
2022-08-11 11:40 ` Re: BUG #17579: 15beta2: strange error when trying to use MERGE statement as a CTE Alvaro Herrera <alvherre@alvh.no-ip.org>
2022-08-11 13:37 ` Re: BUG #17579: 15beta2: strange error when trying to use MERGE statement as a CTE Tom Lane <tgl@sss.pgh.pa.us>
2022-08-11 14:17 ` Re: BUG #17579: 15beta2: strange error when trying to use MERGE statement as a CTE Alvaro Herrera <alvherre@alvh.no-ip.org>
2022-08-11 14:23 ` Re: BUG #17579: 15beta2: strange error when trying to use MERGE statement as a CTE Tom Lane <tgl@sss.pgh.pa.us>
2022-08-11 16:27 ` Re: BUG #17579: 15beta2: strange error when trying to use MERGE statement as a CTE Alvaro Herrera <alvherre@alvh.no-ip.org>
2022-08-11 17:08 ` Re: BUG #17579: 15beta2: strange error when trying to use MERGE statement as a CTE Tom Lane <tgl@sss.pgh.pa.us>
@ 2022-08-12 00:58 ` Richard Guo <guofenglinux@gmail.com>
0 siblings, 0 replies; 10+ messages in thread
From: Richard Guo @ 2022-08-12 00:58 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Alvaro Herrera <alvherre@alvh.no-ip.org>; borz_off@cs.msu.su, PostgreSQL mailing lists <pgsql-bugs@lists.postgresql.org>
On Fri, Aug 12, 2022 at 1:08 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Alvaro Herrera <alvherre@alvh.no-ip.org> writes:
> > Here's a patch. I kept the location in the CTE bit, because it's better
> > than nothing. No location hint is possible for the COPY one, as far as
> > I can see.
>
> OK by me.
+1. The patch looks good to me.
Thanks
Richard
^ permalink raw reply [nested|flat] 10+ messages in thread
end of thread, other threads:[~2022-08-12 00:58 UTC | newest]
Thread overview: 10+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2022-08-07 21:44 BUG #17579: 15beta2: strange error when trying to use MERGE statement as a CTE PG Bug reporting form <noreply@postgresql.org>
2022-08-08 02:21 ` Tom Lane <tgl@sss.pgh.pa.us>
2022-08-11 02:41 ` Richard Guo <guofenglinux@gmail.com>
2022-08-11 11:40 ` Alvaro Herrera <alvherre@alvh.no-ip.org>
2022-08-11 13:37 ` Tom Lane <tgl@sss.pgh.pa.us>
2022-08-11 14:17 ` Alvaro Herrera <alvherre@alvh.no-ip.org>
2022-08-11 14:23 ` Tom Lane <tgl@sss.pgh.pa.us>
2022-08-11 16:27 ` Alvaro Herrera <alvherre@alvh.no-ip.org>
2022-08-11 17:08 ` Tom Lane <tgl@sss.pgh.pa.us>
2022-08-12 00:58 ` Richard Guo <guofenglinux@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox