Re: pg_restore depending on user functions

Ok, I'll do it.

вс, 14 нояб. 2021 г. в 23:46, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>:

> =?UTF-8?B?0JTQvNC40YLRgNC40Lkg0JjQstCw0L3QvtCy?= <firstdismay(at)gmail(dot)com>
> writes:
> > вс, 14 нояб. 2021 г. в 22:31, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)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$;

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