pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
deferred 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>
  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 16:52  Stephan Szabo <sszabo@megazone23.bigpanda.com>
  parent: Michael Richards <michael@fastmail.ca>
  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>
  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:41  Stephan Szabo <sszabo@megazone23.bigpanda.com>
  parent: Michael Richards <michael@fastmail.ca>
  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