1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
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;
|
