pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedBUG #18498: Locking a table from a trigger (with using two sessions) causes a deadlock: the bug or the feature?
6+ messages / 4 participants
[nested] [flat]
* BUG #18498: Locking a table from a trigger (with using two sessions) causes a deadlock: the bug or the feature?
@ 2024-06-07 07:55 PG Bug reporting form <noreply@postgresql.org>
2024-06-07 08:47 ` Re: BUG #18498: Locking a table from a trigger (with using two sessions) causes a deadlock: the bug or the feature? Dmitry Koval <d.koval@postgrespro.ru>
2024-06-07 08:58 ` Re: BUG #18498: Locking a table from a trigger (with using two sessions) causes a deadlock: the bug or the feature? David Rowley <dgrowleyml@gmail.com>
2024-06-07 17:32 ` RE: [EXTERNAL] BUG #18498: Locking a table from a trigger (with using two sessions) causes a deadlock: the bug or the feature? Wetmore, Matthew (CTR) <Matthew.Wetmore@evernorth.com>
0 siblings, 3 replies; 6+ messages in thread
From: PG Bug reporting form @ 2024-06-07 07:55 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org; +Cc: d.koval@postgrespro.ru
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 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: http://postgrespro.com
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: BUG #18498: Locking a table from a trigger (with using two sessions) causes a deadlock: the bug or the feature?
2024-06-07 07:55 BUG #18498: Locking a table from a trigger (with using two sessions) causes a deadlock: the bug or the feature? PG Bug reporting form <noreply@postgresql.org>
@ 2024-06-07 08:47 ` Dmitry Koval <d.koval@postgrespro.ru>
2 siblings, 0 replies; 6+ messages in thread
From: Dmitry Koval @ 2024-06-07 08:47 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org
Additional attachments for previous email.
--
With best regards,
Dmitry Koval
Postgres Professional: http://postgrespro.com
Attachments: download all (2 files, .tar.gz)
v1-0001-LOCK-TABLE-with-deadlocks.patch (text/plain, 2.3K) download
From 0b2f83e079b500370784ae4838b247ab72cc2e82 Mon Sep 17 00:00:00 2001
From: Koval Dmitry <d.koval@postgrespro.ru>
Date: Thu, 6 Jun 2024 19:53:15 +0300
Subject: [PATCH v1] LOCK TABLE with deadlocks
---
.../test_misc/t/006_parallel_inserts.pl | 77 +++++++++++++++++++
1 file changed, 77 insertions(+)
create mode 100644 src/test/modules/test_misc/t/006_parallel_inserts.pl
diff --git a/src/test/modules/test_misc/t/006_parallel_inserts.pl b/src/test/modules/test_misc/t/006_parallel_inserts.pl
new file mode 100644
index 0000000000..e8185ea188
--- /dev/null
+++ b/src/test/modules/test_misc/t/006_parallel_inserts.pl
@@ -0,0 +1,77 @@
+use strict;
+use warnings;
+
+use threads;
+use IPC::Run;
+use PostgreSQL::Test::Cluster;
+use PostgreSQL::Test::Utils;
+use Test::More;
+
+my $THREADS = 4;
+my $ROWS = 365;
+
+# Initialize new node.
+my $node = PostgreSQL::Test::Cluster->new("node");
+$node->init();
+$node->start;
+
+# Initial queries.
+$node->safe_psql("postgres", "
+CREATE TABLE test(i int, t text);
+CREATE TABLE test2(i int, t text);
+
+CREATE FUNCTION test_func() RETURNS trigger AS
+\$BODY\$
+DECLARE
+ q text;
+BEGIN
+ RAISE LOG 'Before lock (pid %)', pg_backend_pid();
+ LOCK TABLE test;
+ RAISE LOG 'After lock (pid %)', pg_backend_pid();
+ q = 'INSERT INTO test2 SELECT ((\$1)::test).*';
+ EXECUTE q USING NEW;
+ RETURN NULL;
+END;
+\$BODY\$ LANGUAGE plpgsql SECURITY DEFINER;
+
+CREATE TRIGGER test_trigger BEFORE INSERT ON test FOR EACH ROW EXECUTE PROCEDURE test_func();
+");
+
+# Invoke psql.
+sub psql_worker
+{
+ my ($worker_name) = @_;
+
+ for (my $j = 1; $j <= $ROWS; $j += 1)
+ {
+ my $query = qq[INSERT INTO test VALUES ($j, '$worker_name');];
+ my @cmd = ['psql', '-XAtq', '-d', $node->connstr('postgres'), '-v', 'ON_ERROR_STOP=1', '-c', $query];
+
+ print("# Executing: $query\n");
+ IPC::Run::run @cmd;
+ }
+}
+
+# Start the workers.
+my @workers;
+for (my $i = 1; $i <= $THREADS; $i += 1)
+{
+ my $worker_name_i = "worker_$i";
+ print("# Worker: $worker_name_i\n");
+ push @workers, threads->create('psql_worker', $worker_name_i);
+}
+
+# Wait for the workers to finish.
+foreach my $thr (@workers)
+{
+ $thr->join();
+}
+
+# Check rows.
+my $psql_stdout;
+$node->psql('postgres', q(SELECT count(*) FROM test2;), stdout => \$psql_stdout);
+is($psql_stdout, $ROWS * $THREADS, 'rows amount');
+
+# Done.
+$node->stop;
+done_testing();
--
2.34.1
v1-0001-LOCK-TABLE-wo-errors.patch (text/plain, 2.2K) download
From 3f64ef12b777d49e37bf338fa4e1e99243255445 Mon Sep 17 00:00:00 2001
From: Koval Dmitry <d.koval@postgrespro.ru>
Date: Thu, 6 Jun 2024 19:53:15 +0300
Subject: [PATCH v1] LOCK TABLE wo errors
---
.../test_misc/t/006_parallel_inserts.pl | 74 +++++++++++++++++++
1 file changed, 74 insertions(+)
create mode 100644 src/test/modules/test_misc/t/006_parallel_inserts.pl
diff --git a/src/test/modules/test_misc/t/006_parallel_inserts.pl b/src/test/modules/test_misc/t/006_parallel_inserts.pl
new file mode 100644
index 0000000000..08967e5aa8
--- /dev/null
+++ b/src/test/modules/test_misc/t/006_parallel_inserts.pl
@@ -0,0 +1,74 @@
+use strict;
+use warnings;
+
+use threads;
+use IPC::Run;
+use PostgreSQL::Test::Cluster;
+use PostgreSQL::Test::Utils;
+use Test::More;
+
+my $THREADS = 4;
+my $ROWS = 365;
+
+# Initialize new node.
+my $node = PostgreSQL::Test::Cluster->new("node");
+$node->init();
+$node->start;
+
+# Initial queries.
+$node->safe_psql("postgres", "
+CREATE TABLE test(i int, t text);
+
+CREATE FUNCTION test_func(a_i int, a_t text) RETURNS void AS
+\$BODY\$
+DECLARE
+ q text;
+BEGIN
+ RAISE LOG 'Before lock (pid %)', pg_backend_pid();
+ LOCK TABLE test;
+ RAISE LOG 'After lock (pid %)', pg_backend_pid();
+ q = 'INSERT INTO test VALUES(' || a_i || ','' || a_t || '')';
+ EXECUTE q;
+END;
+\$BODY\$ LANGUAGE plpgsql SECURITY DEFINER;
+");
+
+# Invoke psql.
+sub psql_worker
+{
+ my ($worker_name) = @_;
+
+ for (my $j = 1; $j <= $ROWS; $j += 1)
+ {
+ my $query = "SELECT test_func($j, '$worker_name');";
+
+ my @cmd = ['psql', '-XAtq', '-d', $node->connstr('postgres'), '-v', 'ON_ERROR_STOP=1', '-c', $query];
+
+ print("# Executing: $query\n");
+ IPC::Run::run @cmd;
+ }
+}
+
+# Start the workers.
+my @workers;
+for (my $i = 1; $i <= $THREADS; $i += 1)
+{
+ my $worker_name_i = "worker_$i";
+ print("# Worker: $worker_name_i\n");
+ push @workers, threads->create('psql_worker', $worker_name_i);
+}
+
+# Wait for the workers to finish.
+foreach my $thr (@workers)
+{
+ $thr->join();
+}
+
+# Check rows.
+my $psql_stdout;
+$node->psql('postgres', q(SELECT count(*) FROM test;), stdout => \$psql_stdout);
+is($psql_stdout, $ROWS * $THREADS, 'rows amount');
+
+# Done.
+$node->stop;
+done_testing();
--
2.34.1
* Re: BUG #18498: Locking a table from a trigger (with using two sessions) causes a deadlock: the bug or the feature?
2024-06-07 07:55 BUG #18498: Locking a table from a trigger (with using two sessions) causes a deadlock: the bug or the feature? PG Bug reporting form <noreply@postgresql.org>
@ 2024-06-07 08:58 ` David Rowley <dgrowleyml@gmail.com>
2024-06-07 09:02 ` Re: BUG #18498: Locking a table from a trigger (with using two sessions) causes a deadlock: the bug or the feature? David Rowley <dgrowleyml@gmail.com>
2 siblings, 1 reply; 6+ messages in thread
From: David Rowley @ 2024-06-07 08:58 UTC (permalink / raw)
To: d.koval@postgrespro.ru; pgsql-bugs@lists.postgresql.org
On Fri, 7 Jun 2024 at 20:35, PG Bug reporting form
<noreply@postgresql.org> wrote:
> The main question: is the "deadlock detected" error (1) correct?
> (I.e. is it necessary to look for the reason for this behavior?)
Looks like a fairly standard lock upgrade problem to me.
s1: create table t ();
s1: begin;
s2: begin;
s1: lock table t in access share mode;
s2: lock table t in access share mode;
s1: lock table t in access exclusive mode;
s2: lock table t in access exclusive mode; -- deadlock
Isn't this more or less the same as what's going on in your scenario?
David
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: BUG #18498: Locking a table from a trigger (with using two sessions) causes a deadlock: the bug or the feature?
2024-06-07 07:55 BUG #18498: Locking a table from a trigger (with using two sessions) causes a deadlock: the bug or the feature? PG Bug reporting form <noreply@postgresql.org>
2024-06-07 08:58 ` Re: BUG #18498: Locking a table from a trigger (with using two sessions) causes a deadlock: the bug or the feature? David Rowley <dgrowleyml@gmail.com>
@ 2024-06-07 09:02 ` David Rowley <dgrowleyml@gmail.com>
2024-06-07 09:45 ` Re: BUG #18498: Locking a table from a trigger (with using two sessions) causes a deadlock: the bug or the feature? Dmitry Koval <d.koval@postgrespro.ru>
0 siblings, 1 reply; 6+ messages in thread
From: David Rowley @ 2024-06-07 09:02 UTC (permalink / raw)
To: d.koval@postgrespro.ru; pgsql-bugs@lists.postgresql.org
On Fri, 7 Jun 2024 at 20:58, David Rowley <dgrowleyml@gmail.com> wrote:
> s1: create table t ();
> s1: begin;
> s2: begin;
> s1: lock table t in access share mode;
> s2: lock table t in access share mode;
> s1: lock table t in access exclusive mode;
> s2: lock table t in access exclusive mode; -- deadlock
>
> Isn't this more or less the same as what's going on in your scenario?
I didn't explain why this needs to happen... s1 can't get the AEL on
"t" because it's blocked by s2's share lock. Subsequently, s2 then
must wait on s1's AEL request and that creates a deadlock cycle that
needs to be broken.
David
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: BUG #18498: Locking a table from a trigger (with using two sessions) causes a deadlock: the bug or the feature?
2024-06-07 07:55 BUG #18498: Locking a table from a trigger (with using two sessions) causes a deadlock: the bug or the feature? PG Bug reporting form <noreply@postgresql.org>
2024-06-07 08:58 ` Re: BUG #18498: Locking a table from a trigger (with using two sessions) causes a deadlock: the bug or the feature? David Rowley <dgrowleyml@gmail.com>
2024-06-07 09:02 ` Re: BUG #18498: Locking a table from a trigger (with using two sessions) causes a deadlock: the bug or the feature? David Rowley <dgrowleyml@gmail.com>
@ 2024-06-07 09:45 ` Dmitry Koval <d.koval@postgrespro.ru>
0 siblings, 0 replies; 6+ messages in thread
From: Dmitry Koval @ 2024-06-07 09:45 UTC (permalink / raw)
To: David Rowley <dgrowleyml@gmail.com>; pgsql-bugs@lists.postgresql.org
David, thanks for the detailed explanation!
I missed the fact that there were two different locks.
Issue resolved.
--
With best regards,
Dmitry Koval
Postgres Professional: http://postgrespro.com
^ permalink raw reply [nested|flat] 6+ messages in thread
* RE: [EXTERNAL] BUG #18498: Locking a table from a trigger (with using two sessions) causes a deadlock: the bug or the feature?
2024-06-07 07:55 BUG #18498: Locking a table from a trigger (with using two sessions) causes a deadlock: the bug or the feature? PG Bug reporting form <noreply@postgresql.org>
@ 2024-06-07 17:32 ` Wetmore, Matthew (CTR) <Matthew.Wetmore@evernorth.com>
2 siblings, 0 replies; 6+ messages in thread
From: Wetmore, Matthew (CTR) @ 2024-06-07 17:32 UTC (permalink / raw)
To: d.koval@postgrespro.ru <d.koval@postgrespro.ru>; pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>
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@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 the feature?
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 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!6wjoXl3ClfzeTnLlOt9xRDNi...
----------------------------------------------------------------------
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
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2024-06-07 17:32 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2024-06-07 07:55 BUG #18498: Locking a table from a trigger (with using two sessions) causes a deadlock: the bug or the feature? PG Bug reporting form <noreply@postgresql.org>
2024-06-07 08:47 ` Dmitry Koval <d.koval@postgrespro.ru>
2024-06-07 08:58 ` David Rowley <dgrowleyml@gmail.com>
2024-06-07 09:02 ` David Rowley <dgrowleyml@gmail.com>
2024-06-07 09:45 ` Dmitry Koval <d.koval@postgrespro.ru>
2024-06-07 17:32 ` Wetmore, Matthew (CTR) <Matthew.Wetmore@evernorth.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox