Restrict the use of temporary namespace in two-phase transactions

Attempting to use a temporary table within a two-phase transaction is
forbidden for ages.  However, there have been uncovered grounds for
a couple of other object types and commands which work on temporary
objects with two-phase commit.  In short, trying to create, lock or drop
an object on a temporary schema should not be authorized within a
two-phase transaction, as it would cause its state to create
dependencies with other sessions, causing all sorts of side effects with
the existing session or other sessions spawned later on trying to use
the same temporary schema name.

Regression tests are added to cover all the grounds found, the original
report mentioned function creation, but monitoring closer there are many
other patterns with LOCK, DROP or CREATE EXTENSION which are involved.
One of the symptoms resulting in combining both is that the session
which used the temporary schema is not able to shut down completely,
waiting for being able to drop the temporary schema, something that it
cannot complete because of the two-phase transaction involved with
temporary objects.  In this case the client is able to disconnect but
the session remains alive on the backend-side, potentially blocking
connection backend slots from being used.  Other problems reported could
also involve server crashes.

This is back-patched down to v10, which is where 9b013dc has introduced
MyXactFlags, something that this patch relies on.

Reported-by: Alexey Bashtanov
Author: Michael Paquier
Reviewed-by: Masahiko Sawada
Discussion: https://postgr.es/m/5d910e2e-0db8-ec06-dd5f-baec420513c3@imap.cc
Backpatch-through: 10

1 files changed, 29 insertions, 0 deletions

diff --git a/src/test/modules/test_extensions/sql/test_extensions.sql b/src/test/modules/test_extensions/sql/test_extensions.sql
index 9e64503eb50..f505466ab4e 100644
--- a/src/test/modules/test_extensions/sql/test_extensions.sql
+++ b/src/test/modules/test_extensions/sql/test_extensions.sql
@@ -64,3 +64,32 @@ end';
 
 -- dropping it should still work
 drop extension test_ext8;
+
+-- Test creation of extension in temporary schema with two-phase commit,
+-- which should not work.  This function wrapper is useful for portability.
+
+-- Avoid noise caused by CONTEXT and NOTICE messages including the temporary
+-- schema name.
+\set SHOW_CONTEXT never
+SET client_min_messages TO 'warning';
+-- First enforce presence of temporary schema.
+CREATE TEMP TABLE test_ext4_tab ();
+CREATE OR REPLACE FUNCTION create_extension_with_temp_schema()
+  RETURNS VOID AS $$
+  DECLARE
+    tmpschema text;
+    query text;
+  BEGIN
+    SELECT INTO tmpschema pg_my_temp_schema()::regnamespace;
+    query := 'CREATE EXTENSION test_ext4 SCHEMA ' || tmpschema || ' CASCADE;';
+    RAISE NOTICE 'query %', query;
+    EXECUTE query;
+  END; $$ LANGUAGE plpgsql;
+BEGIN;
+SELECT create_extension_with_temp_schema();
+PREPARE TRANSACTION 'twophase_extension';
+-- Clean up
+DROP TABLE test_ext4_tab;
+DROP FUNCTION create_extension_with_temp_schema();
+RESET client_min_messages;
+\unset SHOW_CONTEXT