RE: [EXTERNAL] BUG #18498: Locking a table from a trigger (with using two sessions) causes a deadlock: the bug or the feature? - Mailing list pgsql-bugs

Functions are marked Parallel UNSAFE by default.

I'm not sure if you can make the trigger unsafe, but if the trigger is called from the function, make sure you didn't
addPARALLEL SAFE.
 

-----Original Message-----
From: PG Bug reporting form <noreply@postgresql.org> 
Sent: Friday, June 7, 2024 12:55 AM
To: pgsql-bugs@lists.postgresql.org
Cc: d.koval@postgrespro.ru
Subject: [EXTERNAL] BUG #18498: Locking a table from a trigger (with using two sessions) causes a deadlock: the bug or
thefeature?
 

The following bug has been logged on the website:

Bug reference:      18498
Logged by:          Dmitry Koval
Email address:      d.koval@postgrespro.ru
PostgreSQL version: 17beta1
Operating system:   Ubuntu 22.04
Description:        

Hi!

I see strange behavior of the LOCK TABLE command when executed from a trigger.
If LOCK TABLE (for the same table) is executed from a trigger in two different sessions, then there is a possibility of
a“deadlock detected”
 
error.
Is this correct? (I'm guessing the correct behavior is this one: one session getting a table lock, and another session
waitsfor the first session's transaction to complete.)
 

Example.
--------
1) I wrote a tap-test to reproduce the problem (see attached file v1-0001-LOCK-TABLE-with-deadlocks.patch in next
email).
After applying the patch you need to run the test using the command

PROVE_TESTS=t/006_parallel_inserts.pl make check -C src/test/modules/test_misc

Test explanations.
------------------
The test creates BEFORE INSERT trigger. Trigger function contains rows:

  RAISE LOG 'Before lock (pid %)', pg_backend_pid();
  LOCK TABLE test;
  RAISE LOG 'After lock (pid %)', pg_backend_pid();

The test starts 4 threads that insert records, causing the trigger.
As a result of running the test, we see several "deadlock detected" errors with debug messages in the server log, like
these:

[219562] 006_parallel_inserts.pl LOG:  Before lock (pid 219562) [219562] 006_parallel_inserts.pl CONTEXT:  PL/pgSQL
functiontest_func() line 5 at RAISE [219562] 006_parallel_inserts.pl STATEMENT:  INSERT INTO test VALUES (10,
'worker_2');[219563] 006_parallel_inserts.pl LOG:  Before lock (pid 219563) [219563] 006_parallel_inserts.pl CONTEXT:
PL/pgSQLfunction test_func() line 5 at RAISE [219563] 006_parallel_inserts.pl STATEMENT:  INSERT INTO test VALUES (11,
'worker_1');[219563] 006_parallel_inserts.pl ERROR:  deadlock detected [219563] 006_parallel_inserts.pl DETAIL:
Process219563 waits for AccessExclusiveLock on relation 16384 of database 5; blocked by process 219562.
 
    Process 219562 waits for AccessExclusiveLock on relation 16384 of database 5; blocked by process 219563.
    Process 219563: INSERT INTO test VALUES (11, 'worker_1');
    Process 219562: INSERT INTO test VALUES (10, 'worker_2'); [219563] 006_parallel_inserts.pl HINT:  See server log
forquery details.
 
[219563] 006_parallel_inserts.pl CONTEXT:  SQL statement "LOCK TABLE test"
    PL/pgSQL function test_func() line 6 at SQL statement

As we can seen, two sessions 219562 and 2195623 received an AccessExclusiveLock lock on the the same table, after which
adeadlock was immediately diagnosed.
 
----

2) I have attached a similar tap-test using the function, but without using the trigger (file
v1-0001-LOCK-TABLE-wo-errors.patchin next email).
 
When running this test, no "deadlock detected" errors occur.
It's strange because the tests are similar.
----

The main question: is the "deadlock detected" error (1) correct?
(I.e. is it necessary to look for the reason for this behavior?)

--
With best regards,
Dmitry Koval

Postgres Professional:
https://urldefense.com/v3/__http://postgrespro.com__;!!GFE8dS6aclb0h1nkhPf9!6wjoXl3ClfzeTnLlOt9xRDNi77L9TyDNX5UxN67fv3IodNX_NpA_FNIR2MScTXKzq1cVNqCP1G4b23inNxUG7mlZ5cIQes8BpQ$



----------------------------------------------------------------------
CONFIDENTIALITY NOTICE: If you have received this email in error, please immediately notify the sender by e-mail at the
addressshown. This email transmission may contain confidential information.  This information is intended only for the
useof the individual(s) or entity to whom it is intended even if addressed incorrectly.  Please delete it from your
filesif you are not the intended recipient.  Thank you for your compliance.  Copyright (c) 2024 Evernorth