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
