pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedBUG #17782: ERROR: variable not found in subplan target lists
6+ messages / 5 participants
[nested] [flat]
* BUG #17782: ERROR: variable not found in subplan target lists
@ 2023-02-07 13:16 PG Bug reporting form <noreply@postgresql.org>
0 siblings, 1 reply; 6+ messages in thread
From: PG Bug reporting form @ 2023-02-07 13:16 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org; +Cc: d.hanzhelo@indevlab.com
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.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: BUG #17782: ERROR: variable not found in subplan target lists
@ 2023-02-07 13:43 David G. Johnston <david.g.johnston@gmail.com>
parent: PG Bug reporting form <noreply@postgresql.org>
0 siblings, 1 reply; 6+ messages in thread
From: David G. Johnston @ 2023-02-07 13:43 UTC (permalink / raw)
To: d.hanzhelo@indevlab.com; pgsql-bugs@lists.postgresql.org
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.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: BUG #17782: ERROR: variable not found in subplan target lists
@ 2023-02-08 15:42 Dmytro Hanzhelo <d.hanzhelo@indevlab.com>
parent: David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 1 reply; 6+ messages in thread
From: Dmytro Hanzhelo @ 2023-02-08 15:42 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org, "David G. Johnston" <david.g.johnston@gmail.com>; Alex Bluce <amateur@indevlab.com>
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.
> >
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: BUG #17782: ERROR: variable not found in subplan target lists
@ 2023-02-14 12:51 Alexander Bluce <amateur@indevlab.com>
parent: Dmytro Hanzhelo <d.hanzhelo@indevlab.com>
0 siblings, 1 reply; 6+ messages in thread
From: Alexander Bluce @ 2023-02-14 12:51 UTC (permalink / raw)
To: Dmytro Hanzhelo <d.hanzhelo@indevlab.com>; +Cc: pgsql-bugs@lists.postgresql.org
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
Attachments:
corestatus.sql (text/x-sql, 36.6K) download
--pg_dump --dbname=cache --format=plain --schema-only -n 'corestatus' >/app/pg/backup/corestatus.sql
--
-- PostgreSQL database dump
--
-- Dumped from database version 13.7
-- Dumped by pg_dump version 13.7
SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SELECT pg_catalog.set_config('search_path', '', false);
SET check_function_bodies = false;
SET xmloption = content;
SET client_min_messages = warning;
SET row_security = off;
--
-- Name: corestatus; Type: SCHEMA; Schema: -; Owner: corestatus
--
CREATE SCHEMA corestatus;
ALTER SCHEMA corestatus OWNER TO corestatus;
--
-- Name: SCHEMA corestatus; Type: COMMENT; Schema: -; Owner: corestatus
--
COMMENT ON SCHEMA corestatus IS 'schema CORESTATUS';
--
-- Name: enum_action; Type: TYPE; Schema: corestatus; Owner: corestatus
--
CREATE TYPE corestatus.enum_action AS ENUM (
'i',
'u',
'd'
);
ALTER TYPE corestatus.enum_action OWNER TO corestatus;
--
-- Name: TYPE enum_action; Type: COMMENT; Schema: corestatus; Owner: corestatus
--
COMMENT ON TYPE corestatus.enum_action IS 'Перелік можливих значень dml команд';
SET default_tablespace = '';
SET default_table_access_method = heap;
--
-- Name: system_module; Type: TABLE; Schema: corestatus; Owner: corestatus
--
CREATE TABLE corestatus.system_module (
system_name text NOT NULL,
module_name text NOT NULL,
state boolean NOT NULL,
start_time character varying(5),
finish_time character varying(5),
system_day date,
next_system_day date
);
ALTER TABLE corestatus.system_module OWNER TO corestatus;
--
-- Name: TABLE system_module; Type: COMMENT; Schema: corestatus; Owner: corestatus
--
COMMENT ON TABLE corestatus.system_module IS 'Таблиця доступності модулів облікових систем';
--
-- Name: COLUMN system_module.system_name; Type: COMMENT; Schema: corestatus; Owner: corestatus
--
COMMENT ON COLUMN corestatus.system_module.system_name IS 'облікова система';
--
-- Name: COLUMN system_module.module_name; Type: COMMENT; Schema: corestatus; Owner: corestatus
--
COMMENT ON COLUMN corestatus.system_module.module_name IS 'модуль облікової системи';
--
-- Name: COLUMN system_module.state; Type: COMMENT; Schema: corestatus; Owner: corestatus
--
COMMENT ON COLUMN corestatus.system_module.state IS 'ознака доступності';
--
-- Name: COLUMN system_module.start_time; Type: COMMENT; Schema: corestatus; Owner: corestatus
--
COMMENT ON COLUMN corestatus.system_module.start_time IS 'початок доступності бізнес-функціоналу';
--
-- Name: COLUMN system_module.finish_time; Type: COMMENT; Schema: corestatus; Owner: corestatus
--
COMMENT ON COLUMN corestatus.system_module.finish_time IS 'завершення доступності бізнес-функціоналу';
--
-- Name: COLUMN system_module.system_day; Type: COMMENT; Schema: corestatus; Owner: corestatus
--
COMMENT ON COLUMN corestatus.system_module.system_day IS 'поточна операційна дата';
--
-- Name: COLUMN system_module.next_system_day; Type: COMMENT; Schema: corestatus; Owner: corestatus
--
COMMENT ON COLUMN corestatus.system_module.next_system_day IS 'наступна операційна дата';
--
-- Name: get_info(text, text); Type: FUNCTION; Schema: corestatus; Owner: corestatus
--
CREATE FUNCTION corestatus.get_info(i_system text, i_module text) RETURNS SETOF corestatus.system_module
LANGUAGE sql SECURITY DEFINER
SET search_path TO 'corestatus'
AS $$select system_name, module_name, state, start_time, finish_time, system_day, next_system_day
from v_system_module
where system_name = coalesce(lower(i_system), system_name)
and module_name = coalesce(lower(i_module), module_name)$$;
ALTER FUNCTION corestatus.get_info(i_system text, i_module text) OWNER TO corestatus;
--
-- Name: FUNCTION get_info(i_system text, i_module text); Type: COMMENT; Schema: corestatus; Owner: corestatus
--
COMMENT ON FUNCTION corestatus.get_info(i_system text, i_module text) IS 'повертає набір записів представлення v_system_module';
--
-- Name: sub_system_day_settings; Type: TABLE; Schema: corestatus; Owner: corestatus
--
CREATE TABLE corestatus.sub_system_day_settings (
system_name text NOT NULL,
current_day date NOT NULL,
next_day date NOT NULL
);
ALTER TABLE corestatus.sub_system_day_settings OWNER TO corestatus;
--
-- Name: TABLE sub_system_day_settings; Type: COMMENT; Schema: corestatus; Owner: corestatus
--
COMMENT ON TABLE corestatus.sub_system_day_settings IS 'налаштування операційного дня облікової системи';
--
-- Name: COLUMN sub_system_day_settings.system_name; Type: COMMENT; Schema: corestatus; Owner: corestatus
--
COMMENT ON COLUMN corestatus.sub_system_day_settings.system_name IS 'облікова система';
--
-- Name: COLUMN sub_system_day_settings.current_day; Type: COMMENT; Schema: corestatus; Owner: corestatus
--
COMMENT ON COLUMN corestatus.sub_system_day_settings.current_day IS 'поточна операційна дата';
--
-- Name: COLUMN sub_system_day_settings.next_day; Type: COMMENT; Schema: corestatus; Owner: corestatus
--
COMMENT ON COLUMN corestatus.sub_system_day_settings.next_day IS 'дата наступного операційного дня';
--
-- Name: get_system_days(text); Type: FUNCTION; Schema: corestatus; Owner: corestatus
--
CREATE FUNCTION corestatus.get_system_days(i_system text) RETURNS corestatus.sub_system_day_settings
LANGUAGE sql SECURITY DEFINER
SET search_path TO 'corestatus'
AS $$ select * from sub_system_day_settings where lower(system_name) = lower(i_system); $$;
ALTER FUNCTION corestatus.get_system_days(i_system text) OWNER TO corestatus;
--
-- Name: FUNCTION get_system_days(i_system text); Type: COMMENT; Schema: corestatus; Owner: corestatus
--
COMMENT ON FUNCTION corestatus.get_system_days(i_system text) IS 'повертає запис таблиці sub_system_day_settings по заданій обліковій системі';
--
-- Name: sub_system_module; Type: TABLE; Schema: corestatus; Owner: corestatus
--
CREATE TABLE corestatus.sub_system_module (
system_name text NOT NULL,
module_name text NOT NULL,
module_status text NOT NULL
);
ALTER TABLE corestatus.sub_system_module OWNER TO corestatus;
--
-- Name: TABLE sub_system_module; Type: COMMENT; Schema: corestatus; Owner: corestatus
--
COMMENT ON TABLE corestatus.sub_system_module IS 'модулі облікових систем та їх статуси доступності';
--
-- Name: COLUMN sub_system_module.system_name; Type: COMMENT; Schema: corestatus; Owner: corestatus
--
COMMENT ON COLUMN corestatus.sub_system_module.system_name IS 'облікова система';
--
-- Name: COLUMN sub_system_module.module_name; Type: COMMENT; Schema: corestatus; Owner: corestatus
--
COMMENT ON COLUMN corestatus.sub_system_module.module_name IS 'модуль облікової системи';
--
-- Name: COLUMN sub_system_module.module_status; Type: COMMENT; Schema: corestatus; Owner: corestatus
--
COMMENT ON COLUMN corestatus.sub_system_module.module_status IS 'статус модуля облікової системи';
--
-- Name: get_system_module(text, text); Type: FUNCTION; Schema: corestatus; Owner: corestatus
--
CREATE FUNCTION corestatus.get_system_module(i_system text DEFAULT NULL::text, i_module text DEFAULT NULL::text) RETURNS SETOF corestatus.sub_system_module
LANGUAGE sql SECURITY DEFINER
SET search_path TO 'corestatus'
AS $$
select * from sub_system_module
where system_name = coalesce(lower(i_system), system_name) and module_name = coalesce(lower(i_module), module_name);
$$;
ALTER FUNCTION corestatus.get_system_module(i_system text, i_module text) OWNER TO corestatus;
--
-- Name: FUNCTION get_system_module(i_system text, i_module text); Type: COMMENT; Schema: corestatus; Owner: corestatus
--
COMMENT ON FUNCTION corestatus.get_system_module(i_system text, i_module text) IS 'повертає записи з таблиці sub_system_module по заданим параметрам';
--
-- Name: insert_sub_system_day_settings_log(text, text, date, date); Type: FUNCTION; Schema: corestatus; Owner: corestatus
--
CREATE FUNCTION corestatus.insert_sub_system_day_settings_log(i_action text, i_system_name text, i_current_day date, i_next_day date) RETURNS void
LANGUAGE sql SECURITY DEFINER
SET search_path TO 'corestatus'
AS $$
insert into sub_system_day_settings_log
values (i_action, lower(i_system_name), i_current_day, i_next_day, current_timestamp);
$$;
ALTER FUNCTION corestatus.insert_sub_system_day_settings_log(i_action text, i_system_name text, i_current_day date, i_next_day date) OWNER TO corestatus;
--
-- Name: FUNCTION insert_sub_system_day_settings_log(i_action text, i_system_name text, i_current_day date, i_next_day date); Type: COMMENT; Schema: corestatus; Owner: corestatus
--
COMMENT ON FUNCTION corestatus.insert_sub_system_day_settings_log(i_action text, i_system_name text, i_current_day date, i_next_day date) IS 'добавляє запис в журнал змін по записам таблиці insert_sub_system_day_settings_log';
--
-- Name: insert_sub_system_module_log(text, text, text, text); Type: FUNCTION; Schema: corestatus; Owner: corestatus
--
CREATE FUNCTION corestatus.insert_sub_system_module_log(i_action text, i_system_name text, i_module_name text, i_module_status text) RETURNS void
LANGUAGE sql SECURITY DEFINER
SET search_path TO 'corestatus'
AS $$
insert into sub_system_module_log values (
i_action, i_system_name, i_module_name, i_module_status, current_timestamp
);
$$;
ALTER FUNCTION corestatus.insert_sub_system_module_log(i_action text, i_system_name text, i_module_name text, i_module_status text) OWNER TO corestatus;
--
-- Name: FUNCTION insert_sub_system_module_log(i_action text, i_system_name text, i_module_name text, i_module_status text); Type: COMMENT; Schema: corestatus; Owner: corestatus
--
COMMENT ON FUNCTION corestatus.insert_sub_system_module_log(i_action text, i_system_name text, i_module_name text, i_module_status text) IS 'функція добавлення запису в журнал зміни статусів модулів обілкових систем';
--
-- Name: jsonb_nullif_value(jsonb); Type: FUNCTION; Schema: corestatus; Owner: corestatus
--
CREATE FUNCTION corestatus.jsonb_nullif_value(i_jsonb jsonb) RETURNS jsonb
LANGUAGE sql
AS $_$select jsonb_object(array_agg(key), array_agg(nullif(value, ''))) from jsonb_each_text($1)$_$;
ALTER FUNCTION corestatus.jsonb_nullif_value(i_jsonb jsonb) OWNER TO corestatus;
--
-- Name: FUNCTION jsonb_nullif_value(i_jsonb jsonb); Type: COMMENT; Schema: corestatus; Owner: corestatus
--
COMMENT ON FUNCTION corestatus.jsonb_nullif_value(i_jsonb jsonb) IS 'не заповнені значення змінює на null';
--
-- Name: save_log(); Type: FUNCTION; Schema: corestatus; Owner: corestatus
--
CREATE FUNCTION corestatus.save_log() RETURNS trigger
LANGUAGE plpgsql
SET search_path TO 'corestatus'
AS $$declare
v_log system_module_log;
begin
v_log.sync_time := current_timestamp;
if lower(tg_op) = 'insert' then
v_log.new_row := to_json(new.*);
elsif lower(tg_op) = 'update' then
v_log.old_row := to_json(old.*);
v_log.new_row := to_json(new.*);
elsif lower(tg_op) = 'delete' then
v_log.old_row := to_json(old.*);
else
return null;
end if;
insert into system_module_log select v_log.*;
return null;
end$$;
ALTER FUNCTION corestatus.save_log() OWNER TO corestatus;
--
-- Name: FUNCTION save_log(); Type: COMMENT; Schema: corestatus; Owner: corestatus
--
COMMENT ON FUNCTION corestatus.save_log() IS 'тригерна функція для логування змін по рядках таблиці system_module';
--
-- Name: sync(text); Type: PROCEDURE; Schema: corestatus; Owner: corestatus
--
CREATE PROCEDURE corestatus.sync(i_msg_text text)
LANGUAGE plpgsql SECURITY DEFINER
SET search_path TO 'corestatus'
AS $$declare
v_jsonb jsonb;
v_action enum_action;
v_record system_module;
begin
v_jsonb := i_msg_text;
v_jsonb := jsonb_nullif_value(v_jsonb);
v_action := v_jsonb ->> 'action';
v_record := jsonb_populate_record(null::system_module, v_jsonb);
v_record.system_name := lower(v_record.system_name);
v_record.module_name := lower(v_record.module_name);
if v_action = 'i' then
insert into system_module select v_record.*;
elsif v_action = 'u' then
update system_module
set start_time = v_record.start_time, next_system_day = v_record.next_system_day,
system_day = v_record.system_day, finish_time = v_record.finish_time, state = v_record.state
where system_name = v_record.system_name
and module_name = v_record.module_name;
elsif v_action = 'd' then
delete from system_module
where system_name = v_record.system_name
and module_name = v_record.module_name;
else
raise exception 'invalid input value for enum enum_action -> %', v_action;
end if;
end$$;
ALTER PROCEDURE corestatus.sync(i_msg_text text) OWNER TO corestatus;
--
-- Name: PROCEDURE sync(i_msg_text text); Type: COMMENT; Schema: corestatus; Owner: corestatus
--
COMMENT ON PROCEDURE corestatus.sync(i_msg_text text) IS 'аналізує текст вхідного повідомлення та виконує відповідну команду';
--
-- Name: traiud_sub_system_day_settings_do_log(); Type: FUNCTION; Schema: corestatus; Owner: corestatus
--
CREATE FUNCTION corestatus.traiud_sub_system_day_settings_do_log() RETURNS trigger
LANGUAGE plpgsql SECURITY DEFINER
SET search_path TO 'corestatus'
AS $$
begin
perform insert_sub_system_day_settings_log(
i_action => substr(tg_op, 1),
i_system_name => new.system_name,
i_current_day => new.current_day,
i_next_day => new.next_day
);
return null;
end;
$$;
ALTER FUNCTION corestatus.traiud_sub_system_day_settings_do_log() OWNER TO corestatus;
--
-- Name: FUNCTION traiud_sub_system_day_settings_do_log(); Type: COMMENT; Schema: corestatus; Owner: corestatus
--
COMMENT ON FUNCTION corestatus.traiud_sub_system_day_settings_do_log() IS 'допоміжна тригерна функція для тригера tr_log_sub_system_day_settings';
--
-- Name: traiud_sub_system_module_do_log(); Type: FUNCTION; Schema: corestatus; Owner: corestatus
--
CREATE FUNCTION corestatus.traiud_sub_system_module_do_log() RETURNS trigger
LANGUAGE plpgsql SECURITY DEFINER
SET search_path TO 'corestatus'
AS $$
begin
perform insert_sub_system_module_log(
i_action => substr(tg_op, 1, 1)::char,
i_system_name => new.system_name,
i_module_name => new.module_name,
i_module_status => new.module_status
);
return null;
end;
$$;
ALTER FUNCTION corestatus.traiud_sub_system_module_do_log() OWNER TO corestatus;
--
-- Name: FUNCTION traiud_sub_system_module_do_log(); Type: COMMENT; Schema: corestatus; Owner: corestatus
--
COMMENT ON FUNCTION corestatus.traiud_sub_system_module_do_log() IS 'допоміжна тригерна функція для однойменного тригера';
--
-- Name: trau_b2_close_do_same_to_all(); Type: FUNCTION; Schema: corestatus; Owner: corestatus
--
CREATE FUNCTION corestatus.trau_b2_close_do_same_to_all() RETURNS trigger
LANGUAGE plpgsql SECURITY DEFINER
SET search_path TO 'corestatus'
AS $$
declare
rec sub_system_module%rowtype;
begin
for rec in (select *
from sub_system_module
where system_name = new.system_name
and module_name <> 'b2')
loop
call upsert_sub_system_module(
i_system_name => rec.system_name,
i_module_name => rec.module_name,
i_module_status => new.module_status
);
end loop;
return null;
end;
$$;
ALTER FUNCTION corestatus.trau_b2_close_do_same_to_all() OWNER TO corestatus;
--
-- Name: FUNCTION trau_b2_close_do_same_to_all(); Type: COMMENT; Schema: corestatus; Owner: corestatus
--
COMMENT ON FUNCTION corestatus.trau_b2_close_do_same_to_all() IS 'допоміжна функція однойменного тригера';
--
-- Name: upsert_sub_system_day_settings(text, date, date); Type: PROCEDURE; Schema: corestatus; Owner: corestatus
--
CREATE PROCEDURE corestatus.upsert_sub_system_day_settings(i_system_name text, i_current_day date, i_next_day date)
LANGUAGE sql SECURITY DEFINER
SET search_path TO 'corestatus'
AS $$
insert into sub_system_day_settings values (lower(i_system_name), i_current_day, i_next_day)
on conflict (system_name) do update
set current_day = i_current_day, next_day = i_next_day;
$$;
ALTER PROCEDURE corestatus.upsert_sub_system_day_settings(i_system_name text, i_current_day date, i_next_day date) OWNER TO corestatus;
--
-- Name: PROCEDURE upsert_sub_system_day_settings(i_system_name text, i_current_day date, i_next_day date); Type: COMMENT; Schema: corestatus; Owner: corestatus
--
COMMENT ON PROCEDURE corestatus.upsert_sub_system_day_settings(i_system_name text, i_current_day date, i_next_day date) IS 'добавляє або вносить зміни в існуючий запис таблиці sub_system_day_settings';
--
-- Name: upsert_sub_system_module(text, text, text); Type: PROCEDURE; Schema: corestatus; Owner: corestatus
--
CREATE PROCEDURE corestatus.upsert_sub_system_module(i_system_name text, i_module_name text, i_module_status text)
LANGUAGE plpgsql SECURITY DEFINER
SET search_path TO 'corestatus'
AS $$
begin
insert into sub_system_module values (lower(i_system_name), lower(i_module_name), lower(i_module_status))
on conflict (system_name, module_name) do update
set module_status = lower(i_module_status);
end; $$;
ALTER PROCEDURE corestatus.upsert_sub_system_module(i_system_name text, i_module_name text, i_module_status text) OWNER TO corestatus;
--
-- Name: PROCEDURE upsert_sub_system_module(i_system_name text, i_module_name text, i_module_status text); Type: COMMENT; Schema: corestatus; Owner: corestatus
--
COMMENT ON PROCEDURE corestatus.upsert_sub_system_module(i_system_name text, i_module_name text, i_module_status text) IS 'добавляє або вносить зміни в існуючий запис таблиці sub_system_module';
--
-- Name: sub_system_day_settings_log; Type: TABLE; Schema: corestatus; Owner: corestatus
--
CREATE TABLE corestatus.sub_system_day_settings_log (
action text,
system_name text,
current_day date,
next_day date,
sync_time timestamp without time zone DEFAULT CURRENT_TIMESTAMP
);
ALTER TABLE corestatus.sub_system_day_settings_log OWNER TO corestatus;
--
-- Name: TABLE sub_system_day_settings_log; Type: COMMENT; Schema: corestatus; Owner: corestatus
--
COMMENT ON TABLE corestatus.sub_system_day_settings_log IS 'журнал зміни операційних днів облікових систем';
--
-- Name: COLUMN sub_system_day_settings_log.action; Type: COMMENT; Schema: corestatus; Owner: corestatus
--
COMMENT ON COLUMN corestatus.sub_system_day_settings_log.action IS 'dml команда';
--
-- Name: COLUMN sub_system_day_settings_log.system_name; Type: COMMENT; Schema: corestatus; Owner: corestatus
--
COMMENT ON COLUMN corestatus.sub_system_day_settings_log.system_name IS 'облікова система';
--
-- Name: COLUMN sub_system_day_settings_log.current_day; Type: COMMENT; Schema: corestatus; Owner: corestatus
--
COMMENT ON COLUMN corestatus.sub_system_day_settings_log.current_day IS 'поточний операційни день';
--
-- Name: COLUMN sub_system_day_settings_log.next_day; Type: COMMENT; Schema: corestatus; Owner: corestatus
--
COMMENT ON COLUMN corestatus.sub_system_day_settings_log.next_day IS 'наступний операційни день';
--
-- Name: COLUMN sub_system_day_settings_log.sync_time; Type: COMMENT; Schema: corestatus; Owner: corestatus
--
COMMENT ON COLUMN corestatus.sub_system_day_settings_log.sync_time IS 'дата та час виконання dml команди';
--
-- Name: sub_system_module_log; Type: TABLE; Schema: corestatus; Owner: corestatus
--
CREATE TABLE corestatus.sub_system_module_log (
action text,
system_name text,
module_name text,
module_status text,
sync_time timestamp without time zone DEFAULT CURRENT_TIMESTAMP
);
ALTER TABLE corestatus.sub_system_module_log OWNER TO corestatus;
--
-- Name: TABLE sub_system_module_log; Type: COMMENT; Schema: corestatus; Owner: corestatus
--
COMMENT ON TABLE corestatus.sub_system_module_log IS 'журнал зміни статусів модулів облікових систем';
--
-- Name: COLUMN sub_system_module_log.action; Type: COMMENT; Schema: corestatus; Owner: corestatus
--
COMMENT ON COLUMN corestatus.sub_system_module_log.action IS 'dml команда';
--
-- Name: COLUMN sub_system_module_log.system_name; Type: COMMENT; Schema: corestatus; Owner: corestatus
--
COMMENT ON COLUMN corestatus.sub_system_module_log.system_name IS 'облікова система';
--
-- Name: COLUMN sub_system_module_log.module_name; Type: COMMENT; Schema: corestatus; Owner: corestatus
--
COMMENT ON COLUMN corestatus.sub_system_module_log.module_name IS 'модуль облікової системи';
--
-- Name: COLUMN sub_system_module_log.module_status; Type: COMMENT; Schema: corestatus; Owner: corestatus
--
COMMENT ON COLUMN corestatus.sub_system_module_log.module_status IS 'статус модуля облікової системи';
--
-- Name: COLUMN sub_system_module_log.sync_time; Type: COMMENT; Schema: corestatus; Owner: corestatus
--
COMMENT ON COLUMN corestatus.sub_system_module_log.sync_time IS 'дата та час виконання dml команди';
--
-- Name: system_module_log; Type: TABLE; Schema: corestatus; Owner: corestatus
--
CREATE TABLE corestatus.system_module_log (
old_row json,
new_row json,
sync_time timestamp without time zone NOT NULL
);
ALTER TABLE corestatus.system_module_log OWNER TO corestatus;
--
-- Name: TABLE system_module_log; Type: COMMENT; Schema: corestatus; Owner: corestatus
--
COMMENT ON TABLE corestatus.system_module_log IS 'журнал доступності модуля зовнішніх систем';
--
-- Name: COLUMN system_module_log.old_row; Type: COMMENT; Schema: corestatus; Owner: corestatus
--
COMMENT ON COLUMN corestatus.system_module_log.old_row IS 'дані до внесення змін';
--
-- Name: COLUMN system_module_log.new_row; Type: COMMENT; Schema: corestatus; Owner: corestatus
--
COMMENT ON COLUMN corestatus.system_module_log.new_row IS 'дані після внесення змін';
--
-- Name: COLUMN system_module_log.sync_time; Type: COMMENT; Schema: corestatus; Owner: corestatus
--
COMMENT ON COLUMN corestatus.system_module_log.sync_time IS 'час виконання операції';
--
-- Name: v_system_module; Type: VIEW; Schema: corestatus; Owner: corestatus
--
CREATE VIEW corestatus.v_system_module AS
SELECT m.system_name,
m.module_name,
m.start_time,
m.finish_time,
v.system_day,
v.next_system_day,
(((m.state)::integer * v.state))::boolean AS state
FROM corestatus.system_module m,
( SELECT mb2.system_day,
mb2.next_system_day,
(mb2.state)::integer AS state
FROM corestatus.system_module mb2
WHERE ((mb2.system_name = 'b2'::text) AND (mb2.module_name = 'b2'::text))) v;
ALTER TABLE corestatus.v_system_module OWNER TO corestatus;
--
-- Name: VIEW v_system_module; Type: COMMENT; Schema: corestatus; Owner: corestatus
--
COMMENT ON VIEW corestatus.v_system_module IS 'Уявллення доступності модулів облікових систем';
--
-- Name: sub_system_day_settings pk_sub_system_day_settings; Type: CONSTRAINT; Schema: corestatus; Owner: corestatus
--
ALTER TABLE ONLY corestatus.sub_system_day_settings
ADD CONSTRAINT pk_sub_system_day_settings PRIMARY KEY (system_name);
--
-- Name: CONSTRAINT pk_sub_system_day_settings ON sub_system_day_settings; Type: COMMENT; Schema: corestatus; Owner: corestatus
--
COMMENT ON CONSTRAINT pk_sub_system_day_settings ON corestatus.sub_system_day_settings IS 'первинний ключ таблиці sub_system_day_settings';
--
-- Name: sub_system_module pk_sub_system_module; Type: CONSTRAINT; Schema: corestatus; Owner: corestatus
--
ALTER TABLE ONLY corestatus.sub_system_module
ADD CONSTRAINT pk_sub_system_module PRIMARY KEY (system_name, module_name);
--
-- Name: CONSTRAINT pk_sub_system_module ON sub_system_module; Type: COMMENT; Schema: corestatus; Owner: corestatus
--
COMMENT ON CONSTRAINT pk_sub_system_module ON corestatus.sub_system_module IS 'первинний ключ таблиці sub_system_module';
--
-- Name: system_module pk_system_module; Type: CONSTRAINT; Schema: corestatus; Owner: corestatus
--
ALTER TABLE ONLY corestatus.system_module
ADD CONSTRAINT pk_system_module PRIMARY KEY (system_name, module_name);
--
-- Name: system_module on_delete_b2_instead_nothing; Type: RULE; Schema: corestatus; Owner: corestatus
--
CREATE RULE on_delete_b2_instead_nothing AS
ON DELETE TO corestatus.system_module
WHERE (old.module_name = 'b2'::text) DO INSTEAD NOTHING;
--
-- Name: RULE on_delete_b2_instead_nothing ON system_module; Type: COMMENT; Schema: corestatus; Owner: corestatus
--
COMMENT ON RULE on_delete_b2_instead_nothing ON corestatus.system_module IS 'ігнорує видалення запису по модулю b2 з таблиці system_module';
--
-- Name: system_module on_delete_not_b2_also_delete_sub_system_module; Type: RULE; Schema: corestatus; Owner: corestatus
--
CREATE RULE on_delete_not_b2_also_delete_sub_system_module AS
ON DELETE TO corestatus.system_module
WHERE (old.module_name <> 'b2'::text) DO DELETE FROM corestatus.sub_system_module
WHERE ((sub_system_module.system_name = old.system_name) AND (sub_system_module.module_name = old.module_name));
--
-- Name: RULE on_delete_not_b2_also_delete_sub_system_module ON system_module; Type: COMMENT; Schema: corestatus; Owner: corestatus
--
COMMENT ON RULE on_delete_not_b2_also_delete_sub_system_module ON corestatus.system_module IS 'після delete не ключового модуля Б2 в system_module видаляє запис в sub_system_module';
--
-- Name: system_module on_insert_also_insert_sub_system_day_settings; Type: RULE; Schema: corestatus; Owner: corestatus
--
CREATE RULE on_insert_also_insert_sub_system_day_settings AS
ON INSERT TO corestatus.system_module
WHERE (new.module_name = 'b2'::text) DO INSERT INTO corestatus.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;
--
-- Name: RULE on_insert_also_insert_sub_system_day_settings ON system_module; Type: COMMENT; Schema: corestatus; Owner: corestatus
--
COMMENT ON RULE on_insert_also_insert_sub_system_day_settings ON corestatus.system_module IS 'після insert в system_module запису по модулю b2 оновняє дані в sub_system_day_settings';
--
-- Name: system_module on_insert_also_insert_sub_system_module; Type: RULE; Schema: corestatus; Owner: corestatus
--
CREATE RULE on_insert_also_insert_sub_system_module AS
ON INSERT TO corestatus.system_module DO INSERT INTO corestatus.sub_system_module (system_name, module_name, module_status)
VALUES (new.system_name, new.module_name,
CASE
WHEN new.state THEN 'open'::text
ELSE 'close'::text
END) ON CONFLICT(system_name, module_name) DO UPDATE SET module_status =
CASE
WHEN new.state THEN 'open'::text
ELSE 'close'::text
END;
--
-- Name: RULE on_insert_also_insert_sub_system_module ON system_module; Type: COMMENT; Schema: corestatus; Owner: corestatus
--
COMMENT ON RULE on_insert_also_insert_sub_system_module ON corestatus.system_module IS 'після insert в system_module оновняє дані в sub_system_module';
--
-- Name: system_module on_update_also_update_sub_system_day_settings; Type: RULE; Schema: corestatus; Owner: corestatus
--
CREATE RULE on_update_also_update_sub_system_day_settings AS
ON UPDATE TO corestatus.system_module
WHERE (new.module_name = 'b2'::text) DO INSERT INTO corestatus.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;
--
-- Name: RULE on_update_also_update_sub_system_day_settings ON system_module; Type: COMMENT; Schema: corestatus; Owner: corestatus
--
COMMENT ON RULE on_update_also_update_sub_system_day_settings ON corestatus.system_module IS 'після update в system_module запису по модулю b2 оновлює дані sub_system_day_settings';
--
-- Name: system_module on_update_also_update_sub_system_module; Type: RULE; Schema: corestatus; Owner: corestatus
--
CREATE RULE on_update_also_update_sub_system_module AS
ON UPDATE TO corestatus.system_module DO INSERT INTO corestatus.sub_system_module (system_name, module_name, module_status)
VALUES (new.system_name, new.module_name,
CASE
WHEN new.state THEN 'open'::text
ELSE 'close'::text
END) ON CONFLICT(system_name, module_name) DO UPDATE SET module_status =
CASE
WHEN new.state THEN 'open'::text
ELSE 'close'::text
END;
--
-- Name: RULE on_update_also_update_sub_system_module ON system_module; Type: COMMENT; Schema: corestatus; Owner: corestatus
--
COMMENT ON RULE on_update_also_update_sub_system_module ON corestatus.system_module IS 'після update в system_module оновлює дані sub_system_module';
--
-- Name: system_module taiud_system_module; Type: TRIGGER; Schema: corestatus; Owner: corestatus
--
CREATE TRIGGER taiud_system_module AFTER INSERT OR DELETE OR UPDATE ON corestatus.system_module FOR EACH ROW EXECUTE FUNCTION corestatus.save_log();
--
-- Name: TRIGGER taiud_system_module ON system_module; Type: COMMENT; Schema: corestatus; Owner: corestatus
--
COMMENT ON TRIGGER taiud_system_module ON corestatus.system_module IS 'відслідковує зміни по рядках таблиці system_module та ініціює процедуру логування';
--
-- Name: sub_system_day_settings traiud_sub_system_day_settings_do_log; Type: TRIGGER; Schema: corestatus; Owner: corestatus
--
CREATE TRIGGER traiud_sub_system_day_settings_do_log AFTER INSERT OR DELETE OR UPDATE ON corestatus.sub_system_day_settings FOR EACH ROW EXECUTE FUNCTION corestatus.traiud_sub_system_day_settings_do_log();
--
-- Name: TRIGGER traiud_sub_system_day_settings_do_log ON sub_system_day_settings; Type: COMMENT; Schema: corestatus; Owner: corestatus
--
COMMENT ON TRIGGER traiud_sub_system_day_settings_do_log ON corestatus.sub_system_day_settings IS 'відслідковує та фіксує зміни по записам таблиці sub_system_day_settings';
--
-- Name: sub_system_module traiud_sub_system_module_do_log; Type: TRIGGER; Schema: corestatus; Owner: corestatus
--
CREATE TRIGGER traiud_sub_system_module_do_log AFTER INSERT OR DELETE OR UPDATE ON corestatus.sub_system_module FOR EACH ROW EXECUTE FUNCTION corestatus.traiud_sub_system_module_do_log();
--
-- Name: TRIGGER traiud_sub_system_module_do_log ON sub_system_module; Type: COMMENT; Schema: corestatus; Owner: corestatus
--
COMMENT ON TRIGGER traiud_sub_system_module_do_log ON corestatus.sub_system_module IS 'відслідковує зміни в записах таблиці sub_system_module і після цього викликає однойменну функцію ';
--
-- Name: sub_system_module trau_b2_close_do_same_to_all; Type: TRIGGER; Schema: corestatus; Owner: corestatus
--
CREATE TRIGGER trau_b2_close_do_same_to_all AFTER UPDATE ON corestatus.sub_system_module FOR EACH ROW WHEN (((new.module_name = 'b2'::text) AND (new.module_status = 'close'::text))) EXECUTE FUNCTION corestatus.trau_b2_close_do_same_to_all();
--
-- Name: TRIGGER trau_b2_close_do_same_to_all ON sub_system_module; Type: COMMENT; Schema: corestatus; Owner: corestatus
--
COMMENT ON TRIGGER trau_b2_close_do_same_to_all ON corestatus.sub_system_module IS 'відслідковує зміну статусу модуля B2 на close та забезпечує наслідкову дію';
--
-- Name: SCHEMA corestatus; Type: ACL; Schema: -; Owner: corestatus
--
GRANT USAGE ON SCHEMA corestatus TO status_api;
GRANT USAGE ON SCHEMA corestatus TO cache;
--
-- Name: TABLE system_module; Type: ACL; Schema: corestatus; Owner: corestatus
--
GRANT SELECT,DELETE,UPDATE ON TABLE corestatus.system_module TO cache;
--
-- Name: FUNCTION get_info(i_system text, i_module text); Type: ACL; Schema: corestatus; Owner: corestatus
--
GRANT ALL ON FUNCTION corestatus.get_info(i_system text, i_module text) TO status_api;
--
-- Name: TABLE sub_system_day_settings; Type: ACL; Schema: corestatus; Owner: corestatus
--
GRANT SELECT,DELETE,UPDATE ON TABLE corestatus.sub_system_day_settings TO cache;
--
-- Name: FUNCTION get_system_days(i_system text); Type: ACL; Schema: corestatus; Owner: corestatus
--
GRANT ALL ON FUNCTION corestatus.get_system_days(i_system text) TO status_api;
--
-- Name: TABLE sub_system_module; Type: ACL; Schema: corestatus; Owner: corestatus
--
GRANT SELECT,DELETE,UPDATE ON TABLE corestatus.sub_system_module TO cache;
--
-- Name: FUNCTION get_system_module(i_system text, i_module text); Type: ACL; Schema: corestatus; Owner: corestatus
--
GRANT ALL ON FUNCTION corestatus.get_system_module(i_system text, i_module text) TO status_api;
--
-- Name: PROCEDURE sync(i_msg_text text); Type: ACL; Schema: corestatus; Owner: corestatus
--
GRANT ALL ON PROCEDURE corestatus.sync(i_msg_text text) TO status_api;
--
-- Name: PROCEDURE upsert_sub_system_day_settings(i_system_name text, i_current_day date, i_next_day date); Type: ACL; Schema: corestatus; Owner: corestatus
--
GRANT ALL ON PROCEDURE corestatus.upsert_sub_system_day_settings(i_system_name text, i_current_day date, i_next_day date) TO status_api;
--
-- Name: PROCEDURE upsert_sub_system_module(i_system_name text, i_module_name text, i_module_status text); Type: ACL; Schema: corestatus; Owner: corestatus
--
GRANT ALL ON PROCEDURE corestatus.upsert_sub_system_module(i_system_name text, i_module_name text, i_module_status text) TO status_api;
--
-- Name: TABLE sub_system_day_settings_log; Type: ACL; Schema: corestatus; Owner: corestatus
--
GRANT SELECT,DELETE,UPDATE ON TABLE corestatus.sub_system_day_settings_log TO cache;
--
-- Name: TABLE sub_system_module_log; Type: ACL; Schema: corestatus; Owner: corestatus
--
GRANT SELECT,DELETE,UPDATE ON TABLE corestatus.sub_system_module_log TO cache;
--
-- Name: TABLE system_module_log; Type: ACL; Schema: corestatus; Owner: corestatus
--
GRANT SELECT,DELETE,UPDATE ON TABLE corestatus.system_module_log TO cache;
--
-- Name: TABLE v_system_module; Type: ACL; Schema: corestatus; Owner: corestatus
--
GRANT SELECT,DELETE,UPDATE ON TABLE corestatus.v_system_module TO cache;
--
-- PostgreSQL database dump complete
--
* Re: BUG #17782: ERROR: variable not found in subplan target lists
@ 2023-02-14 16:26 Tom Lane <tgl@sss.pgh.pa.us>
parent: Alexander Bluce <amateur@indevlab.com>
0 siblings, 1 reply; 6+ messages in thread
From: Tom Lane @ 2023-02-14 16:26 UTC (permalink / raw)
To: Alexander Bluce <amateur@indevlab.com>; +Cc: Dmytro Hanzhelo <d.hanzhelo@indevlab.com>; pgsql-bugs@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: BUG #17782: ERROR: variable not found in subplan target lists
@ 2023-02-15 05:41 Alexander Bluce <amateur@indevlab.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 6+ messages in thread
From: Alexander Bluce @ 2023-02-15 05:41 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Dmytro Hanzhelo <d.hanzhelo@indevlab.com>; pgsql-bugs <pgsql-bugs@lists.postgresql.org>
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
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2023-02-15 05:41 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2023-02-07 13:16 BUG #17782: ERROR: variable not found in subplan target lists PG Bug reporting form <noreply@postgresql.org>
2023-02-07 13:43 ` Re: BUG #17782: ERROR: variable not found in subplan target lists David G. Johnston <david.g.johnston@gmail.com>
2023-02-08 15:42 ` Re: BUG #17782: ERROR: variable not found in subplan target lists Dmytro Hanzhelo <d.hanzhelo@indevlab.com>
2023-02-14 12:51 ` Re: BUG #17782: ERROR: variable not found in subplan target lists Alexander Bluce <amateur@indevlab.com>
2023-02-14 16:26 ` Re: BUG #17782: ERROR: variable not found in subplan target lists Tom Lane <tgl@sss.pgh.pa.us>
2023-02-15 05:41 ` Re: BUG #17782: ERROR: variable not found in subplan target lists Alexander Bluce <amateur@indevlab.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox