pg_restore depending on user functions

=?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>:

=?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.

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"

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>:

=?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.

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$;

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$;

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$;

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$;
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
);
CREATE CAST (bpd.vclass_prop AS bpd.cclass_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$;

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>:

=?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"

пн, 15 нояб. 2021 г. в 05:06, Tom Lane <tgl@sss.pgh.pa.us>:

=?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?

пн, 15 нояб. 2021 г. в 20:49, Tom Lane <tgl@sss.pgh.pa.us>:

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>:

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"

пн, 15 нояб. 2021 г. в 20:49, Tom Lane <tgl@sss.pgh.pa.us>:

[ redirecting to -bugs ]

=?UTF-8?B?0JTQvNC40YLRgNC40Lkg0JjQstCw0L3QvtCy?= <firstdismay@gmail.com> writes:

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.

Hmm. I do see a potential issue there, though it seems like it should
result in failing to create the views, not the functions. I experimented
with

create function topoint(float8) returns point
as 'select point($1,$1)' language sql;

create cast (float8 as point) with function topoint;

create view vv as select f1, f1::point from float8_tbl;

That results in these pg_depend entries:

regression=# select pg_describe_object(classid,objid,objsubid) as obj,
pg_describe_object(refclassid,refobjid,refobjsubid) as ref, deptype from
pg_depend where ...

obj | ref | deptype
-------------------------------------+------------------------------------+---------
function topoint(double precision) | schema public | n
cast from double precision to point | function topoint(double precision) | n
type vv | view vv | i
type vv[] | type vv | i
view vv | schema public | n
rule _RETURN on view vv | view vv | i
rule _RETURN on view vv | view vv | n
rule _RETURN on view vv | function topoint(double precision) | n
rule _RETURN on view vv | column f1 of table float8_tbl | n
(9 rows)

That is, we made the view depend directly on the function, not on the
cast, which would license pg_dump to dump things in the order function,
view, cast --- which'd fail, since the view is going to be printed with
cast syntax.

So that seems bad, but just because pg_dump could theoretically do
that doesn't mean it will. The object type priority rules built into
pg_dump_sort should normally cause the dump order to be function, cast,
view. It's conceivable that some circular dependency exists in this DB
and pg_dump chooses to break the circularity in a way that causes the
view to be moved ahead of the cast. I'd like to see the details though.

Fixing this "properly" seems like it'd require recording the cast OID in
FuncExpr, RelabelType, and several other node types that can be generated
from cast syntax. Not only would that be invasive and non-back-patchable,
but it'd be really ugly semantically, since at least for optimization
purposes you'd want the cast field to be ignored when deciding if two
expressions are equal(). So I don't think I want to go there. I wonder
if we can fix this by twiddling pg_dump's circularity-breaking rules, or
by forcing it to emit casts immediately after their underlying functions.

Or maybe this has nothing to do with the actual problem. I still want
to see an example before embarking on fixing it.

regards, tom lane

Thanks for the feedback!
It turns out that everything was rolling like a snowball, after the wrong
order of CAST creation

--Line 185:
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
);

--Line 4085:
CREATE TABLE bpd.conception (
id bigint NOT NULL,
name character varying(100) NOT NULL,
"on" boolean NOT NULL,
"desc" character varying(2044),
"default" boolean DEFAULT false NOT NULL,
pos_recycle bigint DEFAULT '-1'::integer NOT NULL,
pos_temp_recycle bigint DEFAULT '-1'::integer NOT NULL,
"timestamp" timestamp without time zone DEFAULT LOCALTIMESTAMP NOT NULL,
group_recycle bigint DEFAULT '-1'::integer NOT NULL,
on_root_create boolean DEFAULT true NOT NULL,
actcatalog integer DEFAULT 1 NOT NULL
);

--Line 4374:
CREATE TABLE bpd.class_prop (
id bigint NOT NULL,
id_prop_inherit bigint DEFAULT '-1'::integer NOT NULL,
id_class bigint DEFAULT 0 NOT NULL,
timestamp_class timestamp without time zone NOT NULL,
id_data_type integer NOT NULL,
name character varying(100) NOT NULL,
"desc" character varying(2044) NOT NULL,
inheritance boolean NOT NULL,
sort integer DEFAULT 0 NOT NULL,
on_inherit boolean NOT NULL,
on_override boolean NOT NULL,
id_prop_type integer NOT NULL,
timestamp_class_inherit timestamp without time zone,
id_conception bigint NOT NULL,
id_prop_definition bigint DEFAULT '-1'::integer NOT NULL,
id_class_definition bigint DEFAULT '-1'::integer NOT NULL,
timestamp_class_definition timestamp without time zone,
tag character varying DEFAULT ''::character varying NOT NULL
);

--Line 4403:
CREATE TABLE bpd.class_prop_enum_val (
id_class_prop bigint NOT NULL,
id_class bigint NOT NULL,
timestamp_class timestamp without time zone NOT NULL,
id_prop_enum bigint NOT NULL,
id_prop_enum_val bigint,
inheritance boolean NOT NULL
);

--Line 4420:
CREATE TABLE bpd.class_prop_link_val (
id_class_prop bigint NOT NULL,
id_class bigint NOT NULL,
timestamp_class timestamp without time zone NOT NULL,
id_entity integer NOT NULL,
id_entity_instance bigint,
inheritance boolean NOT NULL,
id_sub_entity_instance bigint DEFAULT '-1'::integer
);

--Line 4438:
CREATE TABLE bpd.class_prop_obj_val_class (
id bigint NOT NULL,
id_class bigint NOT NULL,
timestamp_class timestamp without time zone NOT NULL,
id_class_prop bigint NOT NULL,
id_class_val bigint,
bquantity_max numeric DEFAULT '-1'::integer NOT NULL,
bquantity_min numeric DEFAULT '-1'::integer NOT NULL,
timestamp_class_val timestamp without time zone,
embed_mode integer DEFAULT 0 NOT NULL,
embed_single boolean DEFAULT true NOT NULL,
embed_class_real_id bigint DEFAULT '-1'::integer NOT NULL,
id_unit_conversion_rule integer DEFAULT '-1'::integer NOT NULL
);

--Line 4484:
CREATE TABLE bpd.class_prop_user_small_val (
id_class_prop bigint NOT NULL,
timestamp_class timestamp without time zone NOT NULL,
val_int integer,
val_boolean boolean,
val_varchar character varying(2044),
val_real real,
val_numeric numeric,
val_date date,
val_time time without time zone,
val_interval interval,
val_timestamp timestamp without time zone,
val_money money,
val_double double precision,
max_val numeric NOT NULL,
round integer NOT NULL,
id_class bigint NOT NULL,
id_data_type integer NOT NULL,
inheritance boolean NOT NULL,
val_bigint bigint,
min_val numeric DEFAULT 0 NOT NULL,
max_on boolean DEFAULT false NOT NULL,
min_on boolean DEFAULT false NOT NULL,
round_on boolean DEFAULT false NOT NULL
);

--Line 4461:
CREATE TABLE bpd.class_prop_user_big_val (
id_class_prop bigint NOT NULL,
timestamp_class timestamp without time zone NOT NULL,
val_int integer,
val_boolean boolean,
val_varchar character varying(2044),
val_real real,
val_numeric numeric,
val_date date,
val_time time without time zone,
val_interval interval,
val_timestamp timestamp without time zone,
val_money money,
val_double double precision,
max_val numeric NOT NULL,
round integer NOT NULL,
id_class bigint NOT NULL,
id_data_type integer NOT NULL,
inheritance boolean NOT NULL,
val_bigint bigint,
min_val numeric DEFAULT 0 NOT NULL,
max_on boolean DEFAULT false NOT NULL,
min_on boolean DEFAULT false NOT NULL,
round_on boolean DEFAULT false NOT NULL
);

--Line 4518:
CREATE TABLE bpd.global_prop_link_class_prop (
id_conception bigint NOT NULL,
id_global_prop bigint NOT NULL,
id_class bigint NOT NULL,
id_class_prop_definition bigint NOT NULL
);

--Line 4533:
CREATE TABLE bpd.prop_enum_val (
id bigint NOT NULL,
id_prop_enum bigint NOT NULL,
id_conception bigint NOT NULL,
val_numeric numeric,
val_varchar character varying(100),
id_object_reference bigint DEFAULT '-1'::integer NOT NULL,
"timestamp" timestamp without time zone NOT NULL,
sort bigint DEFAULT 1 NOT NULL
);

--Line 4552:
CREATE VIEW bpd.vclass_prop AS
SELECT cp.id,
cp.id_class,
cp.timestamp_class,
cp.on_inherit,
cp.inheritance,
cp.id_prop_inherit,
COALESCE(cp.timestamp_class_inherit, '1990-01-01 00:00:00'::timestamp
without time zone) AS timestamp_class_inherit,
cp.id_prop_type,
cp.id_data_type,
cp.name,
cp."desc",
cp.sort,
cp.on_override,
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
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
WHEN 3 THEN (COALESCE(class_val.id, (0)::bigint) > 0)
WHEN 4 THEN (COALESCE(lv.id_entity_instance, (0)::bigint) > 0)
ELSE false
END AS on_val,
COALESCE(
CASE cp.id_prop_type
WHEN 1 THEN
CASE cp.id_data_type
WHEN 1 THEN sv.val_varchar
WHEN 2 THEN (sv.val_int)::character varying
WHEN 3 THEN (sv.val_numeric)::character varying
WHEN 4 THEN (sv.val_real)::character varying
WHEN 5 THEN (sv.val_double)::character varying
WHEN 6 THEN ((sv.val_money)::numeric)::character varying
WHEN 7 THEN 'Текст'::character varying
WHEN 8 THEN 'Изображение'::character varying
WHEN 9 THEN (sv.val_boolean)::character varying
WHEN 10 THEN (sv.val_date)::character varying
WHEN 11 THEN (sv.val_time)::character varying
WHEN 12 THEN (sv.val_interval)::character varying
WHEN 13 THEN (sv.val_timestamp)::character varying
WHEN 14 THEN 'Json'::character varying
WHEN 15 THEN (sv.val_bigint)::character varying
ELSE 'н/д'::character varying
END
WHEN 2 THEN
CASE cp.id_data_type
WHEN 1 THEN pev.val_varchar
WHEN 3 THEN (pev.val_numeric)::character varying
ELSE 'н/д'::character varying
END
WHEN 3 THEN 'Объект'::character varying
WHEN 4 THEN 'Ссылка'::character varying
ELSE 'н/д'::character varying
END, 'н/д'::character varying) AS string_val,
((cp.tableoid)::regclass)::character varying AS tablename,
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 AS ready,
cp.id_conception,
cp.id_class_definition,
cp.timestamp_class_definition,
cp.id_prop_definition,
CASE cp.inheritance
WHEN true THEN COALESCE(cp_inherit.on_override, false)
WHEN false THEN cp.on_override
ELSE NULL::boolean
END AS on_override_prop_inherit,
(lgp.id_global_prop IS NOT NULL) AS on_global,
COALESCE(lgp.id_global_prop, (0)::bigint) AS id_global_prop,
cp.tag
FROM (((((((((ONLY bpd.class_prop cp
LEFT JOIN ONLY bpd.class_prop cp_inherit ON ((cp_inherit.id =
cp.id_prop_inherit)))
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)))
LEFT JOIN bpd.global_prop_link_class_prop lgp ON
((lgp.id_class_prop_definition = cp.id_prop_definition)))
ORDER BY cp.sort, cp.name;

--Line 4690:
CREATE FUNCTION bpd.int_cast_vclass_prop_to_cclass_prop(ivclass_prop
bpd.vclass_prop) RETURNS bpd.cclass_prop
LANGUAGE plpgsql IMMUTABLE PARALLEL SAFE
AS $$
DECLARE
result "bpd"."cclass_prop"%ROWTYPE;
BEGIN
result = ivclass_prop;
RETURN result;
END;
$$;

--Line 4709:
--lost view dependent on CAST (bpd.vclass_prop AS bpd.cclass_prop)
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;

--Line 4723:
--lost view dependent on VIEW bpd.int_class_ext
CREATE VIEW bpd.vclass_ext AS
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 cp_list ON ((cp_list.id = c.id)))
LEFT JOIN bpd.int_class_ready c_ready ON ((c_ready.id = c.id)))
LEFT JOIN bpd.int_class_path c_path ON ((c_path.id = c.id)));

--Line 5848:
--lost function dependent on VIEW bpd.int_class_ext
CREATE FUNCTION bpd.class_act_ext_by_id(iid bigint) RETURNS SETOF
bpd.vclass_ext
LANGUAGE sql STABLE SECURITY DEFINER ROWS 10 PARALLEL SAFE
SET search_path TO 'bpd'
AS $$
SELECT * FROM bpd.vclass_ext WHERE "id" = iid;
$$;

--Line 89334:
CREATE CAST (bpd.vclass_prop AS bpd.cclass_prop) WITH FUNCTION
bpd.int_cast_vclass_prop_to_cclass_prop(bpd.vclass_prop);

----------------------------------------------
Everything else follows the same principle.
I am ready to add what is needed, I did not want to clutter it up too much.

вт, 16 нояб. 2021 г. в 03:07, Tom Lane <tgl@sss.pgh.pa.us>:

=?UTF-8?B?0JTQvNC40YLRgNC40Lkg0JjQstCw0L3QvtCy?= <firstdismay@gmail.com> writes:

It turns out that everything was rolling like a snowball, after the wrong
order of CAST creation

This is missing (at least) bpd.class.

regards, tom lane

--Line 4048:
CREATE TABLE bpd.class (
id bigint NOT NULL,
id_con bigint NOT NULL,
id_group bigint NOT NULL,
id_parent bigint NOT NULL,
id_root bigint NOT NULL,
level integer NOT NULL,
name character varying(100) NOT NULL,
"desc" character varying(2044) DEFAULT 'н/д'::text NOT NULL,
"on" boolean NOT NULL,
on_extensible boolean DEFAULT true NOT NULL,
on_abstraction boolean DEFAULT true NOT NULL,
id_unit_conversion_rule integer NOT NULL,
barcode_manufacturer bigint DEFAULT 0 NOT NULL,
barcode_local bigint DEFAULT 0 NOT NULL,
"timestamp" timestamp without time zone DEFAULT LOCALTIMESTAMP NOT NULL,
on_freeze boolean DEFAULT false NOT NULL,
id_group_root bigint DEFAULT 0 NOT NULL,
timestamp_parent timestamp without time zone DEFAULT LOCALTIMESTAMP(3)
NOT NULL,
id_unit integer DEFAULT '-1'::integer NOT NULL,
timestamp_root timestamp without time zone NOT NULL,
timestamp_child_change timestamp without time zone DEFAULT
LOCALTIMESTAMP(3) NOT NULL,
name_format character varying(255) DEFAULT 'none'::character varying
NOT NULL,
quantity_show boolean DEFAULT true NOT NULL,
path_array bigint[] DEFAULT ARRAY[0] NOT NULL,
CONSTRAINT id CHECK ((id > 0)),
CONSTRAINT id_root CHECK ((id >= 0))
);

вт, 16 нояб. 2021 г. в 21:29, Tom Lane <tgl@sss.pgh.pa.us>:

=?UTF-8?B?0JTQvNC40YLRgNC40Lkg0JjQstCw0L3QvtCy?= <firstdismay@gmail.com> writes:

--Line 4048:
CREATE TABLE bpd.class (

There are still a lot of problems in this example:

* references to nonexistent columns val_text, val_bytea, val_json

* int_class_ext refers to int_class_ready, int_class_path,
bpd.object, which weren't supplied

I figured maybe I didn't need int_class_ext, since it doesn't appear
to be referenced elsewhere. But with the objects I have, pg_dump
doesn't do anything wrong; the output can be loaded just fine.

Please, send a self-contained SQL script that you have actually
tested to be loadable, and which produces a database that
causes pg_dump to do the wrong thing.

regards, tom lane

Ok, I'll do it.
Am I correct in understanding that I need to dump --schema-only in SQL
format and then delete everything, leaving one chain for example? If so, it
will have to be postponed until the weekend.

ср, 17 нояб. 2021 г. в 01:04, Tom Lane <tgl@sss.pgh.pa.us>:

=?UTF-8?B?0JTQvNC40YLRgNC40Lkg0JjQstCw0L3QvtCy?= <firstdismay@gmail.com> writes:

Am I correct in understanding that I need to dump --schema-only in SQL
format and then delete everything, leaving one chain for example? If so, it
will have to be postponed until the weekend.

I don't care how you make the file exactly. But please confirm that
it can be re-loaded and then will reproduce the problem.

regards, tom lane

Ok, I will do that.
Complete data schema. It takes time to create a separate private script,
but I'll do it
bpd.zip 469 Kb

ср, 17 нояб. 2021 г. в 06:47, Tom Lane <tgl@sss.pgh.pa.us>: