Behavior of Oracle 11 + Postgres 9.6. *Common DDL*: create table master (id number primary key); create table detail (id number primary key, master number); alter table detail add foreign key (master) references master(id) on delete cascade;
*Oracle (2 sqlplus windows)*: tx1: set transaction isolation level read committed; insert into master(ID) values(1); tx2: set transaction isolation level serializable; tx1: commit; tx2: insert into detail(ID, master) values(1, 1); ERROR at line 1: ORA-08177: can't serialize access for this transaction *Postgres (2 psql windows):* tx1: begin transaction isolation level read committed; insert into master(ID) values(1); tx2: begin transaction isolation level serializable; tx1: commit; tx2: insert into detail(ID, master) values(1, 1); ERROR: insert or update on table "detail" violates foreign key constraint "il_master_fkey" DETAIL: Key (master)=(1) is not present in table "master". пт, 6 сент. 2019 г. в 22:38, Carlos H. Cantu <lis...@warmboot.com.br>: > Thanks Ann! > > So far all the opinions are that the currently behavior is wrong (or > inconsistent, at last). > > I'll be glad if someone who has other RDBMS installed can compare how > they behave with the exactly described scenario, and report the > results back here. Please pay attention in the transaction isolations > and make sure they are started/commit in the correct times. > > Thanks! > > Carlos > http://www.firebirdnews.org > FireBase - http://www.FireBase.com.br > > > AH> Cheers, > > > AH> Ann > > >> On Sep 6, 2019, at 8:24 AM, Mark Rotteveel <m...@lawinegevaar.nl> > wrote: > >> > >>> On 6-9-2019 01:46, Carlos H. Cantu wrote: > >>> I understand that there are other scenarios where the currently FK > >>> behavior is correct and makes sense, for example, in the case of > >>> avoiding deleting a master record with "commited but not visible > >>> childs", > > AH> Yes. Unique, Primary Key, and Foreign Key constraints are > AH> handled in a special omniscient mode to avoid concurrent, > AH> incompatible changes. Triggers and check constraints operate in > AH> the mode of the user transaction. > > >>> but for the reported example, the currently behavior looks > >>> incorrect, and for people with business logic implemented in triggers, > >>> it may/will lead to incorrect results. > >> > >> I think you're right. You should only be able to insert records that > reference records that are visible to your transaction. Given Tx2 started > before Tx1 committed, the effects from Tx1 aren't visible to your > transaction. Your insert in Tx2 should fail as the master record from Tx1 > doesn't exist from the perspective of Tx2. > > AH> Interesting. In the case of inserting a child, the master must > AH> be visible to the transaction doing the insert. In the case of > AH> deleting a master, the existence of a child - even if uncommitted must > block the delete. > >> > >>> Does anyone knows if this behavior is following the Standard? > >> > >> I don't think this behaviour is correct in view of the standard, but I > haven't looked it up. > >> > > AH> No, this behavior is not standard compliant. > > AH> Good luck, > > AH> Ann > > > > Firebird-Devel mailing list, web interface at > https://lists.sourceforge.net/lists/listinfo/firebird-devel >
Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
