Re: 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(at)barker(dot)dropbear(dot)id(dot)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(at)hrmc(dot)com(dot)au on SlashDot.
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
> message can get through to the mailing list cleanly
>

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