Horton

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

Dean Rasheed <dean.a.rasheed@gmail.com>

View thread View on postgr.es
From: Dean Rasheed <dean.a.rasheed@gmail.com>
To: Alexander Lakhin <exclusion@gmail.com>
Cc: Tom Lane <tgl@sss.pgh.pa.us>, pgsql-bugs@lists.postgresql.org
Date: 2023-02-23T12:00:30Z
Lists: pgsql-bugs 
On Thu, 23 Feb 2023 at 11:00, Alexander Lakhin <exclusion@gmail.com> wrote:
>
> Please look at another anomaly, probably not related to the initial one,
> but related to INSERT VALUES(...), (...), as I can see:
> CREATE TABLE t (a int, b int DEFAULT -1);
> CREATE VIEW v AS SELECT * FROM t;
>
> 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;
>
> INSERT INTO v VALUES (1), (2);

[I just pushed the other fix]

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.

As another clue, if the rule query is modified to prevent subquery
pullup, by adding OFFSET 0, it fails in a different way, with

ERROR:  plan should not reference subplan's variable

in finalize_plan(). Not sure if that helps find the problem.

Regards,
Dean

Commits

  1. Fix mishandling of OLD/NEW references in subqueries in rule actions.

  2. Fix multi-row DEFAULT handling for INSERT ... SELECT rules.

  3. Fix DEFAULT-handling in multi-row VALUES lists for updatable views.