Re: deferred constraints failing on commit

From: "Michael Richards" <michael(at)fastmail(dot)ca>
To: sszabo(at)megazone23(dot)bigpanda(dot)com
Cc: pgsql-sql(at)postgresql(dot)org
Subject: Re: deferred constraints failing on commit
Date: 2001-01-16 18:05:43
Message-ID: 3A648D77.000021.30958@frodo.searchcanada.ca
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

Here is a test case that illustrates the problem. I figured I was
doing it all wrong before and didn't bother to distill and include a
test case.

create table objects(
revisionid int4,
primary key (revisionid));

create table objcatalog(
minrev int4,
maxrev int4,
foreign key (minrev) references objects(revisionid) INITIALLY
DEFERRED,
foreign key (maxrev) references objects(revisionid) INITIALLY
DEFERRED);

insert into objects values (999);
insert into objcatalog values (999,999);

begin;
SET CONSTRAINTS ALL DEFERRED;
update objects set revisionid=1;
insert into objects values (999);

select * from objects;
select * from objcatalog;
commit;

-Michael

> Can you send the full schema of the tables you are using for
> this?
>
> On Tue, 16 Jan 2001, Michael Richards wrote:
>
>> Hi.
>>
>> I'm having trouble with committing a transaction. Intuitively it
>> should work but does not.
>>
>> I've got a table with 2 foreign keys, minrev and maxrev. They
>> refer to a the revisionid value in another table. I need to
>> update the second table to reduce the revisionid, then insert a
>> new row. At the end of this all the keys match up yet the commit
>> fails.
>>
>> urdr=> begin;
>> BEGIN
>> urdr=> update objects set revisionid=2 where id=2 and
>> revisionid=99999999;
>>
>> UPDATE 1
>> urdr=> insert into objects
>> (id,typeid,repositoryid,parentid,deleted,permissions,revisionid,n
>> ame) values (2,2,1,NULL,'f',NULL,99999999,'test.sql');
>> INSERT 246107 1
>> urdr=> select id,revisionid from objects;
>> id | revisionid
>> ----+------------
>> 1 | 99999999
>> 2 | 1
>> 2 | 2
>> 2 | 99999999
>> (4 rows)
>> urdr=> select * from objcatalog ;
>> objectid | repositoryid | minrev | maxrev | key | data
>> ----------+--------------+----------+----------+----------+------
>> ---- 2 | 1 | 99999999 | 99999999 | mimetype
>> |text/plain (1 row)
>>
>> urdr=> commit;
>> ERROR: <unnamed> referential integrity violation - key in
>> objects still referenced from objcatalog
>>
>> At commit all the keys check out properly. minrev and maxrev both
>> point to the same revisionid in the row we just inserted.
>>
>> Is this a bug or me just misreading how things should work again?
>>
>> -Michael
>> _________________________________________________________________
>> http://fastmail.ca/ - Fast Free Web Email for Canadians
>>

_________________________________________________________________
http://fastmail.ca/ - Fast Free Web Email for Canadians
>From pgsql-sql-owner(at)postgresql(dot)org Tue Jan 16 14:55:10 2001
Received: from im.eth.net (mail.uthplanet.com [202.9.136.18])
by mail.postgresql.org (8.11.1/8.11.1) with ESMTP id f0GJt2021822
for <pgsql-sql(at)postgresql(dot)org>; Tue, 16 Jan 2001 14:55:05 -0500 (EST)
(envelope-from sharmad(at)im(dot)eth(dot)net)
Received: from SharmadNaik ([61.11.9.98]) by im.eth.net with Microsoft SMTPSVC(5.5.1877.117.11);
Wed, 17 Jan 2001 01:19:18 +0530
Message-ID: <002301c07ff3$8af0ef00$62090b3d(at)SharmadNaik>
From: "Sharmad Naik" <sharmad(at)im(dot)eth(dot)net>
To: "PGSQL-SQL" <pgsql-sql(at)postgresql(dot)org>
Subject: python+postgresql
Date: Wed, 17 Jan 2001 00:54:11 +0530
MIME-Version: 1.0
Content-Type: multipart/alternative;
boundary="----=_NextPart_000_001A_01C08020.013911C0"
X-Priority: 3
X-MSMail-Priority: Normal
X-Mailer: Microsoft Outlook Express 5.50.4133.2400
X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4133.2400
X-Archive-Number: 200101/175
X-Sequence-Number: 839

This is a multi-part message in MIME format.

------=_NextPart_000_001A_01C08020.013911C0
Content-Type: text/plain; charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

I wanted to compile python will an installed postgres package ...can i do i=
t .How?
TIA
Sharmad

------=_NextPart_000_001A_01C08020.013911C0
Content-Type: text/html; charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=3DContent-Type content=3D"text/html; charset=3Diso-8859-1">
<META content=3D"MSHTML 5.50.4134.600" name=3DGENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY bgColor=3D#ffffff>
<DIV><FONT face=3DArial size=3D2>I wanted to compile python will an install=
ed=20
postgres package ...can i do it .How?</FONT></DIV>
<DIV><FONT face=3DArial size=3D2>TIA</FONT></DIV>
<DIV><FONT face=3DArial size=3D2>Sharmad</FONT></DIV></BODY></HTML>

------=_NextPart_000_001A_01C08020.013911C0--

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message Stephan Szabo 2001-01-16 18:41:13 Re: deferred constraints failing on commit
Previous Message Stephan Szabo 2001-01-16 16:52:18 Re: deferred constraints failing on commit