Another multi-row VALUES bug

From: Dean Rasheed <dean(dot)a(dot)rasheed(at)gmail(dot)com>
To: PostgreSQL Hackers <pgsql-hackers(at)lists(dot)postgresql(dot)org>
Subject: Another multi-row VALUES bug
Date: 2022-11-23 12:43:47
Message-ID: CAEZATCV39OOW7LAR_Xq4i+Lc1Byux=eK3Q=HD_pF1o9LBt=phA@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

I was thinking some more about the recent fix to multi-row VALUES
handling in the rewriter (b8f2687fdc), and I realised that there is
another bug in the way DEFAULT values are handled:

In RewriteQuery(), the code assumes that in a multi-row INSERT query,
the VALUES RTE will be the only thing in the query's fromlist. That's
true for the original query, but it's not necessarily the case for
product queries, if the rule action performs a multi-row insert,
leading to a new VALUES RTE that the DEFAULT-handling code might fail
to process. For example:

CREATE TABLE foo(a int);
INSERT INTO foo VALUES (1);

CREATE TABLE foo_log(t timestamptz DEFAULT now(), a int, c text);
CREATE RULE foo_r AS ON UPDATE TO foo
DO ALSO INSERT INTO foo_log VALUES (DEFAULT, old.a, 'old'),
(DEFAULT, new.a, 'new');

UPDATE foo SET a = 2 WHERE a = 1;

ERROR: unrecognized node type: 43

There's a similar example to this in the regression tests, but it
doesn't test DEFAULT-handling.

It's also possible for the current code to cause the same VALUES RTE
to be rewritten multiple times, when recursing into product queries
(if the rule action doesn't add any more stuff to the query's
fromlist). That turns out to be harmless, because the second time
round it will no longer contain any defaults, but it's technically
incorrect, and certainly a waste of cycles.

So I think what the code needs to do is examine the targetlist, and
identify the VALUES RTE that the current query is using as a source,
and rewrite just that RTE (so any original VALUES RTE is rewritten at
the top level, and any VALUES RTEs from rule actions are rewritten
while recursing, and none are rewritten more than once), as in the
attached patch.

While at it, I noticed an XXX code comment questioning whether any of
this applies to MERGE. The answer is "no", because MERGE actions don't
allow multi-row inserts, so I think it's worth updating that comment
to make that clearer.

Regards,
Dean

Attachment Content-Type Size
multi-row-values.patch text/x-patch 9.6 KB

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Dean Rasheed 2022-11-23 12:49:44 Bug in MERGE's test for tables with rules
Previous Message David Rowley 2022-11-23 11:08:20 Re: [PATCH] Enable using llvm jitlink as an alternative llvm jit linker of old Rtdyld.