Обсуждение: deferred constraints failing on commit
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
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
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;
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 >