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
|
----
-- Regression test to Global Temporary Table implementation
--
-- Test for GTT with TABLE ... (LIKE ...) clause.
--
----
-- Create a GTT like table to test ON COMMIT PRESERVE ROWS
CREATE GLOBAL TEMPORARY TABLE t_glob_temptable1 (
LIKE source
INCLUDING ALL
) ON COMMIT PRESERVE ROWS;
WARNING: GLOBAL is deprecated in temporary table creation
LINE 1: CREATE GLOBAL TEMPORARY TABLE t_glob_temptable1 (
^
-- Look at table description
SELECT a.attname,
pg_catalog.format_type(a.atttypid, a.atttypmod),
(SELECT substring(pg_catalog.pg_get_expr(d.adbin, d.adrelid, true) for 128)
FROM pg_catalog.pg_attrdef d
WHERE d.adrelid = a.attrelid AND d.adnum = a.attnum AND a.atthasdef),
a.attnotnull,
pg_catalog.col_description(a.attrelid, a.attnum)
FROM pg_catalog.pg_attribute a
WHERE a.attrelid = (
SELECT c.oid FROM pg_catalog.pg_class c LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace WHERE c.relname = 't_glob_temptable1' AND n.nspname = 'pgtt_schema'
) AND a.attnum > 0 AND NOT a.attisdropped
ORDER BY a.attnum;
attname | format_type | substring | attnotnull | col_description
---------+-----------------------+------------------------------------+------------+-----------------------
id | integer | nextval('source_id_seq'::regclass) | t | auto generated column
c2 | character varying(50) | | t |
lbl | character varying | '-'::character varying | f |
(3 rows)
-- With indexes defined
SELECT c2.relname, i.indisprimary, i.indisunique, pg_catalog.pg_get_indexdef(i.indexrelid, 0, true),
pg_catalog.pg_get_constraintdef(con.oid, true), contype
FROM pg_catalog.pg_class c, pg_catalog.pg_class c2, pg_catalog.pg_index i
LEFT JOIN pg_catalog.pg_constraint con ON (conrelid = i.indrelid AND conindid = i.indexrelid AND contype IN ('p','u','x'))
WHERE c.oid = (
SELECT c.oid FROM pg_catalog.pg_class c LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace WHERE c.relname = 't_glob_temptable1' AND n.nspname = 'pgtt_schema'
) AND c.oid = i.indrelid AND i.indexrelid = c2.oid
ORDER BY i.indisprimary DESC, c2.relname;
relname | indisprimary | indisunique | pg_get_indexdef | pg_get_constraintdef | contype
---------------------------+--------------+-------------+------------------------------------------------------------------------------------+----------------------+---------
t_glob_temptable1_pkey | t | t | CREATE UNIQUE INDEX t_glob_temptable1_pkey ON t_glob_temptable1 USING btree (id) | PRIMARY KEY (id) | p
t_glob_temptable1_c2_key | f | t | CREATE UNIQUE INDEX t_glob_temptable1_c2_key ON t_glob_temptable1 USING btree (c2) | UNIQUE (c2) | u
t_glob_temptable1_lbl_idx | f | f | CREATE INDEX t_glob_temptable1_lbl_idx ON t_glob_temptable1 USING btree (lbl) | |
(3 rows)
-- Look at Global Temporary Table definition
SELECT nspname, relname, preserved, code FROM pgtt_schema.pg_global_temp_tables;
nspname | relname | preserved | code
-------------+-------------------+-----------+-----------------------
pgtt_schema | t_glob_temptable1 | t | +
| | | LIKE source +
| | | INCLUDING ALL+
| | |
(1 row)
-- A "template" unlogged table should exists
SELECT n.nspname, c.relname FROM pg_class c JOIN pg_namespace n ON (c.relnamespace=n.oid) WHERE relname = 't_glob_temptable1';
nspname | relname
-------------+-------------------
pgtt_schema | t_glob_temptable1
(1 row)
BEGIN;
-- With the first insert some value in the temporary table
INSERT INTO t_glob_temptable1 VALUES (1, 'One');
-- Look at temp table description
SELECT a.attname,
pg_catalog.format_type(a.atttypid, a.atttypmod),
(SELECT substring(pg_catalog.pg_get_expr(d.adbin, d.adrelid, true) for 128)
FROM pg_catalog.pg_attrdef d
WHERE d.adrelid = a.attrelid AND d.adnum = a.attnum AND a.atthasdef),
a.attnotnull,
pg_catalog.col_description(a.attrelid, a.attnum)
FROM pg_catalog.pg_attribute a
WHERE a.attrelid = (
SELECT c.oid FROM pg_catalog.pg_class c LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace WHERE c.relname = 't_glob_temptable1' AND n.nspname LIKE 'pg_temp_%'
) AND a.attnum > 0 AND NOT a.attisdropped
ORDER BY a.attnum;
attname | format_type | substring | attnotnull | col_description
---------+-----------------------+------------------------------------+------------+-----------------------
id | integer | nextval('source_id_seq'::regclass) | t | auto generated column
c2 | character varying(50) | | t |
lbl | character varying | '-'::character varying | f |
(3 rows)
SELECT c2.relname, i.indisprimary, i.indisunique, pg_catalog.pg_get_indexdef(i.indexrelid, 0, true),
pg_catalog.pg_get_constraintdef(con.oid, true), contype
FROM pg_catalog.pg_class c, pg_catalog.pg_class c2, pg_catalog.pg_index i
LEFT JOIN pg_catalog.pg_constraint con ON (conrelid = i.indrelid AND conindid = i.indexrelid AND contype IN ('p','u','x'))
WHERE c.oid = (
SELECT c.oid FROM pg_catalog.pg_class c LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace WHERE c.relname = 't_glob_temptable1' AND n.nspname LIKE 'pg_temp_%'
) AND c.oid = i.indrelid AND i.indexrelid = c2.oid
ORDER BY i.indisprimary DESC, c2.relname;
relname | indisprimary | indisunique | pg_get_indexdef | pg_get_constraintdef | contype
---------------------------+--------------+-------------+------------------------------------------------------------------------------------+----------------------+---------
t_glob_temptable1_pkey | t | t | CREATE UNIQUE INDEX t_glob_temptable1_pkey ON t_glob_temptable1 USING btree (id) | PRIMARY KEY (id) | p
t_glob_temptable1_c2_key | f | t | CREATE UNIQUE INDEX t_glob_temptable1_c2_key ON t_glob_temptable1 USING btree (c2) | UNIQUE (c2) | u
t_glob_temptable1_lbl_idx | f | f | CREATE INDEX t_glob_temptable1_lbl_idx ON t_glob_temptable1 USING btree (lbl) | |
(3 rows)
-- Look if we have two tables now
SELECT regexp_replace(n.nspname, '\d+', 'x', 'g'), c.relname FROM pg_class c JOIN pg_namespace n ON (c.relnamespace=n.oid) WHERE relname = 't_glob_temptable1';
regexp_replace | relname
----------------+-------------------
pgtt_schema | t_glob_temptable1
pg_temp_x | t_glob_temptable1
(2 rows)
-- Look at content of the template for Global Temporary Table, must be empty
SET pgtt.enabled TO off;
SELECT * FROM pgtt_schema.t_glob_temptable1;
id | c2 | lbl
----+----+-----
(0 rows)
SET pgtt.enabled TO on;
-- Look at content of the Global Temporary Table
SELECT * FROM t_glob_temptable1 ORDER BY id;
id | c2 | lbl
----+-----+-----
1 | One | -
(1 row)
COMMIT;
SELECT * FROM t_glob_temptable1 ORDER BY id;
id | c2 | lbl
----+-----+-----
1 | One | -
(1 row)
-- Reconnect and drop it
\c - -
-- Cleanup
DROP TABLE t_glob_temptable1;
|
