From: | PG Bug reporting form <noreply(at)postgresql(dot)org> |
---|---|
To: | pgsql-bugs(at)lists(dot)postgresql(dot)org |
Cc: | artem(dot)voropaev(at)hotmail(dot)com |
Subject: | BUG #17732: pg_restore fails with check constraint |
Date: | 2022-12-27 10:14:47 |
Message-ID: | 17732-2f5c2de910c26e60@postgresql.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
The following bug has been logged on the website:
Bug reference: 17732
Logged by: Artem
Email address: artem(dot)voropaev(at)hotmail(dot)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?
From | Date | Subject | |
---|---|---|---|
Next Message | PG Bug reporting form | 2022-12-28 16:15:27 | BUG #17733: ERROR: could not load library "/Users/frank/postgres/postgresql-13.9/lib/postgresql/llvmjit.so": dl |
Previous Message | PG Bug reporting form | 2022-12-26 06:50:52 | BUG #17731: Server doesn't start after abnormal shutdown while creating unlogged tables |