From dc9c3b0ff21465fa89d71eecf5e6cc956d647eca Mon Sep 17 00:00:00 2001 From: Tom Lane Date: Mon, 20 Dec 2021 14:15:52 -0500 Subject: Remove dynamic translation of regression test scripts, step 2. "git mv" all the input/*.source and output/*.source files into the corresponding sql/ and expected/ directories. Then remove the pg_regress and Makefile infrastructure associated with dynamic translation. Discussion: https://postgr.es/m/1655733.1639871614@sss.pgh.pa.us --- contrib/dblink/Makefile | 1 - contrib/dblink/expected/.gitignore | 1 - contrib/dblink/expected/paths.out | 18 ++ contrib/dblink/input/paths.source | 23 -- contrib/dblink/output/paths.source | 18 -- contrib/dblink/sql/.gitignore | 1 - contrib/dblink/sql/paths.sql | 23 ++ contrib/file_fdw/Makefile | 2 - contrib/file_fdw/expected/.gitignore | 1 - contrib/file_fdw/expected/file_fdw.out | 477 ++++++++++++++++++++++++++++++++ contrib/file_fdw/input/file_fdw.source | 253 ----------------- contrib/file_fdw/output/file_fdw.source | 477 -------------------------------- contrib/file_fdw/sql/.gitignore | 1 - contrib/file_fdw/sql/file_fdw.sql | 253 +++++++++++++++++ 14 files changed, 771 insertions(+), 778 deletions(-) delete mode 100644 contrib/dblink/expected/.gitignore create mode 100644 contrib/dblink/expected/paths.out delete mode 100644 contrib/dblink/input/paths.source delete mode 100644 contrib/dblink/output/paths.source delete mode 100644 contrib/dblink/sql/.gitignore create mode 100644 contrib/dblink/sql/paths.sql delete mode 100644 contrib/file_fdw/expected/.gitignore create mode 100644 contrib/file_fdw/expected/file_fdw.out delete mode 100644 contrib/file_fdw/input/file_fdw.source delete mode 100644 contrib/file_fdw/output/file_fdw.source delete mode 100644 contrib/file_fdw/sql/.gitignore create mode 100644 contrib/file_fdw/sql/file_fdw.sql (limited to 'contrib') diff --git a/contrib/dblink/Makefile b/contrib/dblink/Makefile index b008c8c4c4..6bb3ece38c 100644 --- a/contrib/dblink/Makefile +++ b/contrib/dblink/Makefile @@ -13,7 +13,6 @@ PGFILEDESC = "dblink - connect to other PostgreSQL databases" REGRESS = paths dblink REGRESS_OPTS = --dlpath=$(top_builddir)/src/test/regress -EXTRA_CLEAN = sql/paths.sql expected/paths.out ifdef USE_PGXS PG_CONFIG = pg_config diff --git a/contrib/dblink/expected/.gitignore b/contrib/dblink/expected/.gitignore deleted file mode 100644 index d9c7942c64..0000000000 --- a/contrib/dblink/expected/.gitignore +++ /dev/null @@ -1 +0,0 @@ -/paths.out diff --git a/contrib/dblink/expected/paths.out b/contrib/dblink/expected/paths.out new file mode 100644 index 0000000000..d09b169b19 --- /dev/null +++ b/contrib/dblink/expected/paths.out @@ -0,0 +1,18 @@ +-- Initialization that requires path substitution. +-- directory paths and dlsuffix are passed to us in environment variables +\getenv abs_srcdir PG_ABS_SRCDIR +\getenv libdir PG_LIBDIR +\getenv dlsuffix PG_DLSUFFIX +\set regresslib :libdir '/regress' :dlsuffix +CREATE FUNCTION setenv(text, text) + RETURNS void + AS :'regresslib', 'regress_setenv' + LANGUAGE C STRICT; +CREATE FUNCTION wait_pid(int) + RETURNS void + AS :'regresslib' + LANGUAGE C STRICT; +\set path :abs_srcdir '/' +\set fnbody 'SELECT setenv(''PGSERVICEFILE'', ' :'path' ' || $1)' +CREATE FUNCTION set_pgservicefile(text) RETURNS void LANGUAGE SQL + AS :'fnbody'; diff --git a/contrib/dblink/input/paths.source b/contrib/dblink/input/paths.source deleted file mode 100644 index 30403d9d5f..0000000000 --- a/contrib/dblink/input/paths.source +++ /dev/null @@ -1,23 +0,0 @@ --- Initialization that requires path substitution. - --- directory paths and dlsuffix are passed to us in environment variables -\getenv abs_srcdir PG_ABS_SRCDIR -\getenv libdir PG_LIBDIR -\getenv dlsuffix PG_DLSUFFIX - -\set regresslib :libdir '/regress' :dlsuffix - -CREATE FUNCTION setenv(text, text) - RETURNS void - AS :'regresslib', 'regress_setenv' - LANGUAGE C STRICT; - -CREATE FUNCTION wait_pid(int) - RETURNS void - AS :'regresslib' - LANGUAGE C STRICT; - -\set path :abs_srcdir '/' -\set fnbody 'SELECT setenv(''PGSERVICEFILE'', ' :'path' ' || $1)' -CREATE FUNCTION set_pgservicefile(text) RETURNS void LANGUAGE SQL - AS :'fnbody'; diff --git a/contrib/dblink/output/paths.source b/contrib/dblink/output/paths.source deleted file mode 100644 index d09b169b19..0000000000 --- a/contrib/dblink/output/paths.source +++ /dev/null @@ -1,18 +0,0 @@ --- Initialization that requires path substitution. --- directory paths and dlsuffix are passed to us in environment variables -\getenv abs_srcdir PG_ABS_SRCDIR -\getenv libdir PG_LIBDIR -\getenv dlsuffix PG_DLSUFFIX -\set regresslib :libdir '/regress' :dlsuffix -CREATE FUNCTION setenv(text, text) - RETURNS void - AS :'regresslib', 'regress_setenv' - LANGUAGE C STRICT; -CREATE FUNCTION wait_pid(int) - RETURNS void - AS :'regresslib' - LANGUAGE C STRICT; -\set path :abs_srcdir '/' -\set fnbody 'SELECT setenv(''PGSERVICEFILE'', ' :'path' ' || $1)' -CREATE FUNCTION set_pgservicefile(text) RETURNS void LANGUAGE SQL - AS :'fnbody'; diff --git a/contrib/dblink/sql/.gitignore b/contrib/dblink/sql/.gitignore deleted file mode 100644 index d17507846d..0000000000 --- a/contrib/dblink/sql/.gitignore +++ /dev/null @@ -1 +0,0 @@ -/paths.sql diff --git a/contrib/dblink/sql/paths.sql b/contrib/dblink/sql/paths.sql new file mode 100644 index 0000000000..30403d9d5f --- /dev/null +++ b/contrib/dblink/sql/paths.sql @@ -0,0 +1,23 @@ +-- Initialization that requires path substitution. + +-- directory paths and dlsuffix are passed to us in environment variables +\getenv abs_srcdir PG_ABS_SRCDIR +\getenv libdir PG_LIBDIR +\getenv dlsuffix PG_DLSUFFIX + +\set regresslib :libdir '/regress' :dlsuffix + +CREATE FUNCTION setenv(text, text) + RETURNS void + AS :'regresslib', 'regress_setenv' + LANGUAGE C STRICT; + +CREATE FUNCTION wait_pid(int) + RETURNS void + AS :'regresslib' + LANGUAGE C STRICT; + +\set path :abs_srcdir '/' +\set fnbody 'SELECT setenv(''PGSERVICEFILE'', ' :'path' ' || $1)' +CREATE FUNCTION set_pgservicefile(text) RETURNS void LANGUAGE SQL + AS :'fnbody'; diff --git a/contrib/file_fdw/Makefile b/contrib/file_fdw/Makefile index 4da9f2d697..885459d3c1 100644 --- a/contrib/file_fdw/Makefile +++ b/contrib/file_fdw/Makefile @@ -8,8 +8,6 @@ PGFILEDESC = "file_fdw - foreign data wrapper for files" REGRESS = file_fdw -EXTRA_CLEAN = sql/file_fdw.sql expected/file_fdw.out - ifdef USE_PGXS PG_CONFIG = pg_config PGXS := $(shell $(PG_CONFIG) --pgxs) diff --git a/contrib/file_fdw/expected/.gitignore b/contrib/file_fdw/expected/.gitignore deleted file mode 100644 index a464ad144f..0000000000 --- a/contrib/file_fdw/expected/.gitignore +++ /dev/null @@ -1 +0,0 @@ -/file_fdw.out diff --git a/contrib/file_fdw/expected/file_fdw.out b/contrib/file_fdw/expected/file_fdw.out new file mode 100644 index 0000000000..891146fef3 --- /dev/null +++ b/contrib/file_fdw/expected/file_fdw.out @@ -0,0 +1,477 @@ +-- +-- 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', header 'true'); -- ERROR +ERROR: COPY HEADER available only in CSV mode +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: COPY HEADER available only in CSV 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); +-- 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 member 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 7 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 text_csv +DROP ROLE regress_file_fdw_superuser, regress_file_fdw_user, regress_no_priv_user; diff --git a/contrib/file_fdw/input/file_fdw.source b/contrib/file_fdw/input/file_fdw.source deleted file mode 100644 index 0ea8b14508..0000000000 --- a/contrib/file_fdw/input/file_fdw.source +++ /dev/null @@ -1,253 +0,0 @@ --- --- 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 -CREATE SERVER file_server2 FOREIGN DATA WRAPPER file_fdw; -- ERROR -CREATE USER MAPPING FOR regress_file_fdw_user SERVER file_server; -- ERROR - -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 -CREATE FOREIGN TABLE tbl () SERVER file_server OPTIONS (format 'text', header 'true'); -- ERROR -CREATE FOREIGN TABLE tbl () SERVER file_server OPTIONS (format 'text', quote ':'); -- ERROR -CREATE FOREIGN TABLE tbl () SERVER file_server OPTIONS (format 'text', escape ':'); -- ERROR -CREATE FOREIGN TABLE tbl () SERVER file_server OPTIONS (format 'binary', header 'true'); -- ERROR -CREATE FOREIGN TABLE tbl () SERVER file_server OPTIONS (format 'binary', quote ':'); -- ERROR -CREATE FOREIGN TABLE tbl () SERVER file_server OPTIONS (format 'binary', escape ':'); -- ERROR -CREATE FOREIGN TABLE tbl () SERVER file_server OPTIONS (format 'text', delimiter 'a'); -- ERROR -CREATE FOREIGN TABLE tbl () SERVER file_server OPTIONS (format 'text', escape '-'); -- ERROR -CREATE FOREIGN TABLE tbl () SERVER file_server OPTIONS (format 'csv', quote '-', null '=-='); -- ERROR -CREATE FOREIGN TABLE tbl () SERVER file_server OPTIONS (format 'csv', delimiter '-', null '=-='); -- ERROR -CREATE FOREIGN TABLE tbl () SERVER file_server OPTIONS (format 'csv', delimiter '-', quote '-'); -- ERROR -CREATE FOREIGN TABLE tbl () SERVER file_server OPTIONS (format 'csv', delimiter '---'); -- ERROR -CREATE FOREIGN TABLE tbl () SERVER file_server OPTIONS (format 'csv', quote '---'); -- ERROR -CREATE FOREIGN TABLE tbl () SERVER file_server OPTIONS (format 'csv', escape '---'); -- ERROR -CREATE FOREIGN TABLE tbl () SERVER file_server OPTIONS (format 'text', delimiter '\'); -- ERROR -CREATE FOREIGN TABLE tbl () SERVER file_server OPTIONS (format 'text', delimiter '.'); -- ERROR -CREATE FOREIGN TABLE tbl () SERVER file_server OPTIONS (format 'text', delimiter '1'); -- ERROR -CREATE FOREIGN TABLE tbl () SERVER file_server OPTIONS (format 'text', delimiter 'a'); -- ERROR -CREATE FOREIGN TABLE tbl () SERVER file_server OPTIONS (format 'csv', delimiter ' -'); -- ERROR -CREATE FOREIGN TABLE tbl () SERVER file_server OPTIONS (format 'csv', null ' -'); -- ERROR -CREATE FOREIGN TABLE tbl () SERVER file_server; -- ERROR - -\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); - --- 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 -ALTER FOREIGN TABLE text_csv OPTIONS (SET format 'csv'); -\pset null _null_ -SELECT * FROM text_csv; - --- 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 -ALTER SERVER file_server OPTIONS (ADD force_not_null '*'); -- ERROR -CREATE USER MAPPING FOR public SERVER file_server OPTIONS (force_not_null '*'); -- ERROR -CREATE FOREIGN TABLE tbl () SERVER file_server OPTIONS (force_not_null '*'); -- ERROR - --- force_null is not allowed to be specified at any foreign object level: -ALTER FOREIGN DATA WRAPPER file_fdw OPTIONS (ADD force_null '*'); -- ERROR -ALTER SERVER file_server OPTIONS (ADD force_null '*'); -- ERROR -CREATE USER MAPPING FOR public SERVER file_server OPTIONS (force_null '*'); -- ERROR -CREATE FOREIGN TABLE tbl () SERVER file_server OPTIONS (force_null '*'); -- ERROR - --- basic query tests -SELECT * FROM agg_text WHERE b > 10.0 ORDER BY a; -SELECT * FROM agg_csv ORDER BY a; -SELECT * FROM agg_csv c JOIN agg_text t ON (t.a = c.a) ORDER BY c.a; - --- error context report tests -SELECT * FROM agg_bad; -- ERROR - --- misc query tests -\t on -SELECT explain_filter('EXPLAIN (VERBOSE, COSTS FALSE) SELECT * FROM agg_csv'); -\t off -PREPARE st(int) AS SELECT * FROM agg_csv WHERE a = $1; -EXECUTE st(100); -EXECUTE st(100); -DEALLOCATE st; - --- tableoid -SELECT tableoid::regclass, b FROM agg_csv; - --- updates aren't supported -INSERT INTO agg_csv VALUES(1,2.0); -UPDATE agg_csv SET a = 1; -DELETE FROM agg_csv WHERE a = 100; --- but this should be allowed -SELECT * FROM agg_csv FOR UPDATE; - --- copy from isn't supported either -COPY agg_csv FROM STDIN; -12 3.4 -\. - --- constraint exclusion tests -\t on -SELECT explain_filter('EXPLAIN (VERBOSE, COSTS FALSE) SELECT * FROM agg_csv WHERE a < 0'); -\t off -SELECT * FROM agg_csv WHERE a < 0; -SET constraint_exclusion = 'on'; -\t on -SELECT explain_filter('EXPLAIN (VERBOSE, COSTS FALSE) SELECT * FROM agg_csv WHERE a < 0'); -\t off -SELECT * FROM agg_csv WHERE a < 0; -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; -SELECT tableoid::regclass, * FROM agg_csv; -SELECT tableoid::regclass, * FROM ONLY agg; --- updates aren't supported -UPDATE agg SET a = 1; -DELETE FROM agg WHERE a = 100; --- but this should be allowed -SELECT tableoid::regclass, * FROM agg FOR UPDATE; -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; -SELECT tableoid::regclass, * FROM p1; -SELECT tableoid::regclass, * FROM p2; -\set filename :abs_srcdir '/data/list2.bad' -COPY pt FROM :'filename' with (format 'csv', delimiter ','); -- ERROR -\set filename :abs_srcdir '/data/list2.csv' -COPY pt FROM :'filename' with (format 'csv', delimiter ','); -SELECT tableoid::regclass, * FROM pt; -SELECT tableoid::regclass, * FROM p1; -SELECT tableoid::regclass, * FROM p2; -INSERT INTO pt VALUES (1, 'xyzzy'); -- ERROR -INSERT INTO pt VALUES (2, 'xyzzy'); -UPDATE pt set a = 1 where a = 2; -- ERROR -SELECT tableoid::regclass, * FROM pt; -SELECT tableoid::regclass, * FROM p1; -SELECT tableoid::regclass, * FROM p2; -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; -DROP FOREIGN TABLE gft1; - --- privilege tests -SET ROLE regress_file_fdw_superuser; -SELECT * FROM agg_text ORDER BY a; -SET ROLE regress_file_fdw_user; -SELECT * FROM agg_text ORDER BY a; -SET ROLE regress_no_priv_user; -SELECT * FROM agg_text ORDER BY a; -- ERROR -SET ROLE regress_file_fdw_user; -\t on -SELECT explain_filter('EXPLAIN (VERBOSE, COSTS FALSE) SELECT * FROM agg_text WHERE a > 0'); -\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; - --- 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'); -SET ROLE regress_file_fdw_superuser; - --- cleanup -RESET ROLE; -DROP EXTENSION file_fdw CASCADE; -DROP ROLE regress_file_fdw_superuser, regress_file_fdw_user, regress_no_priv_user; diff --git a/contrib/file_fdw/output/file_fdw.source b/contrib/file_fdw/output/file_fdw.source deleted file mode 100644 index 891146fef3..0000000000 --- a/contrib/file_fdw/output/file_fdw.source +++ /dev/null @@ -1,477 +0,0 @@ --- --- 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', header 'true'); -- ERROR -ERROR: COPY HEADER available only in CSV mode -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: COPY HEADER available only in CSV 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); --- 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 member 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 7 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 text_csv -DROP ROLE regress_file_fdw_superuser, regress_file_fdw_user, regress_no_priv_user; diff --git a/contrib/file_fdw/sql/.gitignore b/contrib/file_fdw/sql/.gitignore deleted file mode 100644 index ebf16fed94..0000000000 --- a/contrib/file_fdw/sql/.gitignore +++ /dev/null @@ -1 +0,0 @@ -/file_fdw.sql diff --git a/contrib/file_fdw/sql/file_fdw.sql b/contrib/file_fdw/sql/file_fdw.sql new file mode 100644 index 0000000000..0ea8b14508 --- /dev/null +++ b/contrib/file_fdw/sql/file_fdw.sql @@ -0,0 +1,253 @@ +-- +-- 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 +CREATE SERVER file_server2 FOREIGN DATA WRAPPER file_fdw; -- ERROR +CREATE USER MAPPING FOR regress_file_fdw_user SERVER file_server; -- ERROR + +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 +CREATE FOREIGN TABLE tbl () SERVER file_server OPTIONS (format 'text', header 'true'); -- ERROR +CREATE FOREIGN TABLE tbl () SERVER file_server OPTIONS (format 'text', quote ':'); -- ERROR +CREATE FOREIGN TABLE tbl () SERVER file_server OPTIONS (format 'text', escape ':'); -- ERROR +CREATE FOREIGN TABLE tbl () SERVER file_server OPTIONS (format 'binary', header 'true'); -- ERROR +CREATE FOREIGN TABLE tbl () SERVER file_server OPTIONS (format 'binary', quote ':'); -- ERROR +CREATE FOREIGN TABLE tbl () SERVER file_server OPTIONS (format 'binary', escape ':'); -- ERROR +CREATE FOREIGN TABLE tbl () SERVER file_server OPTIONS (format 'text', delimiter 'a'); -- ERROR +CREATE FOREIGN TABLE tbl () SERVER file_server OPTIONS (format 'text', escape '-'); -- ERROR +CREATE FOREIGN TABLE tbl () SERVER file_server OPTIONS (format 'csv', quote '-', null '=-='); -- ERROR +CREATE FOREIGN TABLE tbl () SERVER file_server OPTIONS (format 'csv', delimiter '-', null '=-='); -- ERROR +CREATE FOREIGN TABLE tbl () SERVER file_server OPTIONS (format 'csv', delimiter '-', quote '-'); -- ERROR +CREATE FOREIGN TABLE tbl () SERVER file_server OPTIONS (format 'csv', delimiter '---'); -- ERROR +CREATE FOREIGN TABLE tbl () SERVER file_server OPTIONS (format 'csv', quote '---'); -- ERROR +CREATE FOREIGN TABLE tbl () SERVER file_server OPTIONS (format 'csv', escape '---'); -- ERROR +CREATE FOREIGN TABLE tbl () SERVER file_server OPTIONS (format 'text', delimiter '\'); -- ERROR +CREATE FOREIGN TABLE tbl () SERVER file_server OPTIONS (format 'text', delimiter '.'); -- ERROR +CREATE FOREIGN TABLE tbl () SERVER file_server OPTIONS (format 'text', delimiter '1'); -- ERROR +CREATE FOREIGN TABLE tbl () SERVER file_server OPTIONS (format 'text', delimiter 'a'); -- ERROR +CREATE FOREIGN TABLE tbl () SERVER file_server OPTIONS (format 'csv', delimiter ' +'); -- ERROR +CREATE FOREIGN TABLE tbl () SERVER file_server OPTIONS (format 'csv', null ' +'); -- ERROR +CREATE FOREIGN TABLE tbl () SERVER file_server; -- ERROR + +\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); + +-- 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 +ALTER FOREIGN TABLE text_csv OPTIONS (SET format 'csv'); +\pset null _null_ +SELECT * FROM text_csv; + +-- 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 +ALTER SERVER file_server OPTIONS (ADD force_not_null '*'); -- ERROR +CREATE USER MAPPING FOR public SERVER file_server OPTIONS (force_not_null '*'); -- ERROR +CREATE FOREIGN TABLE tbl () SERVER file_server OPTIONS (force_not_null '*'); -- ERROR + +-- force_null is not allowed to be specified at any foreign object level: +ALTER FOREIGN DATA WRAPPER file_fdw OPTIONS (ADD force_null '*'); -- ERROR +ALTER SERVER file_server OPTIONS (ADD force_null '*'); -- ERROR +CREATE USER MAPPING FOR public SERVER file_server OPTIONS (force_null '*'); -- ERROR +CREATE FOREIGN TABLE tbl () SERVER file_server OPTIONS (force_null '*'); -- ERROR + +-- basic query tests +SELECT * FROM agg_text WHERE b > 10.0 ORDER BY a; +SELECT * FROM agg_csv ORDER BY a; +SELECT * FROM agg_csv c JOIN agg_text t ON (t.a = c.a) ORDER BY c.a; + +-- error context report tests +SELECT * FROM agg_bad; -- ERROR + +-- misc query tests +\t on +SELECT explain_filter('EXPLAIN (VERBOSE, COSTS FALSE) SELECT * FROM agg_csv'); +\t off +PREPARE st(int) AS SELECT * FROM agg_csv WHERE a = $1; +EXECUTE st(100); +EXECUTE st(100); +DEALLOCATE st; + +-- tableoid +SELECT tableoid::regclass, b FROM agg_csv; + +-- updates aren't supported +INSERT INTO agg_csv VALUES(1,2.0); +UPDATE agg_csv SET a = 1; +DELETE FROM agg_csv WHERE a = 100; +-- but this should be allowed +SELECT * FROM agg_csv FOR UPDATE; + +-- copy from isn't supported either +COPY agg_csv FROM STDIN; +12 3.4 +\. + +-- constraint exclusion tests +\t on +SELECT explain_filter('EXPLAIN (VERBOSE, COSTS FALSE) SELECT * FROM agg_csv WHERE a < 0'); +\t off +SELECT * FROM agg_csv WHERE a < 0; +SET constraint_exclusion = 'on'; +\t on +SELECT explain_filter('EXPLAIN (VERBOSE, COSTS FALSE) SELECT * FROM agg_csv WHERE a < 0'); +\t off +SELECT * FROM agg_csv WHERE a < 0; +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; +SELECT tableoid::regclass, * FROM agg_csv; +SELECT tableoid::regclass, * FROM ONLY agg; +-- updates aren't supported +UPDATE agg SET a = 1; +DELETE FROM agg WHERE a = 100; +-- but this should be allowed +SELECT tableoid::regclass, * FROM agg FOR UPDATE; +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; +SELECT tableoid::regclass, * FROM p1; +SELECT tableoid::regclass, * FROM p2; +\set filename :abs_srcdir '/data/list2.bad' +COPY pt FROM :'filename' with (format 'csv', delimiter ','); -- ERROR +\set filename :abs_srcdir '/data/list2.csv' +COPY pt FROM :'filename' with (format 'csv', delimiter ','); +SELECT tableoid::regclass, * FROM pt; +SELECT tableoid::regclass, * FROM p1; +SELECT tableoid::regclass, * FROM p2; +INSERT INTO pt VALUES (1, 'xyzzy'); -- ERROR +INSERT INTO pt VALUES (2, 'xyzzy'); +UPDATE pt set a = 1 where a = 2; -- ERROR +SELECT tableoid::regclass, * FROM pt; +SELECT tableoid::regclass, * FROM p1; +SELECT tableoid::regclass, * FROM p2; +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; +DROP FOREIGN TABLE gft1; + +-- privilege tests +SET ROLE regress_file_fdw_superuser; +SELECT * FROM agg_text ORDER BY a; +SET ROLE regress_file_fdw_user; +SELECT * FROM agg_text ORDER BY a; +SET ROLE regress_no_priv_user; +SELECT * FROM agg_text ORDER BY a; -- ERROR +SET ROLE regress_file_fdw_user; +\t on +SELECT explain_filter('EXPLAIN (VERBOSE, COSTS FALSE) SELECT * FROM agg_text WHERE a > 0'); +\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; + +-- 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'); +SET ROLE regress_file_fdw_superuser; + +-- cleanup +RESET ROLE; +DROP EXTENSION file_fdw CASCADE; +DROP ROLE regress_file_fdw_superuser, regress_file_fdw_user, regress_no_priv_user; -- cgit v1.2.3