Обсуждение: How to replace rows in table so that foreign key rows are not deleted
I want to replace ( delete and insert) records in master table . I delete and insert record with same primary key. I want that foreign key records are not deleted. I tried begin; create temp table t1 ( pk integer primary key ); insert into t1 values(1); create temp table t2 (fk integer ); alter table t2 add foreign key (fk) references t1 on delete cascade deferrable initially deferred; insert into t2 values(1); -- Howto: set delete_constraint deferred delete from t1; insert into t1 values(1); commit; select * from t2; Observed: no rows Expected: t2 must contain one row. foreign key check and deletion should occur only when transaction commits. Any idea ? Is there any generic way to turn off foreign key constraints before delete command in transaction ? Andrus.
Andrus wrote: > Is there any generic way to turn off foreign key constraints before delete > command in transaction ? From TFM: "--disable-triggers This option is only relevant when performing a data-only restore. It instructs pg_restore to execute commands to temporarily disable triggers on the target tables while the data is reloaded. Use this if you have referential integrity checks or other triggers on the tables that you do not want to invoke during data reload. Presently, the commands emitted for --disable-triggers must be done as superuser. So, you should also specify a superuser name with -S, or preferably run pg_restore as a PostgreSQL superuser." So, you could use this option with pg_dump/pg_restore, and look at the "commands to temporarily disable triggers" it produces. I did so, and for a table named 'country' the following SQL statements were produced: -- Disable triggers UPDATE pg_catalog.pg_class SET reltriggers = 0 WHERE oid = 'country'::pg_catalog.regclass; /* COPY command goes here to bulk load table data. */ -- Enable triggers UPDATE pg_catalog.pg_class SET reltriggers = (SELECT pg_catalog.count(*) FROM pg_catalog.pg_trigger where pg_class.oid = tgrelid) WHERE oid = 'country'::pg_catalog.regclass; Regards, Berend Tober
> I did so, and for a table named 'country' the following SQL statements > were produced: > > -- Disable triggers > UPDATE pg_catalog.pg_class SET reltriggers = 0 WHERE oid = > 'country'::pg_catalog.regclass; > > /* COPY command goes here to bulk load table data. */ > > -- Enable triggers > UPDATE pg_catalog.pg_class SET reltriggers = (SELECT pg_catalog.count(*) > FROM pg_catalog.pg_trigger where pg_class.oid = tgrelid) WHERE oid = > 'country'::pg_catalog.regclass; Berend, thank you for bright idea. this may cause invalid foreign key data to be loaded into database. I want that in end of transaction Postgres will check for correctness of foreign keys like DEFERRED clause suggests. How this check can be forced ? Andrus.
On Thu, 20 Apr 2006, Andrus wrote: > I want to replace ( delete and insert) records in master table . > I delete and insert record with same primary key. > I want that foreign key records are not deleted. > > I tried > > begin; > create temp table t1 ( pk integer primary key ); > insert into t1 values(1); > create temp table t2 (fk integer ); > alter table t2 add foreign key (fk) references t1 on delete cascade > deferrable initially deferred; > insert into t2 values(1); > -- Howto: set delete_constraint deferred > delete from t1; > insert into t1 values(1); > commit; > select * from t2; > > Observed: no rows > > Expected: t2 must contain one row. > > foreign key check and deletion should occur only when transaction commits. Actually, this looks like a case where SQL99 strongly implies that the action happens even for non-immediate constraints as part of the delete but SQL2003 changed that and we didn't notice. This should probably be reasonably straightforward to change I think (hope). > Any idea ? > Is there any generic way to turn off foreign key constraints before delete > command in transaction ? Right now, probably nothing short of dropping and readding the constraint.
On Thu, 20 Apr 2006, Stephan Szabo wrote: > On Thu, 20 Apr 2006, Andrus wrote: > > > I want to replace ( delete and insert) records in master table . > > I delete and insert record with same primary key. > > I want that foreign key records are not deleted. > > > > I tried > > > > begin; > > create temp table t1 ( pk integer primary key ); > > insert into t1 values(1); > > create temp table t2 (fk integer ); > > alter table t2 add foreign key (fk) references t1 on delete cascade > > deferrable initially deferred; > > insert into t2 values(1); > > -- Howto: set delete_constraint deferred > > delete from t1; > > insert into t1 values(1); > > commit; > > select * from t2; > > > > Observed: no rows > > > > Expected: t2 must contain one row. > > > > foreign key check and deletion should occur only when transaction commits. > > Actually, this looks like a case where SQL99 strongly implies that the > action happens even for non-immediate constraints as part of the delete > but SQL2003 changed that and we didn't notice. This should probably be > reasonably straightforward to change I think (hope). Hmm, actually, it's a little less straightforward than I thought, mostly because I haven't seen something that seems to explicitly say what to do for non-immediate constraints that happened before the commit in the 2003 spec, I'd guess do the action at commit time as well, but the wording of the general rules talk about rows marked for deletion, but by the time of the commit, those rows are not marked for deletion any longer, but actually deleted as far as I can see and there doesn't appear (for non-match partial constraints) seem to be a special case for the referenced row coming back into existance as far as I can tell either. > > Any idea ? > > Is there any generic way to turn off foreign key constraints before delete > > command in transaction ? > > Right now, probably nothing short of dropping and readding the constraint. Or, if you're willing to patch, I think a first order approximation of what you want might be to remove the special cases in trigger.c (afterTriggerSetState) and tablecmds.c (createForeignKeyTriggers), but I haven't tested that.
> ... and there doesn't appear (for > non-match partial constraints) seem to be a special case for the > referenced row coming back into existance as far as I can tell either. > Or, if you're willing to patch, I think a first order approximation of > what you want might be to remove the special cases in trigger.c > (afterTriggerSetState) and tablecmds.c (createForeignKeyTriggers), but I > haven't tested that. Thank you. So I must create and maintain special version of PostgreSQL ? Andrus.
On Fri, 21 Apr 2006, Andrus wrote: > > ... and there doesn't appear (for > > non-match partial constraints) seem to be a special case for the > > referenced row coming back into existance as far as I can tell either. > > > Or, if you're willing to patch, I think a first order approximation of > > what you want might be to remove the special cases in trigger.c > > (afterTriggerSetState) and tablecmds.c (createForeignKeyTriggers), but I > > haven't tested that. > > Thank you. > So I must create and maintain special version of PostgreSQL ? If the standard does say it should do what you want, it'll get changed for a later version, but probably not backpatched, so this would be a short term solution. The hardest part about changing it is making sure there aren't any new holes in the constraint. If the standard doesn't match what you want, then it's a bit more involved. Following the standard would still require you to maintain a special version for the rules you want or changing the expectation. Or, alternatively, you could make a case why the standard does say what you want (or allows what you want or is simply wrong) if there's a disagreement.