Re: BUG #17803: Rule "ALSO INSERT ... SELECT ..." fails to substitute default values

Dean Rasheed <dean(dot)a(dot)rasheed(at)gmail(dot)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