pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
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--




view thread (4+ messages)  latest in thread

Message-ID: <3A648D77.000021.30958@frodo.searchcanada.ca>
Permalink:  ../3A648D77.000021.30958@frodo.searchcanada.ca/
Also on:    postgresql.org/message-id/3A648D77.000021.30958@frodo.searchcanada.ca

 · 

reply

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