Skip site navigation (1) Skip section navigation (2)

Re: referential integrity problem upon deletion and reinsertion

From: Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us>
To: Peter Barker <pbarker(at)barker(dot)dropbear(dot)id(dot)au>
Cc: pgsql-bugs(at)postgresql(dot)org
Subject: Re: referential integrity problem upon deletion and reinsertion
Date: 2001-03-12 22:10:45
Message-ID: 200103122210.RAA23111@candle.pha.pa.us (view raw or flat)
Thread:
Lists: pgsql-bugs
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

In response to

pgsql-bugs by date

Next:From: Tom LaneDate: 2001-03-12 23:43:08
Subject: Re: pgDump sequence dump bug
Previous:From: Peter MountDate: 2001-03-12 16:07:08
Subject: Re: absolute(int rownumber) method not implemented as per Sun guidlines

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group