resolution order for foreign key actions?

On 11/08/2016 12:08 PM, Karl Czajkowski wrote:

Hi,

Is there a formal definition for the order in which constraint actions
(i.e. the ON DELETE or ON UPDATE rules) are applied when there are
multiple overlapping/relevant constraints?

I have struggled to find an answer in the manual, but my experiments
suggest that they are interpreted in the order in which the
constraints were defined and the first rule in this order is applied
while subsequent rules are ignored. This can be very confusing if one
rule says CASCADE and another NO ACTION, and you need to understand
this order of definition to know whether a delete will cascade or
raise an error.

Can you provide an example?

Is there a definitive way to introspect the informatation_schema or
pg_catalog to determine which behaviors will effectively apply to a
given "DELETE FROM ..." or "UPDATE ..." statement?

Just to clear you are talking about FK constraints, correct?

AFAIK they are just a form of trigger and the rules they follow can be
found here:

https://www.postgresql.org/docs/9.5/static/sql-createtrigger.html

"If multiple triggers of the same kind are defined for the same event,
they will be fired in alphabetical order by name."

There is more, so I would read through the whole thing.

Thanks,

Karl

--
Adrian Klaver
adrian.klaver@aklaver.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On Tue, Nov 8, 2016 at 1:20 PM, Adrian Klaver <adrian.klaver@aklaver.com>
wrote:

On 11/08/2016 12:08 PM, Karl Czajkowski wrote:

Hi,

Is there a formal definition for the order in which constraint actions
(i.e. the ON DELETE or ON UPDATE rules) are applied when there are
multiple overlapping/relevant constraints?

I have struggled to find an answer in the manual, but my experiments
suggest that they are interpreted in the order in which the
constraints were defined and the first rule in this order is applied
while subsequent rules are ignored. This can be very confusing if one
rule says CASCADE and another NO ACTION, and you need to understand
this order of definition to know whether a delete will cascade or
raise an error.

Can you provide an example?

​Karl,​

​Yes, please, but...

ON DELETE starts with the "one" side of a one-to-many relationship​. When
deleting the one row the question is what should be done with the many rows
which refer to it. If ALL of the many rows agree to be deleted then the
one row in question can go away and no error is raised. If ANY of the many
rows refuse to die then the one row in question must remain in order to
maintain referential integrity - thus an error will be raised.

​ANY/ALL logic generally shouldn't depend on the order in which the
triggers fire though I suppose you could possible setups a convoluted
series of FKs that would cause it to do so. Its hard to imagine one so if
you have a ready example that would help.

Note, I'm going off of logic here - hopefully the SQL Standards Committee
hasn't gotten to deeply involved in this area :)

David J.​

On Nov 08, David G. Johnston modulated:
...

ON DELETE starts with the "one" side of a one-to-many relationship​. 
When deleting the one row the question is what should be done with the
many rows which refer to it.  If ALL of the many rows agree to be
deleted then the one row in question can go away and no error is
raised.  If ANY of the many rows refuse to die then the one row in
question must remain in order to maintain referential integrity - thus
an error will be raised.

I think I had the same intuition going into this.

However, I am testing with an artificial scenario to focus on the
ordering/precedence behavior. I was even hoping PostgreSQL might raise
an error when I created apparently conflicting constraints, but
unfortunately it does something much more confusing...

You might consider this to explore what happens if someone
accidentally redefines constraints with conflicting actions. I just
redefine the same constraint with only varying constraint name and ON
DELETE clause.

Here, I have assigned constraint names to demonstrate that the rules
are NOT applied based on a lexicographic sort of constraint names but
rather on order of definition (perhaps there is another
internally-generated name that sorts in order of definition?):

======================================
ALTER TABLE refs ADD CONSTRAINT z FOREIGN KEY (t_id) REFERENCES targets (id) ON DELETE NO ACTION;
ALTER TABLE
ALTER TABLE refs ADD CONSTRAINT y FOREIGN KEY (t_id) REFERENCES targets (id) ON DELETE SET NULL;
ALTER TABLE
ALTER TABLE refs ADD CONSTRAINT x FOREIGN KEY (t_id) REFERENCES targets (id) ON DELETE CASCADE;
ALTER TABLE

This test will show that ON DELETE NO ACTION is in effect due to constraint z.

Table "public.refs"
Column | Type | Modifiers
--------+---------+---------------------------------------------------
id | integer | not null default nextval('refs_id_seq'::regclass)
t_id | integer | not null
Indexes:
"refs_pkey" PRIMARY KEY, btree (id)
Foreign-key constraints:
"x" FOREIGN KEY (t_id) REFERENCES targets(id) ON DELETE CASCADE
"y" FOREIGN KEY (t_id) REFERENCES targets(id) ON DELETE SET NULL
"z" FOREIGN KEY (t_id) REFERENCES targets(id)

-------------
Content of refs table before deletion of target:
SELECT * FROM refs;
id | t_id
----+------
1 | 1
2 | 2
3 | 3
(3 rows)

-------------
Attempting to delete a target:
DELETE FROM targets WHERE name = 'foo1' RETURNING *;
ERROR: update or delete on table "targets" violates foreign key constraint "z" on table "refs"
DETAIL: Key (id)=(2) is still referenced from table "refs".

The attached BASH script will perform a sequence of tests defining the
constraints in different orders and showing the results. The excerpt
above is from the first test scenario.

It accepts optional arguments which are passed to 'psql' and can run
with no arguments if you can talk to your default DB with 'psql'
absent of any arguments, i.e. with Unix domain socket authentication.
It only creates and destroys tables public.targets and public.refs...

Thanks,

Karl