Thread

1. referential integrity problem upon deletion and reinsertion

   Peter Barker <pbarker@barker.dropbear.id.au> — 2001-03-09T03:40:05Z

   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.
   
   
   

2. Re: referential integrity problem upon deletion and reinsertion

   Bruce Momjian <pgman@candle.pha.pa.us> — 2001-03-12T22:10:45Z

   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