foreign key on delete cascade order?

George Woodring <george.woodring@iglass.net> writes:

When using FOREIGN KEY ON DELETE CASCADE, is there an order the entries are
being deleted?

No, there's no particular attempt to order the deletions. Each cascaded
delete ought to be removing a disjoint set of rows in the referencing
table, so I'm not quite sure why order should matter.

regards, tom lane

On Thu, Mar 10, 2022 at 10:56 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:

Each cascaded delete ought to be removing a disjoint set of rows in the
referencing

table, so I'm not quite sure why order should matter.

regards, tom lane

I have always thought the way to avoid deadlocks was to update rows in the
same order by the different updaters. Is there a better chain of thought
for updating and deleting rows at the same time? Do we need to put a lock
on the table to update, then have the delete queue up waiting for the lock
to be removed?

Thanks,
George

George Woodring <george.woodring@iglass.net> writes:

I have always thought the way to avoid deadlocks was to update rows in the
same order by the different updaters. Is there a better chain of thought
for updating and deleting rows at the same time? Do we need to put a lock
on the table to update, then have the delete queue up waiting for the lock
to be removed?

With the amount of detail you've provided (viz: none), it's impossible
for anyone to guess what your problem actually is, let alone speculate
on suitable solutions.

regards, tom lane

On Thu, Mar 10, 2022 at 12:38 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:

With the amount of detail you've provided (viz: none)

This is an example of the error we are seeing from our application. Sorry,
I cannot find the postgresql log entry for this one.

2020-11-30T13:16:08,835 ERROR [foo/bar/01EF2.W01E/55159]
GlobalControllerAdvice: Caught exception (
https://noc.iglass.net/networkMachDelete.htm
<https://noc.iglass.net/jglass/admin/networkMachDelete.htm&gt;):
org.springframework.dao.DeadlockLoserDataAccessException:
PreparedStatementCallback; SQL [DELETE FROM mach WHERE (machid=?)]; ERROR:
deadlock detected
Detail: Process 27442 waits for ShareLock on transaction 1895244982;
blocked by process 21064.
Process 21064 waits for ShareLock on transaction 1895245026; blocked by
process 27442.
Hint: See server log for query details.
Where: while deleting tuple (5,18) in relation "status"
SQL statement "DELETE FROM ONLY "public"."status" WHERE $1
OPERATOR(pg_catalog.=) "pollid""; nested exception is
org.postgresql.util.PSQLException: ERROR: deadlock detected
Detail: Process 27442 waits for ShareLock on transaction 1895244982;
blocked by process 21064.
Process 21064 waits for ShareLock on transaction 1895245026; blocked by
process 27442.
Hint: See server log for query details.
Where: while deleting tuple (5,18) in relation "status"
SQL statement "DELETE FROM ONLY "public"."status" WHERE $1
OPERATOR(pg_catalog.=) "pollid""
org.springframework.dao.DeadlockLoserDataAccessException:
PreparedStatementCallback; SQL [DELETE FROM mach WHERE (machid=?)]; ERROR:
deadlock detected
Detail: Process 27442 waits for ShareLock on transaction 1895244982;
blocked by process 21064.
Process 21064 waits for ShareLock on transaction 1895245026; blocked by
process 27442.
Hint: See server log for query details.
Where: while deleting tuple (5,18) in relation "status"
SQL statement "DELETE FROM ONLY "public"."status" WHERE $1
OPERATOR(pg_catalog.=) "pollid""; nested exception is
org.postgresql.util.PSQLException: ERROR: deadlock detected
Detail: Process 27442 waits for ShareLock on transaction 1895244982;
blocked by process 21064.
Process 21064 waits for ShareLock on transaction 1895245026; blocked by
process 27442.
Hint: See server log for query details.
Where: while deleting tuple (5,18) in relation "status"
SQL statement "DELETE FROM ONLY "public"."status" WHERE $1
OPERATOR(pg_catalog.=) "pollid""

The tables are involved are
CREATE TABLE mach ( machid serial, constraint mach_pkey primary key
(machid) ... );
CREATE TABLE pollgrpinfo ( pollgrpid serial, constraint pollgrpinfo_pkey
primary key (pollgrpid),
machid int4 NOT NULL, constraint mach_exists FOREIGN
KEY(machid) REFERENCES mach ON DELETE CASCADE, ... );
CREATE TABLE poll ( pollid serial, constraint poll_pkey primary key
(pollid),
pollgrpid int4 not null, constraint pollgrp_exists FOREIGN
KEY(pollgrpid) REFERENCES pollgrpinfo (pollgrpid) ON DELETE CASCADE, ...);
CREATE TABLE status ( statusid serial, constraint status_pkey primary key
(statusid),
pollid int4 not null, constraint poll_exists FOREIGN KEY(pollid)
REFERENCES poll ON DELETE CASCADE, ...);

We are updating the entire status table every 5 minutes with
BEGIN;
UPDATE status SET () WHERE pollid = $1;
COMMIT;

The issue is arriving when some does a DELETE during the UPDATE of status
DELETE FROM mach WHERE machid=$1;

I don't know if this sheds any more light on it.

George
iGLASS Networks

We are updating the entire status table every 5 minutes with
BEGIN;
UPDATE status SET () WHERE pollid = $1;
COMMIT;

The issue is arriving when some does a DELETE during the UPDATE of status
DELETE FROM mach WHERE machid=$1;

Could you set lock_timeout, lock table explicitly for SHARE UPDATE
EXCLUSIVE (pretty sure that would be the proper level), then retry if it
fails because a delete is already going on?

Also, are you confident that before you call 'begin' to do the update, you
are not already in a transaction which might have some lock on row(s) in
mach, or one of the other tables involved?

For the update processes, all other tables are read-only reference tables,
I don't think they would be locked.

iGLASS Networks
www.iglass.net

On Fri, Mar 11, 2022 at 10:03 PM Michael Lewis <mlewis@entrata.com> wrote:

Could you set lock_timeout, lock table explicitly for SHARE UPDATE
EXCLUSIVE (pretty sure that would be the proper level), then retry if it
fails because a delete is already going on?

I don't think I can take a lock on the table, there are multiple process
that update the status table. While each process does not overlap, I think
locking the table would cause them to block each other.
I think we would just have to retry the delete after the deadlock, which is
currently done manually by refreshing the web page. The update never seems
to be interupted, probably because it longer running and starts before the
random delete.

Also, are you confident that before you call 'begin' to do the update, you
are not already in a transaction which might have some lock on row(s) in
mach, or one of the other tables involved?

For the update processes, all other tables are read-only reference
tables, I don't think they would be locked.

Thank you for your help.
George