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-22T12:29:36Z
Lists: pgsql-bugs 
On Wed, 22 Feb 2023 at 12:00, Alexander Lakhin <exclusion@gmail.com> wrote:
>
> Thanks! I understand this place better now.
> I mistakenly supposed that VALUES RTE can intervene with INSERT only.
> But while exploring how they can affect other statements,
> I've found one more interesting thing:
> 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 NEW.b;
> INSERT INTO v VALUES(10, -1), (20, DEFAULT);
> SELECT * FROM v;
>
>   a  | b
> ----+----
>   10 | -1
>   20 | -1
>   -1 | -1
>      | -1
>

Yes, that's correct. Or at least that's the way it's always been. We
even have regression tests similar to that.

Any DEFAULT values from the top-level command that haven't been
replaced are set to NULL before the rule is evaluated, so a DEFAULT in
the top-level command becomes a NULL rather than a DEFAULT in the rule
action.

Arguably, that isn't what some users might expect, but it's
long-standing behaviour that would probably be more trouble than it's
worth to change. There are many other surprises in the way rules work,
which is why their use is discouraged, almost to the point of being
deprecated.

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.