pg.ddx.io pgsql-bugs@postgresql.org mailing list archive help / color / mirror / Atom feed From: Alexander Bluce <amateur@indevlab.com>
To: Dmytro Hanzhelo <d.hanzhelo@indevlab.com>
Cc: pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #17782: ERROR: variable not found in subplan target lists
Date: Tue, 14 Feb 2023 10:51:33 -0200 (GMT-02:00)
Message-ID: <1125463649.611902.1676379093613.JavaMail.zimbra@indevlab.com> (raw )
In-Reply-To: <8fceb0e9-de0b-42b9-b12b-e3d6b32332ba@Spark >
References: <17782-23a2f2a4c868a72e@postgresql.org >
<CAKFQuwaCB+u2JOkS+1X6U9yjLKAt1AbUBOrzs5-3nM6BJALdBg@mail.gmail.com >
<8fceb0e9-de0b-42b9-b12b-e3d6b32332ba@Spark >
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
--
view thread (6+ messages) latest in thread
Message-ID: <1125463649.611902.1676379093613.JavaMail.zimbra@indevlab.com>
Permalink: ../1125463649.611902.1676379093613.JavaMail.zimbra@indevlab.com/
Also on: postgresql.org/message-id/1125463649.611902.1676379093613.JavaMail.zimbra@indevlab.com
copy link · copy postgr.es
reply Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: amateur@indevlab.com, d.hanzhelo@indevlab.com, pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #17782: ERROR: variable not found in subplan target lists
In-Reply-To: <1125463649.611902.1676379093613.JavaMail.zimbra@indevlab.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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