|
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 |
|
