Re: deferred constraints failing on commit - Mailing list 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