Hi,
We think we have found a problem when deleting and inserting in
the same transaction with constraints deferred:
========================
machine=> create table foo (bar int4 primary key, ref int4 references foo
deferrable);
NOTICE: CREATE TABLE/PRIMARY KEY will create implicit index 'foo_pkey'
for table 'foo'
NOTICE: CREATE TABLE will create implicit trigger(s) for FOREIGN KEY
check(s)
CREATE
machine=> begin work;
BEGIN
machine=> insert into foo (bar,ref) values (1,null);
INSERT 215987 1
machine=> insert into foo (bar,ref) values (2,1);
INSERT 215988 1
machine=> commit;
COMMIT
machine=> begin work;
BEGIN
machine=> set constraints all deferred;
SET CONSTRAINTS
machine=> delete from foo where bar=1;
DELETE 1
machine=> insert into foo (bar,ref) values (1,null);
INSERT 215989 1
machine=> commit;
ERROR: <unnamed> referential integrity violation - key in foo still
referenced from foo
machine=>
=============================================================
As far as I can see, since the table meets the constraints at the end of
the transaction, the transaction should commit OK.
The real-world problem I've come across for this is where you want to
reinitialise a table; basically:
==========
begin work;
set constraints all deferred;
delete from foo;
insert into foo (2,1);
insert into foo (1,null);
commit;
===========
AFAICS, this should also work.
It doesn't, but
===========
begin work;
delete from foo;
set constraints all deferred;
insert into foo (2,1);
insert into foo (1,null);
commit;
=========== ( moving the set_constraints below the delete)
does work. This "hack" works in this case but may not in others.
Thanks for a great product.
Yours,
--
Peter Barker | N _--_|\ /---- Barham, Vic
Programmer,Sysadmin,Geek | W + E / /\
pbarker@barker.dropbear.id.au | S \_,--?_*<-- Canberra
You need a bigger hammer. | v [35S, 149E]
"Besides, what most US companies would call R&D, we call 'getting shit done'.
We're an emminently practical people in many ways."
- jeremyl@hrmc.com.au on SlashDot.
