Hi,
During the PGDAY.Spain'2024 Yurii Rashkovskii found out boring behaviour
related to functions versioning:
If you open a transaction in one session and execute the function, then
replace this function with a new version or drop it in a concurrent
session; the first session doesn't see any changes: it will use the old
version of the function in the following query.
If you execute the function without explicit transaction, subsequent
query execution will employ the new version of the function.
It happens because of documented behaviour [1], which doesn't guarantee
isolation levels for internal access to the system catalogues. But it
looks more consistent for a user to see changes the same way with and
without explicit transactions, doesn't it? At least, an extension
developer may be confident that after updating to a new version, no one
user will employ the old version of the extension's function and not
think about the consequences it may cause.
I don't know whether to classify this as a bug. The sketch of the patch
with an example isolation test is attached.
[1] https://www.postgresql.org/docs/16/mvcc-caveats.html
--
regards, Andrei Lepikhov
From 67b15acb4db396a24deab0efefb602227050ed63 Mon Sep 17 00:00:00 2001
From: "Andrei V. Lepikhov" <lepi...@gmail.com>
Date: Wed, 11 Sep 2024 21:16:59 +0200
Subject: [PATCH] Accept invalidation messages before the start of a query in
transaction
---
src/backend/tcop/postgres.c | 3 +++
.../isolation/expected/function-replace.out | 20 ++++++++++++++
src/test/isolation/isolation_schedule | 1 +
.../isolation/specs/function-replace.spec | 26 +++++++++++++++++++
4 files changed, 50 insertions(+)
create mode 100644 src/test/isolation/expected/function-replace.out
create mode 100644 src/test/isolation/specs/function-replace.spec
diff --git a/src/backend/tcop/postgres.c b/src/backend/tcop/postgres.c
index 8bc6bea113..4ed8d6f786 100644
--- a/src/backend/tcop/postgres.c
+++ b/src/backend/tcop/postgres.c
@@ -73,6 +73,7 @@
#include "tcop/utility.h"
#include "utils/guc_hooks.h"
#include "utils/injection_point.h"
+#include "utils/inval.h"
#include "utils/lsyscache.h"
#include "utils/memutils.h"
#include "utils/ps_status.h"
@@ -2774,6 +2775,8 @@ start_xact_command(void)
!get_timeout_active(CLIENT_CONNECTION_CHECK_TIMEOUT))
enable_timeout_after(CLIENT_CONNECTION_CHECK_TIMEOUT,
client_connection_check_interval);
+
+ AcceptInvalidationMessages();
}
static void
diff --git a/src/test/isolation/expected/function-replace.out
b/src/test/isolation/expected/function-replace.out
new file mode 100644
index 0000000000..b1393be4bf
--- /dev/null
+++ b/src/test/isolation/expected/function-replace.out
@@ -0,0 +1,20 @@
+Parsed test spec with 2 sessions
+
+starting permutation: s1_exec_fn s2_replace s1_exec_fn s2_drop s1_exec_fn
s1_end
+step s1_exec_fn: SELECT test_fn();
+test_fn
+-------
+ 1
+(1 row)
+
+step s2_replace: CREATE OR REPLACE FUNCTION test_fn() RETURNS integer LANGUAGE
sql AS $$ SELECT 2; $$;
+step s1_exec_fn: SELECT test_fn();
+test_fn
+-------
+ 2
+(1 row)
+
+step s2_drop: DROP FUNCTION test_fn;
+step s1_exec_fn: SELECT test_fn();
+ERROR: function test_fn() does not exist
+step s1_end: END;
diff --git a/src/test/isolation/isolation_schedule
b/src/test/isolation/isolation_schedule
index 143109aa4d..91a8ad67a6 100644
--- a/src/test/isolation/isolation_schedule
+++ b/src/test/isolation/isolation_schedule
@@ -115,3 +115,4 @@ test: serializable-parallel-2
test: serializable-parallel-3
test: matview-write-skew
test: lock-nowait
+test: function-replace
diff --git a/src/test/isolation/specs/function-replace.spec
b/src/test/isolation/specs/function-replace.spec
new file mode 100644
index 0000000000..54ce156d29
--- /dev/null
+++ b/src/test/isolation/specs/function-replace.spec
@@ -0,0 +1,26 @@
+#
+# Accept invalidation messages before the start of a new query inside the
+# transaction
+#
+
+setup
+{
+ CREATE FUNCTION test_fn() RETURNS integer LANGUAGE sql AS $$ SELECT 1; $$;
+}
+
+session s1
+setup { BEGIN; }
+step s1_exec_fn { SELECT test_fn(); }
+step s1_end { END; }
+
+session s2
+step s2_drop { DROP FUNCTION test_fn; }
+step s2_replace { CREATE OR REPLACE FUNCTION test_fn() RETURNS integer
LANGUAGE sql AS $$ SELECT 2; $$; }
+
+permutation
+ s1_exec_fn
+ s2_replace
+ s1_exec_fn
+ s2_drop
+ s1_exec_fn
+ s1_end
\ No newline at end of file
--
2.46.0
