Adding upstream version 13.4.upstream/13.4upstream

Signed-off-by: Daniel Baumann <daniel.baumann@progress-linux.org>

30 files changed, 706 insertions, 0 deletions

diff --git a/src/test/modules/test_extensions/.gitignore b/src/test/modules/test_extensions/.gitignore
new file mode 100644
index 0000000..5dcb3ff
--- /dev/null
+++ b/src/test/modules/test_extensions/.gitignore
@@ -0,0 +1,4 @@
+# Generated subdirectories
+/log/
+/results/
+/tmp_check/
diff --git a/src/test/modules/test_extensions/Makefile b/src/test/modules/test_extensions/Makefile
new file mode 100644
index 0000000..77ee4d5
--- /dev/null
+++ b/src/test/modules/test_extensions/Makefile
@@ -0,0 +1,26 @@
+# src/test/modules/test_extensions/Makefile
+
+MODULE = test_extensions
+PGFILEDESC = "test_extensions - regression testing for EXTENSION support"
+
+EXTENSION = test_ext1 test_ext2 test_ext3 test_ext4 test_ext5 test_ext6 \
+            test_ext7 test_ext8 test_ext_cyclic1 test_ext_cyclic2 \
+            test_ext_evttrig
+DATA = test_ext1--1.0.sql test_ext2--1.0.sql test_ext3--1.0.sql \
+       test_ext4--1.0.sql test_ext5--1.0.sql test_ext6--1.0.sql \
+       test_ext7--1.0.sql test_ext7--1.0--2.0.sql test_ext8--1.0.sql \
+       test_ext_cyclic1--1.0.sql test_ext_cyclic2--1.0.sql \
+       test_ext_evttrig--1.0.sql test_ext_evttrig--1.0--2.0.sql
+
+REGRESS = test_extensions test_extdepend
+
+ifdef USE_PGXS
+PG_CONFIG = pg_config
+PGXS := $(shell $(PG_CONFIG) --pgxs)
+include $(PGXS)
+else
+subdir = src/test/modules/test_extensions
+top_builddir = ../../../..
+include $(top_builddir)/src/Makefile.global
+include $(top_srcdir)/contrib/contrib-global.mk
+endif
diff --git a/src/test/modules/test_extensions/expected/test_extdepend.out b/src/test/modules/test_extensions/expected/test_extdepend.out
new file mode 100644
index 0000000..0b62015
--- /dev/null
+++ b/src/test/modules/test_extensions/expected/test_extdepend.out
@@ -0,0 +1,188 @@
+--
+-- test ALTER THING name DEPENDS ON EXTENSION
+--
+-- Common setup for all tests
+CREATE TABLE test_extdep_commands (command text);
+COPY test_extdep_commands FROM stdin;
+SELECT * FROM test_extdep_commands;
+                                 command                                 
+-------------------------------------------------------------------------
+  CREATE SCHEMA test_ext
+  CREATE EXTENSION test_ext5 SCHEMA test_ext
+  SET search_path TO test_ext
+  CREATE TABLE a (a1 int)
+ 
+  CREATE FUNCTION b() RETURNS TRIGGER LANGUAGE plpgsql AS               +
+    $$ BEGIN NEW.a1 := NEW.a1 + 42; RETURN NEW; END; $$
+  ALTER FUNCTION b() DEPENDS ON EXTENSION test_ext5
+ 
+  CREATE TRIGGER c BEFORE INSERT ON a FOR EACH ROW EXECUTE PROCEDURE b()
+  ALTER TRIGGER c ON a DEPENDS ON EXTENSION test_ext5
+ 
+  CREATE MATERIALIZED VIEW d AS SELECT * FROM a
+  ALTER MATERIALIZED VIEW d DEPENDS ON EXTENSION test_ext5
+ 
+  CREATE INDEX e ON a (a1)
+  ALTER INDEX e DEPENDS ON EXTENSION test_ext5
+  RESET search_path
+(17 rows)
+
+-- First, test that dependent objects go away when the extension is dropped.
+SELECT * FROM test_extdep_commands \gexec
+ CREATE SCHEMA test_ext
+ CREATE EXTENSION test_ext5 SCHEMA test_ext
+ SET search_path TO test_ext
+ CREATE TABLE a (a1 int)
+
+ CREATE FUNCTION b() RETURNS TRIGGER LANGUAGE plpgsql AS
+   $$ BEGIN NEW.a1 := NEW.a1 + 42; RETURN NEW; END; $$
+ ALTER FUNCTION b() DEPENDS ON EXTENSION test_ext5
+
+ CREATE TRIGGER c BEFORE INSERT ON a FOR EACH ROW EXECUTE PROCEDURE b()
+ ALTER TRIGGER c ON a DEPENDS ON EXTENSION test_ext5
+
+ CREATE MATERIALIZED VIEW d AS SELECT * FROM a
+ ALTER MATERIALIZED VIEW d DEPENDS ON EXTENSION test_ext5
+
+ CREATE INDEX e ON a (a1)
+ ALTER INDEX e DEPENDS ON EXTENSION test_ext5
+ RESET search_path
+-- A dependent object made dependent again has no effect
+ALTER FUNCTION test_ext.b() DEPENDS ON EXTENSION test_ext5;
+-- make sure we have the right dependencies on the extension
+SELECT deptype, p.*
+  FROM pg_depend, pg_identify_object(classid, objid, objsubid) AS p
+ WHERE refclassid = 'pg_extension'::regclass AND
+       refobjid = (SELECT oid FROM pg_extension WHERE extname = 'test_ext5')
+ORDER BY type;
+ deptype |       type        |  schema  | name |    identity     
+---------+-------------------+----------+------+-----------------
+ x       | function          | test_ext |      | test_ext.b()
+ x       | index             | test_ext | e    | test_ext.e
+ x       | materialized view | test_ext | d    | test_ext.d
+ x       | trigger           |          |      | c on test_ext.a
+(4 rows)
+
+DROP EXTENSION test_ext5;
+-- anything still depending on the table?
+SELECT deptype, i.*
+  FROM pg_catalog.pg_depend, pg_identify_object(classid, objid, objsubid) i
+WHERE refclassid='pg_class'::regclass AND
+ refobjid='test_ext.a'::regclass AND NOT deptype IN ('i', 'a');
+ deptype | type | schema | name | identity 
+---------+------+--------+------+----------
+(0 rows)
+
+DROP SCHEMA test_ext CASCADE;
+NOTICE:  drop cascades to table test_ext.a
+-- Second test: If we drop the table, the objects are dropped too and no
+-- vestige remains in pg_depend.
+SELECT * FROM test_extdep_commands \gexec
+ CREATE SCHEMA test_ext
+ CREATE EXTENSION test_ext5 SCHEMA test_ext
+ SET search_path TO test_ext
+ CREATE TABLE a (a1 int)
+
+ CREATE FUNCTION b() RETURNS TRIGGER LANGUAGE plpgsql AS
+   $$ BEGIN NEW.a1 := NEW.a1 + 42; RETURN NEW; END; $$
+ ALTER FUNCTION b() DEPENDS ON EXTENSION test_ext5
+
+ CREATE TRIGGER c BEFORE INSERT ON a FOR EACH ROW EXECUTE PROCEDURE b()
+ ALTER TRIGGER c ON a DEPENDS ON EXTENSION test_ext5
+
+ CREATE MATERIALIZED VIEW d AS SELECT * FROM a
+ ALTER MATERIALIZED VIEW d DEPENDS ON EXTENSION test_ext5
+
+ CREATE INDEX e ON a (a1)
+ ALTER INDEX e DEPENDS ON EXTENSION test_ext5
+ RESET search_path
+DROP TABLE test_ext.a;		-- should fail, require cascade
+ERROR:  cannot drop table test_ext.a because other objects depend on it
+DETAIL:  materialized view test_ext.d depends on table test_ext.a
+HINT:  Use DROP ... CASCADE to drop the dependent objects too.
+DROP TABLE test_ext.a CASCADE;
+NOTICE:  drop cascades to materialized view test_ext.d
+-- anything still depending on the extension?  Should be only function b()
+SELECT deptype, i.*
+  FROM pg_catalog.pg_depend, pg_identify_object(classid, objid, objsubid) i
+ WHERE refclassid='pg_extension'::regclass AND
+ refobjid=(SELECT oid FROM pg_extension WHERE extname='test_ext5');
+ deptype |   type   |  schema  | name |   identity   
+---------+----------+----------+------+--------------
+ x       | function | test_ext |      | test_ext.b()
+(1 row)
+
+DROP EXTENSION test_ext5;
+DROP SCHEMA test_ext CASCADE;
+-- Third test: we can drop the objects individually
+SELECT * FROM test_extdep_commands \gexec
+ CREATE SCHEMA test_ext
+ CREATE EXTENSION test_ext5 SCHEMA test_ext
+ SET search_path TO test_ext
+ CREATE TABLE a (a1 int)
+
+ CREATE FUNCTION b() RETURNS TRIGGER LANGUAGE plpgsql AS
+   $$ BEGIN NEW.a1 := NEW.a1 + 42; RETURN NEW; END; $$
+ ALTER FUNCTION b() DEPENDS ON EXTENSION test_ext5
+
+ CREATE TRIGGER c BEFORE INSERT ON a FOR EACH ROW EXECUTE PROCEDURE b()
+ ALTER TRIGGER c ON a DEPENDS ON EXTENSION test_ext5
+
+ CREATE MATERIALIZED VIEW d AS SELECT * FROM a
+ ALTER MATERIALIZED VIEW d DEPENDS ON EXTENSION test_ext5
+
+ CREATE INDEX e ON a (a1)
+ ALTER INDEX e DEPENDS ON EXTENSION test_ext5
+ RESET search_path
+SET search_path TO test_ext;
+DROP TRIGGER c ON a;
+DROP FUNCTION b();
+DROP MATERIALIZED VIEW d;
+DROP INDEX e;
+SELECT deptype, i.*
+  FROM pg_catalog.pg_depend, pg_identify_object(classid, objid, objsubid) i
+ WHERE (refclassid='pg_extension'::regclass AND
+        refobjid=(SELECT oid FROM pg_extension WHERE extname='test_ext5'))
+	OR (refclassid='pg_class'::regclass AND refobjid='test_ext.a'::regclass)
+   AND NOT deptype IN ('i', 'a');
+ deptype | type | schema | name | identity 
+---------+------+--------+------+----------
+(0 rows)
+
+DROP TABLE a;
+RESET search_path;
+DROP SCHEMA test_ext CASCADE;
+NOTICE:  drop cascades to extension test_ext5
+-- Fourth test: we can mark the objects as dependent, then unmark; then the
+-- drop of the extension does nothing
+SELECT * FROM test_extdep_commands \gexec
+ CREATE SCHEMA test_ext
+ CREATE EXTENSION test_ext5 SCHEMA test_ext
+ SET search_path TO test_ext
+ CREATE TABLE a (a1 int)
+
+ CREATE FUNCTION b() RETURNS TRIGGER LANGUAGE plpgsql AS
+   $$ BEGIN NEW.a1 := NEW.a1 + 42; RETURN NEW; END; $$
+ ALTER FUNCTION b() DEPENDS ON EXTENSION test_ext5
+
+ CREATE TRIGGER c BEFORE INSERT ON a FOR EACH ROW EXECUTE PROCEDURE b()
+ ALTER TRIGGER c ON a DEPENDS ON EXTENSION test_ext5
+
+ CREATE MATERIALIZED VIEW d AS SELECT * FROM a
+ ALTER MATERIALIZED VIEW d DEPENDS ON EXTENSION test_ext5
+
+ CREATE INDEX e ON a (a1)
+ ALTER INDEX e DEPENDS ON EXTENSION test_ext5
+ RESET search_path
+SET search_path TO test_ext;
+ALTER FUNCTION b() NO DEPENDS ON EXTENSION test_ext5;
+ALTER TRIGGER c ON a NO DEPENDS ON EXTENSION test_ext5;
+ALTER MATERIALIZED VIEW d NO DEPENDS ON EXTENSION test_ext5;
+ALTER INDEX e NO DEPENDS ON EXTENSION test_ext5;
+DROP EXTENSION test_ext5;
+DROP TRIGGER c ON a;
+DROP FUNCTION b();
+DROP MATERIALIZED VIEW d;
+DROP INDEX e;
+DROP SCHEMA test_ext CASCADE;
+NOTICE:  drop cascades to table a
diff --git a/src/test/modules/test_extensions/expected/test_extensions.out b/src/test/modules/test_extensions/expected/test_extensions.out
new file mode 100644
index 0000000..30ae621
--- /dev/null
+++ b/src/test/modules/test_extensions/expected/test_extensions.out
@@ -0,0 +1,161 @@
+-- test some errors
+CREATE EXTENSION test_ext1;
+ERROR:  required extension "test_ext2" is not installed
+HINT:  Use CREATE EXTENSION ... CASCADE to install required extensions too.
+CREATE EXTENSION test_ext1 SCHEMA test_ext1;
+ERROR:  schema "test_ext1" does not exist
+CREATE EXTENSION test_ext1 SCHEMA test_ext;
+ERROR:  schema "test_ext" does not exist
+CREATE SCHEMA test_ext;
+CREATE EXTENSION test_ext1 SCHEMA test_ext;
+ERROR:  extension "test_ext1" must be installed in schema "test_ext1"
+-- finally success
+CREATE EXTENSION test_ext1 SCHEMA test_ext CASCADE;
+NOTICE:  installing required extension "test_ext2"
+NOTICE:  installing required extension "test_ext3"
+NOTICE:  installing required extension "test_ext5"
+NOTICE:  installing required extension "test_ext4"
+SELECT extname, nspname, extversion, extrelocatable FROM pg_extension e, pg_namespace n WHERE extname LIKE 'test_ext%' AND e.extnamespace = n.oid ORDER BY 1;
+  extname  |  nspname  | extversion | extrelocatable 
+-----------+-----------+------------+----------------
+ test_ext1 | test_ext1 | 1.0        | f
+ test_ext2 | test_ext  | 1.0        | t
+ test_ext3 | test_ext  | 1.0        | t
+ test_ext4 | test_ext  | 1.0        | t
+ test_ext5 | test_ext  | 1.0        | t
+(5 rows)
+
+CREATE EXTENSION test_ext_cyclic1 CASCADE;
+NOTICE:  installing required extension "test_ext_cyclic2"
+ERROR:  cyclic dependency detected between extensions "test_ext_cyclic1" and "test_ext_cyclic2"
+DROP SCHEMA test_ext CASCADE;
+NOTICE:  drop cascades to 5 other objects
+DETAIL:  drop cascades to extension test_ext3
+drop cascades to extension test_ext5
+drop cascades to extension test_ext2
+drop cascades to extension test_ext4
+drop cascades to extension test_ext1
+CREATE EXTENSION test_ext6;
+DROP EXTENSION test_ext6;
+CREATE EXTENSION test_ext6;
+-- test dropping of member tables that own extensions:
+-- this table will be absorbed into test_ext7
+create table old_table1 (col1 serial primary key);
+create extension test_ext7;
+\dx+ test_ext7
+Objects in extension "test_ext7"
+      Object description       
+-------------------------------
+ sequence ext7_table1_col1_seq
+ sequence ext7_table2_col2_seq
+ sequence old_table1_col1_seq
+ table ext7_table1
+ table ext7_table2
+ table old_table1
+(6 rows)
+
+alter extension test_ext7 update to '2.0';
+\dx+ test_ext7
+Objects in extension "test_ext7"
+      Object description       
+-------------------------------
+ sequence ext7_table2_col2_seq
+ table ext7_table2
+(2 rows)
+
+-- test handling of temp objects created by extensions
+create extension test_ext8;
+-- \dx+ would expose a variable pg_temp_nn schema name, so we can't use it here
+select regexp_replace(pg_describe_object(classid, objid, objsubid),
+                      'pg_temp_\d+', 'pg_temp', 'g') as "Object description"
+from pg_depend
+where refclassid = 'pg_extension'::regclass and deptype = 'e' and
+  refobjid = (select oid from pg_extension where extname = 'test_ext8')
+order by 1;
+           Object description            
+-----------------------------------------
+ function ext8_even(posint)
+ function pg_temp.ext8_temp_even(posint)
+ table ext8_table1
+ table ext8_temp_table1
+ type posint
+(5 rows)
+
+-- Should be possible to drop and recreate this extension
+drop extension test_ext8;
+create extension test_ext8;
+select regexp_replace(pg_describe_object(classid, objid, objsubid),
+                      'pg_temp_\d+', 'pg_temp', 'g') as "Object description"
+from pg_depend
+where refclassid = 'pg_extension'::regclass and deptype = 'e' and
+  refobjid = (select oid from pg_extension where extname = 'test_ext8')
+order by 1;
+           Object description            
+-----------------------------------------
+ function ext8_even(posint)
+ function pg_temp.ext8_temp_even(posint)
+ table ext8_table1
+ table ext8_temp_table1
+ type posint
+(5 rows)
+
+-- here we want to start a new session and wait till old one is gone
+select pg_backend_pid() as oldpid \gset
+\c -
+do 'declare c int = 0;
+begin
+  while (select count(*) from pg_stat_activity where pid = '
+    :'oldpid'
+  ') > 0 loop c := c + 1; perform pg_stat_clear_snapshot(); end loop;
+  raise log ''test_extensions looped % times'', c;
+end';
+-- extension should now contain no temp objects
+\dx+ test_ext8
+Objects in extension "test_ext8"
+     Object description     
+----------------------------
+ function ext8_even(posint)
+ table ext8_table1
+ type posint
+(3 rows)
+
+-- 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();
+ create_extension_with_temp_schema 
+-----------------------------------
+ 
+(1 row)
+
+PREPARE TRANSACTION 'twophase_extension';
+ERROR:  cannot PREPARE a transaction that has operated on temporary objects
+-- Clean up
+DROP TABLE test_ext4_tab;
+DROP FUNCTION create_extension_with_temp_schema();
+RESET client_min_messages;
+\unset SHOW_CONTEXT
+-- Test case of an event trigger run in an extension upgrade script.
+-- See: https://postgr.es/m/20200902193715.6e0269d4@firost
+CREATE EXTENSION test_ext_evttrig;
+ALTER EXTENSION test_ext_evttrig UPDATE TO '2.0';
+DROP EXTENSION test_ext_evttrig;
diff --git a/src/test/modules/test_extensions/sql/test_extdepend.sql b/src/test/modules/test_extensions/sql/test_extdepend.sql
new file mode 100644
index 0000000..63240a1
--- /dev/null
+++ b/src/test/modules/test_extensions/sql/test_extdepend.sql
@@ -0,0 +1,90 @@
+--
+-- test ALTER THING name DEPENDS ON EXTENSION
+--
+
+-- Common setup for all tests
+CREATE TABLE test_extdep_commands (command text);
+COPY test_extdep_commands FROM stdin;
+ CREATE SCHEMA test_ext
+ CREATE EXTENSION test_ext5 SCHEMA test_ext
+ SET search_path TO test_ext
+ CREATE TABLE a (a1 int)
+
+ CREATE FUNCTION b() RETURNS TRIGGER LANGUAGE plpgsql AS\n   $$ BEGIN NEW.a1 := NEW.a1 + 42; RETURN NEW; END; $$
+ ALTER FUNCTION b() DEPENDS ON EXTENSION test_ext5
+
+ CREATE TRIGGER c BEFORE INSERT ON a FOR EACH ROW EXECUTE PROCEDURE b()
+ ALTER TRIGGER c ON a DEPENDS ON EXTENSION test_ext5
+
+ CREATE MATERIALIZED VIEW d AS SELECT * FROM a
+ ALTER MATERIALIZED VIEW d DEPENDS ON EXTENSION test_ext5
+
+ CREATE INDEX e ON a (a1)
+ ALTER INDEX e DEPENDS ON EXTENSION test_ext5
+ RESET search_path
+\.
+
+SELECT * FROM test_extdep_commands;
+-- First, test that dependent objects go away when the extension is dropped.
+SELECT * FROM test_extdep_commands \gexec
+-- A dependent object made dependent again has no effect
+ALTER FUNCTION test_ext.b() DEPENDS ON EXTENSION test_ext5;
+-- make sure we have the right dependencies on the extension
+SELECT deptype, p.*
+  FROM pg_depend, pg_identify_object(classid, objid, objsubid) AS p
+ WHERE refclassid = 'pg_extension'::regclass AND
+       refobjid = (SELECT oid FROM pg_extension WHERE extname = 'test_ext5')
+ORDER BY type;
+DROP EXTENSION test_ext5;
+-- anything still depending on the table?
+SELECT deptype, i.*
+  FROM pg_catalog.pg_depend, pg_identify_object(classid, objid, objsubid) i
+WHERE refclassid='pg_class'::regclass AND
+ refobjid='test_ext.a'::regclass AND NOT deptype IN ('i', 'a');
+DROP SCHEMA test_ext CASCADE;
+
+-- Second test: If we drop the table, the objects are dropped too and no
+-- vestige remains in pg_depend.
+SELECT * FROM test_extdep_commands \gexec
+DROP TABLE test_ext.a;		-- should fail, require cascade
+DROP TABLE test_ext.a CASCADE;
+-- anything still depending on the extension?  Should be only function b()
+SELECT deptype, i.*
+  FROM pg_catalog.pg_depend, pg_identify_object(classid, objid, objsubid) i
+ WHERE refclassid='pg_extension'::regclass AND
+ refobjid=(SELECT oid FROM pg_extension WHERE extname='test_ext5');
+DROP EXTENSION test_ext5;
+DROP SCHEMA test_ext CASCADE;
+
+-- Third test: we can drop the objects individually
+SELECT * FROM test_extdep_commands \gexec
+SET search_path TO test_ext;
+DROP TRIGGER c ON a;
+DROP FUNCTION b();
+DROP MATERIALIZED VIEW d;
+DROP INDEX e;
+
+SELECT deptype, i.*
+  FROM pg_catalog.pg_depend, pg_identify_object(classid, objid, objsubid) i
+ WHERE (refclassid='pg_extension'::regclass AND
+        refobjid=(SELECT oid FROM pg_extension WHERE extname='test_ext5'))
+	OR (refclassid='pg_class'::regclass AND refobjid='test_ext.a'::regclass)
+   AND NOT deptype IN ('i', 'a');
+DROP TABLE a;
+RESET search_path;
+DROP SCHEMA test_ext CASCADE;
+
+-- Fourth test: we can mark the objects as dependent, then unmark; then the
+-- drop of the extension does nothing
+SELECT * FROM test_extdep_commands \gexec
+SET search_path TO test_ext;
+ALTER FUNCTION b() NO DEPENDS ON EXTENSION test_ext5;
+ALTER TRIGGER c ON a NO DEPENDS ON EXTENSION test_ext5;
+ALTER MATERIALIZED VIEW d NO DEPENDS ON EXTENSION test_ext5;
+ALTER INDEX e NO DEPENDS ON EXTENSION test_ext5;
+DROP EXTENSION test_ext5;
+DROP TRIGGER c ON a;
+DROP FUNCTION b();
+DROP MATERIALIZED VIEW d;
+DROP INDEX e;
+DROP SCHEMA test_ext CASCADE;
diff --git a/src/test/modules/test_extensions/sql/test_extensions.sql b/src/test/modules/test_extensions/sql/test_extensions.sql
new file mode 100644
index 0000000..c16fd36
--- /dev/null
+++ b/src/test/modules/test_extensions/sql/test_extensions.sql
@@ -0,0 +1,101 @@
+-- test some errors
+CREATE EXTENSION test_ext1;
+CREATE EXTENSION test_ext1 SCHEMA test_ext1;
+CREATE EXTENSION test_ext1 SCHEMA test_ext;
+CREATE SCHEMA test_ext;
+CREATE EXTENSION test_ext1 SCHEMA test_ext;
+
+-- finally success
+CREATE EXTENSION test_ext1 SCHEMA test_ext CASCADE;
+
+SELECT extname, nspname, extversion, extrelocatable FROM pg_extension e, pg_namespace n WHERE extname LIKE 'test_ext%' AND e.extnamespace = n.oid ORDER BY 1;
+
+CREATE EXTENSION test_ext_cyclic1 CASCADE;
+
+DROP SCHEMA test_ext CASCADE;
+
+CREATE EXTENSION test_ext6;
+DROP EXTENSION test_ext6;
+CREATE EXTENSION test_ext6;
+
+-- test dropping of member tables that own extensions:
+-- this table will be absorbed into test_ext7
+create table old_table1 (col1 serial primary key);
+create extension test_ext7;
+\dx+ test_ext7
+alter extension test_ext7 update to '2.0';
+\dx+ test_ext7
+
+-- test handling of temp objects created by extensions
+create extension test_ext8;
+
+-- \dx+ would expose a variable pg_temp_nn schema name, so we can't use it here
+select regexp_replace(pg_describe_object(classid, objid, objsubid),
+                      'pg_temp_\d+', 'pg_temp', 'g') as "Object description"
+from pg_depend
+where refclassid = 'pg_extension'::regclass and deptype = 'e' and
+  refobjid = (select oid from pg_extension where extname = 'test_ext8')
+order by 1;
+
+-- Should be possible to drop and recreate this extension
+drop extension test_ext8;
+create extension test_ext8;
+
+select regexp_replace(pg_describe_object(classid, objid, objsubid),
+                      'pg_temp_\d+', 'pg_temp', 'g') as "Object description"
+from pg_depend
+where refclassid = 'pg_extension'::regclass and deptype = 'e' and
+  refobjid = (select oid from pg_extension where extname = 'test_ext8')
+order by 1;
+
+-- here we want to start a new session and wait till old one is gone
+select pg_backend_pid() as oldpid \gset
+\c -
+do 'declare c int = 0;
+begin
+  while (select count(*) from pg_stat_activity where pid = '
+    :'oldpid'
+  ') > 0 loop c := c + 1; perform pg_stat_clear_snapshot(); end loop;
+  raise log ''test_extensions looped % times'', c;
+end';
+
+-- extension should now contain no temp objects
+\dx+ test_ext8
+
+-- 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
+
+-- Test case of an event trigger run in an extension upgrade script.
+-- See: https://postgr.es/m/20200902193715.6e0269d4@firost
+CREATE EXTENSION test_ext_evttrig;
+ALTER EXTENSION test_ext_evttrig UPDATE TO '2.0';
+DROP EXTENSION test_ext_evttrig;
diff --git a/src/test/modules/test_extensions/test_ext1--1.0.sql b/src/test/modules/test_extensions/test_ext1--1.0.sql
new file mode 100644
index 0000000..9a4bb1b
--- /dev/null
+++ b/src/test/modules/test_extensions/test_ext1--1.0.sql
@@ -0,0 +1,3 @@
+/* src/test/modules/test_extensions/test_ext1--1.0.sql */
+-- complain if script is sourced in psql, rather than via CREATE EXTENSION
+\echo Use "CREATE EXTENSION test_ext1" to load this file. \quit
diff --git a/src/test/modules/test_extensions/test_ext1.control b/src/test/modules/test_extensions/test_ext1.control
new file mode 100644
index 0000000..9c069df
--- /dev/null
+++ b/src/test/modules/test_extensions/test_ext1.control
@@ -0,0 +1,5 @@
+comment = 'Test extension 1'
+default_version = '1.0'
+schema = 'test_ext1'
+relocatable = false
+requires = 'test_ext2,test_ext4'
diff --git a/src/test/modules/test_extensions/test_ext2--1.0.sql b/src/test/modules/test_extensions/test_ext2--1.0.sql
new file mode 100644
index 0000000..0f6d4ec
--- /dev/null
+++ b/src/test/modules/test_extensions/test_ext2--1.0.sql
@@ -0,0 +1,3 @@
+/* src/test/modules/test_extensions/test_ext2--1.0.sql */
+-- complain if script is sourced in psql, rather than via CREATE EXTENSION
+\echo Use "CREATE EXTENSION test_ext2" to load this file. \quit
diff --git a/src/test/modules/test_extensions/test_ext2.control b/src/test/modules/test_extensions/test_ext2.control
new file mode 100644
index 0000000..946b7d5
--- /dev/null
+++ b/src/test/modules/test_extensions/test_ext2.control
@@ -0,0 +1,4 @@
+comment = 'Test extension 2'
+default_version = '1.0'
+relocatable = true
+requires = 'test_ext3,test_ext5'
diff --git a/src/test/modules/test_extensions/test_ext3--1.0.sql b/src/test/modules/test_extensions/test_ext3--1.0.sql
new file mode 100644
index 0000000..4fcb63d
--- /dev/null
+++ b/src/test/modules/test_extensions/test_ext3--1.0.sql
@@ -0,0 +1,9 @@
+/* src/test/modules/test_extensions/test_ext3--1.0.sql */
+-- complain if script is sourced in psql, rather than via CREATE EXTENSION
+\echo Use "CREATE EXTENSION test_ext3" to load this file. \quit
+
+CREATE TABLE test_ext3_table (col_old INT);
+
+ALTER TABLE test_ext3_table RENAME col_old TO col_new;
+
+UPDATE test_ext3_table SET col_new = 0;
diff --git a/src/test/modules/test_extensions/test_ext3.control b/src/test/modules/test_extensions/test_ext3.control
new file mode 100644
index 0000000..5f1afe7
--- /dev/null
+++ b/src/test/modules/test_extensions/test_ext3.control
@@ -0,0 +1,3 @@
+comment = 'Test extension 3'
+default_version = '1.0'
+relocatable = true
diff --git a/src/test/modules/test_extensions/test_ext4--1.0.sql b/src/test/modules/test_extensions/test_ext4--1.0.sql
new file mode 100644
index 0000000..19f051f
--- /dev/null
+++ b/src/test/modules/test_extensions/test_ext4--1.0.sql
@@ -0,0 +1,3 @@
+/* src/test/modules/test_extensions/test_ext4--1.0.sql */
+-- complain if script is sourced in psql, rather than via CREATE EXTENSION
+\echo Use "CREATE EXTENSION test_ext4" to load this file. \quit
diff --git a/src/test/modules/test_extensions/test_ext4.control b/src/test/modules/test_extensions/test_ext4.control
new file mode 100644
index 0000000..fc62591
--- /dev/null
+++ b/src/test/modules/test_extensions/test_ext4.control
@@ -0,0 +1,4 @@
+comment = 'Test extension 4'
+default_version = '1.0'
+relocatable = true
+requires = 'test_ext5'
diff --git a/src/test/modules/test_extensions/test_ext5--1.0.sql b/src/test/modules/test_extensions/test_ext5--1.0.sql
new file mode 100644
index 0000000..baf6ef8
--- /dev/null
+++ b/src/test/modules/test_extensions/test_ext5--1.0.sql
@@ -0,0 +1,3 @@
+/* src/test/modules/test_extensions/test_ext5--1.0.sql */
+-- complain if script is sourced in psql, rather than via CREATE EXTENSION
+\echo Use "CREATE EXTENSION test_ext5" to load this file. \quit
diff --git a/src/test/modules/test_extensions/test_ext5.control b/src/test/modules/test_extensions/test_ext5.control
new file mode 100644
index 0000000..51bc57e
--- /dev/null
+++ b/src/test/modules/test_extensions/test_ext5.control
@@ -0,0 +1,3 @@
+comment = 'Test extension 5'
+default_version = '1.0'
+relocatable = true
diff --git a/src/test/modules/test_extensions/test_ext6--1.0.sql b/src/test/modules/test_extensions/test_ext6--1.0.sql
new file mode 100644
index 0000000..65a4fc5
--- /dev/null
+++ b/src/test/modules/test_extensions/test_ext6--1.0.sql
@@ -0,0 +1 @@
+grant usage on schema @extschema@ to public;
diff --git a/src/test/modules/test_extensions/test_ext6.control b/src/test/modules/test_extensions/test_ext6.control
new file mode 100644
index 0000000..04b2146
--- /dev/null
+++ b/src/test/modules/test_extensions/test_ext6.control
@@ -0,0 +1,5 @@
+comment = 'test_ext6'
+default_version = '1.0'
+relocatable = false
+superuser = true
+schema = 'test_ext6'
diff --git a/src/test/modules/test_extensions/test_ext7--1.0--2.0.sql b/src/test/modules/test_extensions/test_ext7--1.0--2.0.sql
new file mode 100644
index 0000000..50e3dca
--- /dev/null
+++ b/src/test/modules/test_extensions/test_ext7--1.0--2.0.sql
@@ -0,0 +1,8 @@
+/* src/test/modules/test_extensions/test_ext7--1.0--2.0.sql */
+
+-- complain if script is sourced in psql, rather than via ALTER EXTENSION
+\echo Use "ALTER EXTENSION test_ext7 UPDATE TO '2.0'" to load this file. \quit
+
+-- drop some tables with serial columns
+drop table ext7_table1;
+drop table old_table1;
diff --git a/src/test/modules/test_extensions/test_ext7--1.0.sql b/src/test/modules/test_extensions/test_ext7--1.0.sql
new file mode 100644
index 0000000..0c2d72a
--- /dev/null
+++ b/src/test/modules/test_extensions/test_ext7--1.0.sql
@@ -0,0 +1,13 @@
+/* src/test/modules/test_extensions/test_ext7--1.0.sql */
+
+-- complain if script is sourced in psql, rather than via CREATE EXTENSION
+\echo Use "CREATE EXTENSION test_ext7" to load this file. \quit
+
+-- link some existing serial-owning table to the extension
+alter extension test_ext7 add table old_table1;
+alter extension test_ext7 add sequence old_table1_col1_seq;
+
+-- ordinary member tables with serial columns
+create table ext7_table1 (col1 serial primary key);
+
+create table ext7_table2 (col2 serial primary key);
diff --git a/src/test/modules/test_extensions/test_ext7.control b/src/test/modules/test_extensions/test_ext7.control
new file mode 100644
index 0000000..b58df53
--- /dev/null
+++ b/src/test/modules/test_extensions/test_ext7.control
@@ -0,0 +1,4 @@
+comment = 'Test extension 7'
+default_version = '1.0'
+schema = 'public'
+relocatable = false
diff --git a/src/test/modules/test_extensions/test_ext8--1.0.sql b/src/test/modules/test_extensions/test_ext8--1.0.sql
new file mode 100644
index 0000000..1561ffe
--- /dev/null
+++ b/src/test/modules/test_extensions/test_ext8--1.0.sql
@@ -0,0 +1,21 @@
+/* src/test/modules/test_extensions/test_ext8--1.0.sql */
+
+-- complain if script is sourced in psql, rather than via CREATE EXTENSION
+\echo Use "CREATE EXTENSION test_ext8" to load this file. \quit
+
+-- create some random data type
+create domain posint as int check (value > 0);
+
+-- use it in regular and temporary tables and functions
+
+create table ext8_table1 (f1 posint);
+
+create temp table ext8_temp_table1 (f1 posint);
+
+create function ext8_even (posint) returns bool as
+  'select ($1 % 2) = 0' language sql;
+
+create function pg_temp.ext8_temp_even (posint) returns bool as
+  'select ($1 % 2) = 0' language sql;
+
+-- we intentionally don't drop the temp objects before exiting
diff --git a/src/test/modules/test_extensions/test_ext8.control b/src/test/modules/test_extensions/test_ext8.control
new file mode 100644
index 0000000..70f8caa
--- /dev/null
+++ b/src/test/modules/test_extensions/test_ext8.control
@@ -0,0 +1,4 @@
+comment = 'Test extension 8'
+default_version = '1.0'
+schema = 'public'
+relocatable = false
diff --git a/src/test/modules/test_extensions/test_ext_cyclic1--1.0.sql b/src/test/modules/test_extensions/test_ext_cyclic1--1.0.sql
new file mode 100644
index 0000000..81bdaf4
--- /dev/null
+++ b/src/test/modules/test_extensions/test_ext_cyclic1--1.0.sql
@@ -0,0 +1,3 @@
+/* src/test/modules/test_extensions/test_ext_cyclic1--1.0.sql */
+-- complain if script is sourced in psql, rather than via CREATE EXTENSION
+\echo Use "CREATE EXTENSION test_ext_cyclic1" to load this file. \quit
diff --git a/src/test/modules/test_extensions/test_ext_cyclic1.control b/src/test/modules/test_extensions/test_ext_cyclic1.control
new file mode 100644
index 0000000..aaab403
--- /dev/null
+++ b/src/test/modules/test_extensions/test_ext_cyclic1.control
@@ -0,0 +1,4 @@
+comment = 'Test extension cyclic 1'
+default_version = '1.0'
+relocatable = true
+requires = 'test_ext_cyclic2'
diff --git a/src/test/modules/test_extensions/test_ext_cyclic2--1.0.sql b/src/test/modules/test_extensions/test_ext_cyclic2--1.0.sql
new file mode 100644
index 0000000..ae2b3e9
--- /dev/null
+++ b/src/test/modules/test_extensions/test_ext_cyclic2--1.0.sql
@@ -0,0 +1,3 @@
+/* src/test/modules/test_extensions/test_ext_cyclic2--1.0.sql */
+-- complain if script is sourced in psql, rather than via CREATE EXTENSION
+\echo Use "CREATE EXTENSION test_ext_cyclic2" to load this file. \quit
diff --git a/src/test/modules/test_extensions/test_ext_cyclic2.control b/src/test/modules/test_extensions/test_ext_cyclic2.control
new file mode 100644
index 0000000..1e28f96
--- /dev/null
+++ b/src/test/modules/test_extensions/test_ext_cyclic2.control
@@ -0,0 +1,4 @@
+comment = 'Test extension cyclic 2'
+default_version = '1.0'
+relocatable = true
+requires = 'test_ext_cyclic1'
diff --git a/src/test/modules/test_extensions/test_ext_evttrig--1.0--2.0.sql b/src/test/modules/test_extensions/test_ext_evttrig--1.0--2.0.sql
new file mode 100644
index 0000000..fdd2f35
--- /dev/null
+++ b/src/test/modules/test_extensions/test_ext_evttrig--1.0--2.0.sql
@@ -0,0 +1,7 @@
+/* src/test/modules/test_extensions/test_event_trigger--1.0--2.0.sql */
+-- complain if script is sourced in psql, rather than via ALTER EXTENSION
+\echo Use "ALTER EXTENSION test_event_trigger UPDATE TO '2.0'" to load this file. \quit
+
+-- Test extension upgrade with event trigger.
+ALTER EVENT TRIGGER table_rewrite_trg DISABLE;
+ALTER TABLE t DROP COLUMN id;
diff --git a/src/test/modules/test_extensions/test_ext_evttrig--1.0.sql b/src/test/modules/test_extensions/test_ext_evttrig--1.0.sql
new file mode 100644
index 0000000..0071712
--- /dev/null
+++ b/src/test/modules/test_extensions/test_ext_evttrig--1.0.sql
@@ -0,0 +1,16 @@
+/* src/test/modules/test_extensions/test_event_trigger--1.0.sql */
+-- complain if script is sourced in psql, rather than via CREATE EXTENSION
+\echo Use "CREATE EXTENSION test_event_trigger" to load this file. \quit
+
+-- Base table with event trigger, used in a regression test involving
+-- extension upgrades.
+CREATE TABLE t (id text);
+CREATE OR REPLACE FUNCTION _evt_table_rewrite_fnct()
+RETURNS EVENT_TRIGGER LANGUAGE plpgsql AS
+$$
+  BEGIN
+  END;
+$$;
+CREATE EVENT TRIGGER table_rewrite_trg
+  ON table_rewrite
+  EXECUTE PROCEDURE _evt_table_rewrite_fnct();
diff --git a/src/test/modules/test_extensions/test_ext_evttrig.control b/src/test/modules/test_extensions/test_ext_evttrig.control
new file mode 100644
index 0000000..915fae6
--- /dev/null
+++ b/src/test/modules/test_extensions/test_ext_evttrig.control
@@ -0,0 +1,3 @@
+comment = 'Test extension - event trigger'
+default_version = '1.0'
+relocatable = true