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

The following bug has been logged on the website:

Bug reference:      17782
Logged by:          Dmytro Hanzhelo
Email address:      d.hanzhelo@indevlab.com
PostgreSQL version: 13.9
Operating system:   CentOS Linux release 8.3.2011
Description:

The our client contacted us and we reproduced the error in our test
environment. 
We reproduced the error on PostgresQL v.13.3 and then upgrade to v.13.9 and
still get this ERROR.
We expect a fix in the release "10.21, 11.16, 12.11, 13.7, 14.3 released
2022-05-12" but nothing.

This SQL request returned ERROR. 

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

We can provide dump for reproduce. 
Please advice.

Alexander Bluce <amateur@indevlab.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

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@sss.pgh.pa.us>
To: "Alexander Bluce" <amateur@indevlab.com>
Cc: "Dmytro Hanzhelo" <d.hanzhelo@indevlab.com>, "pgsql-bugs" <pgsql-bugs@lists.postgresql.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@indevlab.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