Обсуждение: Problem using pg_restore with -a option
Hi all, I'm using PostgreSQL 7.4.7-6sarge2 (debian) Using the schema below, doing a pg_dump -Fc, delete all data, and a pg_restore -a, the sequence of inserting data isn't correct issuing an error. Could this be a bug? Notice that on schema, table periodo depends on tipo, but the sequence is table periodo, then table tipo and after table periodo is changed to reference tipo. My schema file: drop table periodo cascade ; drop table tipo cascade ; create table periodo ( "idPeriodo" serial not null, "idTipo" int4, designacao varchar(50), constraint pk_Periodo primary key ("idPeriodo") ) ; create table tipo ( "idTipo" serial not null, tipo varchar(5), designacao varchar(50), constraint pk_Tipo primary key ("idTipo") ) ; alter table periodo add constraint fk_tipo foreign key ("idTipo") references tipo ("idTipo") ; My data on tables: INSERT INTO tipo (tipo,designacao) VALUES ('T','Type'); INSERT INTO periodo ("idTipo",designacao) VALUES (1,'periodo'); Error: pg_restore: ERROR: insert or update on table "periodo" violates foreign key constraint "fk_tipo" DETAIL: Key (idTipo)=(1) is not present in table "tipo". Best regards, Luís Sousa
Luís Sousa wrote: > Hi all, > > I'm using PostgreSQL 7.4.7-6sarge2 (debian) > > Using the schema below, doing a pg_dump -Fc, delete all data, and a > pg_restore -a, the sequence of inserting data isn't correct issuing an > error. > Could this be a bug? Not a bug -- rather a known deficiency. pg_dump 8.0 and beyond knows how to sort stuff so that these problems do not occur, but 7.4 doesn't. The typical workaround was to use pg_restore -l/-L to generate the list of objects to restore and sort them appropiately, then do the actual restore. -- Alvaro Herrera http://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc.
=?ISO-8859-1?Q?Lu=EDs_Sousa?= <llsousa@ualg.pt> writes: > Using the schema below, doing a pg_dump -Fc, delete all data, and a > pg_restore -a, the sequence of inserting data isn't correct issuing an > error. Data-only restores make no attempt to avoid foreign-key constraint problems --- in general there is no solution, since you could have circular constraints. You could drop and re-add the FK constraints, or if you trust that the data is valid there's the --disable-triggers option to suppress FK constraint checking. regards, tom lane