Re: pg_restore depending on user functions

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(at)sss(dot)pgh(dot)pa(dot)us>:

> [ redirecting to -bugs ]
>
> =?UTF-8?B?0JTQvNC40YLRgNC40Lkg0JjQstCw0L3QvtCy?= <firstdismay(at)gmail(dot)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
>