pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedBUG #17732: pg_restore fails with check constraint
2+ messages / 2 participants
[nested] [flat]
* BUG #17732: pg_restore fails with check constraint
@ 2022-12-27 10:14 PG Bug reporting form <noreply@postgresql.org>
2022-12-28 17:08 ` BUG #17732: pg_restore fails with check constraint David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 1 reply; 2+ messages in thread
From: PG Bug reporting form @ 2022-12-27 10:14 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org; +Cc: artem.voropaev@hotmail.com
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?
^ permalink raw reply [nested|flat] 2+ messages in thread
* BUG #17732: pg_restore fails with check constraint
2022-12-27 10:14 BUG #17732: pg_restore fails with check constraint PG Bug reporting form <noreply@postgresql.org>
@ 2022-12-28 17:08 ` David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 0 replies; 2+ messages in thread
From: David G. Johnston @ 2022-12-28 17:08 UTC (permalink / raw)
To: artem.voropaev@hotmail.com <artem.voropaev@hotmail.com>; pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>
On Tuesday, December 27, 2022, PG Bug reporting form <noreply@postgresql.org>
wrote:
> 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:
>
> Is there any solution for this specific problem?
>
>
Unbreak the source system by converting the check constraint into a
trigger. Check constraints, as documented, must be immutable.
Unfortunately the system doesn’t warn you when you’ve violated this rule
upfront.
David J.
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2022-12-28 17:08 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2022-12-27 10:14 BUG #17732: pg_restore fails with check constraint PG Bug reporting form <noreply@postgresql.org>
2022-12-28 17:08 ` David G. Johnston <david.g.johnston@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox