From: Michael Richards <michael@fastmail.ca>
To: sszabo@megazone23.bigpanda.com
Cc: pgsql-sql@postgresql.org
Subject: Re: deferred constraints failing on commit
Date: Tue, 16 Jan 2001 13:05:43 -0500 (EST)
Message-ID: <3A648D77.000021.30958@frodo.searchcanada.ca> (raw)
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@postgresql.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@postgresql.org>; Tue, 16 Jan 2001 14:55:05 -0500 (EST)
(envelope-from sharmad@im.eth.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@SharmadNaik>
From: "Sharmad Naik" <sharmad@im.eth.net>
To: "PGSQL-SQL" <pgsql-sql@postgresql.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--
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-sql@postgresql.org
Cc: michael@fastmail.ca, sszabo@megazone23.bigpanda.com
Subject: Re: deferred constraints failing on commit
In-Reply-To: <3A648D77.000021.30958@frodo.searchcanada.ca>
References: <3A648D77.000021.30958@frodo.searchcanada.ca>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox