pg.ddx.io  pgsql-bugs@postgresql.org mailing list archive  

help / color / mirror / Atom feed
From: Tom Lane <tgl@sss.pgh.pa.us>
To: Dean Rasheed <dean.a.rasheed@gmail.com>
Cc: Alexander Lakhin <exclusion@gmail.com>
Cc: pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #17803: Rule "ALSO INSERT ... SELECT ..." fails to substitute default values
Date: Thu, 23 Feb 2023 16:50:04 -0500
Message-ID: <716957.1677189004@sss.pgh.pa.us> (raw)
In-Reply-To: <CAEZATCW2BPV8H_FoAj6V-_4aK3zn2+DX0pUSL76aXzqYUQsoWQ@mail.gmail.com>
References: <17803-53c63ed4ecb4eac6@postgresql.org>
	<CAEZATCUQtGx6gQtryMbC3ZsEd4GUcc5THVnkXP1F0Fan_uLG-Q@mail.gmail.com>
	<3953951.1676991950@sss.pgh.pa.us>
	<CAEZATCWoXX9-+ZaaLiOMRsZgwmStayBGmCdA0BdwVzo5GCU51Q@mail.gmail.com>
	<3977948.1677002558@sss.pgh.pa.us>
	<7f8f1bbb-9fb3-80fb-a400-5c7e755b5aa2@gmail.com>
	<CAEZATCUrXMUK8F6daS_f0kH7o_=nDtEqA1Ny23SwHWbFvH61PA@mail.gmail.com>
	<60780f8c-3132-133e-8286-538b732bff19@gmail.com>
	<CAEZATCW8WkFajCvOzhcXeSmX-TrHY3mJgd3pS09jV=BY1mOS3g@mail.gmail.com>
	<5e09da43-aaba-7ea7-0a51-a2eb981b058b@gmail.com>
	<CAEZATCW2BPV8H_FoAj6V-_4aK3zn2+DX0pUSL76aXzqYUQsoWQ@mail.gmail.com>

Dean Rasheed <dean.a.rasheed@gmail.com> writes:
> This is definitely an independent bug, nothing to do with DEFAULTs,
> and also not specific to multi-row VALUES lists either, since it also
> fails with INSERT INTO v SELECT 1.

> Apparently the rewriter is generating a query that the planner is not
> able to handle, though I'm not clear on the precise details.

It looks to me like the rewriter is failing to set the rte->lateral flag
on the sub-select, or maybe the fault is even earlier in the parser.
That NEW reference sure looks like a lateral ref to me:

CREATE RULE vr AS ON INSERT TO v DO ALSO INSERT INTO t
   SELECT * FROM (SELECT a FROM t WHERE NEW.a = t.a) tt;

The planner is Asserting because it's seeing a reference to RTE 4
(the VALUES RTE) in a place where it'd only expect to see a reference
to RTE 8 (the sub-select's "t" rel) unless the query uses LATERAL.

Everything goes through fine if I manually add LATERAL:

regression=# CREATE or replace RULE vr AS ON INSERT TO v DO ALSO INSERT INTO t
  SELECT * FROM LATERAL (SELECT a FROM t WHERE NEW.a = t.a) tt;
CREATE RULE
regression=# INSERT INTO v VALUES (1), (2);
INSERT 0 2

Arguably, the user should have written LATERAL on that sub-select in
the first place, but we probably can't start enforcing that ex post
facto.  We'll have to do something that causes NEW (and OLD?) references
in sub-selects to generate a LATERAL marking silently.

Kinda surprising nobody noticed this before, because I'm sure it's
been busted a long time.

			regards, tom lane





view thread (17+ messages)  latest in thread

Message-ID: <716957.1677189004@sss.pgh.pa.us>
Permalink:  ../716957.1677189004@sss.pgh.pa.us/
Also on:    postgresql.org/message-id/716957.1677189004@sss.pgh.pa.us

 · 

reply
Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-bugs@postgresql.org
  Cc: tgl@sss.pgh.pa.us, dean.a.rasheed@gmail.com, exclusion@gmail.com, pgsql-bugs@lists.postgresql.org
  Subject: Re: BUG #17803: Rule "ALSO INSERT ... SELECT ..." fails to substitute default values
  In-Reply-To: <716957.1677189004@sss.pgh.pa.us>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

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