-- 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