Received: from mail.interchange.ca (ns.interchange.ca [216.126.79.2]) by mail.postgresql.org (8.11.1/8.11.1) with ESMTP id f0G9mQ073318 for ; Tue, 16 Jan 2001 04:48:26 -0500 (EST) (envelope-from michael@fastmail.ca) Received: by mail.interchange.ca (Fastmailer, from userid 555) id 6CC242195; Tue, 16 Jan 2001 04:48:16 -0500 (EST) MIME-Version: 1.0 Message-Id: <3A6418E0.0000CB.62512@frodo.searchcanada.ca> To: pgsql-sql@postgresql.org Subject: deferred constraints failing on commit From: "Michael Richards" X-Fastmail-IP: 24.156.176.65 Date: Tue, 16 Jan 2001 04:48:16 -0500 (EST) Content-Transfer-Encoding: 7bit Content-Type: Text/Plain X-Archive-Number: 200101/164 X-Sequence-Number: 825 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: 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 ; 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: 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 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