Обсуждение: pg_dump insert column GENERATED

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

pg_dump insert column GENERATED

От
Дмитрий Иванов
Дата:
Good afternoon.
Why does pg_dump generate an insertion script in the generated columns? This causes insertion errors.
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
is_inside - column should be excluded how to do it?

INSERT INTO bpd.object (id, id_class, id_position, bquantity, id_position_root, id_conception, barcode_unit, id_unit_conversion_rule, "timestamp", on_freeze, timestamp_class, name, id_class_root, id_group, id_group_root, id_object_carrier, "desc", id_class_prop_object_carrier, id_pos_temp_prop, is_inside, mc) VALUES (51253, 1015, 461, 1, 461, 84, 2020000512530, 14, '2021-11-14 08:40:31.381', false, '2021-02-19 11:01:28.402', 'NFC метка самоклеющаяся', 1013, 138, 138, -1, '', -1, -1, false, 1);

Table:
CREATE TABLE IF NOT EXISTS bpd.object
(
    id bigint NOT NULL DEFAULT nextval('bpd.object_general_id_seq'::regclass),
    id_class bigint NOT NULL,
    id_position bigint NOT NULL DEFAULT '-1'::integer,
    bquantity numeric NOT NULL,
    id_position_root bigint NOT NULL,
    id_conception bigint NOT NULL,
    barcode_unit bigint NOT NULL DEFAULT 0,
    id_unit_conversion_rule integer NOT NULL,
    "timestamp" timestamp without time zone NOT NULL DEFAULT LOCALTIMESTAMP,
    on_freeze boolean NOT NULL DEFAULT false,
    timestamp_class timestamp without time zone NOT NULL DEFAULT LOCALTIMESTAMP(3),
    name character varying(255) COLLATE pg_catalog."default" NOT NULL,
    id_class_root bigint NOT NULL,
    id_group bigint NOT NULL,
    id_group_root bigint NOT NULL,
    id_object_carrier bigint NOT NULL DEFAULT '-1'::integer,
    "desc" character varying(2044) COLLATE pg_catalog."default" NOT NULL DEFAULT 'н/д'::character varying,
    id_class_prop_object_carrier bigint NOT NULL DEFAULT '-1'::integer,
    id_pos_temp_prop bigint NOT NULL DEFAULT '-1'::integer,
    is_inside boolean GENERATED ALWAYS AS (((id_object_carrier > 0) OR (id_pos_temp_prop > 0))) STORED,
    mc numeric NOT NULL DEFAULT 0,
    CONSTRAINT object_pkey PRIMARY KEY (id),
    CONSTRAINT unique_id_object_id_object_prop UNIQUE (id, id_class_prop_object_carrier),
    CONSTRAINT lnk_class_snapshot_object FOREIGN KEY (id_class, timestamp_class)
        REFERENCES bpd.class_snapshot (id, "timestamp") MATCH FULL
        ON UPDATE CASCADE
        ON DELETE CASCADE,
    CONSTRAINT lnk_conception_object FOREIGN KEY (id_conception)
        REFERENCES bpd.conception (id) MATCH FULL
        ON UPDATE CASCADE
        ON DELETE CASCADE,
    CONSTRAINT lnk_position_object FOREIGN KEY (id_position)
        REFERENCES bpd."position" (id) MATCH FULL
        ON UPDATE CASCADE
        ON DELETE CASCADE,
    CONSTRAINT check_self_integration CHECK (id <> id_object_carrier)
)

Re: pg_dump insert column GENERATED

От
Adrian Klaver
Дата:
On 11/20/21 04:27, Дмитрий Иванов wrote:
> Good afternoon.
> Why does pg_dump generate an insertion script in the generated columns? 
> This causes insertion errors.
> 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
> is_inside - column should be excluded how to do it?

Actually I think it should be DEFAULT.

What is your pg_dump version?

It shouldn't unless you are specifying --inserts.

What is the full pg_dump command you are using?

What are the errors?

> 
> INSERT INTO bpd.object (id, id_class, id_position, bquantity, 
> id_position_root, id_conception, barcode_unit, id_unit_conversion_rule, 
> "timestamp", on_freeze, timestamp_class, name, id_class_root, id_group, 
> id_group_root, id_object_carrier, "desc", id_class_prop_object_carrier, 
> id_pos_temp_prop, is_inside, mc) VALUES (51253, 1015, 461, 1, 461, 84, 
> 2020000512530, 14, '2021-11-14 08:40:31.381', false, '2021-02-19 
> 11:01:28.402', 'NFC метка самоклеющаяся', 1013, 138, 138, -1, '', -1, 
> -1, false, 1);
> 



-- 
Adrian Klaver
adrian.klaver@aklaver.com

Re: pg_dump insert column GENERATED

От
Tom Lane
Дата:
Adrian Klaver <adrian.klaver@aklaver.com> writes:
> On 11/20/21 04:27, Дмитрий Иванов wrote:
>> Why does pg_dump generate an insertion script in the generated columns?

> Actually I think it should be DEFAULT.

It should be, and it is when I try this example.  I get output like

--
-- Data for Name: object; Type: TABLE DATA; Schema: bpd; Owner: postgres
--

INSERT INTO bpd.object (id, id_class, id_position, bquantity, id_position_root, id_conception, barcode_unit,
id_unit_conversion_rule,"timestamp", on_freeze, timestamp_class, name, id_class_root, id_group, id_group_root,
id_object_carrier,"desc", id_class_prop_object_carrier, id_pos_temp_prop, is_inside, mc) VALUES (51253, 1015, 461, 1,
461,84, 2020000512530, 14, '2021-11-14 08:40:31.381', false, '2021-02-19 11:01:28.402', 'NFC метка самоклеющаяся',
1013,138, 138, -1, '', -1, -1, DEFAULT, 1); 

I wonder what version of pg_dump is actually being used there.

            regards, tom lane

Re: pg_dump insert column GENERATED

От
Дмитрий Иванов
Дата:
Ok, I see. 
This is actually an interesting question. I don't understand which approach to use. I am using pd_dump and pg_restore of the receiving server. It might make sense to use pg_dump source, pg_restore sink


сб, 20 нояб. 2021 г. в 22:33, Tom Lane <tgl@sss.pgh.pa.us>:
Adrian Klaver <adrian.klaver@aklaver.com> writes:
> On 11/20/21 04:27, Дмитрий Иванов wrote:
>> Why does pg_dump generate an insertion script in the generated columns?

> Actually I think it should be DEFAULT.

It should be, and it is when I try this example.  I get output like

--
-- Data for Name: object; Type: TABLE DATA; Schema: bpd; Owner: postgres
--

INSERT INTO bpd.object (id, id_class, id_position, bquantity, id_position_root, id_conception, barcode_unit, id_unit_conversion_rule, "timestamp", on_freeze, timestamp_class, name, id_class_root, id_group, id_group_root, id_object_carrier, "desc", id_class_prop_object_carrier, id_pos_temp_prop, is_inside, mc) VALUES (51253, 1015, 461, 1, 461, 84, 2020000512530, 14, '2021-11-14 08:40:31.381', false, '2021-02-19 11:01:28.402', 'NFC метка самоклеющаяся', 1013, 138, 138, -1, '', -1, -1, DEFAULT, 1);

I wonder what version of pg_dump is actually being used there.

                        regards, tom lane

Re: pg_dump insert column GENERATED

От
Дмитрий Иванов
Дата:
I don't know. 
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"
I used the pg_dump version of the receiving server, but the pg_dump of the source server initially gave almost the same result, a COPY error of the empty table
PostgreSQL 12.9, compiled by Visual C++ build 1914, 64-bit
to
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



сб, 20 нояб. 2021 г. в 22:00, Adrian Klaver <adrian.klaver@aklaver.com>:
On 11/20/21 04:27, Дмитрий Иванов wrote:
> Good afternoon.
> Why does pg_dump generate an insertion script in the generated columns?
> This causes insertion errors.
> 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
> is_inside - column should be excluded how to do it?

Actually I think it should be DEFAULT.

What is your pg_dump version?

It shouldn't unless you are specifying --inserts.

What is the full pg_dump command you are using?

What are the errors?

>
> INSERT INTO bpd.object (id, id_class, id_position, bquantity,
> id_position_root, id_conception, barcode_unit, id_unit_conversion_rule,
> "timestamp", on_freeze, timestamp_class, name, id_class_root, id_group,
> id_group_root, id_object_carrier, "desc", id_class_prop_object_carrier,
> id_pos_temp_prop, is_inside, mc) VALUES (51253, 1015, 461, 1, 461, 84,
> 2020000512530, 14, '2021-11-14 08:40:31.381', false, '2021-02-19
> 11:01:28.402', 'NFC метка самоклеющаяся', 1013, 138, 138, -1, '', -1,
> -1, false, 1);
>



--
Adrian Klaver
adrian.klaver@aklaver.com

Re: pg_dump insert column GENERATED

От
Adrian Klaver
Дата:
On 11/20/21 10:33, Дмитрий Иванов wrote:
> I don't know.
> 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"
> I used the pg_dump version of the receiving server, but the pg_dump of 
> the source server initially gave almost the same result, a COPY error of 
> the empty table
> PostgreSQL 12.9, compiled by Visual C++ build 1914, 64-bit
> to
> 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

To be clear you used the Postgres 14 version of pg_dump to dump from a 
Postgres 12 version database, correct?

What version of pg_restore did you use to restore to the Postgres 14 
database?

Where did you install the Postgres 12.9 version package from?

-- 
Adrian Klaver
adrian.klaver@aklaver.com

Re: pg_dump insert column GENERATED

От
Дмитрий Иванов
Дата:
Yes and yes. 
I ended up using the pg_dump of the receiving server.

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"

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"

sudo /usr/lib/postgresql/14/bin/pg_restore --host "127.0.0.1" --port "5432" --username "back" --no-password --dbname "Uchet" --disable-triggers --table="bpd.object" --format=c --verbose "/home/dismay/uchet/Uchet.backup"

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

Server source:
PostgreSQL 12.9, compiled by Visual C++ build 1914, 64-bit
EDB assembly installed from "Application Stack Builder"

вс, 21 нояб. 2021 г. в 00:06, Adrian Klaver <adrian.klaver@aklaver.com>:
On 11/20/21 10:33, Дмитрий Иванов wrote:
> I don't know.
> 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"
> I used the pg_dump version of the receiving server, but the pg_dump of
> the source server initially gave almost the same result, a COPY error of
> the empty table
> PostgreSQL 12.9, compiled by Visual C++ build 1914, 64-bit
> to
> 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

To be clear you used the Postgres 14 version of pg_dump to dump from a
Postgres 12 version database, correct?

What version of pg_restore did you use to restore to the Postgres 14
database?

Where did you install the Postgres 12.9 version package from?

--
Adrian Klaver
adrian.klaver@aklaver.com

Re: pg_dump insert column GENERATED

От
Adrian Klaver
Дата:
On 11/20/21 17:11, Дмитрий Иванов wrote:
> Yes and yes.
> I ended up using the pg_dump of the receiving server.
> 
> 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"
> 
> 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"
> 
> sudo /usr/lib/postgresql/14/bin/pg_restore --host "127.0.0.1" --port 
> "5432" --username "back" --no-password --dbname "Uchet" 
> --disable-triggers --table="bpd.object" --format=c --verbose 
> "/home/dismay/uchet/Uchet.backup"
> 
> Receiving server:
> 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
> 
> Server source:
> PostgreSQL 12.9, compiled by Visual C++ build 1914, 64-bit
> EDB assembly installed from "Application Stack Builder"
> 
> вс, 21 нояб. 2021 г. в 00:06, Adrian Klaver <adrian.klaver@aklaver.com 

Hmm. I cannot replicate, though in my case both servers(12.9, 14.1) are 
one same Linux machine.

What is the history of the database in the 12.0 instance?

Was it upgraded from another instance?

If so dump/restore or pg_upgrade?

Is it a promoted replica?

-- 
Adrian Klaver
adrian.klaver@aklaver.com

Re: pg_dump insert column GENERATED

От
Дмитрий Иванов
Дата:
Yes and yes. 
I filled some tables with GENERATED fields as follows:
"C:\Program Files\PostgreSQL\12\bin\pg_dump" --file "D:\UPLoad\-=PG-Uchet=-\Base\bpd.sql" --host "127.0.0.1" --port "5999" --username "back" --no-password --verbose --format=p --quote-all-identifiers --column-inserts --inserts --encoding="UTF8" --schema "bpd" "Uchet"
pg_dump (PostgreSQL) 12.9

sudo /usr/lib/postgresql/14/bin/psql --file "/home/dismay/uchet/bpd.sql" --host "127.0.0.1" --port "5432" --username "back" --no-password --dbname "postgres" 2> "/home/dismay/uchet/bpd.log"
psql (PostgreSQL) 14.1 (Debian 14.1-1.pgdg110+1)


INSERT INTO bpg.object (create in version 10)
cannot insert a non-DEFAULT value into column "is_inside"

INSERT INTO bpd.plan_calendar (create in version 12)
OK

PostgresSQL server history:
Windows 10 build EDB PostgreSQL 12.9, compiled by Visual C++ build 1914, 64-bit
10.х ->pg_upgrade(12)->12.x (I can't remember exactly, I don't want to lie.)

LINUX DEBIAN 11 (VirtualBOX ORACLE)
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

>Is it a promoted replica?
I am not familiar with this term.

CREATE TABLE IF NOT EXISTS bpd.object
(
    id bigint NOT NULL DEFAULT nextval('bpd.object_general_id_seq'::regclass),
    id_class bigint NOT NULL,
    id_position bigint NOT NULL DEFAULT '-1'::integer,
    bquantity numeric NOT NULL,
    id_position_root bigint NOT NULL,
    id_conception bigint NOT NULL,
    barcode_unit bigint NOT NULL DEFAULT 0,
    id_unit_conversion_rule integer NOT NULL,
    "timestamp" timestamp without time zone NOT NULL DEFAULT LOCALTIMESTAMP,
    on_freeze boolean NOT NULL DEFAULT false,
    timestamp_class timestamp without time zone NOT NULL DEFAULT LOCALTIMESTAMP(3),
    name character varying(255) COLLATE pg_catalog."default" NOT NULL,
    id_class_root bigint NOT NULL,
    id_group bigint NOT NULL,
    id_group_root bigint NOT NULL,
    id_object_carrier bigint NOT NULL DEFAULT '-1'::integer,
    "desc" character varying(2044) COLLATE pg_catalog."default" NOT NULL DEFAULT 'н/д'::character varying,
    id_class_prop_object_carrier bigint NOT NULL DEFAULT '-1'::integer,
    id_pos_temp_prop bigint NOT NULL DEFAULT '-1'::integer,
    is_inside boolean GENERATED ALWAYS AS (((id_object_carrier > 0) OR (id_pos_temp_prop > 0))) STORED,
    mc numeric NOT NULL DEFAULT 0,
    CONSTRAINT object_pkey PRIMARY KEY (id),
    CONSTRAINT unique_id_object_id_object_prop UNIQUE (id, id_class_prop_object_carrier),
    CONSTRAINT lnk_class_snapshot_object FOREIGN KEY (id_class, timestamp_class)
        REFERENCES bpd.class_snapshot (id, "timestamp") MATCH FULL
        ON UPDATE CASCADE
        ON DELETE CASCADE,
    CONSTRAINT lnk_conception_object FOREIGN KEY (id_conception)
        REFERENCES bpd.conception (id) MATCH FULL
        ON UPDATE CASCADE
        ON DELETE CASCADE,
    CONSTRAINT lnk_position_object FOREIGN KEY (id_position)
        REFERENCES bpd."position" (id) MATCH FULL
        ON UPDATE CASCADE
        ON DELETE CASCADE,
    CONSTRAINT check_self_integration CHECK (id <> id_object_carrier)
)

TABLESPACE pg_default;

CREATE TABLE IF NOT EXISTS bpd.plan_calendar
(
    id bigint NOT NULL DEFAULT nextval('bpd.work_calendar_id_seq'::regclass),
    work_date date NOT NULL,
    work_year integer NOT NULL GENERATED ALWAYS AS (date_part('year'::text, work_date)) STORED,
    work_month integer NOT NULL GENERATED ALWAYS AS (date_part('month'::text, work_date)) STORED,
    work_month_day integer NOT NULL GENERATED ALWAYS AS (date_part('day'::text, work_date)) STORED,
    day_type bpd.day_type NOT NULL DEFAULT 'working'::bpd.day_type,
    work_year_day integer GENERATED ALWAYS AS (date_part('doy'::text, work_date)) STORED,
    week40_day numeric,
    week40_month numeric,
    week39_day numeric,
    week39_month numeric,
    week36_day numeric,
    week36_month numeric,
    week35_day numeric,
    week35_month numeric,
    week33_day numeric,
    week33_month numeric,
    week30_day numeric,
    week30_month numeric,
    week24_day numeric,
    week24_month numeric,
    week20_day numeric,
    week20_month numeric,
    week18_day numeric,
    week18_month numeric,
    range_night_part1 tsrange GENERATED ALWAYS AS (tsrange((work_date)::timestamp without time zone, (work_date + '06:00:00'::interval), '[]'::text)) STORED,
    range_night_part2 tsrange GENERATED ALWAYS AS (tsrange((work_date + '22:00:00'::interval), (work_date + '24:00:00'::interval), '[)'::text)) STORED,
    work_week_day integer GENERATED ALWAYS AS (date_part('isodow'::text, work_date)) STORED,
    work_week_day_name character varying COLLATE pg_catalog."default" GENERATED ALWAYS AS (
CASE date_part('isodow'::text, work_date)
    WHEN 1 THEN 'ПН'::character varying
    WHEN 2 THEN 'ВТ'::character varying
    WHEN 3 THEN 'СР'::character varying
    WHEN 4 THEN 'ЧТ'::character varying
    WHEN 5 THEN 'ПН'::character varying
    WHEN 6 THEN 'СБ'::character varying
    WHEN 7 THEN 'ВС'::character varying
    ELSE 'ПН'::character varying
END) STORED,
    work_week_day_name_full character varying COLLATE pg_catalog."default" GENERATED ALWAYS AS (
CASE date_part('isodow'::text, work_date)
    WHEN 1 THEN 'Понедельник'::character varying
    WHEN 2 THEN 'Вторник'::character varying
    WHEN 3 THEN 'Среда'::character varying
    WHEN 4 THEN 'Четверг'::character varying
    WHEN 5 THEN 'Пятница'::character varying
    WHEN 6 THEN 'Суббота'::character varying
    WHEN 7 THEN 'Воскресенье'::character varying
    ELSE 'Понедельник'::character varying
END) STORED,
    work_year_week integer GENERATED ALWAYS AS (date_part('week'::text, work_date)) STORED,
    week40_week numeric,
    week39_week numeric,
    week36_week numeric,
    week35_week numeric,
    week33_week numeric,
    week30_week numeric,
    week24_week numeric,
    week20_week numeric,
    week18_week numeric,
    CONSTRAINT plan_calendar_pkey PRIMARY KEY (id),
    CONSTRAINT unique_plan_calendar UNIQUE (work_date)
)

TABLESPACE pg_default;

вс, 21 нояб. 2021 г. в 06:38, Adrian Klaver <adrian.klaver@aklaver.com>:
On 11/20/21 17:11, Дмитрий Иванов wrote:
> Yes and yes.
> I ended up using the pg_dump of the receiving server.
>
> 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"
>
> 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"
>
> sudo /usr/lib/postgresql/14/bin/pg_restore --host "127.0.0.1" --port
> "5432" --username "back" --no-password --dbname "Uchet"
> --disable-triggers --table="bpd.object" --format=c --verbose
> "/home/dismay/uchet/Uchet.backup"
>
> Receiving server:
> 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
>
> Server source:
> PostgreSQL 12.9, compiled by Visual C++ build 1914, 64-bit
> EDB assembly installed from "Application Stack Builder"
>
> вс, 21 нояб. 2021 г. в 00:06, Adrian Klaver <adrian.klaver@aklaver.com

Hmm. I cannot replicate, though in my case both servers(12.9, 14.1) are
one same Linux machine.

What is the history of the database in the 12.0 instance?

Was it upgraded from another instance?

If so dump/restore or pg_upgrade?

Is it a promoted replica?

--
Adrian Klaver
adrian.klaver@aklaver.com

Re: pg_dump insert column GENERATED

От
Adrian Klaver
Дата:
On 11/20/21 20:39, Дмитрий Иванов wrote:
> Yes and yes.
> I filled some tables with GENERATED fields as follows:
> "C:\Program Files\PostgreSQL\12\bin\pg_dump" --file 
> "D:\UPLoad\-=PG-Uchet=-\Base\bpd.sql" --host "127.0.0.1" --port "5999" 
> --username "back" --no-password --verbose --format=p 
> --quote-all-identifiers --column-inserts --inserts --encoding="UTF8" 
> --schema "bpd" "Uchet"
> pg_dump (PostgreSQL) 12.9

> 
> sudo /usr/lib/postgresql/14/bin/psql --file "/home/dismay/uchet/bpd.sql" 
> --host "127.0.0.1" --port "5432" --username "back" --no-password 
> --dbname "postgres" 2> "/home/dismay/uchet/bpd.log"
> psql (PostgreSQL) 14.1 (Debian 14.1-1.pgdg110+1)
> 
> 
> INSERT INTO bpg.object (create in version 10)
> cannot insert a non-DEFAULT value into column "is_inside"

If I am following the bpg.object table was originally created in 
Postgres 10, correct?

Postgres 10 did not have GENERATED ALWAYS AS, that appeared in Postgres 12.

So did you do an ALTER TABLE ... GENERATED ALWAYS AS on bpg.object in 
the Postgres 12 version of the database?


> 
> INSERT INTO bpd.plan_calendar (create in version 12)
> OK

bpd.plan_calendar was created in the Postgres 12 instance of the 
database with GENERATED ALWAYS AS, correct?

> 
> PostgresSQL server history:
> Windows 10 build EDB PostgreSQL 12.9, compiled by Visual C++ build 1914, 
> 64-bit
> 10.х ->pg_upgrade(12)->12.x (I can't remember exactly, I don't want to lie.)
> 
> LINUX DEBIAN 11 (VirtualBOX ORACLE)
> 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
> 
>  >Is it a promoted replica?
> I am not familiar with this term.

Means was the instance the standby in a replication setup that was then 
moved up(promoted) to the primary.


Are the below from the bpd.sql file?

> 
> CREATE TABLE IF NOT EXISTS bpd.object
> (
>      id bigint NOT NULL DEFAULT 
> nextval('bpd.object_general_id_seq'::regclass),
>      id_class bigint NOT NULL,
>      id_position bigint NOT NULL DEFAULT '-1'::integer,
>      bquantity numeric NOT NULL,
>      id_position_root bigint NOT NULL,
>      id_conception bigint NOT NULL,
>      barcode_unit bigint NOT NULL DEFAULT 0,
>      id_unit_conversion_rule integer NOT NULL,
>      "timestamp" timestamp without time zone NOT NULL DEFAULT 
> LOCALTIMESTAMP,
>      on_freeze boolean NOT NULL DEFAULT false,
>      timestamp_class timestamp without time zone NOT NULL DEFAULT 
> LOCALTIMESTAMP(3),
>      name character varying(255) COLLATE pg_catalog."default" NOT NULL,
>      id_class_root bigint NOT NULL,
>      id_group bigint NOT NULL,
>      id_group_root bigint NOT NULL,
>      id_object_carrier bigint NOT NULL DEFAULT '-1'::integer,
>      "desc" character varying(2044) COLLATE pg_catalog."default" NOT 
> NULL DEFAULT 'н/д'::character varying,
>      id_class_prop_object_carrier bigint NOT NULL DEFAULT '-1'::integer,
>      id_pos_temp_prop bigint NOT NULL DEFAULT '-1'::integer,
>      is_inside boolean GENERATED ALWAYS AS (((id_object_carrier > 0) OR 
> (id_pos_temp_prop > 0))) STORED,
>      mc numeric NOT NULL DEFAULT 0,
>      CONSTRAINT object_pkey PRIMARY KEY (id),
>      CONSTRAINT unique_id_object_id_object_prop UNIQUE (id, 
> id_class_prop_object_carrier),
>      CONSTRAINT lnk_class_snapshot_object FOREIGN KEY (id_class, 
> timestamp_class)
>          REFERENCES bpd.class_snapshot (id, "timestamp") MATCH FULL
>          ON UPDATE CASCADE
>          ON DELETE CASCADE,
>      CONSTRAINT lnk_conception_object FOREIGN KEY (id_conception)
>          REFERENCES bpd.conception (id) MATCH FULL
>          ON UPDATE CASCADE
>          ON DELETE CASCADE,
>      CONSTRAINT lnk_position_object FOREIGN KEY (id_position)
>          REFERENCES bpd."position" (id) MATCH FULL
>          ON UPDATE CASCADE
>          ON DELETE CASCADE,
>      CONSTRAINT check_self_integration CHECK (id <> id_object_carrier)
> )
> 
> TABLESPACE pg_default;
> 
> CREATE TABLE IF NOT EXISTS bpd.plan_calendar
> (
>      id bigint NOT NULL DEFAULT 
> nextval('bpd.work_calendar_id_seq'::regclass),
>      work_date date NOT NULL,
>      work_year integer NOT NULL GENERATED ALWAYS AS 
> (date_part('year'::text, work_date)) STORED,
>      work_month integer NOT NULL GENERATED ALWAYS AS 
> (date_part('month'::text, work_date)) STORED,
>      work_month_day integer NOT NULL GENERATED ALWAYS AS 
> (date_part('day'::text, work_date)) STORED,
>      day_type bpd.day_type NOT NULL DEFAULT 'working'::bpd.day_type,
>      work_year_day integer GENERATED ALWAYS AS (date_part('doy'::text, 
> work_date)) STORED,
>      week40_day numeric,
>      week40_month numeric,
>      week39_day numeric,
>      week39_month numeric,
>      week36_day numeric,
>      week36_month numeric,
>      week35_day numeric,
>      week35_month numeric,
>      week33_day numeric,
>      week33_month numeric,
>      week30_day numeric,
>      week30_month numeric,
>      week24_day numeric,
>      week24_month numeric,
>      week20_day numeric,
>      week20_month numeric,
>      week18_day numeric,
>      week18_month numeric,
>      range_night_part1 tsrange GENERATED ALWAYS AS 
> (tsrange((work_date)::timestamp without time zone, (work_date + 
> '06:00:00'::interval), '[]'::text)) STORED,
>      range_night_part2 tsrange GENERATED ALWAYS AS (tsrange((work_date + 
> '22:00:00'::interval), (work_date + '24:00:00'::interval), '[)'::text)) 
> STORED,
>      work_week_day integer GENERATED ALWAYS AS 
> (date_part('isodow'::text, work_date)) STORED,
>      work_week_day_name character varying COLLATE pg_catalog."default" 
> GENERATED ALWAYS AS (
> CASE date_part('isodow'::text, work_date)
>      WHEN 1 THEN 'ПН'::character varying
>      WHEN 2 THEN 'ВТ'::character varying
>      WHEN 3 THEN 'СР'::character varying
>      WHEN 4 THEN 'ЧТ'::character varying
>      WHEN 5 THEN 'ПН'::character varying
>      WHEN 6 THEN 'СБ'::character varying
>      WHEN 7 THEN 'ВС'::character varying
>      ELSE 'ПН'::character varying
> END) STORED,
>      work_week_day_name_full character varying COLLATE 
> pg_catalog."default" GENERATED ALWAYS AS (
> CASE date_part('isodow'::text, work_date)
>      WHEN 1 THEN 'Понедельник'::character varying
>      WHEN 2 THEN 'Вторник'::character varying
>      WHEN 3 THEN 'Среда'::character varying
>      WHEN 4 THEN 'Четверг'::character varying
>      WHEN 5 THEN 'Пятница'::character varying
>      WHEN 6 THEN 'Суббота'::character varying
>      WHEN 7 THEN 'Воскресенье'::character varying
>      ELSE 'Понедельник'::character varying
> END) STORED,
>      work_year_week integer GENERATED ALWAYS AS (date_part('week'::text, 
> work_date)) STORED,
>      week40_week numeric,
>      week39_week numeric,
>      week36_week numeric,
>      week35_week numeric,
>      week33_week numeric,
>      week30_week numeric,
>      week24_week numeric,
>      week20_week numeric,
>      week18_week numeric,
>      CONSTRAINT plan_calendar_pkey PRIMARY KEY (id),
>      CONSTRAINT unique_plan_calendar UNIQUE (work_date)
> )
> 
> TABLESPACE pg_default;
> 
> вс, 21 нояб. 2021 г. в 06:38, Adrian Klaver <adrian.klaver@aklaver.com 
> <mailto:adrian.klaver@aklaver.com>>:
> 
>     On 11/20/21 17:11, Дмитрий Иванов wrote:
>      > Yes and yes.
>      > I ended up using the pg_dump of the receiving server.
>      >
>      > 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"
>      >
>      > 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"
>      >
>      > sudo /usr/lib/postgresql/14/bin/pg_restore --host "127.0.0.1" --port
>      > "5432" --username "back" --no-password --dbname "Uchet"
>      > --disable-triggers --table="bpd.object" --format=c --verbose
>      > "/home/dismay/uchet/Uchet.backup"
>      >
>      > Receiving server:
>      > 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
>      >
>      > Server source:
>      > PostgreSQL 12.9, compiled by Visual C++ build 1914, 64-bit
>      > EDB assembly installed from "Application Stack Builder"
>      >
>      > вс, 21 нояб. 2021 г. в 00:06, Adrian Klaver
>     <adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>
> 
>     Hmm. I cannot replicate, though in my case both servers(12.9, 14.1) are
>     one same Linux machine.
> 
>     What is the history of the database in the 12.0 instance?
> 
>     Was it upgraded from another instance?
> 
>     If so dump/restore or pg_upgrade?
> 
>     Is it a promoted replica?
> 
>     -- 
>     Adrian Klaver
>     adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com

Re: pg_dump insert column GENERATED

От
Дмитрий Иванов
Дата:
Yes and yes. 
I don't get regular results in my experiments. I filled in some of the tables this morning, but now I can't do it again.
> INSERT INTO bpd.plan_calendar (create in version 12)
> OK
I can't do it again.

>If I am following the bpg.object table was originally created in
>Postgres 10, correct?
Yes

bpd.plan_calendar was created in the Postgres 12 instance of the
database with GENERATED ALWAYS AS, correct?
Yes

>Postgres 10 did not have GENERATED ALWAYS AS, that appeared in Postgres 12.
>So did you do an ALTER TABLE ... GENERATED ALWAYS AS on bpg.object in
>the Postgres 12 version of the database?
I think you're right, I added later.

>Means was the instance the standby in a replication setup that was then
>moved up(promoted) to the primary.

wal_level = replica # minimal, replica, or logical
Never been in a replication relationship.

Are the below from the bpd.sql file?
Yes
Why does pg_dump insert data into the calculated columns?
I entered the data manually via export/import, excluding the problematic field because its value will be calculated when inserted.

Re: pg_dump insert column GENERATED

От
Tom Lane
Дата:
=?UTF-8?B?0JTQvNC40YLRgNC40Lkg0JjQstCw0L3QvtCy?= <firstdismay@gmail.com> writes:
>> So did you do an ALTER TABLE ... GENERATED ALWAYS AS on bpg.object in
>> the Postgres 12 version of the database?

> I think you're right, I added later.

Hmm, that's suggestive, but I tried making a table that way and still did
not see any pg_dump misbehavior.

It might be useful to see the results of this query on the v12 server:

select attname, attgenerated from pg_attribute
where attrelid = 'bpd.object'::regclass and attnum > 0;

We should see attgenerated = 's' for the generated column, but
maybe we don't?

            regards, tom lane

Re: pg_dump insert column GENERATED

От
Adrian Klaver
Дата:
On 11/21/21 07:23, Дмитрий Иванов wrote:
> Yes and yes.
> I don't get regular results in my experiments. I filled in some of the 
> tables this morning, but now I can't do it again.
>  > INSERT INTO bpd.plan_calendar (create in version 12)
>  > OK
> I can't do it again.

You are going need to provide more information about the above:

1) Define regular results.

2) The query used.

3) The outcome vs what you expected.


> Are the below from the bpd.sql file?
> Yes
> Why does pg_dump insert data into the calculated columns?

It shouldn't that is the point of this discussion, why it is happening 
in your case? So far we have not been able to replicate this behavior on 
our end, hence the many questions as to your set up.

> I entered the data manually via export/import, excluding the problematic 
> field because its value will be calculated when inserted.
> 
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com

Re: pg_dump insert column GENERATED

От
Дмитрий Иванов
Дата:
Ok, I'll take a look. 
I recreated the is_inside column yesterday, but it didn't make any difference.
Uchet=# select attname, attgenerated from pg_attribute
Uchet-# where attrelid = 'bpd.object'::regclass and attnum > 0;
            attname            | attgenerated
-------------------------------+--------------
 id                            |
 id_class                      |
 id_position                   |
 ........pg.dropped.4........  |
 ........pg.dropped.5........  |
 ........pg.dropped.6........  |
 bquantity                     |
 ........pg.dropped.8........  |
 ........pg.dropped.9........  |
 id_position_root              |
 id_conception                 |
 barcode_unit                  |
 id_unit_conversion_rule       |
 ........pg.dropped.14........ |
 timestamp                     |
 on_freeze                     |
 timestamp_class               |
 name                          |
 id_class_root                 |
 id_group                      |
 id_group_root                 |
 id_object_carrier             |
 desc                          |
 ........pg.dropped.24........ |
 ........pg.dropped.25........ |
 ........pg.dropped.26........ |
 id_class_prop_object_carrier  |
 id_pos_temp_prop              |
 ........pg.dropped.29........ |
 mc                            |
 is_inside                     | s

вс, 21 нояб. 2021 г. в 21:24, Tom Lane <tgl@sss.pgh.pa.us>:
Дмитрий Иванов <firstdismay@gmail.com> writes:
>> So did you do an ALTER TABLE ... GENERATED ALWAYS AS on bpg.object in
>> the Postgres 12 version of the database?

> I think you're right, I added later.

Hmm, that's suggestive, but I tried making a table that way and still did
not see any pg_dump misbehavior.

It might be useful to see the results of this query on the v12 server:

select attname, attgenerated from pg_attribute
where attrelid = 'bpd.object'::regclass and attnum > 0;

We should see attgenerated = 's' for the generated column, but
maybe we don't?

                        regards, tom lane

Re: pg_dump insert column GENERATED

От
Дмитрий Иванов
Дата:
Got it. 

>You are going need to provide more information about the above:
>1) Define regular results.
>2) The query used.
>3) The outcome vs what you expected.

I will continue to experiment. The question is which option is better (this would reduce the number of options):
pd_dump is the source server;
pg_restore - receiver server;
or
pd_dump - receiver server;
pg_restore - server-receiver; 

Re: pg_dump insert column GENERATED

От
Tom Lane
Дата:
=?UTF-8?B?0JTQvNC40YLRgNC40Lkg0JjQstCw0L3QvtCy?= <firstdismay@gmail.com> writes:
> Uchet=# select attname, attgenerated from pg_attribute
> Uchet-# where attrelid = 'bpd.object'::regclass and attnum > 0;
>             attname            | attgenerated
> -------------------------------+--------------
>  id                            |
>  id_class                      |
>  id_position                   |
>  ........pg.dropped.4........  |
>  ........pg.dropped.5........  |
>  ........pg.dropped.6........  |
>  bquantity                     |
>  ........pg.dropped.8........  |
>  ........pg.dropped.9........  |
>  id_position_root              |
>  id_conception                 |
>  barcode_unit                  |
>  id_unit_conversion_rule       |
>  ........pg.dropped.14........ |
>  timestamp                     |
>  on_freeze                     |
>  timestamp_class               |
>  name                          |
>  id_class_root                 |
>  id_group                      |
>  id_group_root                 |
>  id_object_carrier             |
>  desc                          |
>  ........pg.dropped.24........ |
>  ........pg.dropped.25........ |
>  ........pg.dropped.26........ |
>  id_class_prop_object_carrier  |
>  id_pos_temp_prop              |
>  ........pg.dropped.29........ |
>  mc                            |
>  is_inside                     | s

Hah ... that gave me the clue I needed.  If there are dropped
column(s) before the GENERATED one, pg_dump gets it wrong ---
but only in --inserts mode, not the default COPY mode, which
no doubt explains why nobody noticed.  There is code in there
to inject DEFAULT, but it must be indexing the flag array wrong.

Will fix, thanks for the report!

            regards, tom lane

Re: pg_dump insert column GENERATED

От
Дмитрий Иванов
Дата:
Рад помочь! 
--
С уважением, Дмитрий!


пн, 22 нояб. 2021 г. в 19:55, Tom Lane <tgl@sss.pgh.pa.us>:
Дмитрий Иванов <firstdismay@gmail.com> writes:
> Uchet=# select attname, attgenerated from pg_attribute
> Uchet-# where attrelid = 'bpd.object'::regclass and attnum > 0;
>             attname            | attgenerated
> -------------------------------+--------------
>  id                            |
>  id_class                      |
>  id_position                   |
>  ........pg.dropped.4........  |
>  ........pg.dropped.5........  |
>  ........pg.dropped.6........  |
>  bquantity                     |
>  ........pg.dropped.8........  |
>  ........pg.dropped.9........  |
>  id_position_root              |
>  id_conception                 |
>  barcode_unit                  |
>  id_unit_conversion_rule       |
>  ........pg.dropped.14........ |
>  timestamp                     |
>  on_freeze                     |
>  timestamp_class               |
>  name                          |
>  id_class_root                 |
>  id_group                      |
>  id_group_root                 |
>  id_object_carrier             |
>  desc                          |
>  ........pg.dropped.24........ |
>  ........pg.dropped.25........ |
>  ........pg.dropped.26........ |
>  id_class_prop_object_carrier  |
>  id_pos_temp_prop              |
>  ........pg.dropped.29........ |
>  mc                            |
>  is_inside                     | s

Hah ... that gave me the clue I needed.  If there are dropped
column(s) before the GENERATED one, pg_dump gets it wrong ---
but only in --inserts mode, not the default COPY mode, which
no doubt explains why nobody noticed.  There is code in there
to inject DEFAULT, but it must be indexing the flag array wrong.

Will fix, thanks for the report!

                        regards, tom lane

Re: pg_dump insert column GENERATED

От
Дмитрий Иванов
Дата:
It seems to me that you are right all round (I gave up COPY because of problems with the MONEY type):

sudo /usr/lib/postgresql/14/bin/pg_dump --file "/home/dismay/uchet/object.sql" --host "server" --port "5999" --username "back" --no-password --verbose --format=p --quote-all-identifiers --encoding="UTF8" --table "bpd".object --dbname "Uchet"
--
-- PostgreSQL database dump
--

-- Dumped from database version 12.9
-- Dumped by pg_dump version 14.1 (Debian 14.1-1.pgdg110+1)

-- Started on 2021-11-22 20:21:59 +05

SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SELECT pg_catalog.set_config('search_path', '', false);
SET check_function_bodies = false;
SET xmloption = content;
SET client_min_messages = warning;
SET row_security = off;

SET default_tablespace = '';

SET default_table_access_method = "heap";

--
-- TOC entry 353 (class 1259 OID 94786)
-- Name: object; Type: TABLE; Schema: bpd; Owner: IvanovDU
--

CREATE TABLE "bpd"."object" (
    "id" bigint DEFAULT "nextval"('"bpd"."object_general_id_seq"'::"regclass") NOT NULL,
    "id_class" bigint NOT NULL,
    "id_position" bigint DEFAULT '-1'::integer NOT NULL,
    "bquantity" numeric NOT NULL,
    "id_position_root" bigint NOT NULL,
    "id_conception" bigint NOT NULL,
    "barcode_unit" bigint DEFAULT 0 NOT NULL,
    "id_unit_conversion_rule" integer NOT NULL,
    "timestamp" timestamp without time zone DEFAULT LOCALTIMESTAMP NOT NULL,
    "on_freeze" boolean DEFAULT false NOT NULL,
    "timestamp_class" timestamp without time zone DEFAULT LOCALTIMESTAMP(3) NOT NULL,
    "name" character varying(255) NOT NULL,
    "id_class_root" bigint NOT NULL,
    "id_group" bigint NOT NULL,
    "id_group_root" bigint NOT NULL,
    "id_object_carrier" bigint DEFAULT '-1'::integer NOT NULL,
    "desc" character varying(2044) DEFAULT 'н/д'::character varying NOT NULL,
    "id_class_prop_object_carrier" bigint DEFAULT '-1'::integer NOT NULL,
    "id_pos_temp_prop" bigint DEFAULT '-1'::integer NOT NULL,
    "mc" numeric DEFAULT 0 NOT NULL,
    "is_inside" boolean GENERATED ALWAYS AS ((("id_object_carrier" > 0) OR ("id_pos_temp_prop" >= 0))) STORED,
    CONSTRAINT "check_self_integration" CHECK (("id" <> "id_object_carrier"))
);
ALTER TABLE "bpd"."object" OWNER TO "IvanovDU";
--
-- TOC entry 5225 (class 0 OID 94786)
-- Dependencies: 353
-- Data for Name: object; Type: TABLE DATA; Schema: bpd; Owner: IvanovDU
--
COPY "bpd"."object" ("id", "id_class", "id_position", "bquantity", "id_position_root", "id_conception", "barcode_unit", "id_unit_conversion_rule", "timestamp", "on_freeze", "timestamp_class", "name", "id_class_root", "id_group", "id_group_root", "id_object_carrier", "desc", "id_class_prop_object_carrier", "id_pos_temp_prop", "mc") FROM stdin;
51253 1015 461 1 461 84 2020000512530 14 2021-11-14 08:40:31.381 f 2021-02-19 11:01:28.402 NFC метка самоклеющаяся 1013 138 138 -1 -1 -1 1................

*************************************************************************************************************************************************************************************
sudo /usr/lib/postgresql/14/bin/pg_dump --file "/home/dismay/uchet/object2.sql" --host "server" --port "5999" --username "back" --no-password --verbose --format=p --quote-all-identifiers --column-inserts --inserts --encoding="UTF8" --table "bpd".object --dbname "Uchet"
--
-- PostgreSQL database dump
--

-- Dumped from database version 12.9
-- Dumped by pg_dump version 14.1 (Debian 14.1-1.pgdg110+1)

-- Started on 2021-11-22 20:24:31 +05

SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SELECT pg_catalog.set_config('search_path', '', false);
SET check_function_bodies = false;
SET xmloption = content;
SET client_min_messages = warning;
SET row_security = off;

SET default_tablespace = '';

SET default_table_access_method = "heap";

--
-- TOC entry 353 (class 1259 OID 94786)
-- Name: object; Type: TABLE; Schema: bpd; Owner: IvanovDU
--

CREATE TABLE "bpd"."object" (
    "id" bigint DEFAULT "nextval"('"bpd"."object_general_id_seq"'::"regclass") NOT NULL,
    "id_class" bigint NOT NULL,
    "id_position" bigint DEFAULT '-1'::integer NOT NULL,
    "bquantity" numeric NOT NULL,
    "id_position_root" bigint NOT NULL,
    "id_conception" bigint NOT NULL,
    "barcode_unit" bigint DEFAULT 0 NOT NULL,
    "id_unit_conversion_rule" integer NOT NULL,
    "timestamp" timestamp without time zone DEFAULT LOCALTIMESTAMP NOT NULL,
    "on_freeze" boolean DEFAULT false NOT NULL,
    "timestamp_class" timestamp without time zone DEFAULT LOCALTIMESTAMP(3) NOT NULL,
    "name" character varying(255) NOT NULL,
    "id_class_root" bigint NOT NULL,
    "id_group" bigint NOT NULL,
    "id_group_root" bigint NOT NULL,
    "id_object_carrier" bigint DEFAULT '-1'::integer NOT NULL,
    "desc" character varying(2044) DEFAULT 'н/д'::character varying NOT NULL,
    "id_class_prop_object_carrier" bigint DEFAULT '-1'::integer NOT NULL,
    "id_pos_temp_prop" bigint DEFAULT '-1'::integer NOT NULL,
    "mc" numeric DEFAULT 0 NOT NULL,
    "is_inside" boolean GENERATED ALWAYS AS ((("id_object_carrier" > 0) OR ("id_pos_temp_prop" >= 0))) STORED,
    CONSTRAINT "check_self_integration" CHECK (("id" <> "id_object_carrier"))
);


ALTER TABLE "bpd"."object" OWNER TO "IvanovDU";

--
-- TOC entry 5225 (class 0 OID 94786)
-- Dependencies: 353
-- Data for Name: object; Type: TABLE DATA; Schema: bpd; Owner: IvanovDU
--

INSERT INTO "bpd"."object" ("id", "id_class", "id_position", "bquantity", "id_position_root", "id_conception", "barcode_unit", "id_unit_conversion_rule", "timestamp", "on_freeze", "timestamp_class", "name", "id_class_root", "id_group", "id_group_root", "id_object_carrier", "desc", "id_class_prop_object_carrier", "id_pos_temp_prop", "mc", "is_inside") VALUES (51253, 1015, 461, 1, 461, 84, 2020000512530, 14, '2021-11-14 08:40:31.381', false, '2021-02-19 11:01:28.402', 'NFC метка самоклеющаяся', 1013, 138, 138, -1, '', -1, -1, 1, false);


--
С уважением, Дмитрий!

Re: pg_dump insert column GENERATED

От
Adrian Klaver
Дата:
On 11/22/21 03:32, Дмитрий Иванов wrote:
> Got it.
> 
> 
>      >You are going need to provide more information about the above:
>      >1) Define regular results.
>      >2) The query used.
>      >3) The outcome vs what you expected.
> 
> I will continue to experiment. The question is which option is better 
> (this would reduce the number of options):
> pd_dump is the source server;
> pg_restore - receiver server;
> or
> pd_dump - receiver server;
> pg_restore - server-receiver;

pg_dump is backwards compatible not forwards.

In the explanations below Postgres versioning(major/minor) is determined as:

Pre-version 10:

X.x.x

10+:

X.x

Where  X is major and x is minor.

This means if are moving forwards in Postgres major version then:

1) Use pg_dump from newer version of Postgres to dump from older version 
of Postgres. In your case pg_dump(v14) dump Postgres server v12.

2) To restore use the version of pg_restore for the Postgres version you 
are restoring to. In your case pg_restore(v14).

Staying on the same version:

1) Use the pg_dump/pg_restore for the version your are on.

2) If you are moving from one minor release to another then it would be 
better to use the latest minor release version to get any bug fixes.

Going backwards from newer version to older version:

This is not supported.

-- 
Adrian Klaver
adrian.klaver@aklaver.com

Re: pg_dump insert column GENERATED

От
Дмитрий Иванов
Дата:
Thank you, there is a clear logic to it.
--
Regards, Dmitry!


пн, 22 нояб. 2021 г. в 21:11, Adrian Klaver <adrian.klaver@aklaver.com>:
On 11/22/21 03:32, Дмитрий Иванов wrote:
> Got it.
>
>
>      >You are going need to provide more information about the above:
>      >1) Define regular results.
>      >2) The query used.
>      >3) The outcome vs what you expected.
>
> I will continue to experiment. The question is which option is better
> (this would reduce the number of options):
> pd_dump is the source server;
> pg_restore - receiver server;
> or
> pd_dump - receiver server;
> pg_restore - server-receiver;

pg_dump is backwards compatible not forwards.

In the explanations below Postgres versioning(major/minor) is determined as:

Pre-version 10:

X.x.x

10+:

X.x

Where  X is major and x is minor.

This means if are moving forwards in Postgres major version then:

1) Use pg_dump from newer version of Postgres to dump from older version
of Postgres. In your case pg_dump(v14) dump Postgres server v12.

2) To restore use the version of pg_restore for the Postgres version you
are restoring to. In your case pg_restore(v14).

Staying on the same version:

1) Use the pg_dump/pg_restore for the version your are on.

2) If you are moving from one minor release to another then it would be
better to use the latest minor release version to get any bug fixes.

Going backwards from newer version to older version:

This is not supported.

--
Adrian Klaver
adrian.klaver@aklaver.com