Thread: pg_restore depending on user functions

pg_restore depending on user functions

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

Re: pg_restore depending on user functions

From
Tom Lane
Date:
=?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



Re: pg_restore depending on user functions

From
Дмитрий Иванов
Date:
Спасибо за ваш отзыв. 

вс, 14 нояб. 2021 г. в 22:31, Tom Lane <tgl@sss.pgh.pa.us>:
Дмитрий Иванов <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...

Re: pg_restore depending on user functions

From
Tom Lane
Date:
=?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



Re: pg_restore depending on user functions

From
Дмитрий Иванов
Date:
Ok, I'll do it. 

вс, 14 нояб. 2021 г. в 23:46, Tom Lane <tgl@sss.pgh.pa.us>:
Дмитрий Иванов <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

Re: pg_restore depending on user functions

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

Re: pg_restore depending on user functions

From
Tom Lane
Date:
=?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



Re: pg_restore depending on user functions

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

Re: pg_restore depending on user functions

From
Tom Lane
Date:
=?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



Re: pg_restore depending on user functions

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

Re: pg_restore depending on user functions

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

Re: pg_restore depending on user functions

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