Repeated, never-ending deadlock - Mailing list pgsql-general

Hi All,

OK, apologies in advance, I feel like this is a rather unintelligent
question that I probably know the answer to, but I am banging my head
against a wall trying to understand what is happening.

I have an ETL program, written in Python, that uses mutiple processes
to read and load a bunch of files into a database.  Sometimes
(especially early on, when the database is new) common information
that occurs in several files, and which is normalized out into
sub-tables, causes deadlocks.  That's OK; the code should receive an
exception, back off, try again, reading first, etc.  And I see
exceptions occuring in the Python code log as expected.

However, today, I saw the same deadlock error repeated in the Postgres
log every second for minutes.  The same two processes, with the same
data, and the same tables.  I can't work out how this can happen
without a crazy tight loop in the Python code that's just repeatedly
trying to commit a value (which afaik doesn't exist!)  A fragment of
the Postgres log is below.  The Python logs show nothing.

So my question is - is there some circumstance where instead of
raising an error to the calling code, Postgres instead goes round in
circles?

An answer of "nope, it must be a bug in your code" is fine / expected.
It's just one of those days...

This is Postgres 14 with a fairly default config (except that logical
replication is enabled).

Thanks,
Andrew


2022-04-19 19:55:54.482 UTC [28560] ERROR:  deadlock detected
2022-04-19 19:55:54.482 UTC [28560] DETAIL:  Process 28560 waits for ShareLock on transaction 609318483; blocked by
process28559.
 
    Process 28559 waits for ShareLock on transaction 609318682; blocked by process 28560.
    Process 28560: INSERT INTO channel_nscl (net, sta, chan, loc) VALUES ('AZ', 'KNW', 'HHZ', '--') RETURNING
channel_nscl.rowid,channel_nscl.publisher
 
    Process 28559: INSERT INTO channel_nscl (net, sta, chan, loc) VALUES ('AZ', 'CRY', 'HNZ', '--') RETURNING
channel_nscl.rowid,channel_nscl.publisher
 
2022-04-19 19:55:54.482 UTC [28560] HINT:  See server log for query details.
2022-04-19 19:55:54.482 UTC [28560] CONTEXT:  while inserting index tuple (25,3) in relation "channel_nscl_nscl_idx"
2022-04-19 19:55:54.482 UTC [28560] STATEMENT:  INSERT INTO channel_nscl (net, sta, chan, loc) VALUES ('AZ', 'KNW',
'HHZ','--') RETURNING channel_nscl.rowid, channel_nscl.publisher
 
2022-04-19 19:55:55.488 UTC [28560] ERROR:  deadlock detected
2022-04-19 19:55:55.488 UTC [28560] DETAIL:  Process 28560 waits for ShareLock on transaction 609318483; blocked by
process28559.
 
    Process 28559 waits for ShareLock on transaction 609318682; blocked by process 28560.
    Process 28560: INSERT INTO channel_nscl (net, sta, chan, loc) VALUES ('AZ', 'KNW', 'HHZ', '--') RETURNING
channel_nscl.rowid,channel_nscl.publisher
 
    Process 28559: INSERT INTO channel_nscl (net, sta, chan, loc) VALUES ('AZ', 'CRY', 'HNZ', '--') RETURNING
channel_nscl.rowid,channel_nscl.publisher
 
2022-04-19 19:55:55.488 UTC [28560] HINT:  See server log for query details.
2022-04-19 19:55:55.488 UTC [28560] CONTEXT:  while inserting index tuple (25,4) in relation "channel_nscl_nscl_idx"
2022-04-19 19:55:55.488 UTC [28560] STATEMENT:  INSERT INTO channel_nscl (net, sta, chan, loc) VALUES ('AZ', 'KNW',
'HHZ','--') RETURNING channel_nscl.rowid, channel_nscl.publisher
 
2022-04-19 19:55:56.495 UTC [28560] ERROR:  deadlock detected
2022-04-19 19:55:56.495 UTC [28560] DETAIL:  Process 28560 waits for ShareLock on transaction 609318483; blocked by
process28559.
 
    Process 28559 waits for ShareLock on transaction 609318682; blocked by process 28560.
    Process 28560: INSERT INTO channel_nscl (net, sta, chan, loc) VALUES ('AZ', 'KNW', 'HHZ', '--') RETURNING
channel_nscl.rowid,channel_nscl.publisher
 
    Process 28559: INSERT INTO channel_nscl (net, sta, chan, loc) VALUES ('AZ', 'CRY', 'HNZ', '--') RETURNING
channel_nscl.rowid,channel_nscl.publisher
 
2022-04-19 19:55:56.495 UTC [28560] HINT:  See server log for query details.
2022-04-19 19:55:56.495 UTC [28560] CONTEXT:  while inserting index tuple (25,5) in relation "channel_nscl_nscl_idx"
2022-04-19 19:55:56.495 UTC [28560] STATEMENT:  INSERT INTO channel_nscl (net, sta, chan, loc) VALUES ('AZ', 'KNW',
'HHZ','--') RETURNING channel_nscl.rowid, channel_nscl.publisher