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: exclusion@gmail.com, pgsql-bugs@lists.postgresql.org
Date: 2023-02-21T11:56:00Z
Lists: pgsql-bugs

Attachments

On Tue, 21 Feb 2023 at 09:21, PG Bug reporting form
<noreply@postgresql.org> wrote:
>
> The following queries:
> 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 1;
> INSERT INTO v VALUES (0, DEFAULT), (1, DEFAULT);
>
> lead to an assert for me:
>

Nice catch.

It looks like we need something like the attached, to deal with
product queries that are INSERT ... SELECT queries. In that case the
VALUES RTE will be at the same index, but in the SELECT part of the
product query, not the top-level product query itself.

Annoyingly, this can't use getInsertSelectQuery(), because the product
query will no longer have the OLD and NEW placeholder entries, so I
have just duplicated the relevant portions of its code, rather than
remove those checks from getInsertSelectQuery().

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.