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@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