Обсуждение: pg_restore depending on user functions
Good day!
Deploying a cluster: from PostgreSQL 12 Windows 10 (EDB) to PostgreSQL 14 Debian 11.
In general, I succeeded, but there are a number of questions:
1. I can determine the dependencies of the custom functions when creating the pg_dump? I did not find anything suitable for SUPPORT, probably this is not the case?
To recreate all of the functionality, I had to run schema recovery twice. (--schema-only)
2. Why is pg_restore trying to set the value of fields of type GENERATED ALWAYS?
COPY mode also does not work with INSERT. I had to do a COPY by manually deleting the fields type GENERATED ALWAYS
Can this behavior be influenced?
Deploying a cluster: from PostgreSQL 12 Windows 10 (EDB) to PostgreSQL 14 Debian 11.
In general, I succeeded, but there are a number of questions:
1. I can determine the dependencies of the custom functions when creating the pg_dump? I did not find anything suitable for SUPPORT, probably this is not the case?
To recreate all of the functionality, I had to run schema recovery twice. (--schema-only)
2. Why is pg_restore trying to set the value of fields of type GENERATED ALWAYS?
COPY mode also does not work with INSERT. I had to do a COPY by manually deleting the fields type GENERATED ALWAYS
Can this behavior be influenced?
=?UTF-8?B?0JTQvNC40YLRgNC40Lkg0JjQstCw0L3QvtCy?= <firstdismay@gmail.com> writes:
> 1. I can determine the dependencies of the custom functions when creating
> the pg_dump? I did not find anything suitable for SUPPORT, probably this is
> not the case?
> To recreate all of the functionality, I had to run schema recovery twice.
> (--schema-only)
Usually this is caused by being careless about search_path assumptions
in your functions ... but with no details, it's impossible to say
anything with certainty.
> 2. Why is pg_restore trying to set the value of fields of type GENERATED
> ALWAYS?
Otherwise it couldn't guarantee that the rows were restored with the same
values of the identity column that they had before. I recall that we
had some bugs with GENERATED ALWAYS in early v10 versions, but when I try
it now, dump/restore of a table with an identity column seems to work
as expected. What minor release are you using?
regards, tom lane
Спасибо за ваш отзыв.
вс, 14 нояб. 2021 г. в 22:31, Tom Lane <tgl@sss.pgh.pa.us>:
Дмитрий Иванов <firstdismay@gmail.com> writes:
> 1. I can determine the dependencies of the custom functions when creating
> the pg_dump? I did not find anything suitable for SUPPORT, probably this is
> not the case?
> To recreate all of the functionality, I had to run schema recovery twice.
> (--schema-only)
Usually this is caused by being careless about search_path assumptions
in your functions ... but with no details, it's impossible to say
anything with certainty.
No, in this case it is not:
Function A using function B.
Сreating function A before function B results in a compilation error.
Function B has no dependencies and is generated without errors. The second run of the circuit creates function A.
If I could specify a function dependency, it would change the order of recovery
> 2. Why is pg_restore trying to set the value of fields of type GENERATED
> ALWAYS?
Otherwise it couldn't guarantee that the rows were restored with the same
values of the identity column that they had before. I recall that we
had some bugs with GENERATED ALWAYS in early v10 versions, but when I try
it now, dump/restore of a table with an identity column seems to work
as expected. What minor release are you using?
PostgreSQL 14.1 (Debian 14.1-1.pgdg110+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 10.2.1-6) 10.2.1 20210110, 64-bit
pg_restote, pg_dump from this build
sudo /usr/lib/postgresql/14/bin/pg_dump --file "/home/dismay/uchet/Uchet.backup" --host "server" --port "5999" --username "back" --no-password --verbose --format=c --quote-all-identifiers --blobs --column-inserts --inserts --clean --create --if-exists --disable-triggers --encoding="UTF8" "Uchet"
sudo /usr/lib/postgresql/14/bin/pg_restore --host "127.0.0.1" --port "5432" --username "back" --no-password --dbname "Uchet" --disable-triggers --schema-only --format=c --verbose "/home/dismay/uchet/Uchet.backup"
sudo /usr/lib/postgresql/14/bin/pg_restore --host "127.0.0.1" --port "5432" --username "back" --no-password --dbname "Uchet" --disable-triggers --format=c --verbose "/home/dismay/uchet/Uchet.backup"
sudo /usr/lib/postgresql/14/bin/pg_restore --host "127.0.0.1" --port "5432" --username "back" --no-password --dbname "Uchet" --disable-triggers --schema-only --format=c --verbose "/home/dismay/uchet/Uchet.backup"
sudo /usr/lib/postgresql/14/bin/pg_restore --host "127.0.0.1" --port "5432" --username "back" --no-password --dbname "Uchet" --disable-triggers --format=c --verbose "/home/dismay/uchet/Uchet.backup"
regards, tom lane
PS: Sorry for my english...
=?UTF-8?B?0JTQvNC40YLRgNC40Lkg0JjQstCw0L3QvtCy?= <firstdismay@gmail.com> writes:
> вс, 14 нояб. 2021 г. в 22:31, Tom Lane <tgl@sss.pgh.pa.us>:
>> Usually this is caused by being careless about search_path assumptions
>> in your functions ... but with no details, it's impossible to say
>> anything with certainty.
> No, in this case it is not:
> Function A using function B.
> Сreating function A before function B results in a compilation error.
> Function B has no dependencies and is generated without errors. The second
> run of the circuit creates function A.
> If I could specify a function dependency, it would change the order of
> recovery
This is not "details", this is an evidence-free assertion. Please show
a concrete example of problematic functions.
>> ... What minor release are you using?
> PostgreSQL 14.1 (Debian 14.1-1.pgdg110+1) on x86_64-pc-linux-gnu, compiled
> by gcc (Debian 10.2.1-6) 10.2.1 20210110, 64-bit
> pg_restote, pg_dump from this build
Ok, so you're up to date all right. But again, you didn't say what
concrete problem you were having with a dump/restore of an identity
column. It works fine for me.
regards, tom lane
Ok, I'll do it.
вс, 14 нояб. 2021 г. в 23:46, Tom Lane <tgl@sss.pgh.pa.us>:
Дмитрий Иванов <firstdismay@gmail.com> writes:
> вс, 14 нояб. 2021 г. в 22:31, Tom Lane <tgl@sss.pgh.pa.us>:
>> Usually this is caused by being careless about search_path assumptions
>> in your functions ... but with no details, it's impossible to say
>> anything with certainty.
> No, in this case it is not:
> Function A using function B.
> Сreating function A before function B results in a compilation error.
> Function B has no dependencies and is generated without errors. The second
> run of the circuit creates function A.
> If I could specify a function dependency, it would change the order of
> recovery
This is not "details", this is an evidence-free assertion. Please show
a concrete example of problematic functions.
Function A (not restore) first action:
CREATE OR REPLACE FUNCTION bpd.class_act_ext_by_id_parent(
iid_parent bigint)
RETURNS SETOF bpd.vclass_ext
LANGUAGE 'plpgsql'
COST 100
STABLE SECURITY DEFINER PARALLEL SAFE
ROWS 1000
SET search_path=bpd
AS $BODY$
DECLARE
class_array BIGINT[]; --Массив объектов
BEGIN
class_array = (SELECT array_agg(c.id) FROM ONLY bpd.class c WHERE c.id_parent = iid_parent);
RETURN QUERY
SELECT
c.id,
c."timestamp",
true AS has_active,
c.timestamp_child_change,
c.id_con,
c.id_group,
c.id_group_root,
c.id_parent,
c.timestamp_parent,
c.id_root,
c.timestamp_root,
c.level,
(c.level = 0) AS is_root,
((c.tableoid)::regclass)::character varying AS tablename,
c.name,
c.name_format,
c.quantity_show,
c."desc",
c."on",
c.on_extensible,
c.on_abstraction,
c.id_unit,
c.id_unit_conversion_rule,
c.barcode_manufacturer,
c.barcode_local,
(EXISTS ( SELECT 1
FROM ONLY bpd.class cc
WHERE (cc.id_parent = c.id))) AS include_child_class,
(EXISTS ( SELECT 1
FROM bpd.object co
WHERE ((co.id_class = c.id) AND (co.timestamp_class = c."timestamp")))) AS include_child_object,
(EXISTS ( SELECT 1
FROM ONLY bpd.class cc
WHERE ((cc.id_parent = c.id) AND (NOT cc.on_abstraction)))) AS include_child_real_class,
(EXISTS ( SELECT 1
FROM ONLY bpd.class cc
WHERE ((cc.id_parent = c.id) AND cc.on_abstraction))) AS include_child_abstract_class,
( SELECT count(1) AS count
FROM ONLY bpd.class cc
WHERE (cc.id_parent = c.id)) AS child_count,
(con.group_recycle = c.id_group_root) AS in_recycle,
c.on_freeze,
cp_list.property_list,
c_ready.ready,
c_path.path
FROM ((((ONLY bpd.class c
LEFT JOIN bpd.conception con ON ((con.id = c.id_con)))
LEFT JOIN "bpd"."int_class_ext_prop_by_id_class_array"(class_array) cp_list ON ((cp_list.id = c.id)))
LEFT JOIN "bpd"."int_class_ready_by_id_class_array"(class_array) c_ready ON ((c_ready.id = c.id)))
LEFT JOIN "bpd"."int_class_path_by_id_class_array"(class_array) c_path ON ((c_path.id = c.id)))
WHERE c.id = ANY(class_array)
ORDER BY "name";
END;
$BODY$;
iid_parent bigint)
RETURNS SETOF bpd.vclass_ext
LANGUAGE 'plpgsql'
COST 100
STABLE SECURITY DEFINER PARALLEL SAFE
ROWS 1000
SET search_path=bpd
AS $BODY$
DECLARE
class_array BIGINT[]; --Массив объектов
BEGIN
class_array = (SELECT array_agg(c.id) FROM ONLY bpd.class c WHERE c.id_parent = iid_parent);
RETURN QUERY
SELECT
c.id,
c."timestamp",
true AS has_active,
c.timestamp_child_change,
c.id_con,
c.id_group,
c.id_group_root,
c.id_parent,
c.timestamp_parent,
c.id_root,
c.timestamp_root,
c.level,
(c.level = 0) AS is_root,
((c.tableoid)::regclass)::character varying AS tablename,
c.name,
c.name_format,
c.quantity_show,
c."desc",
c."on",
c.on_extensible,
c.on_abstraction,
c.id_unit,
c.id_unit_conversion_rule,
c.barcode_manufacturer,
c.barcode_local,
(EXISTS ( SELECT 1
FROM ONLY bpd.class cc
WHERE (cc.id_parent = c.id))) AS include_child_class,
(EXISTS ( SELECT 1
FROM bpd.object co
WHERE ((co.id_class = c.id) AND (co.timestamp_class = c."timestamp")))) AS include_child_object,
(EXISTS ( SELECT 1
FROM ONLY bpd.class cc
WHERE ((cc.id_parent = c.id) AND (NOT cc.on_abstraction)))) AS include_child_real_class,
(EXISTS ( SELECT 1
FROM ONLY bpd.class cc
WHERE ((cc.id_parent = c.id) AND cc.on_abstraction))) AS include_child_abstract_class,
( SELECT count(1) AS count
FROM ONLY bpd.class cc
WHERE (cc.id_parent = c.id)) AS child_count,
(con.group_recycle = c.id_group_root) AS in_recycle,
c.on_freeze,
cp_list.property_list,
c_ready.ready,
c_path.path
FROM ((((ONLY bpd.class c
LEFT JOIN bpd.conception con ON ((con.id = c.id_con)))
LEFT JOIN "bpd"."int_class_ext_prop_by_id_class_array"(class_array) cp_list ON ((cp_list.id = c.id)))
LEFT JOIN "bpd"."int_class_ready_by_id_class_array"(class_array) c_ready ON ((c_ready.id = c.id)))
LEFT JOIN "bpd"."int_class_path_by_id_class_array"(class_array) c_path ON ((c_path.id = c.id)))
WHERE c.id = ANY(class_array)
ORDER BY "name";
END;
$BODY$;
Function B:
CREATE OR REPLACE FUNCTION bpd.int_class_ext_prop_by_id_class_array(
class_array bigint[])
RETURNS SETOF bpd.int_class_ext
LANGUAGE 'sql'
COST 100
STABLE PARALLEL SAFE
ROWS 1000
AS $BODY$
SELECT cp.id_class AS id,
array_agg((cp.*)::bpd.cclass_prop ORDER BY cp.sort) AS property_list
FROM bpd.vclass_prop cp
WHERE (cp.id_class = ANY(class_array))
GROUP BY cp.id_class;
$BODY$;
class_array bigint[])
RETURNS SETOF bpd.int_class_ext
LANGUAGE 'sql'
COST 100
STABLE PARALLEL SAFE
ROWS 1000
AS $BODY$
SELECT cp.id_class AS id,
array_agg((cp.*)::bpd.cclass_prop ORDER BY cp.sort) AS property_list
FROM bpd.vclass_prop cp
WHERE (cp.id_class = ANY(class_array))
GROUP BY cp.id_class;
$BODY$;
CREATE OR REPLACE FUNCTION bpd.int_class_ready_by_id_class_array(
class_array bigint[])
RETURNS SETOF bpd.int_class_ready
LANGUAGE 'sql'
COST 100
STABLE PARALLEL SAFE
ROWS 1000
AS $BODY$
SELECT
c.id,
CASE c.on_abstraction
WHEN false THEN
CASE bpd.int_class_format_check(c.id)
WHEN true THEN
CASE (count(cp.id) > 0)
WHEN true THEN bool_and(
CASE cp.id_prop_type
WHEN 1 THEN (
CASE cp.id_data_type
WHEN 1 THEN (sv.val_varchar IS NOT NULL)
WHEN 2 THEN (sv.val_int IS NOT NULL)
WHEN 3 THEN (sv.val_numeric IS NOT NULL)
WHEN 4 THEN (sv.val_real IS NOT NULL)
WHEN 5 THEN (sv.val_double IS NOT NULL)
WHEN 6 THEN (sv.val_money IS NOT NULL)
WHEN 7 THEN (bv.val_text IS NOT NULL)
WHEN 8 THEN (bv.val_bytea IS NOT NULL)
WHEN 9 THEN (sv.val_boolean IS NOT NULL)
WHEN 10 THEN (sv.val_date IS NOT NULL)
WHEN 11 THEN (sv.val_time IS NOT NULL)
WHEN 12 THEN (sv.val_interval IS NOT NULL)
WHEN 13 THEN (sv.val_timestamp IS NOT NULL)
WHEN 14 THEN (bv.val_json IS NOT NULL)
WHEN 15 THEN (sv.val_bigint IS NOT NULL)
ELSE false
END OR cp.on_override)
WHEN 2 THEN (
CASE cp.id_data_type
WHEN 1 THEN (pev.val_varchar IS NOT NULL)
WHEN 3 THEN (pev.val_numeric IS NOT NULL)
ELSE false
END OR (cp.on_override AND (COALESCE(ev.id_prop_enum, (0)::bigint) > 0)))
WHEN 3 THEN (COALESCE(class_val.id, (0)::bigint) > 0)
WHEN 4 THEN ((COALESCE(lv.id_entity_instance, (0)::bigint) > 0) OR (cp.on_override AND (lv.id_entity IS NOT NULL)))
ELSE false
END)
ELSE true
END
ELSE false
END
ELSE false
END AS ready
FROM ONLY bpd.class c
LEFT JOIN ONLY bpd.class_prop cp ON (c.id = cp.id_class)
LEFT JOIN ONLY bpd.class_prop_user_small_val sv ON (sv.id_class_prop = cp.id)
LEFT JOIN ONLY bpd.class_prop_user_big_val bv ON (bv.id_class_prop = cp.id)
LEFT JOIN ONLY bpd.class_prop_enum_val ev ON (ev.id_class_prop = cp.id)
LEFT JOIN ONLY bpd.prop_enum_val pev ON (pev.id = ev.id_prop_enum_val)
LEFT JOIN ONLY bpd.class_prop_link_val lv ON (lv.id_class_prop = cp.id)
LEFT JOIN ONLY bpd.class_prop_obj_val_class ov ON (ov.id_class_prop = cp.id)
LEFT JOIN ONLY bpd.class class_val ON (class_val.id = ov.id_class_val)
WHERE (c.id = ANY(class_array))
GROUP BY c.id;
$BODY$;
class_array bigint[])
RETURNS SETOF bpd.int_class_ready
LANGUAGE 'sql'
COST 100
STABLE PARALLEL SAFE
ROWS 1000
AS $BODY$
SELECT
c.id,
CASE c.on_abstraction
WHEN false THEN
CASE bpd.int_class_format_check(c.id)
WHEN true THEN
CASE (count(cp.id) > 0)
WHEN true THEN bool_and(
CASE cp.id_prop_type
WHEN 1 THEN (
CASE cp.id_data_type
WHEN 1 THEN (sv.val_varchar IS NOT NULL)
WHEN 2 THEN (sv.val_int IS NOT NULL)
WHEN 3 THEN (sv.val_numeric IS NOT NULL)
WHEN 4 THEN (sv.val_real IS NOT NULL)
WHEN 5 THEN (sv.val_double IS NOT NULL)
WHEN 6 THEN (sv.val_money IS NOT NULL)
WHEN 7 THEN (bv.val_text IS NOT NULL)
WHEN 8 THEN (bv.val_bytea IS NOT NULL)
WHEN 9 THEN (sv.val_boolean IS NOT NULL)
WHEN 10 THEN (sv.val_date IS NOT NULL)
WHEN 11 THEN (sv.val_time IS NOT NULL)
WHEN 12 THEN (sv.val_interval IS NOT NULL)
WHEN 13 THEN (sv.val_timestamp IS NOT NULL)
WHEN 14 THEN (bv.val_json IS NOT NULL)
WHEN 15 THEN (sv.val_bigint IS NOT NULL)
ELSE false
END OR cp.on_override)
WHEN 2 THEN (
CASE cp.id_data_type
WHEN 1 THEN (pev.val_varchar IS NOT NULL)
WHEN 3 THEN (pev.val_numeric IS NOT NULL)
ELSE false
END OR (cp.on_override AND (COALESCE(ev.id_prop_enum, (0)::bigint) > 0)))
WHEN 3 THEN (COALESCE(class_val.id, (0)::bigint) > 0)
WHEN 4 THEN ((COALESCE(lv.id_entity_instance, (0)::bigint) > 0) OR (cp.on_override AND (lv.id_entity IS NOT NULL)))
ELSE false
END)
ELSE true
END
ELSE false
END
ELSE false
END AS ready
FROM ONLY bpd.class c
LEFT JOIN ONLY bpd.class_prop cp ON (c.id = cp.id_class)
LEFT JOIN ONLY bpd.class_prop_user_small_val sv ON (sv.id_class_prop = cp.id)
LEFT JOIN ONLY bpd.class_prop_user_big_val bv ON (bv.id_class_prop = cp.id)
LEFT JOIN ONLY bpd.class_prop_enum_val ev ON (ev.id_class_prop = cp.id)
LEFT JOIN ONLY bpd.prop_enum_val pev ON (pev.id = ev.id_prop_enum_val)
LEFT JOIN ONLY bpd.class_prop_link_val lv ON (lv.id_class_prop = cp.id)
LEFT JOIN ONLY bpd.class_prop_obj_val_class ov ON (ov.id_class_prop = cp.id)
LEFT JOIN ONLY bpd.class class_val ON (class_val.id = ov.id_class_val)
WHERE (c.id = ANY(class_array))
GROUP BY c.id;
$BODY$;
CREATE OR REPLACE FUNCTION bpd.int_class_path_by_id_class_array(
class_array bigint[])
RETURNS SETOF bpd.int_class_path
LANGUAGE 'plpgsql'
COST 100
STABLE PARALLEL SAFE
ROWS 1000
SET search_path=bpd
AS $BODY$
DECLARE
BEGIN
RETURN QUERY WITH RECURSIVE rgroup(id_path, id, id_parent, level, path, spath, cycle) AS (
SELECT rg.id,
rg.id,
rg.id_parent,
0,
ARRAY[rg.id] AS "array",
concat(rg.name) AS concat,
false AS bool
FROM bpd."group" rg
WHERE rg.id IN (SELECT ac.id_group FROM ONLY bpd.class ac WHERE ac.id = ANY(class_array))
UNION ALL
SELECT rgroup_1.id_path,
rgc.id,
rgc.id_parent,
(rgroup_1.level + 1),
(ARRAY[rgc.id] || rgroup_1.path),
concat(rgc.name, $$\$$, rgroup_1.spath) AS concat,
(rgc.id = ANY (rgroup_1.path))
FROM (bpd."group" rgc
JOIN rgroup rgroup_1 ON ((rgroup_1.id_parent = rgc.id)))
WHERE (NOT rgroup_1.cycle)
),
grouppath(id_path, spath) AS (
SELECT DISTINCT rg.id_path,
first_value(rg.spath) OVER (PARTITION BY rg.id_path ORDER BY rg.level DESC) AS first_value
FROM rgroup rg
),
rclass(id_path, id, id_parent, level, path, spath, cycle) AS (
SELECT rc.id,
rc.id,
rc.id_parent,
0,
ARRAY[rc.id] AS "array",
concat(rc.name) AS concat,
false AS bool
FROM ONLY bpd.class rc
WHERE (rc.id = ANY(class_array))
UNION ALL
SELECT rclass_1.id_path,
rcc.id,
rcc.id_parent,
(rclass_1.level + 1),
(ARRAY[rcc.id] || rclass_1.path),
concat(rcc.name, $$\$$, rclass_1.spath) AS concat,
(rcc.id = ANY (rclass_1.path))
FROM (ONLY bpd.class rcc
JOIN rclass rclass_1 ON ((rclass_1.id_parent = rcc.id)))
WHERE (NOT rclass_1.cycle)
), classpath(id_path, spath) AS (
SELECT DISTINCT rc.id_path,
first_value(rc.spath) OVER (PARTITION BY rc.id_path ORDER BY rc.level DESC) AS first_value
FROM rclass rc
)
SELECT c.id,
concat(COALESCE(gp.spath, ''::text), '\\', COALESCE(cp.spath, ''::text)) AS path
FROM ((ONLY bpd.class c
LEFT JOIN grouppath gp ON ((gp.id_path = c.id_group)))
LEFT JOIN classpath cp ON ((cp.id_path = c.id)))
WHERE (c.id = ANY(class_array));
END;
$BODY$;
class_array bigint[])
RETURNS SETOF bpd.int_class_path
LANGUAGE 'plpgsql'
COST 100
STABLE PARALLEL SAFE
ROWS 1000
SET search_path=bpd
AS $BODY$
DECLARE
BEGIN
RETURN QUERY WITH RECURSIVE rgroup(id_path, id, id_parent, level, path, spath, cycle) AS (
SELECT rg.id,
rg.id,
rg.id_parent,
0,
ARRAY[rg.id] AS "array",
concat(rg.name) AS concat,
false AS bool
FROM bpd."group" rg
WHERE rg.id IN (SELECT ac.id_group FROM ONLY bpd.class ac WHERE ac.id = ANY(class_array))
UNION ALL
SELECT rgroup_1.id_path,
rgc.id,
rgc.id_parent,
(rgroup_1.level + 1),
(ARRAY[rgc.id] || rgroup_1.path),
concat(rgc.name, $$\$$, rgroup_1.spath) AS concat,
(rgc.id = ANY (rgroup_1.path))
FROM (bpd."group" rgc
JOIN rgroup rgroup_1 ON ((rgroup_1.id_parent = rgc.id)))
WHERE (NOT rgroup_1.cycle)
),
grouppath(id_path, spath) AS (
SELECT DISTINCT rg.id_path,
first_value(rg.spath) OVER (PARTITION BY rg.id_path ORDER BY rg.level DESC) AS first_value
FROM rgroup rg
),
rclass(id_path, id, id_parent, level, path, spath, cycle) AS (
SELECT rc.id,
rc.id,
rc.id_parent,
0,
ARRAY[rc.id] AS "array",
concat(rc.name) AS concat,
false AS bool
FROM ONLY bpd.class rc
WHERE (rc.id = ANY(class_array))
UNION ALL
SELECT rclass_1.id_path,
rcc.id,
rcc.id_parent,
(rclass_1.level + 1),
(ARRAY[rcc.id] || rclass_1.path),
concat(rcc.name, $$\$$, rclass_1.spath) AS concat,
(rcc.id = ANY (rclass_1.path))
FROM (ONLY bpd.class rcc
JOIN rclass rclass_1 ON ((rclass_1.id_parent = rcc.id)))
WHERE (NOT rclass_1.cycle)
), classpath(id_path, spath) AS (
SELECT DISTINCT rc.id_path,
first_value(rc.spath) OVER (PARTITION BY rc.id_path ORDER BY rc.level DESC) AS first_value
FROM rclass rc
)
SELECT c.id,
concat(COALESCE(gp.spath, ''::text), '\\', COALESCE(cp.spath, ''::text)) AS path
FROM ((ONLY bpd.class c
LEFT JOIN grouppath gp ON ((gp.id_path = c.id_group)))
LEFT JOIN classpath cp ON ((cp.id_path = c.id)))
WHERE (c.id = ANY(class_array));
END;
$BODY$;
for compatibility with NPGSQL I had to create a mirrored composite type:
CREATE TYPE bpd.cclass_prop AS
(
id bigint,
id_class bigint,
timestamp_class timestamp without time zone,
on_inherit boolean,
inheritance boolean,
id_prop_inherit bigint,
timestamp_class_inherit timestamp without time zone,
id_prop_type integer,
id_data_type integer,
name character varying,
"desc" character varying,
sort integer,
on_override boolean,
on_val boolean,
string_val character varying,
tablename character varying,
ready boolean,
id_conception bigint,
id_class_definition bigint,
timestamp_class_definition timestamp without time zone,
id_prop_definition bigint,
on_override_prop_inherit boolean,
on_global boolean,
id_global_prop bigint,
tag character varying
);
(
id bigint,
id_class bigint,
timestamp_class timestamp without time zone,
on_inherit boolean,
inheritance boolean,
id_prop_inherit bigint,
timestamp_class_inherit timestamp without time zone,
id_prop_type integer,
id_data_type integer,
name character varying,
"desc" character varying,
sort integer,
on_override boolean,
on_val boolean,
string_val character varying,
tablename character varying,
ready boolean,
id_conception bigint,
id_class_definition bigint,
timestamp_class_definition timestamp without time zone,
id_prop_definition bigint,
on_override_prop_inherit boolean,
on_global boolean,
id_global_prop bigint,
tag character varying
);
CREATE CAST (bpd.vclass_prop AS bpd.cclass_prop)
WITH FUNCTION int_cast_vclass_prop_to_cclass_prop(ivclass_prop bpd.vclass_prop);
WITH FUNCTION int_cast_vclass_prop_to_cclass_prop(ivclass_prop bpd.vclass_prop);
CREATE OR REPLACE FUNCTION bpd.int_cast_vclass_prop_to_cclass_prop(
ivclass_prop bpd.vclass_prop)
RETURNS bpd.cclass_prop
LANGUAGE 'plpgsql'
COST 100
IMMUTABLE PARALLEL SAFE
AS $BODY$
DECLARE
result "bpd"."cclass_prop"%ROWTYPE;
BEGIN
result = ivclass_prop;
RETURN result;
END;
$BODY$;
ivclass_prop bpd.vclass_prop)
RETURNS bpd.cclass_prop
LANGUAGE 'plpgsql'
COST 100
IMMUTABLE PARALLEL SAFE
AS $BODY$
DECLARE
result "bpd"."cclass_prop"%ROWTYPE;
BEGIN
result = ivclass_prop;
RETURN result;
END;
$BODY$;
>> ... What minor release are you using?
> PostgreSQL 14.1 (Debian 14.1-1.pgdg110+1) on x86_64-pc-linux-gnu, compiled
> by gcc (Debian 10.2.1-6) 10.2.1 20210110, 64-bit
> pg_restote, pg_dump from this build
Ok, so you're up to date all right. But again, you didn't say what
concrete problem you were having with a dump/restore of an identity
column. It works fine for me.
regards, tom lane
Ok, I'll do it.
> Сreating function A before function B results in a compilation error.
On my part, this is an incorrect assumption. There are no compilation errors here. she just didn't recover from the first pass.
вс, 14 нояб. 2021 г. в 23:46, Tom Lane <tgl@sss.pgh.pa.us>:
Дмитрий Иванов <firstdismay@gmail.com> writes:
> вс, 14 нояб. 2021 г. в 22:31, Tom Lane <tgl@sss.pgh.pa.us>:
>> Usually this is caused by being careless about search_path assumptions
>> in your functions ... but with no details, it's impossible to say
>> anything with certainty.
> No, in this case it is not:
> Function A using function B.
> Сreating function A before function B results in a compilation error.
> Function B has no dependencies and is generated without errors. The second
> run of the circuit creates function A.
> If I could specify a function dependency, it would change the order of
> recovery
This is not "details", this is an evidence-free assertion. Please show
a concrete example of problematic functions.
>> ... What minor release are you using?
> PostgreSQL 14.1 (Debian 14.1-1.pgdg110+1) on x86_64-pc-linux-gnu, compiled
> by gcc (Debian 10.2.1-6) 10.2.1 20210110, 64-bit
> pg_restote, pg_dump from this build
Ok, so you're up to date all right. But again, you didn't say what
concrete problem you were having with a dump/restore of an identity
column. It works fine for me.
regards, tom lane
=?UTF-8?B?0JTQvNC40YLRgNC40Lkg0JjQstCw0L3QvtCy?= <firstdismay@gmail.com> writes:
> вс, 14 нояб. 2021 г. в 23:46, Tom Lane <tgl@sss.pgh.pa.us>:
>> This is not "details", this is an evidence-free assertion. Please show
>> a concrete example of problematic functions.
> Ok, I'll do it.
> [ example ]
This is pretty far from being a self-contained example; I had to guess
at the definitions of several types that you didn't provide. Having
done so, though, the set of functions and types seem to dump and restore
just fine. So I still don't see any problem here.
Possibly worth noting is that pg_dump is careful to include this in
its output script:
SET check_function_bodies = false;
Without that, I can believe that you'd have some trouble restoring
these functions, since pg_dump doesn't know anything about the
cross-references appearing in the function bodies. But with that,
there don't appear to be any troublesome cross-references here.
regards, tom lane
Thanks for the feedback!
Is it ok to attach a 5MB data schema or is it not possible? Copy one by one to a letter?
It would be nice if in the end I could rebuild the base without hindrance. Thank you in advance.
Restore Windows 10 PostgreSQL 12.9, compiled by Visual C++ build 1914, 64-bit -> Debian 11 PostgreSQL 14.1 (Debian 14.1-1.pgdg110+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 10.2.1-6) 10.2.1 20210110, 64-bit
sudo /usr/lib/postgresql/14/bin/pg_dump --file "/home/dismay/uchet/Uchet.backup" --host "server" --port "5999" --username "back" --no-password --verbose --format=c --quote-all-identifiers --blobs --column-inserts --inserts --create --disable-triggers --encoding="UTF8" "Uchet"
no error/
Drop base
CREATE DATABASE "Uchet"
WITH
OWNER = funcowner
TEMPLATE = template0
ENCODING = 'UTF8'
LC_COLLATE = 'ru_RU.UTF-8'
LC_CTYPE = 'ru_RU.UTF-8'
CONNECTION LIMIT = -1;
SET check_function_bodies = false;
sudo /usr/lib/postgresql/14/bin/pg_restore --host "127.0.0.1" --port "5432" --username "back" --no-password --dbname "Uchet" --disable-triggers --format=c --create --verbose "/home/dismay/uchet/Uchet.backup"
error:
"bpd"."class_prop_user_smal_val" error syntax money
"bpd"."class_prop_user_small_val_snapshot" error syntax money
"bpd"."object error insert column is_inside"
ignored error 26603
visible differences base source base receiver
count function 711 649
count views 125 117
count rows bpd.object 25769 0
count rows bpd.class_prop_user_small_val 28295 28182
count rows bpd.class_prop_user_small_val_snapshot 33550 33491
lost views:
"int_class_ext"
"int_doc_category_ext"
"int_doc_file_ext"
"int_doc_link_ext"
"int_object_ext"
"vclass_ext"
"vdocument_ext"
"vobject_general_ext"
lost function:
"class_act_base_ext_allowed_by_id_group"
"class_act_ext_allowed_rl1_by_id_position"
"class_act_ext_by_id"
"class_act_ext_by_id_conception_msk_name"
"class_act_ext_by_id_global_prop"
"class_act_ext_by_id_group"
"class_act_ext_by_id_group_msk_name"
"class_act_ext_by_id_parent"
"class_act_ext_by_id_parent_msk_name"
"class_act_ext_by_id_parent_strict_name"
"class_act_ext_by_msk_global_prop"
"class_act_ext_by_msk_global_prop_from_class"
"class_act_ext_by_msk_global_prop_from_group"
"class_act_real_ext_allowed_by_id_group"
"class_act_real_ext_by_id_group"
"class_snapshot_base_ext_by_id_position"
"class_snapshot_ext_by_id"
"class_snapshot_ext_by_id_class"
"class_snapshot_ext_by_id_parent_snapshot"
"class_snapshot_ext_on_object_by_id_parent_snapshot_parent_pos"
"document_ext_by_id"
"document_ext_by_id_category"
"document_ext_by_id_class_prop"
"document_ext_by_id_conception"
"document_ext_by_id_group"
"document_ext_by_id_object"
"document_ext_by_id_object_prop"
"document_ext_by_id_parent"
"document_ext_by_id_pos_temp"
"document_ext_by_id_pos_temp_prop"
"document_ext_by_id_position"
"document_ext_by_id_position_prop"
"document_ext_by_id_user"
"document_ext_by_msk_name_from_category"
"document_ext_by_msk_name_from_conception"
"int_class_ext_prop_by_id_class_array"
"int_object_ext_prop_by_id_object_array"
"object_carrier_ext_by_object_class_full"
"object_ext_by_id"
"object_ext_by_id_class_act"
"object_ext_by_id_class_full"
"object_ext_by_id_class_id_pos"
"object_ext_by_id_class_root"
"object_ext_by_id_class_snapshot"
"object_ext_by_id_class_snapshot_id_pos"
"object_ext_by_id_group"
"object_ext_by_id_group_root"
"object_ext_by_id_object_carrier"
"object_ext_by_id_position"
"object_ext_by_id_position_full"
"object_ext_by_id_prop_data_type"
"object_ext_by_id_prop_enum"
"object_ext_by_id_prop_enum_val"
"object_ext_by_id_unit_conversion_rule"
"object_ext_by_link_object"
"object_ext_by_msk_global_prop"
"object_ext_by_msk_global_prop_from_pos"
"object_ext_by_name"
"object_ext_by_name_id_pos"
"object_ext_carrier_by_msk_global_prop"
"object_object_prop_by_id_object_carrier_ext"
"object_object_prop_by_id_position_carrier_ext"
sudo /usr/lib/postgresql/14/bin/pg_dump --file "/home/dismay/uchet/Uchet.backup" --host "server" --port "5999" --username "back" --no-password --verbose --format=c --quote-all-identifiers --blobs --column-inserts --inserts --create --disable-triggers --encoding="UTF8" "Uchet"
no error/
Drop base
CREATE DATABASE "Uchet"
WITH
OWNER = funcowner
TEMPLATE = template0
ENCODING = 'UTF8'
LC_COLLATE = 'ru_RU.UTF-8'
LC_CTYPE = 'ru_RU.UTF-8'
CONNECTION LIMIT = -1;
SET check_function_bodies = false;
sudo /usr/lib/postgresql/14/bin/pg_restore --host "127.0.0.1" --port "5432" --username "back" --no-password --dbname "Uchet" --disable-triggers --format=c --create --verbose "/home/dismay/uchet/Uchet.backup"
error:
"bpd"."class_prop_user_smal_val" error syntax money
"bpd"."class_prop_user_small_val_snapshot" error syntax money
"bpd"."object error insert column is_inside"
ignored error 26603
visible differences base source base receiver
count function 711 649
count views 125 117
count rows bpd.object 25769 0
count rows bpd.class_prop_user_small_val 28295 28182
count rows bpd.class_prop_user_small_val_snapshot 33550 33491
lost views:
"int_class_ext"
"int_doc_category_ext"
"int_doc_file_ext"
"int_doc_link_ext"
"int_object_ext"
"vclass_ext"
"vdocument_ext"
"vobject_general_ext"
lost function:
"class_act_base_ext_allowed_by_id_group"
"class_act_ext_allowed_rl1_by_id_position"
"class_act_ext_by_id"
"class_act_ext_by_id_conception_msk_name"
"class_act_ext_by_id_global_prop"
"class_act_ext_by_id_group"
"class_act_ext_by_id_group_msk_name"
"class_act_ext_by_id_parent"
"class_act_ext_by_id_parent_msk_name"
"class_act_ext_by_id_parent_strict_name"
"class_act_ext_by_msk_global_prop"
"class_act_ext_by_msk_global_prop_from_class"
"class_act_ext_by_msk_global_prop_from_group"
"class_act_real_ext_allowed_by_id_group"
"class_act_real_ext_by_id_group"
"class_snapshot_base_ext_by_id_position"
"class_snapshot_ext_by_id"
"class_snapshot_ext_by_id_class"
"class_snapshot_ext_by_id_parent_snapshot"
"class_snapshot_ext_on_object_by_id_parent_snapshot_parent_pos"
"document_ext_by_id"
"document_ext_by_id_category"
"document_ext_by_id_class_prop"
"document_ext_by_id_conception"
"document_ext_by_id_group"
"document_ext_by_id_object"
"document_ext_by_id_object_prop"
"document_ext_by_id_parent"
"document_ext_by_id_pos_temp"
"document_ext_by_id_pos_temp_prop"
"document_ext_by_id_position"
"document_ext_by_id_position_prop"
"document_ext_by_id_user"
"document_ext_by_msk_name_from_category"
"document_ext_by_msk_name_from_conception"
"int_class_ext_prop_by_id_class_array"
"int_object_ext_prop_by_id_object_array"
"object_carrier_ext_by_object_class_full"
"object_ext_by_id"
"object_ext_by_id_class_act"
"object_ext_by_id_class_full"
"object_ext_by_id_class_id_pos"
"object_ext_by_id_class_root"
"object_ext_by_id_class_snapshot"
"object_ext_by_id_class_snapshot_id_pos"
"object_ext_by_id_group"
"object_ext_by_id_group_root"
"object_ext_by_id_object_carrier"
"object_ext_by_id_position"
"object_ext_by_id_position_full"
"object_ext_by_id_prop_data_type"
"object_ext_by_id_prop_enum"
"object_ext_by_id_prop_enum_val"
"object_ext_by_id_unit_conversion_rule"
"object_ext_by_link_object"
"object_ext_by_msk_global_prop"
"object_ext_by_msk_global_prop_from_pos"
"object_ext_by_name"
"object_ext_by_name_id_pos"
"object_ext_carrier_by_msk_global_prop"
"object_object_prop_by_id_object_carrier_ext"
"object_object_prop_by_id_position_carrier_ext"
пн, 15 нояб. 2021 г. в 05:06, Tom Lane <tgl@sss.pgh.pa.us>:
Дмитрий Иванов <firstdismay@gmail.com> writes:
> вс, 14 нояб. 2021 г. в 23:46, Tom Lane <tgl@sss.pgh.pa.us>:
>> This is not "details", this is an evidence-free assertion. Please show
>> a concrete example of problematic functions.
> Ok, I'll do it.
> [ example ]
This is pretty far from being a self-contained example; I had to guess
at the definitions of several types that you didn't provide. Having
done so, though, the set of functions and types seem to dump and restore
just fine. So I still don't see any problem here.
Possibly worth noting is that pg_dump is careful to include this in
its output script:
SET check_function_bodies = false;
Without that, I can believe that you'd have some trouble restoring
these functions, since pg_dump doesn't know anything about the
cross-references appearing in the function bodies. But with that,
there don't appear to be any troublesome cross-references here.
regards, tom lane
=?UTF-8?B?0JTQvNC40YLRgNC40Lkg0JjQstCw0L3QvtCy?= <firstdismay@gmail.com> writes:
> Is it ok to attach a 5MB data schema or is it not possible? Copy one by one
> to a letter?
Is it smaller if you omit the data (-s switch)? Shouldn't be relevant
here.
regards, tom lane
Yes, it is.
I did so (--schema-only). Removing unnecessary definitions.
That is, you do not need to attach files?
That is, you do not need to attach files?
пн, 15 нояб. 2021 г. в 20:49, Tom Lane <tgl@sss.pgh.pa.us>:
Дмитрий Иванов <firstdismay@gmail.com> writes:
> Is it ok to attach a 5MB data schema or is it not possible? Copy one by one
> to a letter?
Is it smaller if you omit the data (-s switch)? Shouldn't be relevant
here.
regards, tom lane
Yes, it is.
I'll leave behind the lost features, views, table definitions, and dependent objects. It will take some time.
thanks, for the help.
пн, 15 нояб. 2021 г. в 20:49, Tom Lane <tgl@sss.pgh.pa.us>:
Дмитрий Иванов <firstdismay@gmail.com> writes:
> Is it ok to attach a 5MB data schema or is it not possible? Copy one by one
> to a letter?
Is it smaller if you omit the data (-s switch)? Shouldn't be relevant
here.
regards, tom lane
Yes, it is.
I continue to extract data as promised, but I think I see some pattern.
"chicken or egg"
To work with the NPGSQL library, I created a cast. They are created after the views in which I use them.
Here is the order
279: CREATE TYPE bpd.cclass_prop
4646: CREATE VIEW bpd.vclass_prop
4784: CREATE FUNCTION bpd.int_cast_vclass_prop_to_cclass_prop(ivclass_prop bpd.vclass_prop) RETURNS bpd.cclass_prop
4803: CREATE VIEW bpd.int_class_ext AS
SELECT cp.id_class AS id,
array_agg((cp.*)::bpd.cclass_prop ORDER BY cp.sort) AS property_list
FROM bpd.vclass_prop cp
GROUP BY cp.id_class;
89428: CREATE CAST (bpd.vclass_prop AS bpd.cclass_prop) WITH FUNCTION bpd.int_cast_vclass_prop_to_cclass_prop(bpd.vclass_prop);
lost views:
"int_class_ext"
"int_doc_category_ext"
"int_doc_file_ext"
"int_doc_link_ext"
"int_object_ext"
"vclass_ext"
"vdocument_ext"
"vobject_general_ext"
"chicken or egg"
To work with the NPGSQL library, I created a cast. They are created after the views in which I use them.
Here is the order
279: CREATE TYPE bpd.cclass_prop
4646: CREATE VIEW bpd.vclass_prop
4784: CREATE FUNCTION bpd.int_cast_vclass_prop_to_cclass_prop(ivclass_prop bpd.vclass_prop) RETURNS bpd.cclass_prop
4803: CREATE VIEW bpd.int_class_ext AS
SELECT cp.id_class AS id,
array_agg((cp.*)::bpd.cclass_prop ORDER BY cp.sort) AS property_list
FROM bpd.vclass_prop cp
GROUP BY cp.id_class;
89428: CREATE CAST (bpd.vclass_prop AS bpd.cclass_prop) WITH FUNCTION bpd.int_cast_vclass_prop_to_cclass_prop(bpd.vclass_prop);
lost views:
"int_class_ext"
"int_doc_category_ext"
"int_doc_file_ext"
"int_doc_link_ext"
"int_object_ext"
"vclass_ext"
"vdocument_ext"
"vobject_general_ext"
пн, 15 нояб. 2021 г. в 20:49, Tom Lane <tgl@sss.pgh.pa.us>:
Дмитрий Иванов <firstdismay@gmail.com> writes:
> Is it ok to attach a 5MB data schema or is it not possible? Copy one by one
> to a letter?
Is it smaller if you omit the data (-s switch)? Shouldn't be relevant
here.
regards, tom lane