PolarDB-for-PostgreSQL

1
--
2
-- Test foreign-data wrapper file_fdw.
3
--
4

5
-- Clean up in case a prior regression run failed
6
SET client_min_messages TO 'warning';
7
DROP ROLE IF EXISTS regress_file_fdw_superuser, regress_file_fdw_user, regress_no_priv_user;
8
RESET client_min_messages;
9

10
CREATE ROLE regress_file_fdw_superuser LOGIN SUPERUSER; -- is a superuser
11
CREATE ROLE regress_file_fdw_user LOGIN;                -- has priv and user mapping
12
CREATE ROLE regress_no_priv_user LOGIN;                 -- has priv but no user mapping
13

14
-- Install file_fdw
15
CREATE EXTENSION file_fdw;
16

17
-- regress_file_fdw_superuser owns fdw-related objects
18
SET ROLE regress_file_fdw_superuser;
19
CREATE SERVER file_server FOREIGN DATA WRAPPER file_fdw;
20

21
-- privilege tests
22
SET ROLE regress_file_fdw_user;
23
CREATE FOREIGN DATA WRAPPER file_fdw2 HANDLER file_fdw_handler VALIDATOR file_fdw_validator;   -- ERROR
24
CREATE SERVER file_server2 FOREIGN DATA WRAPPER file_fdw;   -- ERROR
25
CREATE USER MAPPING FOR regress_file_fdw_user SERVER file_server;   -- ERROR
26

27
SET ROLE regress_file_fdw_superuser;
28
GRANT USAGE ON FOREIGN SERVER file_server TO regress_file_fdw_user;
29

30
SET ROLE regress_file_fdw_user;
31
CREATE USER MAPPING FOR regress_file_fdw_user SERVER file_server;
32

33
-- create user mappings and grant privilege to test users
34
SET ROLE regress_file_fdw_superuser;
35
CREATE USER MAPPING FOR regress_file_fdw_superuser SERVER file_server;
36
CREATE USER MAPPING FOR regress_no_priv_user SERVER file_server;
37

38
-- validator tests
39
CREATE FOREIGN TABLE tbl () SERVER file_server OPTIONS (format 'xml');  -- ERROR
40
CREATE FOREIGN TABLE tbl () SERVER file_server OPTIONS (format 'text', header 'true');      -- ERROR
41
CREATE FOREIGN TABLE tbl () SERVER file_server OPTIONS (format 'text', quote ':');          -- ERROR
42
CREATE FOREIGN TABLE tbl () SERVER file_server OPTIONS (format 'text', escape ':');         -- ERROR
43
CREATE FOREIGN TABLE tbl () SERVER file_server OPTIONS (format 'binary', header 'true');    -- ERROR
44
CREATE FOREIGN TABLE tbl () SERVER file_server OPTIONS (format 'binary', quote ':');        -- ERROR
45
CREATE FOREIGN TABLE tbl () SERVER file_server OPTIONS (format 'binary', escape ':');       -- ERROR
46
CREATE FOREIGN TABLE tbl () SERVER file_server OPTIONS (format 'text', delimiter 'a');      -- ERROR
47
CREATE FOREIGN TABLE tbl () SERVER file_server OPTIONS (format 'text', escape '-');         -- ERROR
48
CREATE FOREIGN TABLE tbl () SERVER file_server OPTIONS (format 'csv', quote '-', null '=-=');   -- ERROR
49
CREATE FOREIGN TABLE tbl () SERVER file_server OPTIONS (format 'csv', delimiter '-', null '=-=');    -- ERROR
50
CREATE FOREIGN TABLE tbl () SERVER file_server OPTIONS (format 'csv', delimiter '-', quote '-');    -- ERROR
51
CREATE FOREIGN TABLE tbl () SERVER file_server OPTIONS (format 'csv', delimiter '---');     -- ERROR
52
CREATE FOREIGN TABLE tbl () SERVER file_server OPTIONS (format 'csv', quote '---');         -- ERROR
53
CREATE FOREIGN TABLE tbl () SERVER file_server OPTIONS (format 'csv', escape '---');        -- ERROR
54
CREATE FOREIGN TABLE tbl () SERVER file_server OPTIONS (format 'text', delimiter '\');       -- ERROR
55
CREATE FOREIGN TABLE tbl () SERVER file_server OPTIONS (format 'text', delimiter '.');       -- ERROR
56
CREATE FOREIGN TABLE tbl () SERVER file_server OPTIONS (format 'text', delimiter '1');       -- ERROR
57
CREATE FOREIGN TABLE tbl () SERVER file_server OPTIONS (format 'text', delimiter 'a');       -- ERROR
58
CREATE FOREIGN TABLE tbl () SERVER file_server OPTIONS (format 'csv', delimiter '
59
');       -- ERROR
60
CREATE FOREIGN TABLE tbl () SERVER file_server OPTIONS (format 'csv', null '
61
');       -- ERROR
62
CREATE FOREIGN TABLE tbl () SERVER file_server;  -- ERROR
63

64
CREATE FOREIGN TABLE agg_text (
65
	a	int2 CHECK (a >= 0),
66
	b	float4
67
) SERVER file_server
68
OPTIONS (format 'text', filename '@abs_srcdir@/data/agg.data', delimiter '	', null '\N');
69
GRANT SELECT ON agg_text TO regress_file_fdw_user;
70
CREATE FOREIGN TABLE agg_csv (
71
	a	int2,
72
	b	float4
73
) SERVER file_server
74
OPTIONS (format 'csv', filename '@abs_srcdir@/data/agg.csv', header 'true', delimiter ';', quote '@', escape '"', null '');
75
ALTER FOREIGN TABLE agg_csv ADD CHECK (a >= 0);
76
CREATE FOREIGN TABLE agg_bad (
77
	a	int2,
78
	b	float4
79
) SERVER file_server
80
OPTIONS (format 'csv', filename '@abs_srcdir@/data/agg.bad', header 'true', delimiter ';', quote '@', escape '"', null '');
81
ALTER FOREIGN TABLE agg_bad ADD CHECK (a >= 0);
82

83
-- per-column options tests
84
CREATE FOREIGN TABLE text_csv (
85
    word1 text OPTIONS (force_not_null 'true'),
86
    word2 text OPTIONS (force_not_null 'off'),
87
    word3 text OPTIONS (force_null 'true'),
88
    word4 text OPTIONS (force_null 'off')
89
) SERVER file_server
90
OPTIONS (format 'text', filename '@abs_srcdir@/data/text.csv', null 'NULL');
91
SELECT * FROM text_csv; -- ERROR
92
ALTER FOREIGN TABLE text_csv OPTIONS (SET format 'csv');
93
\pset null _null_
94
SELECT * FROM text_csv;
95

96
-- force_not_null and force_null can be used together on the same column
97
ALTER FOREIGN TABLE text_csv ALTER COLUMN word1 OPTIONS (force_null 'true');
98
ALTER FOREIGN TABLE text_csv ALTER COLUMN word3 OPTIONS (force_not_null 'true');
99

100
-- force_not_null is not allowed to be specified at any foreign object level:
101
ALTER FOREIGN DATA WRAPPER file_fdw OPTIONS (ADD force_not_null '*'); -- ERROR
102
ALTER SERVER file_server OPTIONS (ADD force_not_null '*'); -- ERROR
103
CREATE USER MAPPING FOR public SERVER file_server OPTIONS (force_not_null '*'); -- ERROR
104
CREATE FOREIGN TABLE tbl () SERVER file_server OPTIONS (force_not_null '*'); -- ERROR
105

106
-- force_null is not allowed to be specified at any foreign object level:
107
ALTER FOREIGN DATA WRAPPER file_fdw OPTIONS (ADD force_null '*'); -- ERROR
108
ALTER SERVER file_server OPTIONS (ADD force_null '*'); -- ERROR
109
CREATE USER MAPPING FOR public SERVER file_server OPTIONS (force_null '*'); -- ERROR
110
CREATE FOREIGN TABLE tbl () SERVER file_server OPTIONS (force_null '*'); -- ERROR
111

112
-- basic query tests
113
SELECT * FROM agg_text WHERE b > 10.0 ORDER BY a;
114
SELECT * FROM agg_csv ORDER BY a;
115
SELECT * FROM agg_csv c JOIN agg_text t ON (t.a = c.a) ORDER BY c.a;
116

117
-- error context report tests
118
SELECT * FROM agg_bad;               -- ERROR
119

120
-- misc query tests
121
\t on
122
EXPLAIN (VERBOSE, COSTS FALSE) SELECT * FROM agg_csv;
123
\t off
124
PREPARE st(int) AS SELECT * FROM agg_csv WHERE a = $1;
125
EXECUTE st(100);
126
EXECUTE st(100);
127
DEALLOCATE st;
128

129
-- tableoid
130
SELECT tableoid::regclass, b FROM agg_csv;
131

132
-- updates aren't supported
133
INSERT INTO agg_csv VALUES(1,2.0);
134
UPDATE agg_csv SET a = 1;
135
DELETE FROM agg_csv WHERE a = 100;
136
-- but this should be allowed
137
SELECT * FROM agg_csv FOR UPDATE;
138

139
-- copy from isn't supported either
140
COPY agg_csv FROM STDIN;
141
12	3.4
142
\.
143

144
-- constraint exclusion tests
145
\t on
146
EXPLAIN (VERBOSE, COSTS FALSE) SELECT * FROM agg_csv WHERE a < 0;
147
\t off
148
SELECT * FROM agg_csv WHERE a < 0;
149
SET constraint_exclusion = 'on';
150
\t on
151
EXPLAIN (VERBOSE, COSTS FALSE) SELECT * FROM agg_csv WHERE a < 0;
152
\t off
153
SELECT * FROM agg_csv WHERE a < 0;
154
RESET constraint_exclusion;
155

156
-- table inheritance tests
157
CREATE TABLE agg (a int2, b float4);
158
ALTER FOREIGN TABLE agg_csv INHERIT agg;
159
SELECT tableoid::regclass, * FROM agg;
160
SELECT tableoid::regclass, * FROM agg_csv;
161
SELECT tableoid::regclass, * FROM ONLY agg;
162
-- updates aren't supported
163
UPDATE agg SET a = 1;
164
DELETE FROM agg WHERE a = 100;
165
-- but this should be allowed
166
SELECT tableoid::regclass, * FROM agg FOR UPDATE;
167
ALTER FOREIGN TABLE agg_csv NO INHERIT agg;
168
DROP TABLE agg;
169

170
-- declarative partitioning tests
171
SET ROLE regress_file_fdw_superuser;
172
CREATE TABLE pt (a int, b text) partition by list (a);
173
CREATE FOREIGN TABLE p1 partition of pt for values in (1) SERVER file_server
174
OPTIONS (format 'csv', filename '@abs_srcdir@/data/list1.csv', delimiter ',');
175
CREATE TABLE p2 partition of pt for values in (2);
176
SELECT tableoid::regclass, * FROM pt;
177
SELECT tableoid::regclass, * FROM p1;
178
SELECT tableoid::regclass, * FROM p2;
179
COPY pt FROM '@abs_srcdir@/data/list2.bad' with (format 'csv', delimiter ','); -- ERROR
180
COPY pt FROM '@abs_srcdir@/data/list2.csv' with (format 'csv', delimiter ',');
181
SELECT tableoid::regclass, * FROM pt;
182
SELECT tableoid::regclass, * FROM p1;
183
SELECT tableoid::regclass, * FROM p2;
184
INSERT INTO pt VALUES (1, 'xyzzy'); -- ERROR
185
INSERT INTO pt VALUES (2, 'xyzzy');
186
UPDATE pt set a = 1 where a = 2; -- ERROR
187
SELECT tableoid::regclass, * FROM pt;
188
SELECT tableoid::regclass, * FROM p1;
189
SELECT tableoid::regclass, * FROM p2;
190
DROP TABLE pt;
191

192
-- privilege tests
193
SET ROLE regress_file_fdw_superuser;
194
SELECT * FROM agg_text ORDER BY a;
195
SET ROLE regress_file_fdw_user;
196
SELECT * FROM agg_text ORDER BY a;
197
SET ROLE regress_no_priv_user;
198
SELECT * FROM agg_text ORDER BY a;   -- ERROR
199
SET ROLE regress_file_fdw_user;
200
\t on
201
EXPLAIN (VERBOSE, COSTS FALSE) SELECT * FROM agg_text WHERE a > 0;
202
\t off
203
-- file FDW allows foreign tables to be accessed without user mapping
204
DROP USER MAPPING FOR regress_file_fdw_user SERVER file_server;
205
SELECT * FROM agg_text ORDER BY a;
206

207
-- privilege tests for object
208
SET ROLE regress_file_fdw_superuser;
209
ALTER FOREIGN TABLE agg_text OWNER TO regress_file_fdw_user;
210
ALTER FOREIGN TABLE agg_text OPTIONS (SET format 'text');
211
SET ROLE regress_file_fdw_user;
212
ALTER FOREIGN TABLE agg_text OPTIONS (SET format 'text');
213
SET ROLE regress_file_fdw_superuser;
214

215
-- cleanup
216
RESET ROLE;
217
DROP EXTENSION file_fdw CASCADE;
218
DROP ROLE regress_file_fdw_superuser, regress_file_fdw_user, regress_no_priv_user;
219