Re: BUG #17782: ERROR: variable not found in subplan target lists

Tom, thanks for the info, passed it to the client.

Alexander Bluce, Head of IT Infrastructure department at InDevLab LLC.
https://indevlab.com/
Kyiv, Ukraine

----- Original Message -----
From: "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: "Alexander Bluce" <amateur(at)indevlab(dot)com>
Cc: "Dmytro Hanzhelo" <d(dot)hanzhelo(at)indevlab(dot)com>, "pgsql-bugs" <pgsql-bugs(at)lists(dot)postgresql(dot)org>
Sent: Tuesday, February 14, 2023 6:26:51 PM
Subject: Re: BUG #17782: ERROR: variable not found in subplan target lists

Alexander Bluce <amateur(at)indevlab(dot)com> writes:
> The client has agreed to publish an empty dump. The dump in attachment.

Thanks for the info! I can reduce this to

CREATE TABLE system_module (
system_name text NOT NULL,
module_name text NOT NULL,
state boolean NOT NULL,
system_day date,
next_system_day date
);

CREATE TABLE sub_system_day_settings (
system_name text PRIMARY KEY,
current_day date NOT NULL,
next_day date NOT NULL
);

CREATE RULE on_update_also_update_sub_system_day_settings AS
ON UPDATE TO system_module
WHERE (new.module_name = 'b2'::text) DO ALSO
INSERT INTO sub_system_day_settings (system_name, current_day, next_day)
VALUES (new.system_name, new.system_day, new.next_system_day)
ON CONFLICT(system_name) DO
UPDATE SET current_day = new.system_day, next_day = new.next_system_day;

UPDATE system_module SET state=true
WHERE module_name='cfront_exchangerate';
ERROR: variable not found in subplan target lists

However ... it only fails like that in v13 and earlier.
Since v14 (commit 6c0373ab7), the RULE is rejected with

ERROR: invalid reference to FROM-clause entry for table "new"
LINE 7: UPDATE SET current_day = new.system_day, next_day = ne...
^
HINT: There is an entry for table "new", but it cannot be referenced from this part of the query.

I think that's actually correct: the only table references you are
supposed to write in ON CONFLICT are to the target table and EXCLUDED.
So the rule should be written like

... ON CONFLICT(system_name) DO
UPDATE SET current_day = excluded.current_day, next_day = excluded.next_day;

That syntax works for me in v13 and later versions too.

Interestingly, I tried to test the rule and found that this
case does work in v13:

insert into system_module values('sys', 'b2', false);
update system_module set
system_day = current_date, next_system_day = current_date+1;
table sub_system_day_settings;

I haven't dug into it in detail, but I suspect that the NEW
reference works as long as the referenced column is an update
target in the original statement; it's only if we would have
to pull values from the original statement's target table
that it gets confused.

Anyway, given the current situation I would say that making
NEW references work in this context is a possible future
feature. But it's not something that's likely to be a high
priority for anyone given that rules are semi-deprecated.
(You'd likely be better advised to implement this behavior
with a trigger.)

What I *am* thinking about is back-patching 6c0373ab7.
At the time it seemed to be adding a feature, but now we
can see that it's blocking access to strange misbehaviors.
On the other hand, since there are some cases that do work,
perhaps that would just result in breaking applications
that were not broken before.

On the whole I'm inclined to leave things alone.

regards, tom lane