From: Tom Lane <tgl@sss.pgh.pa.us>
To: Dean Rasheed <dean.a.rasheed@gmail.com>
cc: Alexander Lakhin <exclusion@gmail.com>, pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #17803: Rule "ALSO INSERT ... SELECT ..." fails to substitute
 default values
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>
Comments: In-reply-to Dean Rasheed <dean.a.rasheed@gmail.com>
	message dated "Thu, 23 Feb 2023 12:00:30 +0000"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <716956.1677189004.1@sss.pgh.pa.us>
Date: Thu, 23 Feb 2023 16:50:04 -0500
Message-ID: <716957.1677189004@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/716957.1677189004%40sss.pgh.pa.us>
Precedence: bulk

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