From: | George Woodring <george(dot)woodring(at)iglass(dot)net> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | pgsql-general <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: foreign key on delete cascade order? |
Date: | 2022-03-11 14:48:45 |
Message-ID: | CACi+J=R1DLsGUgc9dnKwdLbwUUCAftbxa_Z4ZUeVDJ+hU=5bxA@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On Thu, Mar 10, 2022 at 12:38 PM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)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>):
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
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2022-03-11 15:01:12 | Re: Am I in the same transaction block in complex PLPGSQL? |
Previous Message | hubert depesz lubaczewski | 2022-03-11 10:57:02 | Re: Postgres query |