1-- test some errors 2CREATE EXTENSION test_ext1; 3ERROR: required extension "test_ext2" is not installed 4HINT: Use CREATE EXTENSION ... CASCADE to install required extensions too. 5CREATE EXTENSION test_ext1 SCHEMA test_ext1; 6ERROR: schema "test_ext1" does not exist 7CREATE EXTENSION test_ext1 SCHEMA test_ext; 8ERROR: schema "test_ext" does not exist 9CREATE SCHEMA test_ext; 10CREATE EXTENSION test_ext1 SCHEMA test_ext; 11ERROR: extension "test_ext1" must be installed in schema "test_ext1" 12-- finally success 13CREATE EXTENSION test_ext1 SCHEMA test_ext CASCADE; 14NOTICE: installing required extension "test_ext2" 15NOTICE: installing required extension "test_ext3" 16NOTICE: installing required extension "test_ext5" 17NOTICE: installing required extension "test_ext4" 18SELECT extname, nspname, extversion, extrelocatable FROM pg_extension e, pg_namespace n WHERE extname LIKE 'test_ext%' AND e.extnamespace = n.oid ORDER BY 1; 19 extname | nspname | extversion | extrelocatable 20-----------+-----------+------------+---------------- 21 test_ext1 | test_ext1 | 1.0 | f 22 test_ext2 | test_ext | 1.0 | t 23 test_ext3 | test_ext | 1.0 | t 24 test_ext4 | test_ext | 1.0 | t 25 test_ext5 | test_ext | 1.0 | t 26(5 rows) 27 28CREATE EXTENSION test_ext_cyclic1 CASCADE; 29NOTICE: installing required extension "test_ext_cyclic2" 30ERROR: cyclic dependency detected between extensions "test_ext_cyclic1" and "test_ext_cyclic2" 31DROP SCHEMA test_ext CASCADE; 32NOTICE: drop cascades to 5 other objects 33DETAIL: drop cascades to extension test_ext3 34drop cascades to extension test_ext5 35drop cascades to extension test_ext2 36drop cascades to extension test_ext4 37drop cascades to extension test_ext1 38CREATE EXTENSION test_ext6; 39DROP EXTENSION test_ext6; 40CREATE EXTENSION test_ext6; 41-- test dropping of member tables that own extensions: 42-- this table will be absorbed into test_ext7 43create table old_table1 (col1 serial primary key); 44create extension test_ext7; 45\dx+ test_ext7 46Objects in extension "test_ext7" 47 Object description 48------------------------------- 49 sequence ext7_table1_col1_seq 50 sequence ext7_table2_col2_seq 51 sequence old_table1_col1_seq 52 table ext7_table1 53 table ext7_table2 54 table old_table1 55(6 rows) 56 57alter extension test_ext7 update to '2.0'; 58\dx+ test_ext7 59Objects in extension "test_ext7" 60 Object description 61------------------------------- 62 sequence ext7_table2_col2_seq 63 table ext7_table2 64(2 rows) 65 66-- test handling of temp objects created by extensions 67create extension test_ext8; 68-- \dx+ would expose a variable pg_temp_nn schema name, so we can't use it here 69select regexp_replace(pg_describe_object(classid, objid, objsubid), 70 'pg_temp_\d+', 'pg_temp', 'g') as "Object description" 71from pg_depend 72where refclassid = 'pg_extension'::regclass and deptype = 'e' and 73 refobjid = (select oid from pg_extension where extname = 'test_ext8') 74order by 1; 75 Object description 76----------------------------------------- 77 function ext8_even(posint) 78 function pg_temp.ext8_temp_even(posint) 79 table ext8_table1 80 table ext8_temp_table1 81 type posint 82(5 rows) 83 84-- Should be possible to drop and recreate this extension 85drop extension test_ext8; 86create extension test_ext8; 87select regexp_replace(pg_describe_object(classid, objid, objsubid), 88 'pg_temp_\d+', 'pg_temp', 'g') as "Object description" 89from pg_depend 90where refclassid = 'pg_extension'::regclass and deptype = 'e' and 91 refobjid = (select oid from pg_extension where extname = 'test_ext8') 92order by 1; 93 Object description 94----------------------------------------- 95 function ext8_even(posint) 96 function pg_temp.ext8_temp_even(posint) 97 table ext8_table1 98 table ext8_temp_table1 99 type posint 100(5 rows) 101 102-- here we want to start a new session and wait till old one is gone 103select pg_backend_pid() as oldpid \gset 104\c - 105do 'declare c int = 0; 106begin 107 while (select count(*) from pg_stat_activity where pid = ' 108 :'oldpid' 109 ') > 0 loop c := c + 1; perform pg_stat_clear_snapshot(); end loop; 110 raise log ''test_extensions looped % times'', c; 111end'; 112-- extension should now contain no temp objects 113\dx+ test_ext8 114Objects in extension "test_ext8" 115 Object description 116---------------------------- 117 function ext8_even(posint) 118 table ext8_table1 119 type posint 120(3 rows) 121 122-- dropping it should still work 123drop extension test_ext8; 124-- Test creation of extension in temporary schema with two-phase commit, 125-- which should not work. This function wrapper is useful for portability. 126-- Avoid noise caused by CONTEXT and NOTICE messages including the temporary 127-- schema name. 128\set SHOW_CONTEXT never 129SET client_min_messages TO 'warning'; 130-- First enforce presence of temporary schema. 131CREATE TEMP TABLE test_ext4_tab (); 132CREATE OR REPLACE FUNCTION create_extension_with_temp_schema() 133 RETURNS VOID AS $$ 134 DECLARE 135 tmpschema text; 136 query text; 137 BEGIN 138 SELECT INTO tmpschema pg_my_temp_schema()::regnamespace; 139 query := 'CREATE EXTENSION test_ext4 SCHEMA ' || tmpschema || ' CASCADE;'; 140 RAISE NOTICE 'query %', query; 141 EXECUTE query; 142 END; $$ LANGUAGE plpgsql; 143BEGIN; 144SELECT create_extension_with_temp_schema(); 145 create_extension_with_temp_schema 146----------------------------------- 147 148(1 row) 149 150PREPARE TRANSACTION 'twophase_extension'; 151ERROR: cannot PREPARE a transaction that has operated on temporary objects 152-- Clean up 153DROP TABLE test_ext4_tab; 154DROP FUNCTION create_extension_with_temp_schema(); 155RESET client_min_messages; 156\unset SHOW_CONTEXT 157