referential integrity problem upon deletion and reinsertion

This is a known problem. We don't have a fix yet.

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.

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026