pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feeddeferred constraints failing on commit
4+ messages / 2 participants
[nested] [flat]
* deferred constraints failing on commit
@ 2001-01-16 09:48 Michael Richards <michael@fastmail.ca>
2001-01-16 16:52 ` Re: deferred constraints failing on commit Stephan Szabo <sszabo@megazone23.bigpanda.com>
0 siblings, 1 reply; 4+ messages in thread
From: Michael Richards @ 2001-01-16 09:48 UTC (permalink / raw)
To: pgsql-sql
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,name)
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
From pgsql-sql-owner@postgresql.org Tue Jan 16 04:58:59 2001
Received: from smtp4.mail.yahoo.com (smtp4.mail.yahoo.com [128.11.69.101])
by mail.postgresql.org (8.11.1/8.11.1) with SMTP id f0G9wu073992
for <pgsql-sql@postgresql.org>; Tue, 16 Jan 2001 04:58:56 -0500 (EST)
(envelope-from janwieck@yahoo.com)
Received: from a5706.pppool.de (HELO jupiter.jw.home) (213.6.87.6)
by smtp.mail.vip.suc.yahoo.com with SMTP; 16 Jan 2001 09:57:42 -0000
X-Apparently-From: <janwieck@yahoo.com>
Received: (from janwieck@localhost)
by jupiter.jw.home (8.9.3/8.9.3) id FAA02051
for pgsql-sql@postgresql.org; Tue, 16 Jan 2001 05:03:12 -0500
From: Jan Wieck <janwieck@Yahoo.com>
Message-Id: <200101161003.FAA02051@jupiter.jw.home>
Subject: Re: Question #4 about PL/PGSQL
To: pgsql-sql@postgresql.org
Date: Tue, 16 Jan 2001 05:03:12 -0500 (EST)
X-Mailer: ELM [version 2.4ME+ PL68 (25)]
MIME-Version: 1.0
Content-Type: text/plain; charset=US-ASCII
Content-Transfer-Encoding: 7bit
X-Archive-Number: 200101/165
X-Sequence-Number: 826
Josh Berkus wrote:
> Folks,
>
> Oh, yes, one more:
>
> 4. If I pass a NULL to any of the parameters of a PL/PGSQL
> function, any (other) VARCHAR parameters are set to NULL as
> well.
Fixed in 7.1. Impossible to fix in 7.0.x or earlier due to
missing capabilities of the function manager.
Jan
--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck@Yahoo.com #
_________________________________________________________
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: deferred constraints failing on commit
2001-01-16 09:48 deferred constraints failing on commit Michael Richards <michael@fastmail.ca>
@ 2001-01-16 16:52 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
0 siblings, 0 replies; 4+ messages in thread
From: Stephan Szabo @ 2001-01-16 16:52 UTC (permalink / raw)
To: Michael Richards <michael@fastmail.ca>; +Cc: pgsql-sql
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,name)
> 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
>
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: deferred constraints failing on commit
@ 2001-01-16 18:05 Michael Richards <michael@fastmail.ca>
2001-01-16 18:41 ` Re: deferred constraints failing on commit Stephan Szabo <sszabo@megazone23.bigpanda.com>
0 siblings, 1 reply; 4+ messages in thread
From: Michael Richards @ 2001-01-16 18:05 UTC (permalink / raw)
To: sszabo@megazone23.bigpanda.com; +Cc: 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@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--
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: deferred constraints failing on commit
2001-01-16 18:05 Re: deferred constraints failing on commit Michael Richards <michael@fastmail.ca>
@ 2001-01-16 18:41 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
0 siblings, 0 replies; 4+ messages in thread
From: Stephan Szabo @ 2001-01-16 18:41 UTC (permalink / raw)
To: Michael Richards <michael@fastmail.ca>; +Cc: pgsql-sql
Okay, yep, seems like a garden variety bug to me...
What's happening is that the update trigger is checking to make sure
that there are no rows referencing the one that was changed, but that's
not sufficient for the deferred no action case possibly. It's got to be
that there are no rows that now fail the constraint after the update (no
rows that reference the one that has changed and do not reference a row
that does exist in the table). There is some confusion on the spec to
some details that we're still working out.
This has gotten mentioned on -hackers, but noone's been completely able
to determine what's supposed to happen for all of the combinations of
referential actions on these types of deferred cases.
On Tue, 16 Jan 2001, Michael Richards wrote:
> 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;
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2001-01-16 18:41 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-01-16 09:48 deferred constraints failing on commit Michael Richards <michael@fastmail.ca>
2001-01-16 16:52 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
2001-01-16 18:05 Re: deferred constraints failing on commit Michael Richards <michael@fastmail.ca>
2001-01-16 18:41 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
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