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

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

From
PG Bug reporting form
Date:
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.


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

From
"David G. Johnston"
Date:
On Tue, Feb 7, 2023 at 6:38 AM PG Bug reporting form <noreply@postgresql.org> wrote:
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


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.

Then do so - because without a reproducer it is going to be difficult to observe the behavior to fix this.

David J.

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

From
Dmytro Hanzhelo
Date:
We can't send the dupm to mailing list for security reasons, please provide an e-mail address to send dump personally.

——————————
Best regards
Dmytro Hanzhelo
CEO at InDevLab
On 7 Feb 2023, 15:44 +0200, David G. Johnston <david.g.johnston@gmail.com>, wrote:
On Tue, Feb 7, 2023 at 6:38 AM PG Bug reporting form <noreply@postgresql.org> wrote:
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


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.

Then do so - because without a reproducer it is going to be difficult to observe the behavior to fix this.

David J.

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

From
Alexander Bluce
Date:
The client has agreed to publish an empty dump. The dump in attachment.

Before load dump - please create 3 roles:

CREATE ROLE cache;
CREATE ROLE corestatus;
CREATE ROLE status_api;


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


From: "Dmytro Hanzhelo" <d.hanzhelo@indevlab.com>
To: pgsql-bugs@lists.postgresql.org, "David G. Johnston" <david.g.johnston@gmail.com>, "Alex Bluce" <amateur@indevlab.com>
Sent: Wednesday, February 8, 2023 5:42:37 PM
Subject: Re: BUG #17782: ERROR: variable not found in subplan target lists

We can't send the dupm to mailing list for security reasons, please provide an e-mail address to send dump personally.

——————————
Best regards
Dmytro Hanzhelo
CEO at InDevLab

Attachment

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

From
Tom Lane
Date:
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



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

From
Alexander Bluce
Date:
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