pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Michael Richards <michael@fastmail.ca>
To: pgsql-sql@postgresql.org
Subject: deferred constraints failing on commit
Date: Tue, 16 Jan 2001 04:48:16 -0500 (EST)
Message-ID: <3A6418E0.0000CB.62512@frodo.searchcanada.ca> (raw)

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




view thread (4+ messages)  latest in thread

Message-ID: <3A6418E0.0000CB.62512@frodo.searchcanada.ca>
Permalink:  ../3A6418E0.0000CB.62512@frodo.searchcanada.ca/
Also on:    postgresql.org/message-id/3A6418E0.0000CB.62512@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
  Subject: Re: deferred constraints failing on commit
  In-Reply-To: <3A6418E0.0000CB.62512@frodo.searchcanada.ca>
  References: <3A6418E0.0000CB.62512@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