summaryrefslogtreecommitdiffstats
path: root/contrib/file_fdw/expected
diff options
context:
space:
mode:
Diffstat (limited to 'contrib/file_fdw/expected')
-rw-r--r--contrib/file_fdw/expected/file_fdw.out492
1 files changed, 492 insertions, 0 deletions
diff --git a/contrib/file_fdw/expected/file_fdw.out b/contrib/file_fdw/expected/file_fdw.out
new file mode 100644
index 0000000..0029f36
--- /dev/null
+++ b/contrib/file_fdw/expected/file_fdw.out
@@ -0,0 +1,492 @@
+--
+-- Test foreign-data wrapper file_fdw.
+--
+-- directory paths are passed to us in environment variables
+\getenv abs_srcdir PG_ABS_SRCDIR
+-- Clean up in case a prior regression run failed
+SET client_min_messages TO 'warning';
+DROP ROLE IF EXISTS regress_file_fdw_superuser, regress_file_fdw_user, regress_no_priv_user;
+RESET client_min_messages;
+CREATE ROLE regress_file_fdw_superuser LOGIN SUPERUSER; -- is a superuser
+CREATE ROLE regress_file_fdw_user LOGIN; -- has priv and user mapping
+CREATE ROLE regress_no_priv_user LOGIN; -- has priv but no user mapping
+-- Install file_fdw
+CREATE EXTENSION file_fdw;
+-- create function to filter unstable results of EXPLAIN
+CREATE FUNCTION explain_filter(text) RETURNS setof text
+LANGUAGE plpgsql AS
+$$
+declare
+ ln text;
+begin
+ for ln in execute $1
+ loop
+ -- Remove the path portion of foreign file names
+ ln := regexp_replace(ln, 'Foreign File: .*/([a-z.]+)$', 'Foreign File: .../\1');
+ return next ln;
+ end loop;
+end;
+$$;
+-- regress_file_fdw_superuser owns fdw-related objects
+SET ROLE regress_file_fdw_superuser;
+CREATE SERVER file_server FOREIGN DATA WRAPPER file_fdw;
+-- privilege tests
+SET ROLE regress_file_fdw_user;
+CREATE FOREIGN DATA WRAPPER file_fdw2 HANDLER file_fdw_handler VALIDATOR file_fdw_validator; -- ERROR
+ERROR: permission denied to create foreign-data wrapper "file_fdw2"
+HINT: Must be superuser to create a foreign-data wrapper.
+CREATE SERVER file_server2 FOREIGN DATA WRAPPER file_fdw; -- ERROR
+ERROR: permission denied for foreign-data wrapper file_fdw
+CREATE USER MAPPING FOR regress_file_fdw_user SERVER file_server; -- ERROR
+ERROR: permission denied for foreign server file_server
+SET ROLE regress_file_fdw_superuser;
+GRANT USAGE ON FOREIGN SERVER file_server TO regress_file_fdw_user;
+SET ROLE regress_file_fdw_user;
+CREATE USER MAPPING FOR regress_file_fdw_user SERVER file_server;
+-- create user mappings and grant privilege to test users
+SET ROLE regress_file_fdw_superuser;
+CREATE USER MAPPING FOR regress_file_fdw_superuser SERVER file_server;
+CREATE USER MAPPING FOR regress_no_priv_user SERVER file_server;
+-- validator tests
+CREATE FOREIGN TABLE tbl () SERVER file_server OPTIONS (format 'xml'); -- ERROR
+ERROR: COPY format "xml" not recognized
+CREATE FOREIGN TABLE tbl () SERVER file_server OPTIONS (format 'text', quote ':'); -- ERROR
+ERROR: COPY quote available only in CSV mode
+CREATE FOREIGN TABLE tbl () SERVER file_server OPTIONS (format 'text', escape ':'); -- ERROR
+ERROR: COPY escape available only in CSV mode
+CREATE FOREIGN TABLE tbl () SERVER file_server OPTIONS (format 'binary', header 'true'); -- ERROR
+ERROR: cannot specify HEADER in BINARY mode
+CREATE FOREIGN TABLE tbl () SERVER file_server OPTIONS (format 'binary', quote ':'); -- ERROR
+ERROR: COPY quote available only in CSV mode
+CREATE FOREIGN TABLE tbl () SERVER file_server OPTIONS (format 'binary', escape ':'); -- ERROR
+ERROR: COPY escape available only in CSV mode
+CREATE FOREIGN TABLE tbl () SERVER file_server OPTIONS (format 'text', delimiter 'a'); -- ERROR
+ERROR: COPY delimiter cannot be "a"
+CREATE FOREIGN TABLE tbl () SERVER file_server OPTIONS (format 'text', escape '-'); -- ERROR
+ERROR: COPY escape available only in CSV mode
+CREATE FOREIGN TABLE tbl () SERVER file_server OPTIONS (format 'csv', quote '-', null '=-='); -- ERROR
+ERROR: CSV quote character must not appear in the NULL specification
+CREATE FOREIGN TABLE tbl () SERVER file_server OPTIONS (format 'csv', delimiter '-', null '=-='); -- ERROR
+ERROR: COPY delimiter must not appear in the NULL specification
+CREATE FOREIGN TABLE tbl () SERVER file_server OPTIONS (format 'csv', delimiter '-', quote '-'); -- ERROR
+ERROR: COPY delimiter and quote must be different
+CREATE FOREIGN TABLE tbl () SERVER file_server OPTIONS (format 'csv', delimiter '---'); -- ERROR
+ERROR: COPY delimiter must be a single one-byte character
+CREATE FOREIGN TABLE tbl () SERVER file_server OPTIONS (format 'csv', quote '---'); -- ERROR
+ERROR: COPY quote must be a single one-byte character
+CREATE FOREIGN TABLE tbl () SERVER file_server OPTIONS (format 'csv', escape '---'); -- ERROR
+ERROR: COPY escape must be a single one-byte character
+CREATE FOREIGN TABLE tbl () SERVER file_server OPTIONS (format 'text', delimiter '\'); -- ERROR
+ERROR: COPY delimiter cannot be "\"
+CREATE FOREIGN TABLE tbl () SERVER file_server OPTIONS (format 'text', delimiter '.'); -- ERROR
+ERROR: COPY delimiter cannot be "."
+CREATE FOREIGN TABLE tbl () SERVER file_server OPTIONS (format 'text', delimiter '1'); -- ERROR
+ERROR: COPY delimiter cannot be "1"
+CREATE FOREIGN TABLE tbl () SERVER file_server OPTIONS (format 'text', delimiter 'a'); -- ERROR
+ERROR: COPY delimiter cannot be "a"
+CREATE FOREIGN TABLE tbl () SERVER file_server OPTIONS (format 'csv', delimiter '
+'); -- ERROR
+ERROR: COPY delimiter cannot be newline or carriage return
+CREATE FOREIGN TABLE tbl () SERVER file_server OPTIONS (format 'csv', null '
+'); -- ERROR
+ERROR: COPY null representation cannot use newline or carriage return
+CREATE FOREIGN TABLE tbl () SERVER file_server; -- ERROR
+ERROR: either filename or program is required for file_fdw foreign tables
+\set filename :abs_srcdir '/data/agg.data'
+CREATE FOREIGN TABLE agg_text (
+ a int2 CHECK (a >= 0),
+ b float4
+) SERVER file_server
+OPTIONS (format 'text', filename :'filename', delimiter ' ', null '\N');
+GRANT SELECT ON agg_text TO regress_file_fdw_user;
+\set filename :abs_srcdir '/data/agg.csv'
+CREATE FOREIGN TABLE agg_csv (
+ a int2,
+ b float4
+) SERVER file_server
+OPTIONS (format 'csv', filename :'filename', header 'true', delimiter ';', quote '@', escape '"', null '');
+ALTER FOREIGN TABLE agg_csv ADD CHECK (a >= 0);
+\set filename :abs_srcdir '/data/agg.bad'
+CREATE FOREIGN TABLE agg_bad (
+ a int2,
+ b float4
+) SERVER file_server
+OPTIONS (format 'csv', filename :'filename', header 'true', delimiter ';', quote '@', escape '"', null '');
+ALTER FOREIGN TABLE agg_bad ADD CHECK (a >= 0);
+-- test header matching
+\set filename :abs_srcdir '/data/list1.csv'
+CREATE FOREIGN TABLE header_match ("1" int, foo text) SERVER file_server
+OPTIONS (format 'csv', filename :'filename', delimiter ',', header 'match');
+SELECT * FROM header_match;
+ 1 | foo
+---+-----
+ 1 | bar
+(1 row)
+
+CREATE FOREIGN TABLE header_doesnt_match (a int, foo text) SERVER file_server
+OPTIONS (format 'csv', filename :'filename', delimiter ',', header 'match');
+SELECT * FROM header_doesnt_match; -- ERROR
+ERROR: column name mismatch in header line field 1: got "1", expected "a"
+CONTEXT: COPY header_doesnt_match, line 1: "1,foo"
+-- per-column options tests
+\set filename :abs_srcdir '/data/text.csv'
+CREATE FOREIGN TABLE text_csv (
+ word1 text OPTIONS (force_not_null 'true'),
+ word2 text OPTIONS (force_not_null 'off'),
+ word3 text OPTIONS (force_null 'true'),
+ word4 text OPTIONS (force_null 'off')
+) SERVER file_server
+OPTIONS (format 'text', filename :'filename', null 'NULL');
+SELECT * FROM text_csv; -- ERROR
+ERROR: COPY force not null available only in CSV mode
+ALTER FOREIGN TABLE text_csv OPTIONS (SET format 'csv');
+\pset null _null_
+SELECT * FROM text_csv;
+ word1 | word2 | word3 | word4
+-------+--------+--------+--------
+ AAA | aaa | 123 |
+ XYZ | xyz | | 321
+ NULL | _null_ | _null_ | _null_
+ NULL | _null_ | _null_ | _null_
+ ABC | abc | |
+(5 rows)
+
+-- force_not_null and force_null can be used together on the same column
+ALTER FOREIGN TABLE text_csv ALTER COLUMN word1 OPTIONS (force_null 'true');
+ALTER FOREIGN TABLE text_csv ALTER COLUMN word3 OPTIONS (force_not_null 'true');
+-- force_not_null is not allowed to be specified at any foreign object level:
+ALTER FOREIGN DATA WRAPPER file_fdw OPTIONS (ADD force_not_null '*'); -- ERROR
+ERROR: invalid option "force_not_null"
+HINT: There are no valid options in this context.
+ALTER SERVER file_server OPTIONS (ADD force_not_null '*'); -- ERROR
+ERROR: invalid option "force_not_null"
+HINT: There are no valid options in this context.
+CREATE USER MAPPING FOR public SERVER file_server OPTIONS (force_not_null '*'); -- ERROR
+ERROR: invalid option "force_not_null"
+HINT: There are no valid options in this context.
+CREATE FOREIGN TABLE tbl () SERVER file_server OPTIONS (force_not_null '*'); -- ERROR
+ERROR: invalid option "force_not_null"
+HINT: Valid options in this context are: filename, program, format, header, delimiter, quote, escape, null, encoding
+-- force_null is not allowed to be specified at any foreign object level:
+ALTER FOREIGN DATA WRAPPER file_fdw OPTIONS (ADD force_null '*'); -- ERROR
+ERROR: invalid option "force_null"
+HINT: There are no valid options in this context.
+ALTER SERVER file_server OPTIONS (ADD force_null '*'); -- ERROR
+ERROR: invalid option "force_null"
+HINT: There are no valid options in this context.
+CREATE USER MAPPING FOR public SERVER file_server OPTIONS (force_null '*'); -- ERROR
+ERROR: invalid option "force_null"
+HINT: There are no valid options in this context.
+CREATE FOREIGN TABLE tbl () SERVER file_server OPTIONS (force_null '*'); -- ERROR
+ERROR: invalid option "force_null"
+HINT: Valid options in this context are: filename, program, format, header, delimiter, quote, escape, null, encoding
+-- basic query tests
+SELECT * FROM agg_text WHERE b > 10.0 ORDER BY a;
+ a | b
+-----+--------
+ 42 | 324.78
+ 100 | 99.097
+(2 rows)
+
+SELECT * FROM agg_csv ORDER BY a;
+ a | b
+-----+---------
+ 0 | 0.09561
+ 42 | 324.78
+ 100 | 99.097
+(3 rows)
+
+SELECT * FROM agg_csv c JOIN agg_text t ON (t.a = c.a) ORDER BY c.a;
+ a | b | a | b
+-----+---------+-----+---------
+ 0 | 0.09561 | 0 | 0.09561
+ 42 | 324.78 | 42 | 324.78
+ 100 | 99.097 | 100 | 99.097
+(3 rows)
+
+-- error context report tests
+SELECT * FROM agg_bad; -- ERROR
+ERROR: invalid input syntax for type real: "aaa"
+CONTEXT: COPY agg_bad, line 3, column b: "aaa"
+-- misc query tests
+\t on
+SELECT explain_filter('EXPLAIN (VERBOSE, COSTS FALSE) SELECT * FROM agg_csv');
+ Foreign Scan on public.agg_csv
+ Output: a, b
+ Foreign File: .../agg.csv
+
+\t off
+PREPARE st(int) AS SELECT * FROM agg_csv WHERE a = $1;
+EXECUTE st(100);
+ a | b
+-----+--------
+ 100 | 99.097
+(1 row)
+
+EXECUTE st(100);
+ a | b
+-----+--------
+ 100 | 99.097
+(1 row)
+
+DEALLOCATE st;
+-- tableoid
+SELECT tableoid::regclass, b FROM agg_csv;
+ tableoid | b
+----------+---------
+ agg_csv | 99.097
+ agg_csv | 0.09561
+ agg_csv | 324.78
+(3 rows)
+
+-- updates aren't supported
+INSERT INTO agg_csv VALUES(1,2.0);
+ERROR: cannot insert into foreign table "agg_csv"
+UPDATE agg_csv SET a = 1;
+ERROR: cannot update foreign table "agg_csv"
+DELETE FROM agg_csv WHERE a = 100;
+ERROR: cannot delete from foreign table "agg_csv"
+-- but this should be allowed
+SELECT * FROM agg_csv FOR UPDATE;
+ a | b
+-----+---------
+ 100 | 99.097
+ 0 | 0.09561
+ 42 | 324.78
+(3 rows)
+
+-- copy from isn't supported either
+COPY agg_csv FROM STDIN;
+ERROR: cannot insert into foreign table "agg_csv"
+-- constraint exclusion tests
+\t on
+SELECT explain_filter('EXPLAIN (VERBOSE, COSTS FALSE) SELECT * FROM agg_csv WHERE a < 0');
+ Foreign Scan on public.agg_csv
+ Output: a, b
+ Filter: (agg_csv.a < 0)
+ Foreign File: .../agg.csv
+
+\t off
+SELECT * FROM agg_csv WHERE a < 0;
+ a | b
+---+---
+(0 rows)
+
+SET constraint_exclusion = 'on';
+\t on
+SELECT explain_filter('EXPLAIN (VERBOSE, COSTS FALSE) SELECT * FROM agg_csv WHERE a < 0');
+ Result
+ Output: a, b
+ One-Time Filter: false
+
+\t off
+SELECT * FROM agg_csv WHERE a < 0;
+ a | b
+---+---
+(0 rows)
+
+RESET constraint_exclusion;
+-- table inheritance tests
+CREATE TABLE agg (a int2, b float4);
+ALTER FOREIGN TABLE agg_csv INHERIT agg;
+SELECT tableoid::regclass, * FROM agg;
+ tableoid | a | b
+----------+-----+---------
+ agg_csv | 100 | 99.097
+ agg_csv | 0 | 0.09561
+ agg_csv | 42 | 324.78
+(3 rows)
+
+SELECT tableoid::regclass, * FROM agg_csv;
+ tableoid | a | b
+----------+-----+---------
+ agg_csv | 100 | 99.097
+ agg_csv | 0 | 0.09561
+ agg_csv | 42 | 324.78
+(3 rows)
+
+SELECT tableoid::regclass, * FROM ONLY agg;
+ tableoid | a | b
+----------+---+---
+(0 rows)
+
+-- updates aren't supported
+UPDATE agg SET a = 1;
+ERROR: cannot update foreign table "agg_csv"
+DELETE FROM agg WHERE a = 100;
+ERROR: cannot delete from foreign table "agg_csv"
+-- but this should be allowed
+SELECT tableoid::regclass, * FROM agg FOR UPDATE;
+ tableoid | a | b
+----------+-----+---------
+ agg_csv | 100 | 99.097
+ agg_csv | 0 | 0.09561
+ agg_csv | 42 | 324.78
+(3 rows)
+
+ALTER FOREIGN TABLE agg_csv NO INHERIT agg;
+DROP TABLE agg;
+-- declarative partitioning tests
+SET ROLE regress_file_fdw_superuser;
+CREATE TABLE pt (a int, b text) partition by list (a);
+\set filename :abs_srcdir '/data/list1.csv'
+CREATE FOREIGN TABLE p1 partition of pt for values in (1) SERVER file_server
+OPTIONS (format 'csv', filename :'filename', delimiter ',');
+CREATE TABLE p2 partition of pt for values in (2);
+SELECT tableoid::regclass, * FROM pt;
+ tableoid | a | b
+----------+---+-----
+ p1 | 1 | foo
+ p1 | 1 | bar
+(2 rows)
+
+SELECT tableoid::regclass, * FROM p1;
+ tableoid | a | b
+----------+---+-----
+ p1 | 1 | foo
+ p1 | 1 | bar
+(2 rows)
+
+SELECT tableoid::regclass, * FROM p2;
+ tableoid | a | b
+----------+---+---
+(0 rows)
+
+\set filename :abs_srcdir '/data/list2.bad'
+COPY pt FROM :'filename' with (format 'csv', delimiter ','); -- ERROR
+ERROR: cannot insert into foreign table "p1"
+CONTEXT: COPY pt, line 2: "1,qux"
+\set filename :abs_srcdir '/data/list2.csv'
+COPY pt FROM :'filename' with (format 'csv', delimiter ',');
+SELECT tableoid::regclass, * FROM pt;
+ tableoid | a | b
+----------+---+-----
+ p1 | 1 | foo
+ p1 | 1 | bar
+ p2 | 2 | baz
+ p2 | 2 | qux
+(4 rows)
+
+SELECT tableoid::regclass, * FROM p1;
+ tableoid | a | b
+----------+---+-----
+ p1 | 1 | foo
+ p1 | 1 | bar
+(2 rows)
+
+SELECT tableoid::regclass, * FROM p2;
+ tableoid | a | b
+----------+---+-----
+ p2 | 2 | baz
+ p2 | 2 | qux
+(2 rows)
+
+INSERT INTO pt VALUES (1, 'xyzzy'); -- ERROR
+ERROR: cannot insert into foreign table "p1"
+INSERT INTO pt VALUES (2, 'xyzzy');
+UPDATE pt set a = 1 where a = 2; -- ERROR
+ERROR: cannot insert into foreign table "p1"
+SELECT tableoid::regclass, * FROM pt;
+ tableoid | a | b
+----------+---+-------
+ p1 | 1 | foo
+ p1 | 1 | bar
+ p2 | 2 | baz
+ p2 | 2 | qux
+ p2 | 2 | xyzzy
+(5 rows)
+
+SELECT tableoid::regclass, * FROM p1;
+ tableoid | a | b
+----------+---+-----
+ p1 | 1 | foo
+ p1 | 1 | bar
+(2 rows)
+
+SELECT tableoid::regclass, * FROM p2;
+ tableoid | a | b
+----------+---+-------
+ p2 | 2 | baz
+ p2 | 2 | qux
+ p2 | 2 | xyzzy
+(3 rows)
+
+DROP TABLE pt;
+-- generated column tests
+\set filename :abs_srcdir '/data/list1.csv'
+CREATE FOREIGN TABLE gft1 (a int, b text, c text GENERATED ALWAYS AS ('foo') STORED) SERVER file_server
+OPTIONS (format 'csv', filename :'filename', delimiter ',');
+SELECT a, c FROM gft1;
+ a | c
+---+--------
+ 1 | _null_
+ 1 | _null_
+(2 rows)
+
+DROP FOREIGN TABLE gft1;
+-- privilege tests
+SET ROLE regress_file_fdw_superuser;
+SELECT * FROM agg_text ORDER BY a;
+ a | b
+-----+---------
+ 0 | 0.09561
+ 42 | 324.78
+ 56 | 7.8
+ 100 | 99.097
+(4 rows)
+
+SET ROLE regress_file_fdw_user;
+SELECT * FROM agg_text ORDER BY a;
+ a | b
+-----+---------
+ 0 | 0.09561
+ 42 | 324.78
+ 56 | 7.8
+ 100 | 99.097
+(4 rows)
+
+SET ROLE regress_no_priv_user;
+SELECT * FROM agg_text ORDER BY a; -- ERROR
+ERROR: permission denied for foreign table agg_text
+SET ROLE regress_file_fdw_user;
+\t on
+SELECT explain_filter('EXPLAIN (VERBOSE, COSTS FALSE) SELECT * FROM agg_text WHERE a > 0');
+ Foreign Scan on public.agg_text
+ Output: a, b
+ Filter: (agg_text.a > 0)
+ Foreign File: .../agg.data
+
+\t off
+-- file FDW allows foreign tables to be accessed without user mapping
+DROP USER MAPPING FOR regress_file_fdw_user SERVER file_server;
+SELECT * FROM agg_text ORDER BY a;
+ a | b
+-----+---------
+ 0 | 0.09561
+ 42 | 324.78
+ 56 | 7.8
+ 100 | 99.097
+(4 rows)
+
+-- privilege tests for object
+SET ROLE regress_file_fdw_superuser;
+ALTER FOREIGN TABLE agg_text OWNER TO regress_file_fdw_user;
+ALTER FOREIGN TABLE agg_text OPTIONS (SET format 'text');
+SET ROLE regress_file_fdw_user;
+ALTER FOREIGN TABLE agg_text OPTIONS (SET format 'text');
+ERROR: only superuser or a role with privileges of the pg_read_server_files role may specify the filename option of a file_fdw foreign table
+SET ROLE regress_file_fdw_superuser;
+-- cleanup
+RESET ROLE;
+DROP EXTENSION file_fdw CASCADE;
+NOTICE: drop cascades to 9 other objects
+DETAIL: drop cascades to server file_server
+drop cascades to user mapping for regress_file_fdw_superuser on server file_server
+drop cascades to user mapping for regress_no_priv_user on server file_server
+drop cascades to foreign table agg_text
+drop cascades to foreign table agg_csv
+drop cascades to foreign table agg_bad
+drop cascades to foreign table header_match
+drop cascades to foreign table header_doesnt_match
+drop cascades to foreign table text_csv
+DROP ROLE regress_file_fdw_superuser, regress_file_fdw_user, regress_no_priv_user;