Обсуждение: CASCADE/fkey order
Hi all,
We've got an interesting case where we want deletes to cascade if one table was hit directly, but not another. We can show that the delete _would_ cascade from one foreign key relationship, but the delete is actually blocked by the foreign key constraint from the other relationship.
A sort of simplified view of the tables:
create table foo (
id integer primary key generated always as identity
);
create table bar (
id integer primary key generated always as identity
);
create table foo_bar (
foo_id integer not null,
bar_id integer not null,
primary key (foo_id, bar_id)
);
alter table foo_bar add constraint foo_bar_foo foreign key (foo_id) references foo(id) on delete cascade;
alter table foo_bar add constraint foo_bar_bar foreign key (bar_id) references bar(id);
create table baz (
id integer primary key generated always as identity,
foo_id integer not null
);
alter table baz add constraint baz_foo foreign key (foo_id) references foo(id) on delete cascade;
create table bazinga (
id integer primary key generated always as identity,
foo_id integer not null,
bar_id integer not null,
baz_id integer not null
);
alter table bazinga add constraint bazinga_foo_bar foreign key (foo_id, bar_id) references foo_bar (foo_id, bar_id);
alter table bazinga add constraint bazinga_baz foreign key (baz_id) references baz(id) on delete cascade;
What we wanted to happen:
delete from foo where id = 3;
-- cascades through the tree, deleting rows in bazinga through the baz_id relationship
delete from foo_bar where foo_id = 3 and bar_id = 1;
-- violates foreign key constraint bazinga_foo_bar
-- (this works as expected)
What actually happened:
delete from foo where id = 3;
-- violates foreign key constraint bazinga_foo_bar
How I've currently fixed it:
alter table bazinga add constraint bazinga_foo foreign key (foo_id) references foo(id) on delete cascade;
-- this foreign key relationship seems to be cascaded to earlier in the query
My questions:
What is the order of operations between cascading deletes and constraint checking? From what I can tell from the above, it seems like the delete cascades to each table in turn, and the constraints are checked at the time that the table is hit.
How do I know which table will be cascaded to first?
Is there a way to force the delete to cascade to tables in a specific order?
-Sam
https://github.com/nelsam
"As an adolescent I aspired to lasting fame, I craved factual certainty, and
I thirsted for a meaningful vision of human life -- so I became a scientist.
This is like becoming an archbishop so you can meet girls."
-- Matt Cartmill
"As an adolescent I aspired to lasting fame, I craved factual certainty, and
I thirsted for a meaningful vision of human life -- so I became a scientist.
This is like becoming an archbishop so you can meet girls."
-- Matt Cartmill
On Wed, Jul 22, 2020 at 8:24 AM Samuel Nelson <valczir.darkvein@gmail.com> wrote:
Is there a way to force the delete to cascade to tables in a specific order?
No really, but you can defer constraint checking.
David J.
I checked, and changing the `bazinga_foo_bar` constraint to:
alter table bazinga add constraint bazinga_foo_bar foreign key (foo_id, bar_id) references foo_bar (foo_id, bar_id) deferrable initially deferred;
seems to fix it to work as we were expecting. Is that particularly costly? Should I only set the constraint to be deferred when we really need it? Would it be more efficient to perform the deletes explicitly within a transaction rather than relying on the cascades and deferring that one constraint?
Our resident ex-Oracle DBA said that deferred constraints used to be heavily recommended against, but he also admitted that he hasn't kept up with that in the past 10 years.
-Sam
https://github.com/nelsam
"As an adolescent I aspired to lasting fame, I craved factual certainty, and
I thirsted for a meaningful vision of human life -- so I became a scientist.
This is like becoming an archbishop so you can meet girls."
-- Matt Cartmill
"As an adolescent I aspired to lasting fame, I craved factual certainty, and
I thirsted for a meaningful vision of human life -- so I became a scientist.
This is like becoming an archbishop so you can meet girls."
-- Matt Cartmill
On Wed, Jul 22, 2020 at 10:31 AM David G. Johnston <david.g.johnston@gmail.com> wrote:
On Wed, Jul 22, 2020 at 8:24 AM Samuel Nelson <valczir.darkvein@gmail.com> wrote:Is there a way to force the delete to cascade to tables in a specific order?No really, but you can defer constraint checking.David J.
On Wed, Jul 22, 2020 at 9:03 AM Samuel Nelson <valczir.darkvein@gmail.com> wrote:
seems to fix it to work as we were expecting. Is that particularly costly? Should I only set the constraint to be deferred when we really need it? Would it be more efficient to perform the deletes explicitly within a transaction rather than relying on the cascades and deferring that one constraint?
I don't know. I tend to go with only deferring the check if the specific transaction requires it. If there are no issues I would presume that checking at the end would be more efficient. But if there are problems you could end up performing unnecessary work. Memory consumption probably increases as well since constraint related information cannot be discarded as each command completes but must be kept around for the eventual validation.
David J.
You can benchmark your scenario with and without constraint using a tool like nancy:
it lets you A/B test different configurations with your own scenarios or using pgbench synthetic workloads.
-Michel
On Wed, Jul 22, 2020 at 9:27 AM Samuel Nelson <valczir.darkvein@gmail.com> wrote:
I checked, and changing the `bazinga_foo_bar` constraint to:alter table bazinga add constraint bazinga_foo_bar foreign key (foo_id, bar_id) references foo_bar (foo_id, bar_id) deferrable initially deferred;seems to fix it to work as we were expecting. Is that particularly costly? Should I only set the constraint to be deferred when we really need it? Would it be more efficient to perform the deletes explicitly within a transaction rather than relying on the cascades and deferring that one constraint?Our resident ex-Oracle DBA said that deferred constraints used to be heavily recommended against, but he also admitted that he hasn't kept up with that in the past 10 years.-Samhttps://github.com/nelsam
"As an adolescent I aspired to lasting fame, I craved factual certainty, and
I thirsted for a meaningful vision of human life -- so I became a scientist.
This is like becoming an archbishop so you can meet girls."
-- Matt CartmillOn Wed, Jul 22, 2020 at 10:31 AM David G. Johnston <david.g.johnston@gmail.com> wrote:On Wed, Jul 22, 2020 at 8:24 AM Samuel Nelson <valczir.darkvein@gmail.com> wrote:Is there a way to force the delete to cascade to tables in a specific order?No really, but you can defer constraint checking.David J.