Horton

Thread

Commits

  1. Adjust pg_dump's priority ordering for casts.

  1. pg_restore depending on user functions

    Дмитрий Иванов <firstdismay@gmail.com> — 2021-11-14T10:10:32Z

    Good day!
Deploying a cluster: from PostgreSQL 12 Windows 10 (EDB) to PostgreSQL 14
Debian 11.
In general, I succeeded, but there are a number of questions:
1. I can determine the dependencies of the custom functions when creating
the pg_dump? I did not find anything suitable for SUPPORT, probably this is
not the case?
To recreate all of the functionality, I had to run schema recovery twice.
(--schema-only)
2. Why is pg_restore trying to set the value of fields of type GENERATED
ALWAYS?
COPY mode also does not work with INSERT. I had to do a COPY by manually
deleting the fields type GENERATED ALWAYS
Can this behavior be influenced?

  2. Re: pg_restore depending on user functions

    Tom Lane <tgl@sss.pgh.pa.us> — 2021-11-14T17:31:09Z

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

  3. Re: pg_restore depending on user functions

    Дмитрий Иванов <firstdismay@gmail.com> — 2021-11-14T18:37:19Z

    Спасибо за ваш отзыв.

вс, 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...

  4. Re: pg_restore depending on user functions

    Tom Lane <tgl@sss.pgh.pa.us> — 2021-11-14T18:46:19Z

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

  5. Re: pg_restore depending on user functions

    Дмитрий Иванов <firstdismay@gmail.com> — 2021-11-14T22:49:27Z

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


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

  6. Re: pg_restore depending on user functions

    Дмитрий Иванов <firstdismay@gmail.com> — 2021-11-14T23:52:47Z

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

  7. Re: pg_restore depending on user functions

    Tom Lane <tgl@sss.pgh.pa.us> — 2021-11-15T00:06:11Z

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

  8. Re: pg_restore depending on user functions

    Дмитрий Иванов <firstdismay@gmail.com> — 2021-11-15T15:46:47Z

    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:
> > вс, 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
>

  9. Re: pg_restore depending on user functions

    Tom Lane <tgl@sss.pgh.pa.us> — 2021-11-15T15:49:25Z

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

  10. Re: pg_restore depending on user functions

    Дмитрий Иванов <firstdismay@gmail.com> — 2021-11-15T16:03:42Z

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

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

  11. Re: pg_restore depending on user functions

    Дмитрий Иванов <firstdismay@gmail.com> — 2021-11-15T16:36:14Z

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

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

  12. Re: pg_restore depending on user functions

    Дмитрий Иванов <firstdismay@gmail.com> — 2021-11-15T21:24:31Z

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

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

  13. Re: pg_restore depending on user functions

    Tom Lane <tgl@sss.pgh.pa.us> — 2021-11-15T22:07:45Z

    [ 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

  14. Re: pg_restore depending on user functions

    Дмитрий Иванов <firstdismay@gmail.com> — 2021-11-16T13:56:00Z

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

  15. Re: pg_restore depending on user functions

    Tom Lane <tgl@sss.pgh.pa.us> — 2021-11-16T16:29:14Z

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

  16. Re: pg_restore depending on user functions

    Дмитрий Иванов <firstdismay@gmail.com> — 2021-11-16T16:57:38Z

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

  17. Re: pg_restore depending on user functions

    Tom Lane <tgl@sss.pgh.pa.us> — 2021-11-16T20:04:06Z

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

  18. Re: pg_restore depending on user functions

    Дмитрий Иванов <firstdismay@gmail.com> — 2021-11-17T01:33:40Z

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

  19. Re: pg_restore depending on user functions

    Tom Lane <tgl@sss.pgh.pa.us> — 2021-11-17T01:47:44Z

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

  20. Re: pg_restore depending on user functions

    Дмитрий Иванов <firstdismay@gmail.com> — 2021-11-17T02:06:25Z

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

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

  21. Re: pg_restore depending on user functions

    Дмитрий Иванов <firstdismay@gmail.com> — 2021-11-18T07:33:40Z

    Выполнено.
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>:

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

  22. Re: pg_restore depending on user functions

    Tom Lane <tgl@sss.pgh.pa.us> — 2021-11-18T15:32:36Z

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

  23. Re: pg_restore depending on user functions

    Дмитрий Иванов <firstdismay@gmail.com> — 2021-11-18T16:58:51Z

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

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

  24. Re: pg_restore depending on user functions

    Дмитрий Иванов <firstdismay@gmail.com> — 2021-11-18T18:07:12Z

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

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

  25. Re: pg_restore depending on user functions

    Дмитрий Иванов <firstdismay@gmail.com> — 2021-11-19T04:45:45Z

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

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

  26. Re: pg_restore depending on user functions

    Tom Lane <tgl@sss.pgh.pa.us> — 2021-11-20T20:25:47Z

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

  27. Re: pg_restore depending on user functions

    Дмитрий Иванов <firstdismay@gmail.com> — 2021-11-21T15:30:16Z

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

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