PolarDB-for-PostgreSQL

1
--
2
-- Test foreign-data wrapper file_fdw.
3
--
4
-- Clean up in case a prior regression run failed
5
SET client_min_messages TO 'warning';
6
DROP ROLE IF EXISTS regress_file_fdw_superuser, regress_file_fdw_user, regress_no_priv_user;
7
RESET client_min_messages;
8
CREATE ROLE regress_file_fdw_superuser LOGIN SUPERUSER; -- is a superuser
9
CREATE ROLE regress_file_fdw_user LOGIN;                -- has priv and user mapping
10
CREATE ROLE regress_no_priv_user LOGIN;                 -- has priv but no user mapping
11
-- Install file_fdw
12
CREATE EXTENSION file_fdw;
13
-- regress_file_fdw_superuser owns fdw-related objects
14
SET ROLE regress_file_fdw_superuser;
15
CREATE SERVER file_server FOREIGN DATA WRAPPER file_fdw;
16
-- privilege tests
17
SET ROLE regress_file_fdw_user;
18
CREATE FOREIGN DATA WRAPPER file_fdw2 HANDLER file_fdw_handler VALIDATOR file_fdw_validator;   -- ERROR
19
ERROR:  permission denied to create foreign-data wrapper "file_fdw2"
20
HINT:  Must be superuser to create a foreign-data wrapper.
21
CREATE SERVER file_server2 FOREIGN DATA WRAPPER file_fdw;   -- ERROR
22
ERROR:  permission denied for foreign-data wrapper file_fdw
23
CREATE USER MAPPING FOR regress_file_fdw_user SERVER file_server;   -- ERROR
24
ERROR:  permission denied for foreign server file_server
25
SET ROLE regress_file_fdw_superuser;
26
GRANT USAGE ON FOREIGN SERVER file_server TO regress_file_fdw_user;
27
SET ROLE regress_file_fdw_user;
28
CREATE USER MAPPING FOR regress_file_fdw_user SERVER file_server;
29
-- create user mappings and grant privilege to test users
30
SET ROLE regress_file_fdw_superuser;
31
CREATE USER MAPPING FOR regress_file_fdw_superuser SERVER file_server;
32
CREATE USER MAPPING FOR regress_no_priv_user SERVER file_server;
33
-- validator tests
34
CREATE FOREIGN TABLE tbl () SERVER file_server OPTIONS (format 'xml');  -- ERROR
35
ERROR:  COPY format "xml" not recognized
36
CREATE FOREIGN TABLE tbl () SERVER file_server OPTIONS (format 'text', header 'true');      -- ERROR
37
ERROR:  COPY HEADER available only in CSV mode
38
CREATE FOREIGN TABLE tbl () SERVER file_server OPTIONS (format 'text', quote ':');          -- ERROR
39
ERROR:  COPY quote available only in CSV mode
40
CREATE FOREIGN TABLE tbl () SERVER file_server OPTIONS (format 'text', escape ':');         -- ERROR
41
ERROR:  COPY escape available only in CSV mode
42
CREATE FOREIGN TABLE tbl () SERVER file_server OPTIONS (format 'binary', header 'true');    -- ERROR
43
ERROR:  COPY HEADER available only in CSV mode
44
CREATE FOREIGN TABLE tbl () SERVER file_server OPTIONS (format 'binary', quote ':');        -- ERROR
45
ERROR:  COPY quote available only in CSV mode
46
CREATE FOREIGN TABLE tbl () SERVER file_server OPTIONS (format 'binary', escape ':');       -- ERROR
47
ERROR:  COPY escape available only in CSV mode
48
CREATE FOREIGN TABLE tbl () SERVER file_server OPTIONS (format 'text', delimiter 'a');      -- ERROR
49
ERROR:  COPY delimiter cannot be "a"
50
CREATE FOREIGN TABLE tbl () SERVER file_server OPTIONS (format 'text', escape '-');         -- ERROR
51
ERROR:  COPY escape available only in CSV mode
52
CREATE FOREIGN TABLE tbl () SERVER file_server OPTIONS (format 'csv', quote '-', null '=-=');   -- ERROR
53
ERROR:  CSV quote character must not appear in the NULL specification
54
CREATE FOREIGN TABLE tbl () SERVER file_server OPTIONS (format 'csv', delimiter '-', null '=-=');    -- ERROR
55
ERROR:  COPY delimiter must not appear in the NULL specification
56
CREATE FOREIGN TABLE tbl () SERVER file_server OPTIONS (format 'csv', delimiter '-', quote '-');    -- ERROR
57
ERROR:  COPY delimiter and quote must be different
58
CREATE FOREIGN TABLE tbl () SERVER file_server OPTIONS (format 'csv', delimiter '---');     -- ERROR
59
ERROR:  COPY delimiter must be a single one-byte character
60
CREATE FOREIGN TABLE tbl () SERVER file_server OPTIONS (format 'csv', quote '---');         -- ERROR
61
ERROR:  COPY quote must be a single one-byte character
62
CREATE FOREIGN TABLE tbl () SERVER file_server OPTIONS (format 'csv', escape '---');        -- ERROR
63
ERROR:  COPY escape must be a single one-byte character
64
CREATE FOREIGN TABLE tbl () SERVER file_server OPTIONS (format 'text', delimiter '\');       -- ERROR
65
ERROR:  COPY delimiter cannot be "\"
66
CREATE FOREIGN TABLE tbl () SERVER file_server OPTIONS (format 'text', delimiter '.');       -- ERROR
67
ERROR:  COPY delimiter cannot be "."
68
CREATE FOREIGN TABLE tbl () SERVER file_server OPTIONS (format 'text', delimiter '1');       -- ERROR
69
ERROR:  COPY delimiter cannot be "1"
70
CREATE FOREIGN TABLE tbl () SERVER file_server OPTIONS (format 'text', delimiter 'a');       -- ERROR
71
ERROR:  COPY delimiter cannot be "a"
72
CREATE FOREIGN TABLE tbl () SERVER file_server OPTIONS (format 'csv', delimiter '
73
');       -- ERROR
74
ERROR:  COPY delimiter cannot be newline or carriage return
75
CREATE FOREIGN TABLE tbl () SERVER file_server OPTIONS (format 'csv', null '
76
');       -- ERROR
77
ERROR:  COPY null representation cannot use newline or carriage return
78
CREATE FOREIGN TABLE tbl () SERVER file_server;  -- ERROR
79
ERROR:  either filename or program is required for file_fdw foreign tables
80
CREATE FOREIGN TABLE agg_text (
81
	a	int2 CHECK (a >= 0),
82
	b	float4
83
) SERVER file_server
84
OPTIONS (format 'text', filename '@abs_srcdir@/data/agg.data', delimiter '	', null '\N');
85
GRANT SELECT ON agg_text TO regress_file_fdw_user;
86
CREATE FOREIGN TABLE agg_csv (
87
	a	int2,
88
	b	float4
89
) SERVER file_server
90
OPTIONS (format 'csv', filename '@abs_srcdir@/data/agg.csv', header 'true', delimiter ';', quote '@', escape '"', null '');
91
ALTER FOREIGN TABLE agg_csv ADD CHECK (a >= 0);
92
CREATE FOREIGN TABLE agg_bad (
93
	a	int2,
94
	b	float4
95
) SERVER file_server
96
OPTIONS (format 'csv', filename '@abs_srcdir@/data/agg.bad', header 'true', delimiter ';', quote '@', escape '"', null '');
97
ALTER FOREIGN TABLE agg_bad ADD CHECK (a >= 0);
98
-- per-column options tests
99
CREATE FOREIGN TABLE text_csv (
100
    word1 text OPTIONS (force_not_null 'true'),
101
    word2 text OPTIONS (force_not_null 'off'),
102
    word3 text OPTIONS (force_null 'true'),
103
    word4 text OPTIONS (force_null 'off')
104
) SERVER file_server
105
OPTIONS (format 'text', filename '@abs_srcdir@/data/text.csv', null 'NULL');
106
SELECT * FROM text_csv; -- ERROR
107
ERROR:  COPY force not null available only in CSV mode
108
ALTER FOREIGN TABLE text_csv OPTIONS (SET format 'csv');
109
\pset null _null_
110
SELECT * FROM text_csv;
111
 word1 | word2  | word3  | word4  
112
-------+--------+--------+--------
113
 AAA   | aaa    | 123    | 
114
 XYZ   | xyz    |        | 321
115
 NULL  | _null_ | _null_ | _null_
116
 NULL  | _null_ | _null_ | _null_
117
 ABC   | abc    |        | 
118
(5 rows)
119

120
-- force_not_null and force_null can be used together on the same column
121
ALTER FOREIGN TABLE text_csv ALTER COLUMN word1 OPTIONS (force_null 'true');
122
ALTER FOREIGN TABLE text_csv ALTER COLUMN word3 OPTIONS (force_not_null 'true');
123
-- force_not_null is not allowed to be specified at any foreign object level:
124
ALTER FOREIGN DATA WRAPPER file_fdw OPTIONS (ADD force_not_null '*'); -- ERROR
125
ERROR:  invalid option "force_not_null"
126
HINT:  There are no valid options in this context.
127
ALTER SERVER file_server OPTIONS (ADD force_not_null '*'); -- ERROR
128
ERROR:  invalid option "force_not_null"
129
HINT:  There are no valid options in this context.
130
CREATE USER MAPPING FOR public SERVER file_server OPTIONS (force_not_null '*'); -- ERROR
131
ERROR:  invalid option "force_not_null"
132
HINT:  There are no valid options in this context.
133
CREATE FOREIGN TABLE tbl () SERVER file_server OPTIONS (force_not_null '*'); -- ERROR
134
ERROR:  invalid option "force_not_null"
135
HINT:  Valid options in this context are: filename, program, format, header, delimiter, quote, escape, null, encoding
136
-- force_null is not allowed to be specified at any foreign object level:
137
ALTER FOREIGN DATA WRAPPER file_fdw OPTIONS (ADD force_null '*'); -- ERROR
138
ERROR:  invalid option "force_null"
139
HINT:  There are no valid options in this context.
140
ALTER SERVER file_server OPTIONS (ADD force_null '*'); -- ERROR
141
ERROR:  invalid option "force_null"
142
HINT:  There are no valid options in this context.
143
CREATE USER MAPPING FOR public SERVER file_server OPTIONS (force_null '*'); -- ERROR
144
ERROR:  invalid option "force_null"
145
HINT:  There are no valid options in this context.
146
CREATE FOREIGN TABLE tbl () SERVER file_server OPTIONS (force_null '*'); -- ERROR
147
ERROR:  invalid option "force_null"
148
HINT:  Valid options in this context are: filename, program, format, header, delimiter, quote, escape, null, encoding
149
-- basic query tests
150
SELECT * FROM agg_text WHERE b > 10.0 ORDER BY a;
151
  a  |   b    
152
-----+--------
153
  42 | 324.78
154
 100 | 99.097
155
(2 rows)
156

157
SELECT * FROM agg_csv ORDER BY a;
158
  a  |    b    
159
-----+---------
160
   0 | 0.09561
161
  42 |  324.78
162
 100 |  99.097
163
(3 rows)
164

165
SELECT * FROM agg_csv c JOIN agg_text t ON (t.a = c.a) ORDER BY c.a;
166
  a  |    b    |  a  |    b    
167
-----+---------+-----+---------
168
   0 | 0.09561 |   0 | 0.09561
169
  42 |  324.78 |  42 |  324.78
170
 100 |  99.097 | 100 |  99.097
171
(3 rows)
172

173
-- error context report tests
174
SELECT * FROM agg_bad;               -- ERROR
175
ERROR:  invalid input syntax for type real: "aaa"
176
CONTEXT:  COPY agg_bad, line 3, column b: "aaa"
177
-- misc query tests
178
\t on
179
EXPLAIN (VERBOSE, COSTS FALSE) SELECT * FROM agg_csv;
180
 Foreign Scan on public.agg_csv
181
   Output: a, b
182
   Foreign File: @abs_srcdir@/data/agg.csv
183

184
\t off
185
PREPARE st(int) AS SELECT * FROM agg_csv WHERE a = $1;
186
EXECUTE st(100);
187
  a  |   b    
188
-----+--------
189
 100 | 99.097
190
(1 row)
191

192
EXECUTE st(100);
193
  a  |   b    
194
-----+--------
195
 100 | 99.097
196
(1 row)
197

198
DEALLOCATE st;
199
-- tableoid
200
SELECT tableoid::regclass, b FROM agg_csv;
201
 tableoid |    b    
202
----------+---------
203
 agg_csv  |  99.097
204
 agg_csv  | 0.09561
205
 agg_csv  |  324.78
206
(3 rows)
207

208
-- updates aren't supported
209
INSERT INTO agg_csv VALUES(1,2.0);
210
ERROR:  cannot insert into foreign table "agg_csv"
211
UPDATE agg_csv SET a = 1;
212
ERROR:  cannot update foreign table "agg_csv"
213
DELETE FROM agg_csv WHERE a = 100;
214
ERROR:  cannot delete from foreign table "agg_csv"
215
-- but this should be allowed
216
SELECT * FROM agg_csv FOR UPDATE;
217
  a  |    b    
218
-----+---------
219
 100 |  99.097
220
   0 | 0.09561
221
  42 |  324.78
222
(3 rows)
223

224
-- copy from isn't supported either
225
COPY agg_csv FROM STDIN;
226
ERROR:  cannot insert into foreign table "agg_csv"
227
-- constraint exclusion tests
228
\t on
229
EXPLAIN (VERBOSE, COSTS FALSE) SELECT * FROM agg_csv WHERE a < 0;
230
 Foreign Scan on public.agg_csv
231
   Output: a, b
232
   Filter: (agg_csv.a < 0)
233
   Foreign File: @abs_srcdir@/data/agg.csv
234

235
\t off
236
SELECT * FROM agg_csv WHERE a < 0;
237
 a | b 
238
---+---
239
(0 rows)
240

241
SET constraint_exclusion = 'on';
242
\t on
243
EXPLAIN (VERBOSE, COSTS FALSE) SELECT * FROM agg_csv WHERE a < 0;
244
 Result
245
   Output: a, b
246
   One-Time Filter: false
247

248
\t off
249
SELECT * FROM agg_csv WHERE a < 0;
250
 a | b 
251
---+---
252
(0 rows)
253

254
RESET constraint_exclusion;
255
-- table inheritance tests
256
CREATE TABLE agg (a int2, b float4);
257
ALTER FOREIGN TABLE agg_csv INHERIT agg;
258
SELECT tableoid::regclass, * FROM agg;
259
 tableoid |  a  |    b    
260
----------+-----+---------
261
 agg_csv  | 100 |  99.097
262
 agg_csv  |   0 | 0.09561
263
 agg_csv  |  42 |  324.78
264
(3 rows)
265

266
SELECT tableoid::regclass, * FROM agg_csv;
267
 tableoid |  a  |    b    
268
----------+-----+---------
269
 agg_csv  | 100 |  99.097
270
 agg_csv  |   0 | 0.09561
271
 agg_csv  |  42 |  324.78
272
(3 rows)
273

274
SELECT tableoid::regclass, * FROM ONLY agg;
275
 tableoid | a | b 
276
----------+---+---
277
(0 rows)
278

279
-- updates aren't supported
280
UPDATE agg SET a = 1;
281
ERROR:  cannot update foreign table "agg_csv"
282
DELETE FROM agg WHERE a = 100;
283
ERROR:  cannot delete from foreign table "agg_csv"
284
-- but this should be allowed
285
SELECT tableoid::regclass, * FROM agg FOR UPDATE;
286
 tableoid |  a  |    b    
287
----------+-----+---------
288
 agg_csv  | 100 |  99.097
289
 agg_csv  |   0 | 0.09561
290
 agg_csv  |  42 |  324.78
291
(3 rows)
292

293
ALTER FOREIGN TABLE agg_csv NO INHERIT agg;
294
DROP TABLE agg;
295
-- declarative partitioning tests
296
SET ROLE regress_file_fdw_superuser;
297
CREATE TABLE pt (a int, b text) partition by list (a);
298
CREATE FOREIGN TABLE p1 partition of pt for values in (1) SERVER file_server
299
OPTIONS (format 'csv', filename '@abs_srcdir@/data/list1.csv', delimiter ',');
300
CREATE TABLE p2 partition of pt for values in (2);
301
SELECT tableoid::regclass, * FROM pt;
302
 tableoid | a |  b  
303
----------+---+-----
304
 p1       | 1 | foo
305
 p1       | 1 | bar
306
(2 rows)
307

308
SELECT tableoid::regclass, * FROM p1;
309
 tableoid | a |  b  
310
----------+---+-----
311
 p1       | 1 | foo
312
 p1       | 1 | bar
313
(2 rows)
314

315
SELECT tableoid::regclass, * FROM p2;
316
 tableoid | a | b 
317
----------+---+---
318
(0 rows)
319

320
COPY pt FROM '@abs_srcdir@/data/list2.bad' with (format 'csv', delimiter ','); -- ERROR
321
ERROR:  cannot insert into foreign table "p1"
322
CONTEXT:  COPY pt, line 2: "1,qux"
323
COPY pt FROM '@abs_srcdir@/data/list2.csv' with (format 'csv', delimiter ',');
324
SELECT tableoid::regclass, * FROM pt;
325
 tableoid | a |  b  
326
----------+---+-----
327
 p1       | 1 | foo
328
 p1       | 1 | bar
329
 p2       | 2 | baz
330
 p2       | 2 | qux
331
(4 rows)
332

333
SELECT tableoid::regclass, * FROM p1;
334
 tableoid | a |  b  
335
----------+---+-----
336
 p1       | 1 | foo
337
 p1       | 1 | bar
338
(2 rows)
339

340
SELECT tableoid::regclass, * FROM p2;
341
 tableoid | a |  b  
342
----------+---+-----
343
 p2       | 2 | baz
344
 p2       | 2 | qux
345
(2 rows)
346

347
INSERT INTO pt VALUES (1, 'xyzzy'); -- ERROR
348
ERROR:  cannot insert into foreign table "p1"
349
INSERT INTO pt VALUES (2, 'xyzzy');
350
UPDATE pt set a = 1 where a = 2; -- ERROR
351
ERROR:  cannot insert into foreign table "p1"
352
SELECT tableoid::regclass, * FROM pt;
353
 tableoid | a |   b   
354
----------+---+-------
355
 p1       | 1 | foo
356
 p1       | 1 | bar
357
 p2       | 2 | baz
358
 p2       | 2 | qux
359
 p2       | 2 | xyzzy
360
(5 rows)
361

362
SELECT tableoid::regclass, * FROM p1;
363
 tableoid | a |  b  
364
----------+---+-----
365
 p1       | 1 | foo
366
 p1       | 1 | bar
367
(2 rows)
368

369
SELECT tableoid::regclass, * FROM p2;
370
 tableoid | a |   b   
371
----------+---+-------
372
 p2       | 2 | baz
373
 p2       | 2 | qux
374
 p2       | 2 | xyzzy
375
(3 rows)
376

377
DROP TABLE pt;
378
-- privilege tests
379
SET ROLE regress_file_fdw_superuser;
380
SELECT * FROM agg_text ORDER BY a;
381
  a  |    b    
382
-----+---------
383
   0 | 0.09561
384
  42 |  324.78
385
  56 |     7.8
386
 100 |  99.097
387
(4 rows)
388

389
SET ROLE regress_file_fdw_user;
390
SELECT * FROM agg_text ORDER BY a;
391
  a  |    b    
392
-----+---------
393
   0 | 0.09561
394
  42 |  324.78
395
  56 |     7.8
396
 100 |  99.097
397
(4 rows)
398

399
SET ROLE regress_no_priv_user;
400
SELECT * FROM agg_text ORDER BY a;   -- ERROR
401
ERROR:  permission denied for foreign table agg_text
402
SET ROLE regress_file_fdw_user;
403
\t on
404
EXPLAIN (VERBOSE, COSTS FALSE) SELECT * FROM agg_text WHERE a > 0;
405
 Foreign Scan on public.agg_text
406
   Output: a, b
407
   Filter: (agg_text.a > 0)
408
   Foreign File: @abs_srcdir@/data/agg.data
409

410
\t off
411
-- file FDW allows foreign tables to be accessed without user mapping
412
DROP USER MAPPING FOR regress_file_fdw_user SERVER file_server;
413
SELECT * FROM agg_text ORDER BY a;
414
  a  |    b    
415
-----+---------
416
   0 | 0.09561
417
  42 |  324.78
418
  56 |     7.8
419
 100 |  99.097
420
(4 rows)
421

422
-- privilege tests for object
423
SET ROLE regress_file_fdw_superuser;
424
ALTER FOREIGN TABLE agg_text OWNER TO regress_file_fdw_user;
425
ALTER FOREIGN TABLE agg_text OPTIONS (SET format 'text');
426
SET ROLE regress_file_fdw_user;
427
ALTER FOREIGN TABLE agg_text OPTIONS (SET format 'text');
428
ERROR:  only superuser or a member of the pg_read_server_files role may specify the filename option of a file_fdw foreign table
429
SET ROLE regress_file_fdw_superuser;
430
-- cleanup
431
RESET ROLE;
432
DROP EXTENSION file_fdw CASCADE;
433
NOTICE:  drop cascades to 7 other objects
434
DETAIL:  drop cascades to server file_server
435
drop cascades to user mapping for regress_file_fdw_superuser on server file_server
436
drop cascades to user mapping for regress_no_priv_user on server file_server
437
drop cascades to foreign table agg_text
438
drop cascades to foreign table agg_csv
439
drop cascades to foreign table agg_bad
440
drop cascades to foreign table text_csv
441
DROP ROLE regress_file_fdw_superuser, regress_file_fdw_user, regress_no_priv_user;
442