Обсуждение: Re: pg_restore depending on user functions

Поиск
Список
Период
Сортировка

Re: pg_restore depending on user functions

От
Tom Lane
Дата:
[ 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



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@sss.pgh.pa.us>:
[ redirecting to -bugs ]

Дмитрий Иванов <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

Re: pg_restore depending on user functions

От
Tom Lane
Дата:
=?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



Re: pg_restore depending on user functions

От
Дмитрий Иванов
Дата:
--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>:
Дмитрий Иванов <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

Re: pg_restore depending on user functions

От
Tom Lane
Дата:
=?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



Re: pg_restore depending on user functions

От
Дмитрий Иванов
Дата:
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>:
Дмитрий Иванов <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

Re: pg_restore depending on user functions

От
Tom Lane
Дата:
=?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



Re: pg_restore depending on user functions

От
Дмитрий Иванов
Дата:
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>:
Дмитрий Иванов <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

Re: pg_restore depending on user functions

От
Дмитрий Иванов
Дата:
Выполнено. 
I only made a bpd schema, so I added an artificial cast, but in fact this is almost the end of the general base script:
CREATE CAST (bpd.vclass_prop AS bpd.cclass_prop) WITH FUNCTION bpd.int_cast_vclass_prop_to_cclass_prop(bpd.vclass_prop);
PS:
Thanks to your instructions, I now know how, in principle, to manually correct the script. But I would like it to be operational right away. Thanks.

ср, 17 нояб. 2021 г. в 06:47, Tom Lane <tgl@sss.pgh.pa.us>:
Дмитрий Иванов <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
Вложения

Re: pg_restore depending on user functions

От
Tom Lane
Дата:
=?UTF-8?B?0JTQvNC40YLRgNC40Lkg0JjQstCw0L3QvtCy?= <firstdismay@gmail.com> writes:
> I only made a bpd schema, so I added an artificial cast, but in fact this
> is almost the end of the general base script:
> CREATE CAST (bpd.vclass_prop AS bpd.cclass_prop) WITH FUNCTION
> bpd.int_cast_vclass_prop_to_cclass_prop(bpd.vclass_prop);

I'm only going to say this one more time, because I'm getting really
tired of looping around on this: please send a file that you have
tested to be loadable.  This one is missing at least three casts
and several functions:

psql:bpd_tl3.sql:2274: ERROR:  cannot cast type bpd.vdoc_category to bpd.cdoc_ca
tegory
psql:bpd_tl3.sql:2287: ERROR:  cannot cast type bpd.vdoc_file to bpd.cdoc_file
psql:bpd_tl3.sql:3110: ERROR:  cannot cast type bpd.vobject_prop to bpd.cobject_
prop
psql:bpd_tl3.sql:5388: ERROR:  function bpd.cfg_procargs(oid) does not exist
psql:bpd_tl3.sql:5654: ERROR:  function bpd.cfg_tblcol2(oid) does not exist
psql:bpd_tl3.sql:6189: ERROR:  function bpd.int_cast_vclass_prop_to_cclass_prop(
bpd.vclass_prop_snapshot) does not exist
psql:bpd_tl3.sql:9815: ERROR:  function bpd.int_class_name_format_check(bigint)
does not exist

I might've missed some in the cascade of follow-on errors, but those
objects are certainly not defined in this script.

            regards, tom lane



Re: pg_restore depending on user functions

От
Дмитрий Иванов
Дата:
Ok, I'll check it out. 
I entered 82k lines in 5k.
These errors occur later in parallel branches do not affect the reproduction of the error of creating functions using the bpd.vclass_ext view, which depends on "CREATE CAST (bpd.vclass_prop AS bpd.cclass_prop) WITH FUNCTION
> bpd.int_cast_vclass_prop_to_cclass_prop(bpd.vclass_prop);".
I won't argue if you think you need them. But note that all "CREATE CAST" is not included in the bpd schema and is created at the end, as I checked. Dependencies on "CREATE CAST" may not be tracked at all or may not execute correctly. In fact, all errors of interest to me occur after this step and go strictly down the chain:
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;
I'll try again but maybe I just can't do what you need...

чт, 18 нояб. 2021 г. в 20:32, Tom Lane <tgl@sss.pgh.pa.us>:
Дмитрий Иванов <firstdismay@gmail.com> writes:
> I only made a bpd schema, so I added an artificial cast, but in fact this
> is almost the end of the general base script:
> CREATE CAST (bpd.vclass_prop AS bpd.cclass_prop) WITH FUNCTION
> bpd.int_cast_vclass_prop_to_cclass_prop(bpd.vclass_prop);

I'm only going to say this one more time, because I'm getting really
tired of looping around on this: please send a file that you have
tested to be loadable.  This one is missing at least three casts
and several functions:

psql:bpd_tl3.sql:2274: ERROR:  cannot cast type bpd.vdoc_category to bpd.cdoc_ca
tegory
psql:bpd_tl3.sql:2287: ERROR:  cannot cast type bpd.vdoc_file to bpd.cdoc_file
psql:bpd_tl3.sql:3110: ERROR:  cannot cast type bpd.vobject_prop to bpd.cobject_
prop
psql:bpd_tl3.sql:5388: ERROR:  function bpd.cfg_procargs(oid) does not exist
psql:bpd_tl3.sql:5654: ERROR:  function bpd.cfg_tblcol2(oid) does not exist
psql:bpd_tl3.sql:6189: ERROR:  function bpd.int_cast_vclass_prop_to_cclass_prop(
bpd.vclass_prop_snapshot) does not exist
psql:bpd_tl3.sql:9815: ERROR:  function bpd.int_class_name_format_check(bigint)
does not exist

I might've missed some in the cascade of follow-on errors, but those
objects are certainly not defined in this script.

                        regards, tom lane

Re: pg_restore depending on user functions

От
Дмитрий Иванов
Дата:
Ok, I'll check it out. 
Before I send you anything else, I want to ask you:
I decided that you wanted the part of the script that reproduces the error, assuming that you were going to analyze the script itself. If you had explained how you were going to analyze the error, perhaps I would have understood you better.
Do you want a workable script of my database schema? Are you going to back it up yourself with pg_dump and analyze the results?

чт, 18 нояб. 2021 г. в 20:32, Tom Lane <tgl@sss.pgh.pa.us>:
Дмитрий Иванов <firstdismay@gmail.com> writes:
> I only made a bpd schema, so I added an artificial cast, but in fact this
> is almost the end of the general base script:
> CREATE CAST (bpd.vclass_prop AS bpd.cclass_prop) WITH FUNCTION
> bpd.int_cast_vclass_prop_to_cclass_prop(bpd.vclass_prop);

I'm only going to say this one more time, because I'm getting really
tired of looping around on this: please send a file that you have
tested to be loadable.  This one is missing at least three casts
and several functions:

psql:bpd_tl3.sql:2274: ERROR:  cannot cast type bpd.vdoc_category to bpd.cdoc_ca
tegory
psql:bpd_tl3.sql:2287: ERROR:  cannot cast type bpd.vdoc_file to bpd.cdoc_file
psql:bpd_tl3.sql:3110: ERROR:  cannot cast type bpd.vobject_prop to bpd.cobject_
prop
psql:bpd_tl3.sql:5388: ERROR:  function bpd.cfg_procargs(oid) does not exist
psql:bpd_tl3.sql:5654: ERROR:  function bpd.cfg_tblcol2(oid) does not exist
psql:bpd_tl3.sql:6189: ERROR:  function bpd.int_cast_vclass_prop_to_cclass_prop(
bpd.vclass_prop_snapshot) does not exist
psql:bpd_tl3.sql:9815: ERROR:  function bpd.int_class_name_format_check(bigint)
does not exist

I might've missed some in the cascade of follow-on errors, but those
objects are certainly not defined in this script.

                        regards, tom lane

Re: pg_restore depending on user functions

От
Дмитрий Иванов
Дата:
Done. 
I hope this helps you.
Basic test:
step 1:
Create a test base using the create_test.sql script (I have no errors)
Step 2:
Create a dump of the test base. My output is test.sql (I have no errors)
step 3.
Try to deploy the test.sql dump (it started with errors).
Lost views: int_a_ext, va_ext
Lost functions: a_ext_by_id(bigint);

чт, 18 нояб. 2021 г. в 20:32, Tom Lane <tgl@sss.pgh.pa.us>:
Дмитрий Иванов <firstdismay@gmail.com> writes:
> I only made a bpd schema, so I added an artificial cast, but in fact this
> is almost the end of the general base script:
> CREATE CAST (bpd.vclass_prop AS bpd.cclass_prop) WITH FUNCTION
> bpd.int_cast_vclass_prop_to_cclass_prop(bpd.vclass_prop);

I'm only going to say this one more time, because I'm getting really
tired of looping around on this: please send a file that you have
tested to be loadable.  This one is missing at least three casts
and several functions:

psql:bpd_tl3.sql:2274: ERROR:  cannot cast type bpd.vdoc_category to bpd.cdoc_ca
tegory
psql:bpd_tl3.sql:2287: ERROR:  cannot cast type bpd.vdoc_file to bpd.cdoc_file
psql:bpd_tl3.sql:3110: ERROR:  cannot cast type bpd.vobject_prop to bpd.cobject_
prop
psql:bpd_tl3.sql:5388: ERROR:  function bpd.cfg_procargs(oid) does not exist
psql:bpd_tl3.sql:5654: ERROR:  function bpd.cfg_tblcol2(oid) does not exist
psql:bpd_tl3.sql:6189: ERROR:  function bpd.int_cast_vclass_prop_to_cclass_prop(
bpd.vclass_prop_snapshot) does not exist
psql:bpd_tl3.sql:9815: ERROR:  function bpd.int_class_name_format_check(bigint)
does not exist

I might've missed some in the cascade of follow-on errors, but those
objects are certainly not defined in this script.

                        regards, tom lane
Вложения

Re: pg_restore depending on user functions

От
Tom Lane
Дата:
=?UTF-8?B?0JTQvNC40YLRgNC40Lkg0JjQstCw0L3QvtCy?= <firstdismay@gmail.com> writes:
> I hope this helps you.

Okay, that's better.

The problem seems to be that the view using the cast is itself depended
on (indirectly through another view) by another function a_ext_by_id(),
which is using the intermediate view's rowtype as its output type.
So that causes the dependency sort to hoist those two views above the
function a_ext_by_id(), and now they're ahead of the cast, which is
just left at its initial priority-driven location.

I think we can fix this by adjusting the sort priority order as per
the attached patch.  This fixes this toy test case anyway.  Can you
check to see if it fixes your real database?

(Note that as given, the patch will only apply to v14 not earlier
branches.)

            regards, tom lane

diff --git a/src/bin/pg_dump/pg_dump_sort.c b/src/bin/pg_dump/pg_dump_sort.c
index 410d1790ee..5399ec1cd9 100644
--- a/src/bin/pg_dump/pg_dump_sort.c
+++ b/src/bin/pg_dump/pg_dump_sort.c
@@ -34,6 +34,15 @@
  * restore state).  If you think to change this, see also the RestorePass
  * mechanism in pg_backup_archiver.c.
  *
+ * On the other hand, casts are intentionally sorted earlier than you might
+ * expect; logically they should come after functions, since they usually
+ * depend on those.  This works around the backend's habit of recording
+ * views that use casts as dependent on the cast's underlying function.
+ * We initially sort casts first, and then any functions used by casts
+ * will be hoisted above the casts, and in turn views that those functions
+ * depend on will be hoisted above the functions.  But views not used that
+ * way won't be hoisted.
+ *
  * NOTE: object-type priorities must match the section assignments made in
  * pg_dump.c; that is, PRE_DATA objects must sort before DO_PRE_DATA_BOUNDARY,
  * POST_DATA objects must sort after DO_POST_DATA_BOUNDARY, and DATA objects
@@ -49,12 +58,12 @@ enum dbObjectTypePriorities
     PRIO_TRANSFORM,
     PRIO_EXTENSION,
     PRIO_TYPE,                    /* used for DO_TYPE and DO_SHELL_TYPE */
+    PRIO_CAST,
     PRIO_FUNC,
     PRIO_AGG,
     PRIO_ACCESS_METHOD,
     PRIO_OPERATOR,
     PRIO_OPFAMILY,                /* used for DO_OPFAMILY and DO_OPCLASS */
-    PRIO_CAST,
     PRIO_CONVERSION,
     PRIO_TSPARSER,
     PRIO_TSTEMPLATE,

Re: pg_restore depending on user functions

От
Дмитрий Иванов
Дата:
Ok. 
I have a very general idea of how to do it. I'm afraid I won't be able to do it immediately, I'm looking into it. But when there is an update I will definitely check and let you know.

вс, 21 нояб. 2021 г. в 01:25, Tom Lane <tgl@sss.pgh.pa.us>:
Дмитрий Иванов <firstdismay@gmail.com> writes:
> I hope this helps you.

Okay, that's better.

The problem seems to be that the view using the cast is itself depended
on (indirectly through another view) by another function a_ext_by_id(),
which is using the intermediate view's rowtype as its output type.
So that causes the dependency sort to hoist those two views above the
function a_ext_by_id(), and now they're ahead of the cast, which is
just left at its initial priority-driven location.

I think we can fix this by adjusting the sort priority order as per
the attached patch.  This fixes this toy test case anyway.  Can you
check to see if it fixes your real database?

(Note that as given, the patch will only apply to v14 not earlier
branches.)

                        regards, tom lane