pg.ddx.io pgsql-bugs@postgresql.org mailing list archive help / color / mirror / Atom feed From: Dmitry Koval <d.koval@postgrespro.ru>
To: pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #18498: Locking a table from a trigger (with using two sessions) causes a deadlock: the bug or the feature?
Date: Fri, 7 Jun 2024 11:47:02 +0300
Message-ID: <3a6d9e93-3c29-4fc4-b41d-edec72937db9@postgrespro.ru> (raw )
In-Reply-To: <18498-b95f3ee4c156de45@postgresql.org >
References: <18498-b95f3ee4c156de45@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
view thread (6+ messages) latest in thread
Message-ID: <3a6d9e93-3c29-4fc4-b41d-edec72937db9@postgrespro.ru>
Permalink: ../3a6d9e93-3c29-4fc4-b41d-edec72937db9@postgrespro.ru/
Also on: postgresql.org/message-id/3a6d9e93-3c29-4fc4-b41d-edec72937db9@postgrespro.ru
copy link · copy postgr.es · copy git am
reply Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: d.koval@postgrespro.ru, pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #18498: Locking a table from a trigger (with using two sessions) causes a deadlock: the bug or the feature?
In-Reply-To: <3a6d9e93-3c29-4fc4-b41d-edec72937db9@postgrespro.ru>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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