BUG #17732: pg_restore fails with check constraint - Mailing list pgsql-bugs

From PG Bug reporting form
Subject BUG #17732: pg_restore fails with check constraint
Date
Msg-id 17732-2f5c2de910c26e60@postgresql.org
Whole thread Raw
Responses BUG #17732: pg_restore fails with check constraint
List pgsql-bugs
The following bug has been logged on the website:

Bug reference:      17732
Logged by:          Artem
Email address:      artem.voropaev@hotmail.com
PostgreSQL version: 13.9
Operating system:   Debian
Description:

Hi team! We faced an error using pg_restore on our database. We have
database tables like this one:

CREATE FUNCTION public.table_b_check
(
    id_b int
)
    RETURNS int
AS $$
BEGIN
    if id_b is null
    then
        return 1;
    end if;
    return coalesce((select 1 from public.tableB where id = id_b), 0);
END;
$$ LANGUAGE plpgsql;

CREATE TABLE public.tableA
(
    id integer NOT NULL,
    idb integer NOT NULL,
    value varchar(40) NOT NULL,
    CONSTRAINT CK_Field_Id_b CHECK ((public.table_b_check(idb::int)=(1)))
);

CREATE TABLE public.tableB
(
    id integer NOT NULL,
    value varchar(40) NOT NULL
);

INSERT INTO public.tableB VALUES (1, 'a');
INSERT INTO public.tableB VALUES (2, 'b');
INSERT INTO public.tableA VALUES (1, 1, 'a');
INSERT INTO public.tableA VALUES (2, 2, 'b');

We backuped this database with pg_dump and then trying to restore it by
pg_restore, but error occured on tableA:

pg_restore: error: COPY failed for table "tablea": ERROR:  new row for
relation "tablea" violates check constraint "ck_field_id_b"
DETAIL:  Failing row contains (1, 1, a).
CONTEXT:  COPY tablea, line 1: "1       1       a"

As we understand that this happens because restore function works in
alphabetical order on tables. we've tryed to use  pre-data/post-data option
but have ho succes. In all cases we've lost data in tableA.

Is there any solution for this specific problem?


pgsql-bugs by date:

Previous
From: PG Bug reporting form
Date:
Subject: BUG #17731: Server doesn't start after abnormal shutdown while creating unlogged tables
Next
From: PG Bug reporting form
Date:
Subject: BUG #17733: ERROR: could not load library "/Users/frank/postgres/postgresql-13.9/lib/postgresql/llvmjit.so": dl