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

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 add PARALLEL SAFE.

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

The following bug has been logged on the website:

Bug reference: 18498
Logged by: Dmitry Koval
Email address: d(dot)koval(at)postgrespro(dot)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 waits for 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 function test_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/pgSQL function 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: Process 219563 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 for query 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 a deadlock 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.patch in 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 address shown. This email transmission may contain confidential information. This information is intended only for the use of the individual(s) or entity to whom it is intended even if addressed incorrectly. Please delete it from your files if you are not the intended recipient. Thank you for your compliance. Copyright (c) 2024 Evernorth