From: | Peter Barker <pbarker(at)barker(dot)dropbear(dot)id(dot)au> |
---|---|
To: | pgsql-bugs(at)postgresql(dot)org |
Subject: | referential integrity problem upon deletion and reinsertion |
Date: | 2001-03-09 03:40:05 |
Message-ID: | Pine.LNX.4.21.0103091421120.22964-100000@moriarty.fith.priv |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
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.
From | Date | Subject | |
---|---|---|---|
Next Message | pgsql-bugs | 2001-03-09 15:41:16 | Memory leak in ODBC driver |
Previous Message | Ian Lance Taylor | 2001-03-09 00:18:43 | Re: [HACKERS] Re: Depending on system install scripts (was Re: COBOL) |