Google Git
Sign in
apache / cloudberry / HEAD / . / contrib / postgres_fdw / sql / gp2pg_postgres_fdw.sql
blob: 5f87ddfd091c798c26ac6f10a4085b53492df44f [file] [log] [blame]
-- This file is based on postgres_fdw.sql.
-- Compared with postgres_fdw.sql, there are such changes as follows.
-- 1. Using postgres_fdw, gpdb connects to remote postgres server. This file is run in gpdb.
-- 2. Some features are not supported in gpdb, so related test cases is disabled.
-- The features no supported in gpdb are as follows.
-- 2.1 gpdb don't support that ModifyTable mixes distributed and entry-only tables.
-- 2.2 gpdb don't support triggers for statements.
-- 2.3 gpdb don't support REINDEX CONCURRENTLY.
-- 3. gpdb will generate different PLAN from postgres, such as join local table and remote table, select for update/share and so on.
-- ===================================================================
-- create FDW objects
-- ===================================================================
SET timezone = 'PST8PDT';
set optimizer_trace_fallback = on;
CREATE EXTENSION postgres_fdw;
CREATE SERVER testserver1 FOREIGN DATA WRAPPER postgres_fdw;
CREATE SERVER pgserver FOREIGN DATA WRAPPER postgres_fdw
OPTIONS (dbname 'contrib_regression', host 'localhost', port '5432');
CREATE SERVER pgserver2 FOREIGN DATA WRAPPER postgres_fdw
OPTIONS (dbname 'contrib_regression', host 'localhost', port '5432');
CREATE USER MAPPING FOR public SERVER testserver1
OPTIONS (user 'value', password 'value');
CREATE USER MAPPING FOR CURRENT_USER SERVER pgserver;
CREATE USER MAPPING FOR CURRENT_USER SERVER pgserver2;
-- ===================================================================
-- create objects used through FDW pgserver server
-- ===================================================================
\! env PGOPTIONS='' psql -p ${PG_PORT} contrib_regression -f sql/postgres_sql/gp2pg_postgres_init.sql
CREATE TYPE user_enum AS ENUM ('foo', 'bar', 'buz');
CREATE SCHEMA "S 1";
CREATE TABLE "S 1"."T 1" (
"C 1" int NOT NULL,
c2 int NOT NULL,
c3 text,
c4 timestamptz,
c5 timestamp,
c6 varchar(10),
c7 char(10),
c8 user_enum,
CONSTRAINT t1_pkey PRIMARY KEY ("C 1")
);
CREATE TABLE "S 1"."T 2" (
c1 int NOT NULL,
c2 text,
CONSTRAINT t2_pkey PRIMARY KEY (c1)
);
CREATE TABLE "S 1"."T 3" (
c1 int NOT NULL,
c2 int NOT NULL,
c3 text,
CONSTRAINT t3_pkey PRIMARY KEY (c1)
);
CREATE TABLE "S 1"."T 4" (
c1 int NOT NULL,
c2 int NOT NULL,
c3 text,
CONSTRAINT t4_pkey PRIMARY KEY (c1)
);
-- Disable autovacuum for these tables to avoid unexpected effects of that
ALTER TABLE "S 1"."T 1" SET (autovacuum_enabled = 'false');
ALTER TABLE "S 1"."T 2" SET (autovacuum_enabled = 'false');
ALTER TABLE "S 1"."T 3" SET (autovacuum_enabled = 'false');
ALTER TABLE "S 1"."T 4" SET (autovacuum_enabled = 'false');
INSERT INTO "S 1"."T 1"
SELECT id,
id % 10,
to_char(id, 'FM00000'),
'1970-01-01'::timestamptz + ((id % 100) || ' days')::interval,
'1970-01-01'::timestamp + ((id % 100) || ' days')::interval,
id % 10,
id % 10,
'foo'::user_enum
FROM generate_series(1, 1000) id;
INSERT INTO "S 1"."T 2"
SELECT id,
'AAA' || to_char(id, 'FM000')
FROM generate_series(1, 100) id;
INSERT INTO "S 1"."T 3"
SELECT id,
id + 1,
'AAA' || to_char(id, 'FM000')
FROM generate_series(1, 100) id;
DELETE FROM "S 1"."T 3" WHERE c1 % 2 != 0; -- delete for outer join tests
INSERT INTO "S 1"."T 4"
SELECT id,
id + 1,
'AAA' || to_char(id, 'FM000')
FROM generate_series(1, 100) id;
DELETE FROM "S 1"."T 4" WHERE c1 % 3 != 0; -- delete for outer join tests
ANALYZE "S 1"."T 1";
ANALYZE "S 1"."T 2";
ANALYZE "S 1"."T 3";
ANALYZE "S 1"."T 4";
-- ===================================================================
-- create foreign tables
-- ===================================================================
CREATE FOREIGN TABLE ft1 (
c0 int,
c1 int NOT NULL,
c2 int NOT NULL,
c3 text,
c4 timestamptz,
c5 timestamp,
c6 varchar(10),
c7 char(10) default 'ft1',
c8 user_enum
) SERVER pgserver;
ALTER FOREIGN TABLE ft1 DROP COLUMN c0;
CREATE FOREIGN TABLE ft2 (
c1 int NOT NULL,
c2 int NOT NULL,
cx int,
c3 text,
c4 timestamptz,
c5 timestamp,
c6 varchar(10),
c7 char(10) default 'ft2',
c8 user_enum
) SERVER pgserver;
ALTER FOREIGN TABLE ft2 DROP COLUMN cx;
CREATE FOREIGN TABLE ft4 (
c1 int NOT NULL,
c2 int NOT NULL,
c3 text
) SERVER pgserver OPTIONS (schema_name 'S 1', table_name 'T 3');
CREATE FOREIGN TABLE ft5 (
c1 int NOT NULL,
c2 int NOT NULL,
c3 text
) SERVER pgserver OPTIONS (schema_name 'S 1', table_name 'T 4');
CREATE FOREIGN TABLE ft6 (
c1 int NOT NULL,
c2 int NOT NULL,
c3 text
) SERVER pgserver2 OPTIONS (schema_name 'S 1', table_name 'T 4');
-- ===================================================================
-- tests for validator
-- ===================================================================
-- requiressl, krbsrvname and gsslib are omitted because they depend on
-- configure options
ALTER SERVER testserver1 OPTIONS (
use_remote_estimate 'false',
updatable 'true',
fdw_startup_cost '123.456',
fdw_tuple_cost '0.123',
service 'value',
connect_timeout 'value',
dbname 'value',
host 'value',
hostaddr 'value',
port 'value',
--client_encoding 'value',
application_name 'value',
--fallback_application_name 'value',
keepalives 'value',
keepalives_idle 'value',
keepalives_interval 'value',
tcp_user_timeout 'value',
-- requiressl 'value',
sslcompression 'value',
sslmode 'value',
sslcert 'value',
sslkey 'value',
sslrootcert 'value',
sslcrl 'value'
--requirepeer 'value',
-- krbsrvname 'value',
-- gsslib 'value',
--replication 'value'
);
-- Error, invalid list syntax
ALTER SERVER testserver1 OPTIONS (ADD extensions 'foo; bar');
-- OK but gets a warning
ALTER SERVER testserver1 OPTIONS (ADD extensions 'foo, bar');
ALTER SERVER testserver1 OPTIONS (DROP extensions);
ALTER USER MAPPING FOR public SERVER testserver1
OPTIONS (DROP user, DROP password);
ALTER FOREIGN TABLE ft1 OPTIONS (schema_name 'S 1', table_name 'T 1');
ALTER FOREIGN TABLE ft2 OPTIONS (schema_name 'S 1', table_name 'T 1');
ALTER FOREIGN TABLE ft1 ALTER COLUMN c1 OPTIONS (column_name 'C 1');
ALTER FOREIGN TABLE ft2 ALTER COLUMN c1 OPTIONS (column_name 'C 1');
\det+
-- Test that alteration of server options causes reconnection
-- Remote's errors might be non-English, so hide them to ensure stable results
\set VERBOSITY terse
SELECT c3, c4 FROM ft1 ORDER BY c3, c1 LIMIT 1; -- should work
ALTER SERVER pgserver OPTIONS (SET dbname 'no such database');
SELECT c3, c4 FROM ft1 ORDER BY c3, c1 LIMIT 1; -- should fail
DO $d$
BEGIN
EXECUTE $$ALTER SERVER pgserver
OPTIONS (SET dbname '$$||current_database()||$$')$$;
END;
$d$;
SELECT c3, c4 FROM ft1 ORDER BY c3, c1 LIMIT 1; -- should work again
-- Test that alteration of user mapping options causes reconnection
ALTER USER MAPPING FOR CURRENT_USER SERVER pgserver
OPTIONS (ADD user 'no such user');
SELECT c3, c4 FROM ft1 ORDER BY c3, c1 LIMIT 1; -- should fail
ALTER USER MAPPING FOR CURRENT_USER SERVER pgserver
OPTIONS (DROP user);
SELECT c3, c4 FROM ft1 ORDER BY c3, c1 LIMIT 1; -- should work again
\set VERBOSITY default
-- Now we should be able to run ANALYZE.
-- To exercise multiple code paths, we use local stats on ft1
-- and remote-estimate mode on ft2.
ANALYZE ft1;
ALTER FOREIGN TABLE ft2 OPTIONS (use_remote_estimate 'true');
-- ===================================================================
-- simple queries
-- ===================================================================
-- single table without alias
EXPLAIN (COSTS OFF) SELECT * FROM ft1 ORDER BY c3, c1 OFFSET 100 LIMIT 10;
SELECT * FROM ft1 ORDER BY c3, c1 OFFSET 100 LIMIT 10;
-- single table with alias - also test that tableoid sort is not pushed to remote side
EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM ft1 t1 ORDER BY t1.c3, t1.c1, t1.tableoid OFFSET 100 LIMIT 10;
SELECT * FROM ft1 t1 ORDER BY t1.c3, t1.c1, t1.tableoid OFFSET 100 LIMIT 10;
-- whole-row reference
EXPLAIN (VERBOSE, COSTS OFF) SELECT t1 FROM ft1 t1 ORDER BY t1.c3, t1.c1 OFFSET 100 LIMIT 10;
SELECT t1 FROM ft1 t1 ORDER BY t1.c3, t1.c1 OFFSET 100 LIMIT 10;
-- empty result
SELECT * FROM ft1 WHERE false;
-- with WHERE clause
EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM ft1 t1 WHERE t1.c1 = 101 AND t1.c6 = '1' AND t1.c7 >= '1';
SELECT * FROM ft1 t1 WHERE t1.c1 = 101 AND t1.c6 = '1' AND t1.c7 >= '1';
-- with FOR UPDATE/SHARE
EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM ft1 t1 WHERE c1 = 101 FOR UPDATE;
SELECT * FROM ft1 t1 WHERE c1 = 101 FOR UPDATE;
EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM ft1 t1 WHERE c1 = 102 FOR SHARE;
SELECT * FROM ft1 t1 WHERE c1 = 102 FOR SHARE;
-- aggregate
SELECT COUNT(*) FROM ft1 t1;
-- subquery
SELECT * FROM ft1 t1 WHERE t1.c3 IN (SELECT c3 FROM ft2 t2 WHERE c1 <= 10) ORDER BY c1;
-- subquery+MAX
SELECT * FROM ft1 t1 WHERE t1.c3 = (SELECT MAX(c3) FROM ft2 t2) ORDER BY c1;
-- used in CTE
WITH t1 AS (SELECT * FROM ft1 WHERE c1 <= 10) SELECT t2.c1, t2.c2, t2.c3, t2.c4 FROM t1, ft2 t2 WHERE t1.c1 = t2.c1 ORDER BY t1.c1;
-- fixed values
SELECT 'fixed', NULL FROM ft1 t1 WHERE c1 = 1;
-- Test forcing the remote server to produce sorted data for a merge join.
/* In Greenplum, enable_hashjoin is on by default; enable_nestloop and enable_mergejoin are false by default. */
SET enable_hashjoin TO false;
SET enable_nestloop TO false;
SET enable_mergejoin TO true;
-- inner join; expressions in the clauses appear in the equivalence class list
EXPLAIN (VERBOSE, COSTS OFF)
SELECT t1.c1, t2."C 1" FROM ft2 t1 JOIN "S 1"."T 1" t2 ON (t1.c1 = t2."C 1") order by t1.c1 OFFSET 100 LIMIT 10;
SELECT t1.c1, t2."C 1" FROM ft2 t1 JOIN "S 1"."T 1" t2 ON (t1.c1 = t2."C 1") order by t1.c1 OFFSET 100 LIMIT 10;
-- outer join; expressions in the clauses do not appear in equivalence class
-- list but no output change as compared to the previous query
EXPLAIN (VERBOSE, COSTS OFF)
SELECT t1.c1, t2."C 1" FROM ft2 t1 LEFT JOIN "S 1"."T 1" t2 ON (t1.c1 = t2."C 1") order by t1.c1 OFFSET 100 LIMIT 10;
SELECT t1.c1, t2."C 1" FROM ft2 t1 LEFT JOIN "S 1"."T 1" t2 ON (t1.c1 = t2."C 1") order by t1.c1 OFFSET 100 LIMIT 10;
-- A join between local table and foreign join. ORDER BY clause is added to the
-- foreign join so that the local table can be joined using merge join strategy.
EXPLAIN (VERBOSE, COSTS OFF)
SELECT t1."C 1" FROM "S 1"."T 1" t1 left join ft1 t2 join ft2 t3 on (t2.c1 = t3.c1) on (t3.c1 = t1."C 1") order by t1."C 1" OFFSET 100 LIMIT 10;
SELECT t1."C 1" FROM "S 1"."T 1" t1 left join ft1 t2 join ft2 t3 on (t2.c1 = t3.c1) on (t3.c1 = t1."C 1") order by t1."C 1" OFFSET 100 LIMIT 10;
-- Test similar to above, except that the full join prevents any equivalence
-- classes from being merged. This produces single relation equivalence classes
-- included in join restrictions.
EXPLAIN (VERBOSE, COSTS OFF)
SELECT t1."C 1", t2.c1, t3.c1 FROM "S 1"."T 1" t1 left join ft1 t2 full join ft2 t3 on (t2.c1 = t3.c1) on (t3.c1 = t1."C 1") order by t1."C 1" OFFSET 100 LIMIT 10;
SELECT t1."C 1", t2.c1, t3.c1 FROM "S 1"."T 1" t1 left join ft1 t2 full join ft2 t3 on (t2.c1 = t3.c1) on (t3.c1 = t1."C 1") order by t1."C 1" OFFSET 100 LIMIT 10;
-- Test similar to above with all full outer joins
EXPLAIN (VERBOSE, COSTS OFF)
SELECT t1."C 1", t2.c1, t3.c1 FROM "S 1"."T 1" t1 full join ft1 t2 full join ft2 t3 on (t2.c1 = t3.c1) on (t3.c1 = t1."C 1") order by t1."C 1" OFFSET 100 LIMIT 10;
SELECT t1."C 1", t2.c1, t3.c1 FROM "S 1"."T 1" t1 full join ft1 t2 full join ft2 t3 on (t2.c1 = t3.c1) on (t3.c1 = t1."C 1") order by t1."C 1" OFFSET 100 LIMIT 10;
RESET enable_hashjoin;
RESET enable_nestloop;
RESET enable_mergejoin;
-- ===================================================================
-- WHERE with remotely-executable conditions
-- ===================================================================
EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM ft1 t1 WHERE t1.c1 = 1; -- Var, OpExpr(b), Const
EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM ft1 t1 WHERE t1.c1 = 100 AND t1.c2 = 0; -- BoolExpr
EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM ft1 t1 WHERE c1 IS NULL; -- NullTest
EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM ft1 t1 WHERE c1 IS NOT NULL; -- NullTest
EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM ft1 t1 WHERE round(abs(c1), 0) = 1; -- FuncExpr
EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM ft1 t1 WHERE c1 = -c1; -- OpExpr(l)
EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM ft1 t1 WHERE 1 = c1!; -- OpExpr(r)
EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM ft1 t1 WHERE (c1 IS NOT NULL) IS DISTINCT FROM (c1 IS NOT NULL); -- DistinctExpr
EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM ft1 t1 WHERE c1 = ANY(ARRAY[c2, 1, c1 + 0]); -- ScalarArrayOpExpr
EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM ft1 t1 WHERE c1 = (ARRAY[c1,c2,3])[1]; -- SubscriptingRef
EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM ft1 t1 WHERE c6 = E'foo''s\\bar'; -- check special chars
EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM ft1 t1 WHERE c8 = 'foo'; -- can't be sent to remote
-- parameterized remote path for foreign table
EXPLAIN (VERBOSE, COSTS OFF)
SELECT * FROM "S 1"."T 1" a, ft2 b WHERE a."C 1" = 47 AND b.c1 = a.c2;
SELECT * FROM ft2 a, ft2 b WHERE a.c1 = 47 AND b.c1 = a.c2;
-- check both safe and unsafe join conditions
EXPLAIN (VERBOSE, COSTS OFF)
SELECT * FROM ft2 a, ft2 b
WHERE a.c2 = 6 AND b.c1 = a.c1 AND a.c8 = 'foo' AND b.c7 = upper(a.c7);
SELECT * FROM ft2 a, ft2 b
WHERE a.c2 = 6 AND b.c1 = a.c1 AND a.c8 = 'foo' AND b.c7 = upper(a.c7);
-- bug before 9.3.5 due to sloppy handling of remote-estimate parameters
SELECT * FROM ft1 WHERE c1 = ANY (ARRAY(SELECT c1 FROM ft2 WHERE c1 < 5));
SELECT * FROM ft2 WHERE c1 = ANY (ARRAY(SELECT c1 FROM ft1 WHERE c1 < 5));
-- we should not push order by clause with volatile expressions or unsafe
-- collations
EXPLAIN (VERBOSE, COSTS OFF)
SELECT * FROM ft2 ORDER BY ft2.c1, random();
EXPLAIN (VERBOSE, COSTS OFF)
SELECT * FROM ft2 ORDER BY ft2.c1, ft2.c3 collate "C";
-- user-defined operator/function
CREATE FUNCTION postgres_fdw_abs(int) RETURNS int AS $$
BEGIN
RETURN abs($1);
END
$$ LANGUAGE plpgsql IMMUTABLE;
CREATE OPERATOR === (
LEFTARG = int,
RIGHTARG = int,
PROCEDURE = int4eq,
COMMUTATOR = ===
);
-- built-in operators and functions can be shipped for remote execution
EXPLAIN (VERBOSE, COSTS OFF)
SELECT count(c3) FROM ft1 t1 WHERE t1.c1 = abs(t1.c2);
SELECT count(c3) FROM ft1 t1 WHERE t1.c1 = abs(t1.c2);
EXPLAIN (VERBOSE, COSTS OFF)
SELECT count(c3) FROM ft1 t1 WHERE t1.c1 = t1.c2;
SELECT count(c3) FROM ft1 t1 WHERE t1.c1 = t1.c2;
-- by default, user-defined ones cannot
EXPLAIN (VERBOSE, COSTS OFF)
SELECT count(c3) FROM ft1 t1 WHERE t1.c1 = postgres_fdw_abs(t1.c2);
SELECT count(c3) FROM ft1 t1 WHERE t1.c1 = postgres_fdw_abs(t1.c2);
EXPLAIN (VERBOSE, COSTS OFF)
SELECT count(c3) FROM ft1 t1 WHERE t1.c1 === t1.c2;
SELECT count(c3) FROM ft1 t1 WHERE t1.c1 === t1.c2;
-- ORDER BY can be shipped, though
EXPLAIN (VERBOSE, COSTS OFF)
SELECT * FROM ft1 t1 WHERE t1.c1 === t1.c2 order by t1.c2 limit 1;
SELECT * FROM ft1 t1 WHERE t1.c1 === t1.c2 order by t1.c2 limit 1;
-- but let's put them in an extension ...
ALTER EXTENSION postgres_fdw ADD FUNCTION postgres_fdw_abs(int);
ALTER EXTENSION postgres_fdw ADD OPERATOR === (int, int);
ALTER SERVER pgserver OPTIONS (ADD extensions 'postgres_fdw');
-- ... now they can be shipped
EXPLAIN (VERBOSE, COSTS OFF)
SELECT count(c3) FROM ft1 t1 WHERE t1.c1 = postgres_fdw_abs(t1.c2);
SELECT count(c3) FROM ft1 t1 WHERE t1.c1 = postgres_fdw_abs(t1.c2);
EXPLAIN (VERBOSE, COSTS OFF)
SELECT count(c3) FROM ft1 t1 WHERE t1.c1 === t1.c2;
SELECT count(c3) FROM ft1 t1 WHERE t1.c1 === t1.c2;
-- and both ORDER BY and LIMIT can be shipped
EXPLAIN (VERBOSE, COSTS OFF)
SELECT * FROM ft1 t1 WHERE t1.c1 === t1.c2 order by t1.c2 limit 1;
SELECT * FROM ft1 t1 WHERE t1.c1 === t1.c2 order by t1.c2 limit 1;
-- check schema-qualification of regconfig constant
\! env PGOPTIONS='' psql -p ${PG_PORT} contrib_regression -c 'CREATE TEXT SEARCH CONFIGURATION public.custom_search (COPY = pg_catalog.english);'
CREATE TEXT SEARCH CONFIGURATION public.custom_search
(COPY = pg_catalog.english);
EXPLAIN (VERBOSE, COSTS OFF)
SELECT c1, to_tsvector('custom_search'::regconfig, c3) FROM ft1
WHERE c1 = 642 AND length(to_tsvector('custom_search'::regconfig, c3)) > 0;
SELECT c1, to_tsvector('custom_search'::regconfig, c3) FROM ft1
WHERE c1 = 642 AND length(to_tsvector('custom_search'::regconfig, c3)) > 0;
-- ===================================================================
-- JOIN queries
-- ===================================================================
-- Analyze ft4 and ft5 so that we have better statistics. These tables do not
-- have use_remote_estimate set.
ANALYZE ft4;
ANALYZE ft5;
-- join two tables
EXPLAIN (VERBOSE, COSTS OFF)
SELECT t1.c1, t2.c1 FROM ft1 t1 JOIN ft2 t2 ON (t1.c1 = t2.c1) ORDER BY t1.c3, t1.c1 OFFSET 100 LIMIT 10;
SELECT t1.c1, t2.c1 FROM ft1 t1 JOIN ft2 t2 ON (t1.c1 = t2.c1) ORDER BY t1.c3, t1.c1 OFFSET 100 LIMIT 10;
-- join three tables
EXPLAIN (VERBOSE, COSTS OFF)
SELECT t1.c1, t2.c2, t3.c3 FROM ft1 t1 JOIN ft2 t2 ON (t1.c1 = t2.c1) JOIN ft4 t3 ON (t3.c1 = t1.c1) ORDER BY t1.c3, t1.c1 OFFSET 10 LIMIT 10;
SELECT t1.c1, t2.c2, t3.c3 FROM ft1 t1 JOIN ft2 t2 ON (t1.c1 = t2.c1) JOIN ft4 t3 ON (t3.c1 = t1.c1) ORDER BY t1.c3, t1.c1 OFFSET 10 LIMIT 10;
-- left outer join
EXPLAIN (VERBOSE, COSTS OFF)
SELECT t1.c1, t2.c1 FROM ft4 t1 LEFT JOIN ft5 t2 ON (t1.c1 = t2.c1) ORDER BY t1.c1, t2.c1 OFFSET 10 LIMIT 10;
SELECT t1.c1, t2.c1 FROM ft4 t1 LEFT JOIN ft5 t2 ON (t1.c1 = t2.c1) ORDER BY t1.c1, t2.c1 OFFSET 10 LIMIT 10;
-- left outer join three tables
EXPLAIN (VERBOSE, COSTS OFF)
SELECT t1.c1, t2.c2, t3.c3 FROM ft2 t1 LEFT JOIN ft2 t2 ON (t1.c1 = t2.c1) LEFT JOIN ft4 t3 ON (t2.c1 = t3.c1) OFFSET 10 LIMIT 10;
SELECT t1.c1, t2.c2, t3.c3 FROM ft2 t1 LEFT JOIN ft2 t2 ON (t1.c1 = t2.c1) LEFT JOIN ft4 t3 ON (t2.c1 = t3.c1) OFFSET 10 LIMIT 10;
-- left outer join + placement of clauses.
-- clauses within the nullable side are not pulled up, but top level clause on
-- non-nullable side is pushed into non-nullable side
EXPLAIN (VERBOSE, COSTS OFF)
SELECT t1.c1, t1.c2, t2.c1, t2.c2 FROM ft4 t1 LEFT JOIN (SELECT * FROM ft5 WHERE c1 < 10) t2 ON (t1.c1 = t2.c1) WHERE t1.c1 < 10;
SELECT t1.c1, t1.c2, t2.c1, t2.c2 FROM ft4 t1 LEFT JOIN (SELECT * FROM ft5 WHERE c1 < 10) t2 ON (t1.c1 = t2.c1) WHERE t1.c1 < 10;
-- clauses within the nullable side are not pulled up, but the top level clause
-- on nullable side is not pushed down into nullable side
EXPLAIN (VERBOSE, COSTS OFF)
SELECT t1.c1, t1.c2, t2.c1, t2.c2 FROM ft4 t1 LEFT JOIN (SELECT * FROM ft5 WHERE c1 < 10) t2 ON (t1.c1 = t2.c1)
WHERE (t2.c1 < 10 OR t2.c1 IS NULL) AND t1.c1 < 10;
SELECT t1.c1, t1.c2, t2.c1, t2.c2 FROM ft4 t1 LEFT JOIN (SELECT * FROM ft5 WHERE c1 < 10) t2 ON (t1.c1 = t2.c1)
WHERE (t2.c1 < 10 OR t2.c1 IS NULL) AND t1.c1 < 10;
-- right outer join
EXPLAIN (VERBOSE, COSTS OFF)
SELECT t1.c1, t2.c1 FROM ft5 t1 RIGHT JOIN ft4 t2 ON (t1.c1 = t2.c1) ORDER BY t2.c1, t1.c1 OFFSET 10 LIMIT 10;
SELECT t1.c1, t2.c1 FROM ft5 t1 RIGHT JOIN ft4 t2 ON (t1.c1 = t2.c1) ORDER BY t2.c1, t1.c1 OFFSET 10 LIMIT 10;
-- right outer join three tables
EXPLAIN (VERBOSE, COSTS OFF)
SELECT t1.c1, t2.c2, t3.c3 FROM ft2 t1 RIGHT JOIN ft2 t2 ON (t1.c1 = t2.c1) RIGHT JOIN ft4 t3 ON (t2.c1 = t3.c1) OFFSET 10 LIMIT 10;
SELECT t1.c1, t2.c2, t3.c3 FROM ft2 t1 RIGHT JOIN ft2 t2 ON (t1.c1 = t2.c1) RIGHT JOIN ft4 t3 ON (t2.c1 = t3.c1) OFFSET 10 LIMIT 10;
-- full outer join
EXPLAIN (VERBOSE, COSTS OFF)
SELECT t1.c1, t2.c1 FROM ft4 t1 FULL JOIN ft5 t2 ON (t1.c1 = t2.c1) ORDER BY t1.c1, t2.c1 OFFSET 45 LIMIT 10;
SELECT t1.c1, t2.c1 FROM ft4 t1 FULL JOIN ft5 t2 ON (t1.c1 = t2.c1) ORDER BY t1.c1, t2.c1 OFFSET 45 LIMIT 10;
-- full outer join with restrictions on the joining relations
-- a. the joining relations are both base relations
EXPLAIN (VERBOSE, COSTS OFF)
SELECT t1.c1, t2.c1 FROM (SELECT c1 FROM ft4 WHERE c1 between 50 and 60) t1 FULL JOIN (SELECT c1 FROM ft5 WHERE c1 between 50 and 60) t2 ON (t1.c1 = t2.c1) ORDER BY t1.c1, t2.c1;
SELECT t1.c1, t2.c1 FROM (SELECT c1 FROM ft4 WHERE c1 between 50 and 60) t1 FULL JOIN (SELECT c1 FROM ft5 WHERE c1 between 50 and 60) t2 ON (t1.c1 = t2.c1) ORDER BY t1.c1, t2.c1;
EXPLAIN (VERBOSE, COSTS OFF)
SELECT 1 FROM (SELECT c1 FROM ft4 WHERE c1 between 50 and 60) t1 FULL JOIN (SELECT c1 FROM ft5 WHERE c1 between 50 and 60) t2 ON (TRUE) OFFSET 10 LIMIT 10;
SELECT 1 FROM (SELECT c1 FROM ft4 WHERE c1 between 50 and 60) t1 FULL JOIN (SELECT c1 FROM ft5 WHERE c1 between 50 and 60) t2 ON (TRUE) OFFSET 10 LIMIT 10;
-- b. one of the joining relations is a base relation and the other is a join
-- relation
EXPLAIN (VERBOSE, COSTS OFF)
SELECT t1.c1, ss.a, ss.b FROM (SELECT c1 FROM ft4 WHERE c1 between 50 and 60) t1 FULL JOIN (SELECT t2.c1, t3.c1 FROM ft4 t2 LEFT JOIN ft5 t3 ON (t2.c1 = t3.c1) WHERE (t2.c1 between 50 and 60)) ss(a, b) ON (t1.c1 = ss.a) ORDER BY t1.c1, ss.a, ss.b;
SELECT t1.c1, ss.a, ss.b FROM (SELECT c1 FROM ft4 WHERE c1 between 50 and 60) t1 FULL JOIN (SELECT t2.c1, t3.c1 FROM ft4 t2 LEFT JOIN ft5 t3 ON (t2.c1 = t3.c1) WHERE (t2.c1 between 50 and 60)) ss(a, b) ON (t1.c1 = ss.a) ORDER BY t1.c1, ss.a, ss.b;
-- c. test deparsing the remote query as nested subqueries
EXPLAIN (VERBOSE, COSTS OFF)
SELECT t1.c1, ss.a, ss.b FROM (SELECT c1 FROM ft4 WHERE c1 between 50 and 60) t1 FULL JOIN (SELECT t2.c1, t3.c1 FROM (SELECT c1 FROM ft4 WHERE c1 between 50 and 60) t2 FULL JOIN (SELECT c1 FROM ft5 WHERE c1 between 50 and 60) t3 ON (t2.c1 = t3.c1) WHERE t2.c1 IS NULL OR t2.c1 IS NOT NULL) ss(a, b) ON (t1.c1 = ss.a) ORDER BY t1.c1, ss.a, ss.b;
SELECT t1.c1, ss.a, ss.b FROM (SELECT c1 FROM ft4 WHERE c1 between 50 and 60) t1 FULL JOIN (SELECT t2.c1, t3.c1 FROM (SELECT c1 FROM ft4 WHERE c1 between 50 and 60) t2 FULL JOIN (SELECT c1 FROM ft5 WHERE c1 between 50 and 60) t3 ON (t2.c1 = t3.c1) WHERE t2.c1 IS NULL OR t2.c1 IS NOT NULL) ss(a, b) ON (t1.c1 = ss.a) ORDER BY t1.c1, ss.a, ss.b;
-- d. test deparsing rowmarked relations as subqueries
EXPLAIN (VERBOSE, COSTS OFF)
SELECT t1.c1, ss.a, ss.b FROM (SELECT c1 FROM "S 1"."T 3" WHERE c1 = 50) t1 INNER JOIN (SELECT t2.c1, t3.c1 FROM (SELECT c1 FROM ft4 WHERE c1 between 50 and 60) t2 FULL JOIN (SELECT c1 FROM ft5 WHERE c1 between 50 and 60) t3 ON (t2.c1 = t3.c1) WHERE t2.c1 IS NULL OR t2.c1 IS NOT NULL) ss(a, b) ON (TRUE) ORDER BY t1.c1, ss.a, ss.b FOR UPDATE OF t1;
SELECT t1.c1, ss.a, ss.b FROM (SELECT c1 FROM "S 1"."T 3" WHERE c1 = 50) t1 INNER JOIN (SELECT t2.c1, t3.c1 FROM (SELECT c1 FROM ft4 WHERE c1 between 50 and 60) t2 FULL JOIN (SELECT c1 FROM ft5 WHERE c1 between 50 and 60) t3 ON (t2.c1 = t3.c1) WHERE t2.c1 IS NULL OR t2.c1 IS NOT NULL) ss(a, b) ON (TRUE) ORDER BY t1.c1, ss.a, ss.b FOR UPDATE OF t1;
-- full outer join + inner join
EXPLAIN (VERBOSE, COSTS OFF)
SELECT t1.c1, t2.c1, t3.c1 FROM ft4 t1 INNER JOIN ft5 t2 ON (t1.c1 = t2.c1 + 1 and t1.c1 between 50 and 60) FULL JOIN ft4 t3 ON (t2.c1 = t3.c1) ORDER BY t1.c1, t2.c1, t3.c1 LIMIT 10;
SELECT t1.c1, t2.c1, t3.c1 FROM ft4 t1 INNER JOIN ft5 t2 ON (t1.c1 = t2.c1 + 1 and t1.c1 between 50 and 60) FULL JOIN ft4 t3 ON (t2.c1 = t3.c1) ORDER BY t1.c1, t2.c1, t3.c1 LIMIT 10;
-- full outer join three tables
EXPLAIN (VERBOSE, COSTS OFF)
SELECT t1.c1, t2.c2, t3.c3 FROM ft2 t1 FULL JOIN ft2 t2 ON (t1.c1 = t2.c1) FULL JOIN ft4 t3 ON (t2.c1 = t3.c1) OFFSET 10 LIMIT 10;
SELECT t1.c1, t2.c2, t3.c3 FROM ft2 t1 FULL JOIN ft2 t2 ON (t1.c1 = t2.c1) FULL JOIN ft4 t3 ON (t2.c1 = t3.c1) ORDER BY t1.c1, t2.c2, t3.c3 OFFSET 10 LIMIT 10;
-- full outer join + right outer join
EXPLAIN (VERBOSE, COSTS OFF)
SELECT t1.c1, t2.c2, t3.c3 FROM ft2 t1 FULL JOIN ft2 t2 ON (t1.c1 = t2.c1) RIGHT JOIN ft4 t3 ON (t2.c1 = t3.c1) OFFSET 10 LIMIT 10;
SELECT t1.c1, t2.c2, t3.c3 FROM ft2 t1 FULL JOIN ft2 t2 ON (t1.c1 = t2.c1) RIGHT JOIN ft4 t3 ON (t2.c1 = t3.c1) ORDER BY t1.c1, t2.c2, t3.c3 OFFSET 10 LIMIT 10;
-- right outer join + full outer join
EXPLAIN (VERBOSE, COSTS OFF)
SELECT t1.c1, t2.c2, t3.c3 FROM ft2 t1 RIGHT JOIN ft2 t2 ON (t1.c1 = t2.c1) FULL JOIN ft4 t3 ON (t2.c1 = t3.c1) OFFSET 10 LIMIT 10;
SELECT t1.c1, t2.c2, t3.c3 FROM ft2 t1 RIGHT JOIN ft2 t2 ON (t1.c1 = t2.c1) FULL JOIN ft4 t3 ON (t2.c1 = t3.c1) ORDER BY t1.c1, t2.c2, t3.c3 OFFSET 10 LIMIT 10;
-- full outer join + left outer join
EXPLAIN (VERBOSE, COSTS OFF)
SELECT t1.c1, t2.c2, t3.c3 FROM ft2 t1 FULL JOIN ft2 t2 ON (t1.c1 = t2.c1) LEFT JOIN ft4 t3 ON (t2.c1 = t3.c1) OFFSET 10 LIMIT 10;
SELECT t1.c1, t2.c2, t3.c3 FROM ft2 t1 FULL JOIN ft2 t2 ON (t1.c1 = t2.c1) LEFT JOIN ft4 t3 ON (t2.c1 = t3.c1) ORDER BY t1.c1, t2.c2, t3.c3 OFFSET 10 LIMIT 10;
-- left outer join + full outer join
EXPLAIN (VERBOSE, COSTS OFF)
SELECT t1.c1, t2.c2, t3.c3 FROM ft2 t1 LEFT JOIN ft2 t2 ON (t1.c1 = t2.c1) FULL JOIN ft4 t3 ON (t2.c1 = t3.c1) OFFSET 10 LIMIT 10;
SELECT t1.c1, t2.c2, t3.c3 FROM ft2 t1 LEFT JOIN ft2 t2 ON (t1.c1 = t2.c1) FULL JOIN ft4 t3 ON (t2.c1 = t3.c1) ORDER BY t1.c1, t2.c2, t3.c3 OFFSET 10 LIMIT 10;
-- right outer join + left outer join
EXPLAIN (VERBOSE, COSTS OFF)
SELECT t1.c1, t2.c2, t3.c3 FROM ft2 t1 RIGHT JOIN ft2 t2 ON (t1.c1 = t2.c1) LEFT JOIN ft4 t3 ON (t2.c1 = t3.c1) OFFSET 10 LIMIT 10;
SELECT t1.c1, t2.c2, t3.c3 FROM ft2 t1 RIGHT JOIN ft2 t2 ON (t1.c1 = t2.c1) LEFT JOIN ft4 t3 ON (t2.c1 = t3.c1) OFFSET 10 LIMIT 10;
-- left outer join + right outer join
EXPLAIN (VERBOSE, COSTS OFF)
SELECT t1.c1, t2.c2, t3.c3 FROM ft2 t1 LEFT JOIN ft2 t2 ON (t1.c1 = t2.c1) RIGHT JOIN ft4 t3 ON (t2.c1 = t3.c1) OFFSET 10 LIMIT 10;
SELECT t1.c1, t2.c2, t3.c3 FROM ft2 t1 LEFT JOIN ft2 t2 ON (t1.c1 = t2.c1) RIGHT JOIN ft4 t3 ON (t2.c1 = t3.c1) OFFSET 10 LIMIT 10;
-- full outer join + WHERE clause, only matched rows
EXPLAIN (VERBOSE, COSTS OFF)
SELECT t1.c1, t2.c1 FROM ft4 t1 FULL JOIN ft5 t2 ON (t1.c1 = t2.c1) WHERE (t1.c1 = t2.c1 OR t1.c1 IS NULL) ORDER BY t1.c1, t2.c1 OFFSET 10 LIMIT 10;
SELECT t1.c1, t2.c1 FROM ft4 t1 FULL JOIN ft5 t2 ON (t1.c1 = t2.c1) WHERE (t1.c1 = t2.c1 OR t1.c1 IS NULL) ORDER BY t1.c1, t2.c1 OFFSET 10 LIMIT 10;
-- full outer join + WHERE clause with shippable extensions set
EXPLAIN (VERBOSE, COSTS OFF)
SELECT t1.c1, t2.c2, t1.c3 FROM ft1 t1 FULL JOIN ft2 t2 ON (t1.c1 = t2.c1) WHERE postgres_fdw_abs(t1.c1) > 0 OFFSET 10 LIMIT 10;
ALTER SERVER pgserver OPTIONS (DROP extensions);
-- full outer join + WHERE clause with shippable extensions not set
EXPLAIN (VERBOSE, COSTS OFF)
SELECT t1.c1, t2.c2, t1.c3 FROM ft1 t1 FULL JOIN ft2 t2 ON (t1.c1 = t2.c1) WHERE postgres_fdw_abs(t1.c1) > 0 OFFSET 10 LIMIT 10;
ALTER SERVER pgserver OPTIONS (ADD extensions 'postgres_fdw');
-- join two tables with FOR UPDATE clause
-- tests whole-row reference for row marks
EXPLAIN (VERBOSE, COSTS OFF)
SELECT t1.c1, t2.c1 FROM ft1 t1 JOIN ft2 t2 ON (t1.c1 = t2.c1) ORDER BY t1.c3, t1.c1 OFFSET 100 LIMIT 10 FOR UPDATE OF t1;
SELECT t1.c1, t2.c1 FROM ft1 t1 JOIN ft2 t2 ON (t1.c1 = t2.c1) ORDER BY t1.c3, t1.c1 OFFSET 100 LIMIT 10 FOR UPDATE OF t1;
EXPLAIN (VERBOSE, COSTS OFF)
SELECT t1.c1, t2.c1 FROM ft1 t1 JOIN ft2 t2 ON (t1.c1 = t2.c1) ORDER BY t1.c3, t1.c1 OFFSET 100 LIMIT 10 FOR UPDATE;
SELECT t1.c1, t2.c1 FROM ft1 t1 JOIN ft2 t2 ON (t1.c1 = t2.c1) ORDER BY t1.c3, t1.c1 OFFSET 100 LIMIT 10 FOR UPDATE;
-- join two tables with FOR SHARE clause
EXPLAIN (VERBOSE, COSTS OFF)
SELECT t1.c1, t2.c1 FROM ft1 t1 JOIN ft2 t2 ON (t1.c1 = t2.c1) ORDER BY t1.c3, t1.c1 OFFSET 100 LIMIT 10 FOR SHARE OF t1;
SELECT t1.c1, t2.c1 FROM ft1 t1 JOIN ft2 t2 ON (t1.c1 = t2.c1) ORDER BY t1.c3, t1.c1 OFFSET 100 LIMIT 10 FOR SHARE OF t1;
EXPLAIN (VERBOSE, COSTS OFF)
SELECT t1.c1, t2.c1 FROM ft1 t1 JOIN ft2 t2 ON (t1.c1 = t2.c1) ORDER BY t1.c3, t1.c1 OFFSET 100 LIMIT 10 FOR SHARE;
SELECT t1.c1, t2.c1 FROM ft1 t1 JOIN ft2 t2 ON (t1.c1 = t2.c1) ORDER BY t1.c3, t1.c1 OFFSET 100 LIMIT 10 FOR SHARE;
-- join in CTE
EXPLAIN (VERBOSE, COSTS OFF)
WITH t (c1_1, c1_3, c2_1) AS MATERIALIZED (SELECT t1.c1, t1.c3, t2.c1 FROM ft1 t1 JOIN ft2 t2 ON (t1.c1 = t2.c1)) SELECT c1_1, c2_1 FROM t ORDER BY c1_3, c1_1 OFFSET 100 LIMIT 10;
WITH t (c1_1, c1_3, c2_1) AS MATERIALIZED (SELECT t1.c1, t1.c3, t2.c1 FROM ft1 t1 JOIN ft2 t2 ON (t1.c1 = t2.c1)) SELECT c1_1, c2_1 FROM t ORDER BY c1_3, c1_1 OFFSET 100 LIMIT 10;
-- ctid with whole-row reference
EXPLAIN (VERBOSE, COSTS OFF)
SELECT t1.ctid, t1, t2, t1.c1 FROM ft1 t1 JOIN ft2 t2 ON (t1.c1 = t2.c1) ORDER BY t1.c3, t1.c1 OFFSET 100 LIMIT 10;
-- SEMI JOIN, not pushed down
-- In Greenplum, enable_mergejoin is false by default.
SET enable_mergejoin TO true;
EXPLAIN (VERBOSE, COSTS OFF)
SELECT t1.c1 FROM ft1 t1 WHERE EXISTS (SELECT 1 FROM ft2 t2 WHERE t1.c1 = t2.c1) ORDER BY t1.c1 OFFSET 100 LIMIT 10;
SELECT t1.c1 FROM ft1 t1 WHERE EXISTS (SELECT 1 FROM ft2 t2 WHERE t1.c1 = t2.c1) ORDER BY t1.c1 OFFSET 100 LIMIT 10;
-- ANTI JOIN, not pushed down
EXPLAIN (VERBOSE, COSTS OFF)
SELECT t1.c1 FROM ft1 t1 WHERE NOT EXISTS (SELECT 1 FROM ft2 t2 WHERE t1.c1 = t2.c2) ORDER BY t1.c1 OFFSET 100 LIMIT 10;
SELECT t1.c1 FROM ft1 t1 WHERE NOT EXISTS (SELECT 1 FROM ft2 t2 WHERE t1.c1 = t2.c2) ORDER BY t1.c1 OFFSET 100 LIMIT 10;
-- CROSS JOIN can be pushed down
EXPLAIN (VERBOSE, COSTS OFF)
SELECT t1.c1, t2.c1 FROM ft1 t1 CROSS JOIN ft2 t2 ORDER BY t1.c1, t2.c1 OFFSET 100 LIMIT 10;
SELECT t1.c1, t2.c1 FROM ft1 t1 CROSS JOIN ft2 t2 ORDER BY t1.c1, t2.c1 OFFSET 100 LIMIT 10;
-- different server, not pushed down. No result expected.
EXPLAIN (VERBOSE, COSTS OFF)
SELECT t1.c1, t2.c1 FROM ft5 t1 JOIN ft6 t2 ON (t1.c1 = t2.c1) ORDER BY t1.c1, t2.c1 OFFSET 100 LIMIT 10;
SELECT t1.c1, t2.c1 FROM ft5 t1 JOIN ft6 t2 ON (t1.c1 = t2.c1) ORDER BY t1.c1, t2.c1 OFFSET 100 LIMIT 10;
-- unsafe join conditions (c8 has a UDT), not pushed down. Practically a CROSS
-- JOIN since c8 in both tables has same value.
EXPLAIN (VERBOSE, COSTS OFF)
SELECT t1.c1, t2.c1 FROM ft1 t1 LEFT JOIN ft2 t2 ON (t1.c8 = t2.c8) ORDER BY t1.c1, t2.c1 OFFSET 100 LIMIT 10;
SELECT t1.c1, t2.c1 FROM ft1 t1 LEFT JOIN ft2 t2 ON (t1.c8 = t2.c8) ORDER BY t1.c1, t2.c1 OFFSET 100 LIMIT 10;
-- unsafe conditions on one side (c8 has a UDT), not pushed down.
EXPLAIN (VERBOSE, COSTS OFF)
SELECT t1.c1, t2.c1 FROM ft1 t1 LEFT JOIN ft2 t2 ON (t1.c1 = t2.c1) WHERE t1.c8 = 'foo' ORDER BY t1.c3, t1.c1 OFFSET 100 LIMIT 10;
SELECT t1.c1, t2.c1 FROM ft1 t1 LEFT JOIN ft2 t2 ON (t1.c1 = t2.c1) WHERE t1.c8 = 'foo' ORDER BY t1.c3, t1.c1 OFFSET 100 LIMIT 10;
-- join where unsafe to pushdown condition in WHERE clause has a column not
-- in the SELECT clause. In this test unsafe clause needs to have column
-- references from both joining sides so that the clause is not pushed down
-- into one of the joining sides.
EXPLAIN (VERBOSE, COSTS OFF)
SELECT t1.c1, t2.c1 FROM ft1 t1 JOIN ft2 t2 ON (t1.c1 = t2.c1) WHERE t1.c8 = t2.c8 ORDER BY t1.c3, t1.c1 OFFSET 100 LIMIT 10;
SELECT t1.c1, t2.c1 FROM ft1 t1 JOIN ft2 t2 ON (t1.c1 = t2.c1) WHERE t1.c8 = t2.c8 ORDER BY t1.c3, t1.c1 OFFSET 100 LIMIT 10;
-- Aggregate after UNION, for testing setrefs
EXPLAIN (VERBOSE, COSTS OFF)
SELECT t1c1, avg(t1c1 + t2c1) FROM (SELECT t1.c1, t2.c1 FROM ft1 t1 JOIN ft2 t2 ON (t1.c1 = t2.c1) UNION SELECT t1.c1, t2.c1 FROM ft1 t1 JOIN ft2 t2 ON (t1.c1 = t2.c1)) AS t (t1c1, t2c1) GROUP BY t1c1 ORDER BY t1c1 OFFSET 100 LIMIT 10;
SELECT t1c1, avg(t1c1 + t2c1) FROM (SELECT t1.c1, t2.c1 FROM ft1 t1 JOIN ft2 t2 ON (t1.c1 = t2.c1) UNION SELECT t1.c1, t2.c1 FROM ft1 t1 JOIN ft2 t2 ON (t1.c1 = t2.c1)) AS t (t1c1, t2c1) GROUP BY t1c1 ORDER BY t1c1 OFFSET 100 LIMIT 10;
-- join with lateral reference
EXPLAIN (VERBOSE, COSTS OFF)
SELECT t1."C 1" FROM "S 1"."T 1" t1, LATERAL (SELECT DISTINCT t2.c1, t3.c1 FROM ft1 t2, ft2 t3 WHERE t2.c1 = t3.c1 AND t2.c2 = t1.c2) q ORDER BY t1."C 1" OFFSET 10 LIMIT 10;
SELECT t1."C 1" FROM "S 1"."T 1" t1, LATERAL (SELECT DISTINCT t2.c1, t3.c1 FROM ft1 t2, ft2 t3 WHERE t2.c1 = t3.c1 AND t2.c2 = t1.c2) q ORDER BY t1."C 1" OFFSET 10 LIMIT 10;
RESET enable_mergejoin;
-- non-Var items in targetlist of the nullable rel of a join preventing
-- push-down in some cases
-- unable to push {ft1, ft2}
EXPLAIN (VERBOSE, COSTS OFF)
SELECT q.a, ft2.c1 FROM (SELECT 13 FROM ft1 WHERE c1 = 13) q(a) RIGHT JOIN ft2 ON (q.a = ft2.c1) WHERE ft2.c1 BETWEEN 10 AND 15;
SELECT q.a, ft2.c1 FROM (SELECT 13 FROM ft1 WHERE c1 = 13) q(a) RIGHT JOIN ft2 ON (q.a = ft2.c1) WHERE ft2.c1 BETWEEN 10 AND 15;
-- ok to push {ft1, ft2} but not {ft1, ft2, ft4}
EXPLAIN (VERBOSE, COSTS OFF)
SELECT ft4.c1, q.* FROM ft4 LEFT JOIN (SELECT 13, ft1.c1, ft2.c1 FROM ft1 RIGHT JOIN ft2 ON (ft1.c1 = ft2.c1) WHERE ft1.c1 = 12) q(a, b, c) ON (ft4.c1 = q.b) WHERE ft4.c1 BETWEEN 10 AND 15;
SELECT ft4.c1, q.* FROM ft4 LEFT JOIN (SELECT 13, ft1.c1, ft2.c1 FROM ft1 RIGHT JOIN ft2 ON (ft1.c1 = ft2.c1) WHERE ft1.c1 = 12) q(a, b, c) ON (ft4.c1 = q.b) WHERE ft4.c1 BETWEEN 10 AND 15;
-- join with nullable side with some columns with null values
UPDATE ft5 SET c3 = null where c1 % 9 = 0;
EXPLAIN (VERBOSE, COSTS OFF)
SELECT ft5, ft5.c1, ft5.c2, ft5.c3, ft4.c1, ft4.c2 FROM ft5 left join ft4 on ft5.c1 = ft4.c1 WHERE ft4.c1 BETWEEN 10 and 30 ORDER BY ft5.c1, ft4.c1;
SELECT ft5, ft5.c1, ft5.c2, ft5.c3, ft4.c1, ft4.c2 FROM ft5 left join ft4 on ft5.c1 = ft4.c1 WHERE ft4.c1 BETWEEN 10 and 30 ORDER BY ft5.c1, ft4.c1;
-- multi-way join involving multiple merge joins
-- (this case used to have EPQ-related planning problems)
CREATE TABLE local_tbl (c1 int NOT NULL, c2 int NOT NULL, c3 text, CONSTRAINT local_tbl_pkey PRIMARY KEY (c1));
INSERT INTO local_tbl SELECT id, id % 10, to_char(id, 'FM0000') FROM generate_series(1, 1000) id;
ANALYZE local_tbl;
\! env PGOPTIONS='' psql -p ${PG_PORT} contrib_regression -c 'CREATE TABLE local_tbl (c1 int NOT NULL, c2 int NOT NULL, c3 text, CONSTRAINT local_tbl_pkey PRIMARY KEY (c1));'
\! env PGOPTIONS='' psql -p ${PG_PORT} contrib_regression -c "INSERT INTO local_tbl SELECT id, id % 10, to_char(id, 'FM0000') FROM generate_series(1, 1000) id;"
\! env PGOPTIONS='' psql -p ${PG_PORT} contrib_regression -c 'ANALYZE local_tbl;'
SET enable_nestloop TO false;
SET enable_hashjoin TO false;
SET enable_mergejoin TO true;
EXPLAIN (VERBOSE, COSTS OFF)
SELECT * FROM ft1, ft2, ft4, ft5, local_tbl WHERE ft1.c1 = ft2.c1 AND ft1.c2 = ft4.c1
AND ft1.c2 = ft5.c1 AND ft1.c2 = local_tbl.c1 AND ft1.c1 < 100 AND ft2.c1 < 100 FOR UPDATE;
SELECT * FROM ft1, ft2, ft4, ft5, local_tbl WHERE ft1.c1 = ft2.c1 AND ft1.c2 = ft4.c1
AND ft1.c2 = ft5.c1 AND ft1.c2 = local_tbl.c1 AND ft1.c1 < 100 AND ft2.c1 < 100 FOR UPDATE;
RESET enable_nestloop;
RESET enable_hashjoin;
RESET enable_mergejoin;
\! env PGOPTIONS='' psql -p ${PG_PORT} contrib_regression -c 'DROP TABLE local_tbl;'
DROP TABLE local_tbl;
-- check join pushdown in situations where multiple userids are involved
CREATE ROLE regress_view_owner SUPERUSER;
CREATE USER MAPPING FOR regress_view_owner SERVER pgserver;
GRANT SELECT ON ft4 TO regress_view_owner;
GRANT SELECT ON ft5 TO regress_view_owner;
CREATE VIEW v4 AS SELECT * FROM ft4;
CREATE VIEW v5 AS SELECT * FROM ft5;
ALTER VIEW v5 OWNER TO regress_view_owner;
EXPLAIN (VERBOSE, COSTS OFF)
SELECT t1.c1, t2.c2 FROM v4 t1 LEFT JOIN v5 t2 ON (t1.c1 = t2.c1) ORDER BY t1.c1, t2.c1 OFFSET 10 LIMIT 10; -- can't be pushed down, different view owners
SELECT t1.c1, t2.c2 FROM v4 t1 LEFT JOIN v5 t2 ON (t1.c1 = t2.c1) ORDER BY t1.c1, t2.c1 OFFSET 10 LIMIT 10;
ALTER VIEW v4 OWNER TO regress_view_owner;
EXPLAIN (VERBOSE, COSTS OFF)
SELECT t1.c1, t2.c2 FROM v4 t1 LEFT JOIN v5 t2 ON (t1.c1 = t2.c1) ORDER BY t1.c1, t2.c1 OFFSET 10 LIMIT 10; -- can be pushed down
SELECT t1.c1, t2.c2 FROM v4 t1 LEFT JOIN v5 t2 ON (t1.c1 = t2.c1) ORDER BY t1.c1, t2.c1 OFFSET 10 LIMIT 10;
EXPLAIN (VERBOSE, COSTS OFF)
SELECT t1.c1, t2.c2 FROM v4 t1 LEFT JOIN ft5 t2 ON (t1.c1 = t2.c1) ORDER BY t1.c1, t2.c1 OFFSET 10 LIMIT 10; -- can't be pushed down, view owner not current user
SELECT t1.c1, t2.c2 FROM v4 t1 LEFT JOIN ft5 t2 ON (t1.c1 = t2.c1) ORDER BY t1.c1, t2.c1 OFFSET 10 LIMIT 10;
ALTER VIEW v4 OWNER TO CURRENT_USER;
EXPLAIN (VERBOSE, COSTS OFF)
SELECT t1.c1, t2.c2 FROM v4 t1 LEFT JOIN ft5 t2 ON (t1.c1 = t2.c1) ORDER BY t1.c1, t2.c1 OFFSET 10 LIMIT 10; -- can be pushed down
SELECT t1.c1, t2.c2 FROM v4 t1 LEFT JOIN ft5 t2 ON (t1.c1 = t2.c1) ORDER BY t1.c1, t2.c1 OFFSET 10 LIMIT 10;
ALTER VIEW v4 OWNER TO regress_view_owner;
-- cleanup
DROP OWNED BY regress_view_owner;
DROP ROLE regress_view_owner;
-- ===================================================================
-- Aggregate and grouping queries
-- ===================================================================
-- Simple aggregates
explain (verbose, costs off)
select count(c6), sum(c1), avg(c1), min(c2), max(c1), stddev(c2), sum(c1) * (random() <= 1)::int as sum2 from ft1 where c2 < 5 group by c2 order by 1, 2;
select count(c6), sum(c1), avg(c1), min(c2), max(c1), stddev(c2), sum(c1) * (random() <= 1)::int as sum2 from ft1 where c2 < 5 group by c2 order by 1, 2;
explain (verbose, costs off)
select count(c6), sum(c1), avg(c1), min(c2), max(c1), stddev(c2), sum(c1) * (random() <= 1)::int as sum2 from ft1 where c2 < 5 group by c2 order by 1, 2 limit 1;
select count(c6), sum(c1), avg(c1), min(c2), max(c1), stddev(c2), sum(c1) * (random() <= 1)::int as sum2 from ft1 where c2 < 5 group by c2 order by 1, 2 limit 1;
-- Aggregate is not pushed down as aggregation contains random()
explain (verbose, costs off)
select sum(c1 * (random() <= 1)::int) as sum, avg(c1) from ft1;
-- Aggregate over join query
explain (verbose, costs off)
select count(*), sum(t1.c1), avg(t2.c1) from ft1 t1 inner join ft1 t2 on (t1.c2 = t2.c2) where t1.c2 = 6;
select count(*), sum(t1.c1), avg(t2.c1) from ft1 t1 inner join ft1 t2 on (t1.c2 = t2.c2) where t1.c2 = 6;
-- Not pushed down due to local conditions present in underneath input rel
explain (verbose, costs off)
select sum(t1.c1), count(t2.c1) from ft1 t1 inner join ft2 t2 on (t1.c1 = t2.c1) where ((t1.c1 * t2.c1)/(t1.c1 * t2.c1)) * random() <= 1;
-- GROUP BY clause having expressions
explain (verbose, costs off)
select c2/2, sum(c2) * (c2/2) from ft1 group by c2/2 order by c2/2;
select c2/2, sum(c2) * (c2/2) from ft1 group by c2/2 order by c2/2;
-- Aggregates in subquery are pushed down.
explain (verbose, costs off)
select count(x.a), sum(x.a) from (select c2 a, sum(c1) b from ft1 group by c2, sqrt(c1) order by 1, 2) x;
select count(x.a), sum(x.a) from (select c2 a, sum(c1) b from ft1 group by c2, sqrt(c1) order by 1, 2) x;
-- Aggregate is still pushed down by taking unshippable expression out
explain (verbose, costs off)
select c2 * (random() <= 1)::int as sum1, sum(c1) * c2 as sum2 from ft1 group by c2 order by 1, 2;
select c2 * (random() <= 1)::int as sum1, sum(c1) * c2 as sum2 from ft1 group by c2 order by 1, 2;
-- Aggregate with unshippable GROUP BY clause are not pushed
explain (verbose, costs off)
select c2 * (random() <= 1)::int as c2 from ft2 group by c2 * (random() <= 1)::int order by 1;
-- GROUP BY clause in various forms, cardinal, alias and constant expression
explain (verbose, costs off)
select count(c2) w, c2 x, 5 y, 7.0 z from ft1 group by 2, y, 9.0::int order by 2;
select count(c2) w, c2 x, 5 y, 7.0 z from ft1 group by 2, y, 9.0::int order by 2;
-- GROUP BY clause referring to same column multiple times
-- Also, ORDER BY contains an aggregate function
explain (verbose, costs off)
select c2, c2 from ft1 where c2 > 6 group by 1, 2 order by sum(c1);
select c2, c2 from ft1 where c2 > 6 group by 1, 2 order by sum(c1);
-- Testing HAVING clause shippability
explain (verbose, costs off)
select c2, sum(c1) from ft2 group by c2 having avg(c1) < 500 and sum(c1) < 49800 order by c2;
select c2, sum(c1) from ft2 group by c2 having avg(c1) < 500 and sum(c1) < 49800 order by c2;
-- Unshippable HAVING clause will be evaluated locally, and other qual in HAVING clause is pushed down
explain (verbose, costs off)
select count(*) from (select c5, count(c1) from ft1 group by c5, sqrt(c2) having (avg(c1) / avg(c1)) * random() <= 1 and avg(c1) < 500) x;
select count(*) from (select c5, count(c1) from ft1 group by c5, sqrt(c2) having (avg(c1) / avg(c1)) * random() <= 1 and avg(c1) < 500) x;
-- Aggregate in HAVING clause is not pushable, and thus aggregation is not pushed down
explain (verbose, costs off)
select sum(c1) from ft1 group by c2 having avg(c1 * (random() <= 1)::int) > 100 order by 1;
-- Remote aggregate in combination with a local Param (for the output
-- of an initplan) can be trouble, per bug #15781
explain (verbose, costs off)
select exists(select 1 from pg_enum), sum(c1) from ft1;
select exists(select 1 from pg_enum), sum(c1) from ft1;
explain (verbose, costs off)
select exists(select 1 from pg_enum), sum(c1) from ft1 group by 1;
select exists(select 1 from pg_enum), sum(c1) from ft1 group by 1;
-- Testing ORDER BY, DISTINCT, FILTER, Ordered-sets and VARIADIC within aggregates
-- ORDER BY within aggregate, same column used to order
explain (verbose, costs off)
select array_agg(c1 order by c1) from ft1 where c1 < 100 group by c2 order by 1;
select array_agg(c1 order by c1) from ft1 where c1 < 100 group by c2 order by 1;
-- ORDER BY within aggregate, different column used to order also using DESC
explain (verbose, costs off)
select array_agg(c5 order by c1 desc) from ft2 where c2 = 6 and c1 < 50;
select array_agg(c5 order by c1 desc) from ft2 where c2 = 6 and c1 < 50;
-- DISTINCT within aggregate
explain (verbose, costs off)
select array_agg(distinct (t1.c1)%5) from ft4 t1 full join ft5 t2 on (t1.c1 = t2.c1) where t1.c1 < 20 or (t1.c1 is null and t2.c1 < 5) group by (t2.c1)%3 order by 1;
select array_agg(distinct (t1.c1)%5) from ft4 t1 full join ft5 t2 on (t1.c1 = t2.c1) where t1.c1 < 20 or (t1.c1 is null and t2.c1 < 5) group by (t2.c1)%3 order by 1;
-- DISTINCT combined with ORDER BY within aggregate
explain (verbose, costs off)
select array_agg(distinct (t1.c1)%5 order by (t1.c1)%5) from ft4 t1 full join ft5 t2 on (t1.c1 = t2.c1) where t1.c1 < 20 or (t1.c1 is null and t2.c1 < 5) group by (t2.c1)%3 order by 1;
select array_agg(distinct (t1.c1)%5 order by (t1.c1)%5) from ft4 t1 full join ft5 t2 on (t1.c1 = t2.c1) where t1.c1 < 20 or (t1.c1 is null and t2.c1 < 5) group by (t2.c1)%3 order by 1;
explain (verbose, costs off)
select array_agg(distinct (t1.c1)%5 order by (t1.c1)%5 desc nulls last) from ft4 t1 full join ft5 t2 on (t1.c1 = t2.c1) where t1.c1 < 20 or (t1.c1 is null and t2.c1 < 5) group by (t2.c1)%3 order by 1;
select array_agg(distinct (t1.c1)%5 order by (t1.c1)%5 desc nulls last) from ft4 t1 full join ft5 t2 on (t1.c1 = t2.c1) where t1.c1 < 20 or (t1.c1 is null and t2.c1 < 5) group by (t2.c1)%3 order by 1;
-- FILTER within aggregate
explain (verbose, costs off)
select sum(c1) filter (where c1 < 100 and c2 > 5) from ft1 group by c2 order by 1 nulls last;
select sum(c1) filter (where c1 < 100 and c2 > 5) from ft1 group by c2 order by 1 nulls last;
-- DISTINCT, ORDER BY and FILTER within aggregate
explain (verbose, costs off)
select sum(c1%3), sum(distinct c1%3 order by c1%3) filter (where c1%3 < 2), c2 from ft1 where c2 = 6 group by c2;
select sum(c1%3), sum(distinct c1%3 order by c1%3) filter (where c1%3 < 2), c2 from ft1 where c2 = 6 group by c2;
-- Outer query is aggregation query
explain (verbose, costs off)
select distinct (select count(*) filter (where t2.c2 = 6 and t2.c1 < 10) from ft1 t1 where t1.c1 = 6) from ft2 t2 where t2.c2 % 6 = 0 order by 1;
select distinct (select count(*) filter (where t2.c2 = 6 and t2.c1 < 10) from ft1 t1 where t1.c1 = 6) from ft2 t2 where t2.c2 % 6 = 0 order by 1;
-- Inner query is aggregation query
explain (verbose, costs off)
select distinct (select count(t1.c1) filter (where t2.c2 = 6 and t2.c1 < 10) from ft1 t1 where t1.c1 = 6) from ft2 t2 where t2.c2 % 6 = 0 order by 1;
select distinct (select count(t1.c1) filter (where t2.c2 = 6 and t2.c1 < 10) from ft1 t1 where t1.c1 = 6) from ft2 t2 where t2.c2 % 6 = 0 order by 1;
-- Aggregate not pushed down as FILTER condition is not pushable
explain (verbose, costs off)
select sum(c1) filter (where (c1 / c1) * random() <= 1) from ft1 group by c2 order by 1;
explain (verbose, costs off)
select sum(c2) filter (where c2 in (select c2 from ft1 where c2 < 5)) from ft1;
-- Ordered-sets within aggregate
explain (verbose, costs off)
select c2, rank('10'::varchar) within group (order by c6), percentile_cont(c2/10::numeric) within group (order by c1) from ft1 where c2 < 10 group by c2 having percentile_cont(c2/10::numeric) within group (order by c1) < 500 order by c2;
select c2, rank('10'::varchar) within group (order by c6), percentile_cont(c2/10::numeric) within group (order by c1) from ft1 where c2 < 10 group by c2 having percentile_cont(c2/10::numeric) within group (order by c1) < 500 order by c2;
-- Using multiple arguments within aggregates
explain (verbose, costs off)
select c1, rank(c1, c2) within group (order by c1, c2) from ft1 group by c1, c2 having c1 = 6 order by 1;
select c1, rank(c1, c2) within group (order by c1, c2) from ft1 group by c1, c2 having c1 = 6 order by 1;
-- User defined function for user defined aggregate, VARIADIC
create function least_accum(anyelement, variadic anyarray)
returns anyelement language sql as
'select least($1, min($2[i])) from generate_subscripts($2,1) g(i)';
create aggregate least_agg(variadic items anyarray) (
stype = anyelement, sfunc = least_accum
);
-- Disable hash aggregation for plan stability.
set enable_hashagg to false;
-- Not pushed down due to user defined aggregate
explain (verbose, costs off)
select c2, least_agg(c1) from ft1 group by c2 order by c2;
-- Add function and aggregate into extension
alter extension postgres_fdw add function least_accum(anyelement, variadic anyarray);
alter extension postgres_fdw add aggregate least_agg(variadic items anyarray);
alter server pgserver options (set extensions 'postgres_fdw');
-- Now aggregate will be pushed. Aggregate will display VARIADIC argument.
explain (verbose, costs off)
select c2, least_agg(c1) from ft1 where c2 < 100 group by c2 order by c2;
select c2, least_agg(c1) from ft1 where c2 < 100 group by c2 order by c2;
-- Remove function and aggregate from extension
alter extension postgres_fdw drop function least_accum(anyelement, variadic anyarray);
alter extension postgres_fdw drop aggregate least_agg(variadic items anyarray);
alter server pgserver options (set extensions 'postgres_fdw');
-- Not pushed down as we have dropped objects from extension.
explain (verbose, costs off)
select c2, least_agg(c1) from ft1 group by c2 order by c2;
-- Cleanup
reset enable_hashagg;
drop aggregate least_agg(variadic items anyarray);
drop function least_accum(anyelement, variadic anyarray);
-- Testing USING OPERATOR() in ORDER BY within aggregate.
-- For this, we need user defined operators along with operator family and
-- operator class. Create those and then add them in extension. Note that
-- user defined objects are considered unshippable unless they are part of
-- the extension.
create operator public.<^ (
leftarg = int4,
rightarg = int4,
procedure = int4eq
);
create operator public.=^ (
leftarg = int4,
rightarg = int4,
procedure = int4lt
);
create operator public.>^ (
leftarg = int4,
rightarg = int4,
procedure = int4gt
);
create operator family my_op_family using btree;
create function my_op_cmp(a int, b int) returns int as
$$begin return btint4cmp(a, b); end $$ language plpgsql;
create operator class my_op_class for type int using btree family my_op_family as
operator 1 public.<^,
operator 3 public.=^,
operator 5 public.>^,
function 1 my_op_cmp(int, int);
-- This will not be pushed as user defined sort operator is not part of the
-- extension yet.
explain (verbose, costs off)
select array_agg(c1 order by c1 using operator(public.<^)) from ft2 where c2 = 6 and c1 < 100 group by c2;
-- This should not be pushed either.
explain (verbose, costs off)
select * from ft2 order by c1 using operator(public.<^);
-- Update local stats on ft2
ANALYZE ft2;
-- Add into extension
alter extension postgres_fdw add operator class my_op_class using btree;
alter extension postgres_fdw add function my_op_cmp(a int, b int);
alter extension postgres_fdw add operator family my_op_family using btree;
alter extension postgres_fdw add operator public.<^(int, int);
alter extension postgres_fdw add operator public.=^(int, int);
alter extension postgres_fdw add operator public.>^(int, int);
alter server pgserver options (set extensions 'postgres_fdw');
-- Now this will be pushed as sort operator is part of the extension.
explain (verbose, costs off)
select array_agg(c1 order by c1 using operator(public.<^)) from ft2 where c2 = 6 and c1 < 100 group by c2;
select array_agg(c1 order by c1 using operator(public.<^)) from ft2 where c2 = 6 and c1 < 100 group by c2;
-- This should be pushed too.
explain (verbose, costs off)
select * from ft2 order by c1 using operator(public.<^);
-- Remove from extension
alter extension postgres_fdw drop operator class my_op_class using btree;
alter extension postgres_fdw drop function my_op_cmp(a int, b int);
alter extension postgres_fdw drop operator family my_op_family using btree;
alter extension postgres_fdw drop operator public.<^(int, int);
alter extension postgres_fdw drop operator public.=^(int, int);
alter extension postgres_fdw drop operator public.>^(int, int);
alter server pgserver options (set extensions 'postgres_fdw');
-- This will not be pushed as sort operator is now removed from the extension.
explain (verbose, costs off)
select array_agg(c1 order by c1 using operator(public.<^)) from ft2 where c2 = 6 and c1 < 100 group by c2;
-- Cleanup
drop operator class my_op_class using btree;
drop function my_op_cmp(a int, b int);
drop operator family my_op_family using btree;
drop operator public.>^(int, int);
drop operator public.=^(int, int);
drop operator public.<^(int, int);
-- Input relation to aggregate push down hook is not safe to pushdown and thus
-- the aggregate cannot be pushed down to foreign server.
explain (verbose, costs off)
select count(t1.c3) from ft2 t1 left join ft2 t2 on (t1.c1 = random() * t2.c2);
-- Subquery in FROM clause having aggregate
explain (verbose, costs off)
select count(*), x.b from ft1, (select c2 a, sum(c1) b from ft1 group by c2) x where ft1.c2 = x.a group by x.b order by 1, 2;
select count(*), x.b from ft1, (select c2 a, sum(c1) b from ft1 group by c2) x where ft1.c2 = x.a group by x.b order by 1, 2;
-- FULL join with IS NULL check in HAVING
explain (verbose, costs off)
select avg(t1.c1), sum(t2.c1) from ft4 t1 full join ft5 t2 on (t1.c1 = t2.c1) group by t2.c1 having (avg(t1.c1) is null and sum(t2.c1) < 10) or sum(t2.c1) is null order by 1 nulls last, 2;
select avg(t1.c1), sum(t2.c1) from ft4 t1 full join ft5 t2 on (t1.c1 = t2.c1) group by t2.c1 having (avg(t1.c1) is null and sum(t2.c1) < 10) or sum(t2.c1) is null order by 1 nulls last, 2;
-- Aggregate over FULL join needing to deparse the joining relations as
-- subqueries.
explain (verbose, costs off)
select count(*), sum(t1.c1), avg(t2.c1) from (select c1 from ft4 where c1 between 50 and 60) t1 full join (select c1 from ft5 where c1 between 50 and 60) t2 on (t1.c1 = t2.c1);
select count(*), sum(t1.c1), avg(t2.c1) from (select c1 from ft4 where c1 between 50 and 60) t1 full join (select c1 from ft5 where c1 between 50 and 60) t2 on (t1.c1 = t2.c1);
-- ORDER BY expression is part of the target list but not pushed down to
-- foreign server.
explain (verbose, costs off)
select sum(c2) * (random() <= 1)::int as sum from ft1 order by 1;
select sum(c2) * (random() <= 1)::int as sum from ft1 order by 1;
-- LATERAL join, with parameterization
set enable_hashagg to false;
explain (verbose, costs off)
select c2, sum from "S 1"."T 1" t1, lateral (select sum(t2.c1 + t1."C 1") sum from ft2 t2 group by t2.c1) qry where t1.c2 * 2 = qry.sum and t1.c2 < 3 and t1."C 1" < 100 order by 1;
select c2, sum from "S 1"."T 1" t1, lateral (select sum(t2.c1 + t1."C 1") sum from ft2 t2 group by t2.c1) qry where t1.c2 * 2 = qry.sum and t1.c2 < 3 and t1."C 1" < 100 order by 1;
reset enable_hashagg;
-- bug #15613: bad plan for foreign table scan with lateral reference
EXPLAIN (VERBOSE, COSTS OFF)
SELECT ref_0.c2, subq_1.*
FROM
"S 1"."T 1" AS ref_0,
LATERAL (
SELECT ref_0."C 1" c1, subq_0.*
FROM (SELECT ref_0.c2, ref_1.c3
FROM ft1 AS ref_1) AS subq_0
RIGHT JOIN ft2 AS ref_3 ON (subq_0.c3 = ref_3.c3)
) AS subq_1
WHERE ref_0."C 1" < 10 AND subq_1.c3 = '00001'
ORDER BY ref_0."C 1";
SELECT ref_0.c2, subq_1.*
FROM
"S 1"."T 1" AS ref_0,
LATERAL (
SELECT ref_0."C 1" c1, subq_0.*
FROM (SELECT ref_0.c2, ref_1.c3
FROM ft1 AS ref_1) AS subq_0
RIGHT JOIN ft2 AS ref_3 ON (subq_0.c3 = ref_3.c3)
) AS subq_1
WHERE ref_0."C 1" < 10 AND subq_1.c3 = '00001'
ORDER BY ref_0."C 1";
-- Check with placeHolderVars
explain (verbose, costs off)
select sum(q.a), count(q.b) from ft4 left join (select 13, avg(ft1.c1), sum(ft2.c1) from ft1 right join ft2 on (ft1.c1 = ft2.c1)) q(a, b, c) on (ft4.c1 <= q.b);
select sum(q.a), count(q.b) from ft4 left join (select 13, avg(ft1.c1), sum(ft2.c1) from ft1 right join ft2 on (ft1.c1 = ft2.c1)) q(a, b, c) on (ft4.c1 <= q.b);
-- Not supported cases
-- Grouping sets
explain (verbose, costs off)
select c2, sum(c1) from ft1 where c2 < 3 group by rollup(c2) order by 1 nulls last;
select c2, sum(c1) from ft1 where c2 < 3 group by rollup(c2) order by 1 nulls last;
explain (verbose, costs off)
select c2, sum(c1) from ft1 where c2 < 3 group by cube(c2) order by 1 nulls last;
select c2, sum(c1) from ft1 where c2 < 3 group by cube(c2) order by 1 nulls last;
explain (verbose, costs off)
select c2, c6, sum(c1) from ft1 where c2 < 3 group by grouping sets(c2, c6) order by 1 nulls last, 2 nulls last;
select c2, c6, sum(c1) from ft1 where c2 < 3 group by grouping sets(c2, c6) order by 1 nulls last, 2 nulls last;
explain (verbose, costs off)
select c2, sum(c1), grouping(c2) from ft1 where c2 < 3 group by c2 order by 1 nulls last;
select c2, sum(c1), grouping(c2) from ft1 where c2 < 3 group by c2 order by 1 nulls last;
-- DISTINCT itself is not pushed down, whereas underneath aggregate is pushed
explain (verbose, costs off)
select distinct sum(c1)/1000 s from ft2 where c2 < 6 group by c2 order by 1;
select distinct sum(c1)/1000 s from ft2 where c2 < 6 group by c2 order by 1;
-- WindowAgg
explain (verbose, costs off)
select c2, sum(c2), count(c2) over (partition by c2%2) from ft2 where c2 < 10 group by c2 order by 1;
select c2, sum(c2), count(c2) over (partition by c2%2) from ft2 where c2 < 10 group by c2 order by 1;
explain (verbose, costs off)
select c2, array_agg(c2) over (partition by c2%2 order by c2 desc) from ft1 where c2 < 10 group by c2 order by 1;
select c2, array_agg(c2) over (partition by c2%2 order by c2 desc) from ft1 where c2 < 10 group by c2 order by 1;
explain (verbose, costs off)
select c2, array_agg(c2) over (partition by c2%2 order by c2 range between current row and unbounded following) from ft1 where c2 < 10 group by c2 order by 1;
select c2, array_agg(c2) over (partition by c2%2 order by c2 range between current row and unbounded following) from ft1 where c2 < 10 group by c2 order by 1;
-- ===================================================================
-- parameterized queries
-- ===================================================================
-- simple join
PREPARE st1(int, int) AS SELECT t1.c3, t2.c3 FROM ft1 t1, ft2 t2 WHERE t1.c1 = $1 AND t2.c1 = $2;
EXPLAIN (VERBOSE, COSTS OFF) EXECUTE st1(1, 2);
EXECUTE st1(1, 1);
EXECUTE st1(101, 101);
-- subquery using stable function (can't be sent to remote)
PREPARE st2(int) AS SELECT * FROM ft1 t1 WHERE t1.c1 < $2 AND t1.c3 IN (SELECT c3 FROM ft2 t2 WHERE c1 > $1 AND date(c4) = '1970-01-17'::date) ORDER BY c1;
EXPLAIN (VERBOSE, COSTS OFF) EXECUTE st2(10, 20);
EXECUTE st2(10, 20);
EXECUTE st2(101, 121);
-- subquery using immutable function (can be sent to remote)
PREPARE st3(int) AS SELECT * FROM ft1 t1 WHERE t1.c1 < $2 AND t1.c3 IN (SELECT c3 FROM ft2 t2 WHERE c1 > $1 AND date(c5) = '1970-01-17'::date) ORDER BY c1;
EXPLAIN (VERBOSE, COSTS OFF) EXECUTE st3(10, 20);
EXECUTE st3(10, 20);
EXECUTE st3(20, 30);
-- custom plan should be chosen initially
PREPARE st4(int) AS SELECT * FROM ft1 t1 WHERE t1.c1 = $1;
EXPLAIN (VERBOSE, COSTS OFF) EXECUTE st4(1);
EXPLAIN (VERBOSE, COSTS OFF) EXECUTE st4(1);
EXPLAIN (VERBOSE, COSTS OFF) EXECUTE st4(1);
EXPLAIN (VERBOSE, COSTS OFF) EXECUTE st4(1);
EXPLAIN (VERBOSE, COSTS OFF) EXECUTE st4(1);
-- once we try it enough times, should switch to generic plan
EXPLAIN (VERBOSE, COSTS OFF) EXECUTE st4(1);
-- value of $1 should not be sent to remote
PREPARE st5(user_enum,int) AS SELECT * FROM ft1 t1 WHERE c8 = $1 and c1 = $2;
EXPLAIN (VERBOSE, COSTS OFF) EXECUTE st5('foo', 1);
EXPLAIN (VERBOSE, COSTS OFF) EXECUTE st5('foo', 1);
EXPLAIN (VERBOSE, COSTS OFF) EXECUTE st5('foo', 1);
EXPLAIN (VERBOSE, COSTS OFF) EXECUTE st5('foo', 1);
EXPLAIN (VERBOSE, COSTS OFF) EXECUTE st5('foo', 1);
EXPLAIN (VERBOSE, COSTS OFF) EXECUTE st5('foo', 1);
EXECUTE st5('foo', 1);
-- altering FDW options requires replanning
PREPARE st6 AS SELECT * FROM ft1 t1 WHERE t1.c1 = t1.c2;
EXPLAIN (VERBOSE, COSTS OFF) EXECUTE st6;
PREPARE st7 AS INSERT INTO ft1 (c1,c2,c3) VALUES (1001,101,'foo');
EXPLAIN (VERBOSE, COSTS OFF) EXECUTE st7;
\! env PGOPTIONS='' psql -p ${PG_PORT} contrib_regression -c 'ALTER TABLE "S 1"."T 1" RENAME TO "T 0"';
ALTER FOREIGN TABLE ft1 OPTIONS (SET table_name 'T 0');
EXPLAIN (VERBOSE, COSTS OFF) EXECUTE st6;
EXECUTE st6;
EXPLAIN (VERBOSE, COSTS OFF) EXECUTE st7;
\! env PGOPTIONS='' psql -p ${PG_PORT} contrib_regression -c 'ALTER TABLE "S 1"."T 0" RENAME TO "T 1"';
ALTER FOREIGN TABLE ft1 OPTIONS (SET table_name 'T 1');
PREPARE st8 AS SELECT count(c3) FROM ft1 t1 WHERE t1.c1 === t1.c2;
EXPLAIN (VERBOSE, COSTS OFF) EXECUTE st8;
ALTER SERVER pgserver OPTIONS (DROP extensions);
EXPLAIN (VERBOSE, COSTS OFF) EXECUTE st8;
EXECUTE st8;
ALTER SERVER pgserver OPTIONS (ADD extensions 'postgres_fdw');
-- cleanup
DEALLOCATE st1;
DEALLOCATE st2;
DEALLOCATE st3;
DEALLOCATE st4;
DEALLOCATE st5;
DEALLOCATE st6;
DEALLOCATE st7;
DEALLOCATE st8;
-- System columns, except ctid and oid, should not be sent to remote
EXPLAIN (VERBOSE, COSTS OFF)
SELECT * FROM ft1 t1 WHERE t1.tableoid = 'pg_class'::regclass LIMIT 1;
SELECT * FROM ft1 t1 WHERE t1.tableoid = 'ft1'::regclass LIMIT 1;
EXPLAIN (VERBOSE, COSTS OFF)
SELECT tableoid::regclass, * FROM ft1 t1 LIMIT 1;
SELECT tableoid::regclass, * FROM ft1 t1 LIMIT 1;
EXPLAIN (VERBOSE, COSTS OFF)
SELECT * FROM ft1 t1 WHERE t1.ctid = '(0,2)';
SELECT * FROM ft1 t1 WHERE t1.ctid = '(0,2)';
EXPLAIN (VERBOSE, COSTS OFF)
SELECT ctid, * FROM ft1 t1 LIMIT 1;
SELECT ctid, * FROM ft1 t1 LIMIT 1;
-- ===================================================================
-- used in PL/pgSQL function
-- ===================================================================
CREATE OR REPLACE FUNCTION f_test(p_c1 int) RETURNS int AS $$
DECLARE
v_c1 int;
BEGIN
SELECT c1 INTO v_c1 FROM ft1 WHERE c1 = p_c1 LIMIT 1;
PERFORM c1 FROM ft1 WHERE c1 = p_c1 AND p_c1 = v_c1 LIMIT 1;
RETURN v_c1;
END;
$$ LANGUAGE plpgsql;
SELECT f_test(100);
DROP FUNCTION f_test(int);
-- ===================================================================
-- REINDEX
-- ===================================================================
-- remote table is not created here
CREATE FOREIGN TABLE reindex_foreign (c1 int, c2 int)
SERVER pgserver2 OPTIONS (table_name 'reindex_local');
REINDEX TABLE reindex_foreign; -- error
-- In greenplum, REINDEX CONCURRENTLY is not supported.
REINDEX TABLE CONCURRENTLY reindex_foreign; -- error
DROP FOREIGN TABLE reindex_foreign;
-- partitions and foreign tables
CREATE TABLE reind_fdw_parent (c1 int) PARTITION BY RANGE (c1);
CREATE TABLE reind_fdw_0_10 PARTITION OF reind_fdw_parent
FOR VALUES FROM (0) TO (10);
CREATE FOREIGN TABLE reind_fdw_10_20 PARTITION OF reind_fdw_parent
FOR VALUES FROM (10) TO (20)
SERVER pgserver OPTIONS (table_name 'reind_local_10_20');
REINDEX TABLE reind_fdw_parent; -- ok
-- In greenplum, REINDEX CONCURRENTLY is not supported.
REINDEX TABLE CONCURRENTLY reind_fdw_parent; -- ok
DROP TABLE reind_fdw_parent;
-- ===================================================================
-- conversion error
-- ===================================================================
ALTER FOREIGN TABLE ft1 ALTER COLUMN c8 TYPE int;
SELECT * FROM ft1 ftx(x1,x2,x3,x4,x5,x6,x7,x8) WHERE x1 = 1; -- ERROR
SELECT ftx.x1, ft2.c2, ftx.x8 FROM ft1 ftx(x1,x2,x3,x4,x5,x6,x7,x8), ft2
WHERE ftx.x1 = ft2.c1 AND ftx.x1 = 1; -- ERROR
SELECT ftx.x1, ft2.c2, ftx FROM ft1 ftx(x1,x2,x3,x4,x5,x6,x7,x8), ft2
WHERE ftx.x1 = ft2.c1 AND ftx.x1 = 1; -- ERROR
SELECT sum(c2), array_agg(c8) FROM ft1 GROUP BY c8; -- ERROR
ANALYZE ft1; -- ERROR
ALTER FOREIGN TABLE ft1 ALTER COLUMN c8 TYPE user_enum;
-- ===================================================================
-- subtransaction
-- + local/remote error doesn't break cursor
-- ===================================================================
BEGIN;
DECLARE c CURSOR FOR SELECT * FROM ft1 ORDER BY c1;
FETCH c;
SAVEPOINT s;
ERROR OUT; -- ERROR
ROLLBACK TO s;
FETCH c;
SAVEPOINT s;
SELECT * FROM ft1 WHERE 1 / (c1 - 1) > 0; -- ERROR
ROLLBACK TO s;
FETCH c;
SELECT * FROM ft1 ORDER BY c1 LIMIT 1;
COMMIT;
-- ===================================================================
-- test handling of collations
-- ===================================================================
\! env PGOPTIONS='' psql -p ${PG_PORT} contrib_regression -c 'create table loct3 (f1 text collate "C" unique, f2 text, f3 varchar(10) unique);'
create foreign table ft3 (f1 text collate "C", f2 text, f3 varchar(10))
server pgserver options (table_name 'loct3', use_remote_estimate 'true');
-- create table loct3 (f1 text collate "C" unique, f2 text, f3 varchar(10) unique) DISTRIBUTED BY (f1);
-- SQL above in greenplum will report error as follows.
-- ERROR: UNIQUE or PRIMARY KEY definitions are incompatible with each other
-- So change it to SQL as follows.
create table loct3 (f1 text collate "C", f2 text, f3 varchar(10)) DISTRIBUTED BY (f1);
-- can be sent to remote
explain (verbose, costs off) select * from ft3 where f1 = 'foo';
explain (verbose, costs off) select * from ft3 where f1 COLLATE "C" = 'foo';
explain (verbose, costs off) select * from ft3 where f2 = 'foo';
explain (verbose, costs off) select * from ft3 where f3 = 'foo';
explain (verbose, costs off) select * from ft3 f, loct3 l
where f.f3 = l.f3 and l.f1 = 'foo';
-- can't be sent to remote
explain (verbose, costs off) select * from ft3 where f1 COLLATE "POSIX" = 'foo';
explain (verbose, costs off) select * from ft3 where f1 = 'foo' COLLATE "C";
explain (verbose, costs off) select * from ft3 where f2 COLLATE "C" = 'foo';
explain (verbose, costs off) select * from ft3 where f2 = 'foo' COLLATE "C";
explain (verbose, costs off) select * from ft3 f, loct3 l
where f.f3 = l.f3 COLLATE "POSIX" and l.f1 = 'foo';
-- ===================================================================
-- test writable foreign table stuff
-- ===================================================================
EXPLAIN (verbose, costs off)
INSERT INTO ft2 (c1,c2,c3) SELECT c1+1000,c2+100, c3 || c3 FROM ft2 LIMIT 20;
INSERT INTO ft2 (c1,c2,c3) SELECT c1+1000,c2+100, c3 || c3 FROM ft2 LIMIT 20;
INSERT INTO ft2 (c1,c2,c3)
VALUES (1101,201,'aaa'), (1102,202,'bbb'), (1103,203,'ccc') RETURNING *;
INSERT INTO ft2 (c1,c2,c3) VALUES (1104,204,'ddd'), (1105,205,'eee');
EXPLAIN (verbose, costs off)
UPDATE ft2 SET c2 = c2 + 300, c3 = c3 || '_update3' WHERE c1 % 10 = 3; -- can be pushed down
UPDATE ft2 SET c2 = c2 + 300, c3 = c3 || '_update3' WHERE c1 % 10 = 3;
EXPLAIN (verbose, costs off)
UPDATE ft2 SET c2 = c2 + 400, c3 = c3 || '_update7' WHERE c1 % 10 = 7 RETURNING *; -- can be pushed down
UPDATE ft2 SET c2 = c2 + 400, c3 = c3 || '_update7' WHERE c1 % 10 = 7 RETURNING *;
EXPLAIN (verbose, costs off)
UPDATE ft2 SET c2 = ft2.c2 + 500, c3 = ft2.c3 || '_update9', c7 = DEFAULT
FROM ft1 WHERE ft1.c1 = ft2.c2 AND ft1.c1 % 10 = 9; -- can be pushed down
UPDATE ft2 SET c2 = ft2.c2 + 500, c3 = ft2.c3 || '_update9', c7 = DEFAULT
FROM ft1 WHERE ft1.c1 = ft2.c2 AND ft1.c1 % 10 = 9;
EXPLAIN (verbose, costs off)
DELETE FROM ft2 WHERE c1 % 10 = 5 RETURNING c1, c4; -- can be pushed down
DELETE FROM ft2 WHERE c1 % 10 = 5 RETURNING c1, c4;
EXPLAIN (verbose, costs off)
DELETE FROM ft2 USING ft1 WHERE ft1.c1 = ft2.c2 AND ft1.c1 % 10 = 2; -- can be pushed down
DELETE FROM ft2 USING ft1 WHERE ft1.c1 = ft2.c2 AND ft1.c1 % 10 = 2;
SELECT c1,c2,c3,c4 FROM ft2 ORDER BY c1;
EXPLAIN (verbose, costs off)
INSERT INTO ft2 (c1,c2,c3) VALUES (1200,999,'foo') RETURNING tableoid::regclass;
INSERT INTO ft2 (c1,c2,c3) VALUES (1200,999,'foo') RETURNING tableoid::regclass;
EXPLAIN (verbose, costs off)
UPDATE ft2 SET c3 = 'bar' WHERE c1 = 1200 RETURNING tableoid::regclass; -- can be pushed down
UPDATE ft2 SET c3 = 'bar' WHERE c1 = 1200 RETURNING tableoid::regclass;
EXPLAIN (verbose, costs off)
DELETE FROM ft2 WHERE c1 = 1200 RETURNING tableoid::regclass; -- can be pushed down
DELETE FROM ft2 WHERE c1 = 1200 RETURNING tableoid::regclass;
-- Test UPDATE/DELETE with RETURNING on a three-table join
INSERT INTO ft2 (c1,c2,c3)
SELECT id, id - 1200, to_char(id, 'FM00000') FROM generate_series(1201, 1300) id;
EXPLAIN (verbose, costs off)
UPDATE ft2 SET c3 = 'foo'
FROM ft4 INNER JOIN ft5 ON (ft4.c1 = ft5.c1)
WHERE ft2.c1 > 1200 AND ft2.c2 = ft4.c1
RETURNING ft2, ft2.*, ft4, ft4.*; -- can be pushed down
UPDATE ft2 SET c3 = 'foo'
FROM ft4 INNER JOIN ft5 ON (ft4.c1 = ft5.c1)
WHERE ft2.c1 > 1200 AND ft2.c2 = ft4.c1
RETURNING ft2, ft2.*, ft4, ft4.*;
EXPLAIN (verbose, costs off)
DELETE FROM ft2
USING ft4 LEFT JOIN ft5 ON (ft4.c1 = ft5.c1)
WHERE ft2.c1 > 1200 AND ft2.c1 % 10 = 0 AND ft2.c2 = ft4.c1
RETURNING 100; -- can be pushed down
DELETE FROM ft2
USING ft4 LEFT JOIN ft5 ON (ft4.c1 = ft5.c1)
WHERE ft2.c1 > 1200 AND ft2.c1 % 10 = 0 AND ft2.c2 = ft4.c1
RETURNING 100;
DELETE FROM ft2 WHERE ft2.c1 > 1200;
-- Test UPDATE with a MULTIEXPR sub-select
-- (maybe someday this'll be remotely executable, but not today)
EXPLAIN (verbose, costs off)
UPDATE ft2 AS target SET (c2, c7) = (
SELECT c2 * 10, c7
FROM ft2 AS src
WHERE target.c1 = src.c1
) WHERE c1 > 1100;
UPDATE ft2 AS target SET (c2, c7) = (
SELECT c2 * 10, c7
FROM ft2 AS src
WHERE target.c1 = src.c1
) WHERE c1 > 1100;
UPDATE ft2 AS target SET (c2) = (
SELECT c2 / 10
FROM ft2 AS src
WHERE target.c1 = src.c1
) WHERE c1 > 1100;
-- Test UPDATE/DELETE with WHERE or JOIN/ON conditions containing
-- user-defined operators/functions
ALTER SERVER pgserver OPTIONS (DROP extensions);
INSERT INTO ft2 (c1,c2,c3)
SELECT id, id % 10, to_char(id, 'FM00000') FROM generate_series(2001, 2010) id;
EXPLAIN (verbose, costs off)
UPDATE ft2 SET c3 = 'bar' WHERE postgres_fdw_abs(c1) > 2000 RETURNING *; -- can't be pushed down
UPDATE ft2 SET c3 = 'bar' WHERE postgres_fdw_abs(c1) > 2000 RETURNING *;
EXPLAIN (verbose, costs off)
UPDATE ft2 SET c3 = 'baz'
FROM ft4 INNER JOIN ft5 ON (ft4.c1 = ft5.c1)
WHERE ft2.c1 > 2000 AND ft2.c2 === ft4.c1
RETURNING ft2.*, ft4.*, ft5.*; -- can't be pushed down
UPDATE ft2 SET c3 = 'baz'
FROM ft4 INNER JOIN ft5 ON (ft4.c1 = ft5.c1)
WHERE ft2.c1 > 2000 AND ft2.c2 === ft4.c1
RETURNING ft2.*, ft4.*, ft5.*;
EXPLAIN (verbose, costs off)
DELETE FROM ft2
USING ft4 INNER JOIN ft5 ON (ft4.c1 === ft5.c1)
WHERE ft2.c1 > 2000 AND ft2.c2 = ft4.c1
RETURNING ft2.c1, ft2.c2, ft2.c3; -- can't be pushed down
DELETE FROM ft2
USING ft4 INNER JOIN ft5 ON (ft4.c1 === ft5.c1)
WHERE ft2.c1 > 2000 AND ft2.c2 = ft4.c1
RETURNING ft2.c1, ft2.c2, ft2.c3;
DELETE FROM ft2 WHERE ft2.c1 > 2000;
ALTER SERVER pgserver OPTIONS (ADD extensions 'postgres_fdw');
-- Test that trigger on remote table works as expected
-- CREATE OR REPLACE FUNCTION "S 1".F_BRTRIG() RETURNS trigger AS $$
-- BEGIN
-- NEW.c3 = NEW.c3 || '_trig_update';
-- RETURN NEW;
-- END;
-- $$ LANGUAGE plpgsql;
\! env PGOPTIONS='' psql -p ${PG_PORT} contrib_regression -c 'CREATE TRIGGER t1_br_insert BEFORE INSERT OR UPDATE ON "S 1"."T 1" FOR EACH ROW EXECUTE PROCEDURE "S 1".F_BRTRIG();'
-- CREATE TRIGGER t1_br_insert BEFORE INSERT OR UPDATE
-- ON "S 1"."T 1" FOR EACH ROW EXECUTE PROCEDURE "S 1".F_BRTRIG();
INSERT INTO ft2 (c1,c2,c3) VALUES (1208, 818, 'fff') RETURNING *;
INSERT INTO ft2 (c1,c2,c3,c6) VALUES (1218, 818, 'ggg', '(--;') RETURNING *;
UPDATE ft2 SET c2 = c2 + 600 WHERE c1 % 10 = 8 AND c1 < 1200 RETURNING *;
-- Test errors thrown on remote side during update
\! env PGOPTIONS='' psql -p ${PG_PORT} contrib_regression -c 'ALTER TABLE "S 1"."T 1" ADD CONSTRAINT c2positive CHECK (c2 >= 0);'
-- ALTER TABLE "S 1"."T 1" ADD CONSTRAINT c2positive CHECK (c2 >= 0);
INSERT INTO ft1(c1, c2) VALUES(11, 12); -- duplicate key
INSERT INTO ft1(c1, c2) VALUES(11, 12) ON CONFLICT DO NOTHING; -- works
INSERT INTO ft1(c1, c2) VALUES(11, 12) ON CONFLICT (c1, c2) DO NOTHING; -- unsupported
INSERT INTO ft1(c1, c2) VALUES(11, 12) ON CONFLICT (c1, c2) DO UPDATE SET c3 = 'ffg'; -- unsupported
INSERT INTO ft1(c1, c2) VALUES(1111, -2); -- c2positive
UPDATE ft1 SET c2 = -c2 WHERE c1 = 1; -- c2positive
-- Test savepoint/rollback behavior
select c2, count(*) from ft2 where c2 < 500 group by 1 order by 1;
\! env PGOPTIONS='' psql -p ${PG_PORT} contrib_regression -c 'select c2, count(*) from "S 1"."T 1" where c2 < 500 group by 1 order by 1;'
-- select c2, count(*) from "S 1"."T 1" where c2 < 500 group by 1 order by 1;
begin;
update ft2 set c2 = 42 where c2 = 0;
select c2, count(*) from ft2 where c2 < 500 group by 1 order by 1;
savepoint s1;
update ft2 set c2 = 44 where c2 = 4;
select c2, count(*) from ft2 where c2 < 500 group by 1 order by 1;
release savepoint s1;
select c2, count(*) from ft2 where c2 < 500 group by 1 order by 1;
savepoint s2;
update ft2 set c2 = 46 where c2 = 6;
select c2, count(*) from ft2 where c2 < 500 group by 1 order by 1;
rollback to savepoint s2;
select c2, count(*) from ft2 where c2 < 500 group by 1 order by 1;
release savepoint s2;
select c2, count(*) from ft2 where c2 < 500 group by 1 order by 1;
savepoint s3;
update ft2 set c2 = -2 where c2 = 42 and c1 = 10; -- fail on remote side
rollback to savepoint s3;
select c2, count(*) from ft2 where c2 < 500 group by 1 order by 1;
release savepoint s3;
select c2, count(*) from ft2 where c2 < 500 group by 1 order by 1;
-- none of the above is committed yet remotely
\! env PGOPTIONS='' psql -p ${PG_PORT} contrib_regression -c 'select c2, count(*) from "S 1"."T 1" where c2 < 500 group by 1 order by 1;'
-- select c2, count(*) from "S 1"."T 1" where c2 < 500 group by 1 order by 1;
commit;
select c2, count(*) from ft2 where c2 < 500 group by 1 order by 1;
\! env PGOPTIONS='' psql -p ${PG_PORT} contrib_regression -c 'select c2, count(*) from "S 1"."T 1" where c2 < 500 group by 1 order by 1;'
-- select c2, count(*) from "S 1"."T 1" where c2 < 500 group by 1 order by 1;
VACUUM ANALYZE "S 1"."T 1";
-- Above DMLs add data with c6 as NULL in ft1, so test ORDER BY NULLS LAST and NULLs
-- FIRST behavior here.
-- ORDER BY DESC NULLS LAST options
EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM ft1 ORDER BY c6 DESC NULLS LAST, c1 OFFSET 795 LIMIT 10;
SELECT * FROM ft1 ORDER BY c6 DESC NULLS LAST, c1 OFFSET 795 LIMIT 10;
-- ORDER BY DESC NULLS FIRST options
EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM ft1 ORDER BY c6 DESC NULLS FIRST, c1 OFFSET 15 LIMIT 10;
SELECT * FROM ft1 ORDER BY c6 DESC NULLS FIRST, c1 OFFSET 15 LIMIT 10;
-- ORDER BY ASC NULLS FIRST options
EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM ft1 ORDER BY c6 ASC NULLS FIRST, c1 OFFSET 15 LIMIT 10;
SELECT * FROM ft1 ORDER BY c6 ASC NULLS FIRST, c1 OFFSET 15 LIMIT 10;
-- ===================================================================
-- test check constraints
-- ===================================================================
-- Consistent check constraints provide consistent results
SET constraint_exclusion = 'off';
ALTER FOREIGN TABLE ft1 ADD CONSTRAINT ft1_c2positive CHECK (c2 >= 0);
EXPLAIN (VERBOSE, COSTS OFF) SELECT count(*) FROM ft1 WHERE c2 < 0;
SELECT count(*) FROM ft1 WHERE c2 < 0;
SET constraint_exclusion = 'on';
EXPLAIN (VERBOSE, COSTS OFF) SELECT count(*) FROM ft1 WHERE c2 < 0;
SELECT count(*) FROM ft1 WHERE c2 < 0;
SET constraint_exclusion = 'off';
-- RESET constraint_exclusion;
-- check constraint is enforced on the remote side, not locally
INSERT INTO ft1(c1, c2) VALUES(1111, -2); -- c2positive
UPDATE ft1 SET c2 = -c2 WHERE c1 = 1; -- c2positive
ALTER FOREIGN TABLE ft1 DROP CONSTRAINT ft1_c2positive;
-- But inconsistent check constraints provide inconsistent results
ALTER FOREIGN TABLE ft1 ADD CONSTRAINT ft1_c2negative CHECK (c2 < 0);
EXPLAIN (VERBOSE, COSTS OFF) SELECT count(*) FROM ft1 WHERE c2 >= 0;
SELECT count(*) FROM ft1 WHERE c2 >= 0;
SET constraint_exclusion = 'on';
EXPLAIN (VERBOSE, COSTS OFF) SELECT count(*) FROM ft1 WHERE c2 >= 0;
SELECT count(*) FROM ft1 WHERE c2 >= 0;
-- RESET constraint_exclusion0;
SET constraint_exclusion = 'off';
-- local check constraint is not actually enforced
INSERT INTO ft1(c1, c2) VALUES(1111, 2);
UPDATE ft1 SET c2 = c2 + 1 WHERE c1 = 1;
ALTER FOREIGN TABLE ft1 DROP CONSTRAINT ft1_c2negative;
-- ===================================================================
-- test WITH CHECK OPTION constraints
-- ===================================================================
\! env PGOPTIONS='' psql -p ${PG_PORT} contrib_regression -c 'CREATE FUNCTION row_before_insupd_trigfunc() RETURNS trigger AS $$BEGIN NEW.a := NEW.a + 10; RETURN NEW; END$$ LANGUAGE plpgsql;'
\! env PGOPTIONS='' psql -p ${PG_PORT} contrib_regression -c 'CREATE TABLE base_tbl (a int, b int);'
\! env PGOPTIONS='' psql -p ${PG_PORT} contrib_regression -c 'ALTER TABLE base_tbl SET (autovacuum_enabled = 'false');'
\! env PGOPTIONS='' psql -p ${PG_PORT} contrib_regression -c 'CREATE TRIGGER row_before_insupd_trigger BEFORE INSERT OR UPDATE ON base_tbl FOR EACH ROW EXECUTE PROCEDURE row_before_insupd_trigfunc();'
-- CREATE FUNCTION row_before_insupd_trigfunc() RETURNS trigger AS $$BEGIN NEW.a := NEW.a + 10; RETURN NEW; END$$ LANGUAGE plpgsql;
-- CREATE TABLE base_tbl (a int, b int);
-- ALTER TABLE base_tbl SET (autovacuum_enabled = 'false');
-- CREATE TRIGGER row_before_insupd_trigger BEFORE INSERT OR UPDATE ON base_tbl FOR EACH ROW EXECUTE PROCEDURE row_before_insupd_trigfunc();
CREATE FOREIGN TABLE foreign_tbl (a int, b int)
SERVER pgserver OPTIONS (table_name 'base_tbl');
CREATE VIEW rw_view AS SELECT * FROM foreign_tbl
WHERE a < b WITH CHECK OPTION;
\d+ rw_view
EXPLAIN (VERBOSE, COSTS OFF)
INSERT INTO rw_view VALUES (0, 5);
INSERT INTO rw_view VALUES (0, 5); -- should fail
EXPLAIN (VERBOSE, COSTS OFF)
INSERT INTO rw_view VALUES (0, 15);
INSERT INTO rw_view VALUES (0, 15); -- ok
SELECT * FROM foreign_tbl;
EXPLAIN (VERBOSE, COSTS OFF)
UPDATE rw_view SET b = b + 5;
UPDATE rw_view SET b = b + 5; -- should fail
EXPLAIN (VERBOSE, COSTS OFF)
UPDATE rw_view SET b = b + 15;
UPDATE rw_view SET b = b + 15; -- ok
SELECT * FROM foreign_tbl;
DROP FOREIGN TABLE foreign_tbl CASCADE;
\! env PGOPTIONS='' psql -p ${PG_PORT} contrib_regression -c 'DROP TRIGGER row_before_insupd_trigger ON base_tbl;'
\! env PGOPTIONS='' psql -p ${PG_PORT} contrib_regression -c 'DROP TABLE base_tbl;'
-- DROP TRIGGER row_before_insupd_trigger ON base_tbl;
-- DROP TABLE base_tbl;
-- test WCO for partitions
\! env PGOPTIONS='' psql -p ${PG_PORT} contrib_regression -c 'CREATE TABLE child_tbl (a int, b int);'
\! env PGOPTIONS='' psql -p ${PG_PORT} contrib_regression -c 'ALTER TABLE child_tbl SET (autovacuum_enabled = 'false');'
\! env PGOPTIONS='' psql -p ${PG_PORT} contrib_regression -c 'CREATE TRIGGER row_before_insupd_trigger BEFORE INSERT OR UPDATE ON child_tbl FOR EACH ROW EXECUTE PROCEDURE row_before_insupd_trigfunc();'
-- CREATE TABLE child_tbl (a int, b int);
-- ALTER TABLE child_tbl SET (autovacuum_enabled = 'false');
-- CREATE TRIGGER row_before_insupd_trigger BEFORE INSERT OR UPDATE ON child_tbl FOR EACH ROW EXECUTE PROCEDURE row_before_insupd_trigfunc();
CREATE FOREIGN TABLE foreign_tbl (a int, b int)
SERVER pgserver OPTIONS (table_name 'child_tbl');
CREATE TABLE parent_tbl (a int, b int) PARTITION BY RANGE(a);
ALTER TABLE parent_tbl ATTACH PARTITION foreign_tbl FOR VALUES FROM (0) TO (100);
CREATE VIEW rw_view AS SELECT * FROM parent_tbl
WHERE a < b WITH CHECK OPTION;
\d+ rw_view
EXPLAIN (VERBOSE, COSTS OFF)
INSERT INTO rw_view VALUES (0, 5);
INSERT INTO rw_view VALUES (0, 5); -- should fail
EXPLAIN (VERBOSE, COSTS OFF)
INSERT INTO rw_view VALUES (0, 15);
INSERT INTO rw_view VALUES (0, 15); -- ok
SELECT * FROM foreign_tbl;
EXPLAIN (VERBOSE, COSTS OFF)
UPDATE rw_view SET b = b + 5;
UPDATE rw_view SET b = b + 5; -- should fail
EXPLAIN (VERBOSE, COSTS OFF)
UPDATE rw_view SET b = b + 15;
-- In greenplum, table parent_tbl and foreign_tbl have different data distribution policy.
-- Greenplum don't support update/delete to such table as parent_tbl.
-- So here we disable the SQL below.
-- UPDATE rw_view SET b = b + 15; -- ok
SELECT * FROM foreign_tbl;
DROP FOREIGN TABLE foreign_tbl CASCADE;
\! env PGOPTIONS='' psql -p ${PG_PORT} contrib_regression -c 'DROP TRIGGER row_before_insupd_trigger ON child_tbl;'
\! env PGOPTIONS='' psql -p ${PG_PORT} contrib_regression -c 'DROP TABLE child_tbl;'
-- DROP TRIGGER row_before_insupd_trigger ON child_tbl;
DROP TABLE parent_tbl CASCADE;
\! env PGOPTIONS='' psql -p ${PG_PORT} contrib_regression -c 'DROP FUNCTION row_before_insupd_trigfunc;'
-- DROP FUNCTION row_before_insupd_trigfunc;
-- ===================================================================
-- test serial columns (ie, sequence-based defaults)
-- ===================================================================
\! env PGOPTIONS='' psql -p ${PG_PORT} contrib_regression -c 'create table loc1 (f1 serial, f2 text);'
\! env PGOPTIONS='' psql -p ${PG_PORT} contrib_regression -c 'alter table loc1 set (autovacuum_enabled = 'false');'
-- create table loc1 (f1 serial, f2 text);
-- alter table loc1 set (autovacuum_enabled = 'false');
create foreign table rem1 (f1 serial, f2 text)
server pgserver options(table_name 'loc1');
select pg_catalog.setval('rem1_f1_seq', 10, false);
\! env PGOPTIONS='' psql -p ${PG_PORT} contrib_regression -c "insert into loc1(f2) values('hi');"
-- insert into loc1(f2) values('hi');
insert into rem1(f2) values('hi remote');
\! env PGOPTIONS='' psql -p ${PG_PORT} contrib_regression -c "insert into loc1(f2) values('bye');"
-- insert into loc1(f2) values('bye');
insert into rem1(f2) values('bye remote');
\! env PGOPTIONS='' psql -p ${PG_PORT} contrib_regression -c 'select * from loc1;'
-- select * from loc1;
select * from rem1;
-- ===================================================================
-- test generated columns
-- ===================================================================
\! env PGOPTIONS='' psql -p ${PG_PORT} contrib_regression -c 'create table gloc1 (a int, b int generated always as (a * 2) stored);'
\! env PGOPTIONS='' psql -p ${PG_PORT} contrib_regression -c 'alter table gloc1 set (autovacuum_enabled = 'false');'
-- create table gloc1 (
-- a int,
-- b int generated always as (a * 2) stored);
-- alter table gloc1 set (autovacuum_enabled = 'false');
create foreign table grem1 (
a int,
b int generated always as (a * 2) stored)
server pgserver options(table_name 'gloc1');
explain (verbose, costs off)
insert into grem1 (a) values (1), (2);
insert into grem1 (a) values (1), (2);
explain (verbose, costs off)
update grem1 set a = 22 where a = 2;
update grem1 set a = 22 where a = 2;
\! env PGOPTIONS='' psql -p ${PG_PORT} contrib_regression -c 'select * from gloc1;'
-- select * from gloc1;
select * from grem1;
delete from grem1;
-- ===================================================================
-- test local triggers
-- ===================================================================
-- Trigger functions "borrowed" from triggers regress test.
CREATE FUNCTION trigger_func() RETURNS trigger LANGUAGE plpgsql AS $$
BEGIN
RAISE NOTICE 'trigger_func(%) called: action = %, when = %, level = %',
TG_ARGV[0], TG_OP, TG_WHEN, TG_LEVEL;
RETURN NULL;
END;$$;
-- In greenplum, Triggers for statements are not yet supported.
-- So SQLs below are disable here.
-- CREATE TRIGGER trig_stmt_before BEFORE DELETE OR INSERT OR UPDATE ON rem1
-- FOR EACH STATEMENT EXECUTE PROCEDURE trigger_func();
-- CREATE TRIGGER trig_stmt_after AFTER DELETE OR INSERT OR UPDATE ON rem1
-- FOR EACH STATEMENT EXECUTE PROCEDURE trigger_func();
CREATE OR REPLACE FUNCTION trigger_data() RETURNS trigger
LANGUAGE plpgsql AS $$
declare
oldnew text[];
relid text;
argstr text;
begin
relid := TG_relid::regclass;
argstr := '';
for i in 0 .. TG_nargs - 1 loop
if i > 0 then
argstr := argstr || ', ';
end if;
argstr := argstr || TG_argv[i];
end loop;
RAISE NOTICE '%(%) % % % ON %',
tg_name, argstr, TG_when, TG_level, TG_OP, relid;
oldnew := '{}'::text[];
if TG_OP != 'INSERT' then
oldnew := array_append(oldnew, format('OLD: %s', OLD));
end if;
if TG_OP != 'DELETE' then
oldnew := array_append(oldnew, format('NEW: %s', NEW));
end if;
RAISE NOTICE '%', array_to_string(oldnew, ',');
if TG_OP = 'DELETE' then
return OLD;
else
return NEW;
end if;
end;
$$;
-- Test basic functionality
CREATE TRIGGER trig_row_before
BEFORE INSERT OR UPDATE OR DELETE ON rem1
FOR EACH ROW EXECUTE PROCEDURE trigger_data(23,'skidoo');
CREATE TRIGGER trig_row_after
AFTER INSERT OR UPDATE OR DELETE ON rem1
FOR EACH ROW EXECUTE PROCEDURE trigger_data(23,'skidoo');
delete from rem1;
insert into rem1 values(1,'insert');
update rem1 set f2 = 'update' where f1 = 1;
update rem1 set f2 = f2 || f2;
-- cleanup
DROP TRIGGER trig_row_before ON rem1;
DROP TRIGGER trig_row_after ON rem1;
-- In greenplum, Triggers for statements are not yet supported.
-- So SQLs below are disable here.
-- DROP TRIGGER trig_stmt_before ON rem1;
-- DROP TRIGGER trig_stmt_after ON rem1;
DELETE from rem1;
-- Test multiple AFTER ROW triggers on a foreign table
CREATE TRIGGER trig_row_after1
AFTER INSERT OR UPDATE OR DELETE ON rem1
FOR EACH ROW EXECUTE PROCEDURE trigger_data(23,'skidoo');
CREATE TRIGGER trig_row_after2
AFTER INSERT OR UPDATE OR DELETE ON rem1
FOR EACH ROW EXECUTE PROCEDURE trigger_data(23,'skidoo');
insert into rem1 values(1,'insert');
update rem1 set f2 = 'update' where f1 = 1;
update rem1 set f2 = f2 || f2;
delete from rem1;
-- cleanup
DROP TRIGGER trig_row_after1 ON rem1;
DROP TRIGGER trig_row_after2 ON rem1;
-- Test WHEN conditions
CREATE TRIGGER trig_row_before_insupd
BEFORE INSERT OR UPDATE ON rem1
FOR EACH ROW
WHEN (NEW.f2 like '%update%')
EXECUTE PROCEDURE trigger_data(23,'skidoo');
CREATE TRIGGER trig_row_after_insupd
AFTER INSERT OR UPDATE ON rem1
FOR EACH ROW
WHEN (NEW.f2 like '%update%')
EXECUTE PROCEDURE trigger_data(23,'skidoo');
-- Insert or update not matching: nothing happens
INSERT INTO rem1 values(1, 'insert');
UPDATE rem1 set f2 = 'test';
-- Insert or update matching: triggers are fired
INSERT INTO rem1 values(2, 'update');
UPDATE rem1 set f2 = 'update update' where f1 = '2';
CREATE TRIGGER trig_row_before_delete
BEFORE DELETE ON rem1
FOR EACH ROW
WHEN (OLD.f2 like '%update%')
EXECUTE PROCEDURE trigger_data(23,'skidoo');
CREATE TRIGGER trig_row_after_delete
AFTER DELETE ON rem1
FOR EACH ROW
WHEN (OLD.f2 like '%update%')
EXECUTE PROCEDURE trigger_data(23,'skidoo');
-- Trigger is fired for f1=2, not for f1=1
DELETE FROM rem1;
-- cleanup
DROP TRIGGER trig_row_before_insupd ON rem1;
DROP TRIGGER trig_row_after_insupd ON rem1;
DROP TRIGGER trig_row_before_delete ON rem1;
DROP TRIGGER trig_row_after_delete ON rem1;
-- Test various RETURN statements in BEFORE triggers.
CREATE FUNCTION trig_row_before_insupdate() RETURNS TRIGGER AS $$
BEGIN
NEW.f2 := NEW.f2 || ' triggered !';
RETURN NEW;
END
$$ language plpgsql;
CREATE TRIGGER trig_row_before_insupd
BEFORE INSERT OR UPDATE ON rem1
FOR EACH ROW EXECUTE PROCEDURE trig_row_before_insupdate();
-- The new values should have 'triggered' appended
INSERT INTO rem1 values(1, 'insert');
\! env PGOPTIONS='' psql -p ${PG_PORT} contrib_regression -c 'SELECT * from loc1;'
-- SELECT * from loc1;
INSERT INTO rem1 values(2, 'insert') RETURNING f2;
\! env PGOPTIONS='' psql -p ${PG_PORT} contrib_regression -c 'SELECT * from loc1;'
-- SELECT * from loc1;
UPDATE rem1 set f2 = '';
\! env PGOPTIONS='' psql -p ${PG_PORT} contrib_regression -c 'SELECT * from loc1;'
-- SELECT * from loc1;
UPDATE rem1 set f2 = 'skidoo' RETURNING f2;
\! env PGOPTIONS='' psql -p ${PG_PORT} contrib_regression -c 'SELECT * from loc1;'
-- SELECT * from loc1;
EXPLAIN (verbose, costs off)
UPDATE rem1 set f1 = 10; -- all columns should be transmitted
UPDATE rem1 set f1 = 10;
\! env PGOPTIONS='' psql -p ${PG_PORT} contrib_regression -c 'SELECT * from loc1;'
-- SELECT * from loc1;
DELETE FROM rem1;
-- Add a second trigger, to check that the changes are propagated correctly
-- from trigger to trigger
CREATE TRIGGER trig_row_before_insupd2
BEFORE INSERT OR UPDATE ON rem1
FOR EACH ROW EXECUTE PROCEDURE trig_row_before_insupdate();
INSERT INTO rem1 values(1, 'insert');
\! env PGOPTIONS='' psql -p ${PG_PORT} contrib_regression -c 'SELECT * from loc1;'
-- SELECT * from loc1;
INSERT INTO rem1 values(2, 'insert') RETURNING f2;
\! env PGOPTIONS='' psql -p ${PG_PORT} contrib_regression -c 'SELECT * from loc1;'
-- SELECT * from loc1;
UPDATE rem1 set f2 = '';
\! env PGOPTIONS='' psql -p ${PG_PORT} contrib_regression -c 'SELECT * from loc1;'
-- SELECT * from loc1;
UPDATE rem1 set f2 = 'skidoo' RETURNING f2;
\! env PGOPTIONS='' psql -p ${PG_PORT} contrib_regression -c 'SELECT * from loc1;'
-- SELECT * from loc1;
DROP TRIGGER trig_row_before_insupd ON rem1;
DROP TRIGGER trig_row_before_insupd2 ON rem1;
DELETE from rem1;
INSERT INTO rem1 VALUES (1, 'test');
-- Test with a trigger returning NULL
CREATE FUNCTION trig_null() RETURNS TRIGGER AS $$
BEGIN
RETURN NULL;
END
$$ language plpgsql;
CREATE TRIGGER trig_null
BEFORE INSERT OR UPDATE OR DELETE ON rem1
FOR EACH ROW EXECUTE PROCEDURE trig_null();
-- Nothing should have changed.
INSERT INTO rem1 VALUES (2, 'test2');
\! env PGOPTIONS='' psql -p ${PG_PORT} contrib_regression -c 'SELECT * from loc1;'
-- SELECT * from loc1;
UPDATE rem1 SET f2 = 'test2';
\! env PGOPTIONS='' psql -p ${PG_PORT} contrib_regression -c 'SELECT * from loc1;'
-- SELECT * from loc1;
DELETE from rem1;
\! env PGOPTIONS='' psql -p ${PG_PORT} contrib_regression -c 'SELECT * from loc1;'
-- SELECT * from loc1;
DROP TRIGGER trig_null ON rem1;
DELETE from rem1;
-- Test a combination of local and remote triggers
CREATE TRIGGER trig_row_before
BEFORE INSERT OR UPDATE OR DELETE ON rem1
FOR EACH ROW EXECUTE PROCEDURE trigger_data(23,'skidoo');
CREATE TRIGGER trig_row_after
AFTER INSERT OR UPDATE OR DELETE ON rem1
FOR EACH ROW EXECUTE PROCEDURE trigger_data(23,'skidoo');
\! env PGOPTIONS='' psql -p ${PG_PORT} contrib_regression -c 'CREATE TRIGGER trig_local_before BEFORE INSERT OR UPDATE ON loc1 FOR EACH ROW EXECUTE PROCEDURE trig_row_before_insupdate();'
-- CREATE TRIGGER trig_local_before BEFORE INSERT OR UPDATE ON loc1
-- FOR EACH ROW EXECUTE PROCEDURE trig_row_before_insupdate();
INSERT INTO rem1(f2) VALUES ('test');
UPDATE rem1 SET f2 = 'testo';
-- Test returning a system attribute
INSERT INTO rem1(f2) VALUES ('test') RETURNING ctid;
-- cleanup
DROP TRIGGER trig_row_before ON rem1;
DROP TRIGGER trig_row_after ON rem1;
\! env PGOPTIONS='' psql -p ${PG_PORT} contrib_regression -c 'DROP TRIGGER trig_local_before ON loc1;'
-- DROP TRIGGER trig_local_before ON loc1;
-- Test direct foreign table modification functionality
-- Test with statement-level triggers
-- In greenplum, Triggers for statements are not yet supported.
-- So SQLs below are disable here.
-- CREATE TRIGGER trig_stmt_before
-- BEFORE DELETE OR INSERT OR UPDATE ON rem1
-- FOR EACH STATEMENT EXECUTE PROCEDURE trigger_func();
EXPLAIN (verbose, costs off)
UPDATE rem1 set f2 = ''; -- can be pushed down
EXPLAIN (verbose, costs off)
DELETE FROM rem1; -- can be pushed down
-- DROP TRIGGER trig_stmt_before ON rem1;
-- In greenplum, Triggers for statements are not yet supported.
-- So SQLs below are disable here.
-- CREATE TRIGGER trig_stmt_after
-- AFTER DELETE OR INSERT OR UPDATE ON rem1
-- FOR EACH STATEMENT EXECUTE PROCEDURE trigger_func();
EXPLAIN (verbose, costs off)
UPDATE rem1 set f2 = ''; -- can be pushed down
EXPLAIN (verbose, costs off)
DELETE FROM rem1; -- can be pushed down
-- DROP TRIGGER trig_stmt_after ON rem1;
-- Test with row-level ON INSERT triggers
CREATE TRIGGER trig_row_before_insert
BEFORE INSERT ON rem1
FOR EACH ROW EXECUTE PROCEDURE trigger_data(23,'skidoo');
EXPLAIN (verbose, costs off)
UPDATE rem1 set f2 = ''; -- can be pushed down
EXPLAIN (verbose, costs off)
DELETE FROM rem1; -- can be pushed down
DROP TRIGGER trig_row_before_insert ON rem1;
CREATE TRIGGER trig_row_after_insert
AFTER INSERT ON rem1
FOR EACH ROW EXECUTE PROCEDURE trigger_data(23,'skidoo');
EXPLAIN (verbose, costs off)
UPDATE rem1 set f2 = ''; -- can be pushed down
EXPLAIN (verbose, costs off)
DELETE FROM rem1; -- can be pushed down
DROP TRIGGER trig_row_after_insert ON rem1;
-- Test with row-level ON UPDATE triggers
CREATE TRIGGER trig_row_before_update
BEFORE UPDATE ON rem1
FOR EACH ROW EXECUTE PROCEDURE trigger_data(23,'skidoo');
EXPLAIN (verbose, costs off)
UPDATE rem1 set f2 = ''; -- can't be pushed down
EXPLAIN (verbose, costs off)
DELETE FROM rem1; -- can be pushed down
DROP TRIGGER trig_row_before_update ON rem1;
CREATE TRIGGER trig_row_after_update
AFTER UPDATE ON rem1
FOR EACH ROW EXECUTE PROCEDURE trigger_data(23,'skidoo');
EXPLAIN (verbose, costs off)
UPDATE rem1 set f2 = ''; -- can't be pushed down
EXPLAIN (verbose, costs off)
DELETE FROM rem1; -- can be pushed down
DROP TRIGGER trig_row_after_update ON rem1;
-- Test with row-level ON DELETE triggers
CREATE TRIGGER trig_row_before_delete
BEFORE DELETE ON rem1
FOR EACH ROW EXECUTE PROCEDURE trigger_data(23,'skidoo');
EXPLAIN (verbose, costs off)
UPDATE rem1 set f2 = ''; -- can be pushed down
EXPLAIN (verbose, costs off)
DELETE FROM rem1; -- can't be pushed down
DROP TRIGGER trig_row_before_delete ON rem1;
CREATE TRIGGER trig_row_after_delete
AFTER DELETE ON rem1
FOR EACH ROW EXECUTE PROCEDURE trigger_data(23,'skidoo');
EXPLAIN (verbose, costs off)
UPDATE rem1 set f2 = ''; -- can be pushed down
EXPLAIN (verbose, costs off)
DELETE FROM rem1; -- can't be pushed down
DROP TRIGGER trig_row_after_delete ON rem1;
-- ===================================================================
-- test inheritance features
-- ===================================================================
CREATE TABLE a (aa TEXT);
ALTER TABLE a SET (autovacuum_enabled = 'false');
\! env PGOPTIONS='' psql -p ${PG_PORT} contrib_regression -c 'CREATE TABLE loct (aa TEXT, bb TEXT);'
\! env PGOPTIONS='' psql -p ${PG_PORT} contrib_regression -c 'ALTER TABLE loct SET (autovacuum_enabled = 'false');'
-- CREATE TABLE loct (aa TEXT, bb TEXT);
-- ALTER TABLE loct SET (autovacuum_enabled = 'false');
CREATE FOREIGN TABLE b (bb TEXT) INHERITS (a)
SERVER pgserver OPTIONS (table_name 'loct');
INSERT INTO a(aa) VALUES('aaa');
INSERT INTO a(aa) VALUES('aaaa');
INSERT INTO a(aa) VALUES('aaaaa');
INSERT INTO b(aa) VALUES('bbb');
INSERT INTO b(aa) VALUES('bbbb');
INSERT INTO b(aa) VALUES('bbbbb');
SELECT tableoid::regclass, * FROM a;
SELECT tableoid::regclass, * FROM b;
SELECT tableoid::regclass, * FROM ONLY a;
-- Greenplum don't support that ModifyTable mixes distributed and entry-only tables.
-- So SQLs below are disable here.
/*
UPDATE a SET aa = 'zzzzzz' WHERE aa LIKE 'aaaa%';
SELECT tableoid::regclass, * FROM a;
SELECT tableoid::regclass, * FROM b;
SELECT tableoid::regclass, * FROM ONLY a;
*/
UPDATE b SET aa = 'new';
SELECT tableoid::regclass, * FROM a;
SELECT tableoid::regclass, * FROM b;
SELECT tableoid::regclass, * FROM ONLY a;
-- Greenplum don't support that ModifyTable mixes distributed and entry-only tables.
-- So SQLs below are disable here.
/*
UPDATE a SET aa = 'newtoo';
SELECT tableoid::regclass, * FROM a;
SELECT tableoid::regclass, * FROM b;
SELECT tableoid::regclass, * FROM ONLY a;
DELETE FROM a;
SELECT tableoid::regclass, * FROM a;
SELECT tableoid::regclass, * FROM b;
SELECT tableoid::regclass, * FROM ONLY a;
*/
DROP TABLE a CASCADE;
\! env PGOPTIONS='' psql -p ${PG_PORT} contrib_regression -c 'DROP TABLE loct;'
-- DROP TABLE loct;
-- Check SELECT FOR UPDATE/SHARE with an inherited source table
\! env PGOPTIONS='' psql -p ${PG_PORT} contrib_regression -c 'create table loct1 (f1 int, f2 int, f3 int);'
\! env PGOPTIONS='' psql -p ${PG_PORT} contrib_regression -c 'create table loct2 (f1 int, f2 int, f3 int);'
create table loct1 (f1 int, f2 int, f3 int);
create table loct2 (f1 int, f2 int, f3 int);
\! env PGOPTIONS='' psql -p ${PG_PORT} contrib_regression -c 'alter table loct1 set (autovacuum_enabled = 'false');'
\! env PGOPTIONS='' psql -p ${PG_PORT} contrib_regression -c 'alter table loct2 set (autovacuum_enabled = 'false');'
alter table loct1 set (autovacuum_enabled = 'false');
alter table loct2 set (autovacuum_enabled = 'false');
create table foo (f1 int, f2 int);
create foreign table foo2 (f3 int) inherits (foo)
server pgserver options (table_name 'loct1');
create table bar (f1 int, f2 int);
create foreign table bar2 (f3 int) inherits (bar)
server pgserver options (table_name 'loct2');
alter table foo set (autovacuum_enabled = 'false');
alter table bar set (autovacuum_enabled = 'false');
insert into foo values(1,1);
insert into foo values(3,3);
insert into foo2 values(2,2,2);
insert into foo2 values(4,4,4);
insert into bar values(1,11);
insert into bar values(2,22);
insert into bar values(6,66);
insert into bar2 values(3,33,33);
insert into bar2 values(4,44,44);
insert into bar2 values(7,77,77);
explain (verbose, costs off)
select * from bar where f1 in (select f1 from foo) for update;
select * from bar where f1 in (select f1 from foo) for update;
explain (verbose, costs off)
select * from bar where f1 in (select f1 from foo) for share;
select * from bar where f1 in (select f1 from foo) for share;
-- Greenplum don't support that ModifyTable mixes distributed and entry-only tables.
-- So SQLs below are disable here.
/*
-- Now check SELECT FOR UPDATE/SHARE with an inherited source table,
-- where the parent is itself a foreign table
create table loct4 (f1 int, f2 int, f3 int);
create foreign table foo2child (f3 int) inherits (foo2)
server loopback options (table_name 'loct4');
explain (verbose, costs off)
select * from bar where f1 in (select f1 from foo2) for share;
select * from bar where f1 in (select f1 from foo2) for share;
drop foreign table foo2child;
-- And with a local child relation of the foreign table parent
create table foo2child (f3 int) inherits (foo2);
explain (verbose, costs off)
select * from bar where f1 in (select f1 from foo2) for share;
select * from bar where f1 in (select f1 from foo2) for share;
drop table foo2child;
-- Check UPDATE with inherited target and an inherited source table
explain (verbose, costs off)
update bar set f2 = f2 + 100 where f1 in (select f1 from foo);
update bar set f2 = f2 + 100 where f1 in (select f1 from foo);
select tableoid::regclass, * from bar order by 1,2;
-- Check UPDATE with inherited target and an appendrel subquery
explain (verbose, costs off)
update bar set f2 = f2 + 100
from
( select f1 from foo union all select f1+3 from foo ) ss
where bar.f1 = ss.f1;
update bar set f2 = f2 + 100
from
( select f1 from foo union all select f1+3 from foo ) ss
where bar.f1 = ss.f1;
select tableoid::regclass, * from bar order by 1,2;
*/
-- Test forcing the remote server to produce sorted data for a merge join,
-- but the foreign table is an inheritance child.
\! env PGOPTIONS='' psql -p ${PG_PORT} contrib_regression -c 'truncate table loct1;'
truncate table loct1;
truncate table only foo;
-- \! env PGOPTIONS='' psql -p ${PG_PORT} contrib_regression -c "$\set num_rows_foo 2000"
-- \! env PGOPTIONS='' psql -p ${PG_PORT} contrib_regression -c "insert into loct1 select generate_series(0, :num_rows_foo, 2), generate_series(0, :num_rows_foo, 2), generate_series(0, :num_rows_foo, 2);"
\! env PGOPTIONS='' psql -p ${PG_PORT} contrib_regression -c "insert into loct1 select generate_series(0, 2000, 2), generate_series(0, 2000, 2), generate_series(0, 2000, 2);"
\set num_rows_foo 2000
insert into loct1 select generate_series(0, :num_rows_foo, 2), generate_series(0, :num_rows_foo, 2), generate_series(0, :num_rows_foo, 2);
insert into foo select generate_series(1, :num_rows_foo, 2), generate_series(1, :num_rows_foo, 2);
SET enable_hashjoin to false;
SET enable_nestloop to false;
SET enable_mergejoin to true;
alter foreign table foo2 options (use_remote_estimate 'true');
\! env PGOPTIONS='' psql -p ${PG_PORT} contrib_regression -c 'create index i_loct1_f1 on loct1(f1);'
create index i_loct1_f1 on loct1(f1);
create index i_foo_f1 on foo(f1);
analyze foo;
analyze loct1;
-- inner join; expressions in the clauses appear in the equivalence class list
explain (verbose, costs off)
select foo.f1, loct1.f1 from foo join loct1 on (foo.f1 = loct1.f1) order by foo.f2 offset 10 limit 10;
select foo.f1, loct1.f1 from foo join loct1 on (foo.f1 = loct1.f1) order by foo.f2 offset 10 limit 10;
-- outer join; expressions in the clauses do not appear in equivalence class
-- list but no output change as compared to the previous query
explain (verbose, costs off)
select foo.f1, loct1.f1 from foo left join loct1 on (foo.f1 = loct1.f1) order by foo.f2 offset 10 limit 10;
select foo.f1, loct1.f1 from foo left join loct1 on (foo.f1 = loct1.f1) order by foo.f2 offset 10 limit 10;
RESET enable_hashjoin;
RESET enable_nestloop;
RESET enable_mergejoin;
-- Test that WHERE CURRENT OF is not supported
begin;
declare c cursor for select * from bar where f1 = 7;
fetch from c;
-- Greenplum don't support that ModifyTable mixes distributed and entry-only tables.
-- So SQLs below are disable here.
/* update bar set f2 = null where current of c; */
rollback;
-- Greenplum don't support that ModifyTable mixes distributed and entry-only tables.
-- So SQLs below are disable here.
/*
explain (verbose, costs off)
delete from foo where f1 < 5 returning *;
delete from foo where f1 < 5 returning *;
explain (verbose, costs off)
update bar set f2 = f2 + 100 returning *;
update bar set f2 = f2 + 100 returning *;
*/
-- Test that UPDATE/DELETE with inherited target works with row-level triggers
CREATE TRIGGER trig_row_before
BEFORE UPDATE OR DELETE ON bar2
FOR EACH ROW EXECUTE PROCEDURE trigger_data(23,'skidoo');
CREATE TRIGGER trig_row_after
AFTER UPDATE OR DELETE ON bar2
FOR EACH ROW EXECUTE PROCEDURE trigger_data(23,'skidoo');
-- Greenplum don't support that ModifyTable mixes distributed and entry-only tables.
-- So SQLs below are disable here.
/*
explain (verbose, costs off)
update bar set f2 = f2 + 100;
update bar set f2 = f2 + 100;
explain (verbose, costs off)
delete from bar where f2 < 400;
delete from bar where f2 < 400;
*/
-- cleanup
drop table foo cascade;
drop table bar cascade;
\! env PGOPTIONS='' psql -p ${PG_PORT} contrib_regression -c 'drop table loct1;'
\! env PGOPTIONS='' psql -p ${PG_PORT} contrib_regression -c 'drop table loct2;'
drop table loct1;
drop table loct2;
-- Test pushing down UPDATE/DELETE joins to the remote server
create table parent (a int, b text);
\! env PGOPTIONS='' psql -p ${PG_PORT} contrib_regression -c 'create table loct1 (a int, b text);'
\! env PGOPTIONS='' psql -p ${PG_PORT} contrib_regression -c 'create table loct2 (a int, b text);'
-- create table loct1 (a int, b text);
-- create table loct2 (a int, b text);
create foreign table remt1 (a int, b text)
server pgserver options (table_name 'loct1');
create foreign table remt2 (a int, b text)
server pgserver options (table_name 'loct2');
alter foreign table remt1 inherit parent;
insert into remt1 values (1, 'foo');
insert into remt1 values (2, 'bar');
insert into remt2 values (1, 'foo');
insert into remt2 values (2, 'bar');
analyze remt1;
analyze remt2;
-- Greenplum don't support that ModifyTable mixes distributed and entry-only tables.
-- So SQLs below are disable here.
/*
explain (verbose, costs off)
update parent set b = parent.b || remt2.b from remt2 where parent.a = remt2.a returning *;
update parent set b = parent.b || remt2.b from remt2 where parent.a = remt2.a returning *;
explain (verbose, costs off)
delete from parent using remt2 where parent.a = remt2.a returning parent;
delete from parent using remt2 where parent.a = remt2.a returning parent;
*/
-- cleanup
drop foreign table remt1;
drop foreign table remt2;
\! env PGOPTIONS='' psql -p ${PG_PORT} contrib_regression -c 'drop table loct1;'
\! env PGOPTIONS='' psql -p ${PG_PORT} contrib_regression -c 'drop table loct2;'
-- drop table loct1;
-- drop table loct2;
drop table parent;
-- ===================================================================
-- test tuple routing for foreign-table partitions
-- ===================================================================
-- Test insert tuple routing
create table itrtest (a int, b text) partition by list (a);
\! env PGOPTIONS='' psql -p ${PG_PORT} contrib_regression -c 'create table loct1 (a int check (a in (1)), b text);'
-- create table loct1 (a int check (a in (1)), b text);
create foreign table remp1 (a int check (a in (1)), b text) server pgserver options (table_name 'loct1');
\! env PGOPTIONS='' psql -p ${PG_PORT} contrib_regression -c 'create table loct2 (a int check (a in (2)), b text);'
-- create table loct2 (a int check (a in (2)), b text);
create foreign table remp2 (b text, a int check (a in (2))) server pgserver options (table_name 'loct2');
alter table itrtest attach partition remp1 for values in (1);
alter table itrtest attach partition remp2 for values in (2);
insert into itrtest values (1, 'foo');
insert into itrtest values (1, 'bar') returning *;
insert into itrtest values (2, 'baz');
insert into itrtest values (2, 'qux') returning *;
insert into itrtest values (1, 'test1'), (2, 'test2') returning *;
select tableoid::regclass, * FROM itrtest;
select tableoid::regclass, * FROM remp1;
select tableoid::regclass, * FROM remp2;
-- In greenplum, table itrtest and remp1(remp2) have different data distribution policy.
-- Greenplum don't support update/delete to such table as itrtest.
-- So here we disable the SQL below.
/* delete from itrtest; */
\! env PGOPTIONS='' psql -p ${PG_PORT} contrib_regression -c 'delete from loct1;'
\! env PGOPTIONS='' psql -p ${PG_PORT} contrib_regression -c 'delete from loct2;'
\! env PGOPTIONS='' psql -p ${PG_PORT} contrib_regression -c 'create unique index loct1_idx on loct1 (a);'
-- create unique index loct1_idx on loct1 (a);
-- DO NOTHING without an inference specification is supported
insert into itrtest values (1, 'foo') on conflict do nothing returning *;
insert into itrtest values (1, 'foo') on conflict do nothing returning *;
-- But other cases are not supported
insert into itrtest values (1, 'bar') on conflict (a) do nothing;
insert into itrtest values (1, 'bar') on conflict (a) do update set b = excluded.b;
select tableoid::regclass, * FROM itrtest;
-- In greenplum, table itrtest and remp1(remp2) have different data distribution policy.
-- Greenplum don't support update/delete to such table as itrtest.
-- So here we disable the SQL below.
/* delete from itrtest; */
\! env PGOPTIONS='' psql -p ${PG_PORT} contrib_regression -c 'drop index loct1_idx;'
-- drop index loct1_idx;
-- Test that remote triggers work with insert tuple routing
-- create function br_insert_trigfunc() returns trigger as $$
-- begin
-- new.b := new.b || ' triggered !';
-- return new;
-- end
-- $$ language plpgsql;
\! env PGOPTIONS='' psql -p ${PG_PORT} contrib_regression -c 'create trigger loct1_br_insert_trigger before insert on loct1 for each row execute procedure br_insert_trigfunc();'
\! env PGOPTIONS='' psql -p ${PG_PORT} contrib_regression -c 'create trigger loct2_br_insert_trigger before insert on loct2 for each row execute procedure br_insert_trigfunc();'
-- create trigger loct1_br_insert_trigger before insert on loct1
-- for each row execute procedure br_insert_trigfunc();
-- create trigger loct2_br_insert_trigger before insert on loct2
-- for each row execute procedure br_insert_trigfunc();
-- The new values are concatenated with ' triggered !'
insert into itrtest values (1, 'foo') returning *;
insert into itrtest values (2, 'qux') returning *;
insert into itrtest values (1, 'test1'), (2, 'test2') returning *;
with result as (insert into itrtest values (1, 'test1'), (2, 'test2') returning *) select * from result;
\! env PGOPTIONS='' psql -p ${PG_PORT} contrib_regression -c 'drop trigger loct1_br_insert_trigger on loct1;'
\! env PGOPTIONS='' psql -p ${PG_PORT} contrib_regression -c 'drop trigger loct2_br_insert_trigger on loct2;'
-- drop trigger loct1_br_insert_trigger on loct1;
-- drop trigger loct2_br_insert_trigger on loct2;
drop table itrtest;
\! env PGOPTIONS='' psql -p ${PG_PORT} contrib_regression -c 'drop table loct1;'
\! env PGOPTIONS='' psql -p ${PG_PORT} contrib_regression -c 'drop table loct2;'
-- drop table loct1;
-- drop table loct2;
-- Greenplum don't support that ModifyTable mixes distributed and entry-only tables.
-- SQLs below are disable here.
/*
-- Test update tuple routing
create table utrtest (a int, b text) partition by list (a);
\! env PGOPTIONS='' psql -p ${PG_PORT} contrib_regression -c 'create table loct (a int check (a in (1)), b text);'
-- create table loct (a int check (a in (1)), b text);
create foreign table remp (a int check (a in (1)), b text) server pgserver options (table_name 'loct');
create table locp (a int check (a in (2)), b text);
alter table utrtest attach partition remp for values in (1);
alter table utrtest attach partition locp for values in (2);
insert into utrtest values (1, 'foo');
insert into utrtest values (2, 'qux');
select tableoid::regclass, * FROM utrtest;
select tableoid::regclass, * FROM remp;
select tableoid::regclass, * FROM locp;
-- It's not allowed to move a row from a partition that is foreign to another
update utrtest set a = 2 where b = 'foo' returning *;
-- But the reverse is allowed
update utrtest set a = 1 where b = 'qux' returning *;
select tableoid::regclass, * FROM utrtest;
select tableoid::regclass, * FROM remp;
select tableoid::regclass, * FROM locp;
-- The executor should not let unexercised FDWs shut down
update utrtest set a = 1 where b = 'foo';
-- Test that remote triggers work with update tuple routing
\! env PGOPTIONS='' psql -p ${PG_PORT} contrib_regression -c 'create trigger loct_br_insert_trigger before insert on loct for each row execute procedure br_insert_trigfunc();'
-- create trigger loct_br_insert_trigger before insert on loct
-- for each row execute procedure br_insert_trigfunc();
delete from utrtest;
insert into utrtest values (2, 'qux');
-- Check case where the foreign partition is a subplan target rel
explain (verbose, costs off)
update utrtest set a = 1 where a = 1 or a = 2 returning *;
-- The new values are concatenated with ' triggered !'
update utrtest set a = 1 where a = 1 or a = 2 returning *;
delete from utrtest;
insert into utrtest values (2, 'qux');
-- Check case where the foreign partition isn't a subplan target rel
explain (verbose, costs off)
update utrtest set a = 1 where a = 2 returning *;
-- The new values are concatenated with ' triggered !'
update utrtest set a = 1 where a = 2 returning *;
\! env PGOPTIONS='' psql -p ${PG_PORT} contrib_regression -c 'drop trigger loct_br_insert_trigger on loct;'
-- drop trigger loct_br_insert_trigger on loct;
-- We can move rows to a foreign partition that has been updated already,
-- but can't move rows to a foreign partition that hasn't been updated yet
delete from utrtest;
insert into utrtest values (1, 'foo');
insert into utrtest values (2, 'qux');
-- Test the former case:
-- with a direct modification plan
explain (verbose, costs off)
update utrtest set a = 1 returning *;
update utrtest set a = 1 returning *;
delete from utrtest;
insert into utrtest values (1, 'foo');
insert into utrtest values (2, 'qux');
-- with a non-direct modification plan
explain (verbose, costs off)
update utrtest set a = 1 from (values (1), (2)) s(x) where a = s.x returning *;
update utrtest set a = 1 from (values (1), (2)) s(x) where a = s.x returning *;
-- Change the definition of utrtest so that the foreign partition get updated
-- after the local partition
delete from utrtest;
alter table utrtest detach partition remp;
drop foreign table remp;
\! env PGOPTIONS='' psql -p ${PG_PORT} contrib_regression -c 'alter table loct drop constraint loct_a_check;'
\! env PGOPTIONS='' psql -p ${PG_PORT} contrib_regression -c 'alter table loct add check (a in (3));'
-- alter table loct drop constraint loct_a_check;
-- alter table loct add check (a in (3));
create foreign table remp (a int check (a in (3)), b text) server pgserver options (table_name 'loct');
alter table utrtest attach partition remp for values in (3);
insert into utrtest values (2, 'qux');
insert into utrtest values (3, 'xyzzy');
-- Test the latter case:
-- with a direct modification plan
explain (verbose, costs off)
update utrtest set a = 3 returning *;
update utrtest set a = 3 returning *; -- ERROR
-- with a non-direct modification plan
explain (verbose, costs off)
update utrtest set a = 3 from (values (2), (3)) s(x) where a = s.x returning *;
update utrtest set a = 3 from (values (2), (3)) s(x) where a = s.x returning *; -- ERROR
drop table utrtest;
\! env PGOPTIONS='' psql -p ${PG_PORT} contrib_regression -c 'drop table loct;'
-- drop table loct;
*/
-- Test copy tuple routing
create table ctrtest (a int, b text) partition by list (a);
\! env PGOPTIONS='' psql -p ${PG_PORT} contrib_regression -c 'create table loct1 (a int check (a in (1)), b text);'
-- create table loct1 (a int check (a in (1)), b text);
create foreign table remp1 (a int check (a in (1)), b text) server pgserver options (table_name 'loct1');
\! env PGOPTIONS='' psql -p ${PG_PORT} contrib_regression -c 'create table loct2 (a int check (a in (2)), b text);'
-- create table loct2 (a int check (a in (2)), b text);
create foreign table remp2 (b text, a int check (a in (2))) server pgserver options (table_name 'loct2');
alter table ctrtest attach partition remp1 for values in (1);
alter table ctrtest attach partition remp2 for values in (2);
copy ctrtest from stdin;
1 foo
2 qux
\.
select tableoid::regclass, * FROM ctrtest;
select tableoid::regclass, * FROM remp1;
select tableoid::regclass, * FROM remp2;
-- Copying into foreign partitions directly should work as well
copy remp1 from stdin;
1 bar
\.
select tableoid::regclass, * FROM remp1;
drop table ctrtest;
\! env PGOPTIONS='' psql -p ${PG_PORT} contrib_regression -c 'drop table loct1;'
\! env PGOPTIONS='' psql -p ${PG_PORT} contrib_regression -c 'drop table loct2;'
-- drop table loct1;
-- drop table loct2;
-- ===================================================================
-- test COPY FROM
-- ===================================================================
\! env PGOPTIONS='' psql -p ${PG_PORT} contrib_regression -c 'create table loc2 (f1 int, f2 text);'
\! env PGOPTIONS='' psql -p ${PG_PORT} contrib_regression -c 'alter table loc2 set (autovacuum_enabled = 'false');'
-- create table loc2 (f1 int, f2 text);
-- alter table loc2 set (autovacuum_enabled = 'false');
create foreign table rem2 (f1 int, f2 text) server pgserver options(table_name 'loc2');
-- Test basic functionality
copy rem2 from stdin;
1 foo
2 bar
\.
select * from rem2;
delete from rem2;
-- Test check constraints
\! env PGOPTIONS='' psql -p ${PG_PORT} contrib_regression -c 'alter table loc2 add constraint loc2_f1positive check (f1 >= 0);'
-- alter table loc2 add constraint loc2_f1positive check (f1 >= 0);
alter foreign table rem2 add constraint rem2_f1positive check (f1 >= 0);
-- check constraint is enforced on the remote side, not locally
copy rem2 from stdin;
1 foo
2 bar
\.
copy rem2 from stdin; -- ERROR
-1 xyzzy
\.
select * from rem2;
alter foreign table rem2 drop constraint rem2_f1positive;
\! env PGOPTIONS='' psql -p ${PG_PORT} contrib_regression -c 'alter table loc2 drop constraint loc2_f1positive;'
-- alter table loc2 drop constraint loc2_f1positive;
delete from rem2;
-- In greenplum, Triggers for statements are not yet supported.
-- So SQLs below are disable here.
-- Test local triggers
/*
create trigger trig_stmt_before before insert on rem2
for each statement execute procedure trigger_func();
create trigger trig_stmt_after after insert on rem2
for each statement execute procedure trigger_func();
*/
create trigger trig_row_before before insert on rem2
for each row execute procedure trigger_data(23,'skidoo');
create trigger trig_row_after after insert on rem2
for each row execute procedure trigger_data(23,'skidoo');
copy rem2 from stdin;
1 foo
2 bar
\.
select * from rem2;
drop trigger trig_row_before on rem2;
drop trigger trig_row_after on rem2;
/*
drop trigger trig_stmt_before on rem2;
drop trigger trig_stmt_after on rem2;
*/
delete from rem2;
create trigger trig_row_before_insert before insert on rem2
for each row execute procedure trig_row_before_insupdate();
-- The new values are concatenated with ' triggered !'
copy rem2 from stdin;
1 foo
2 bar
\.
select * from rem2;
drop trigger trig_row_before_insert on rem2;
delete from rem2;
create trigger trig_null before insert on rem2
for each row execute procedure trig_null();
-- Nothing happens
copy rem2 from stdin;
1 foo
2 bar
\.
select * from rem2;
drop trigger trig_null on rem2;
delete from rem2;
-- Test remote triggers
\! env PGOPTIONS='' psql -p ${PG_PORT} contrib_regression -c 'create trigger trig_row_before_insert before insert on loc2 for each row execute procedure trig_row_before_insupdate();'
-- create trigger trig_row_before_insert before insert on loc2
-- for each row execute procedure trig_row_before_insupdate();
-- The new values are concatenated with ' triggered !'
copy rem2 from stdin;
1 foo
2 bar
\.
select * from rem2;
\! env PGOPTIONS='' psql -p ${PG_PORT} contrib_regression -c 'drop trigger trig_row_before_insert on loc2;'
-- drop trigger trig_row_before_insert on loc2;
delete from rem2;
\! env PGOPTIONS='' psql -p ${PG_PORT} contrib_regression -c 'create trigger trig_null before insert on loc2 for each row execute procedure trig_null();'
-- create trigger trig_null before insert on loc2
-- for each row execute procedure trig_null();
-- Nothing happens
copy rem2 from stdin;
1 foo
2 bar
\.
select * from rem2;
\! env PGOPTIONS='' psql -p ${PG_PORT} contrib_regression -c 'drop trigger trig_null on loc2;'
-- drop trigger trig_null on loc2;
delete from rem2;
-- Test a combination of local and remote triggers
create trigger rem2_trig_row_before before insert on rem2
for each row execute procedure trigger_data(23,'skidoo');
create trigger rem2_trig_row_after after insert on rem2
for each row execute procedure trigger_data(23,'skidoo');
\! env PGOPTIONS='' psql -p ${PG_PORT} contrib_regression -c 'create trigger loc2_trig_row_before_insert before insert on loc2 for each row execute procedure trig_row_before_insupdate();'
-- create trigger loc2_trig_row_before_insert before insert on loc2
-- for each row execute procedure trig_row_before_insupdate();
copy rem2 from stdin;
1 foo
2 bar
\.
select * from rem2;
drop trigger rem2_trig_row_before on rem2;
drop trigger rem2_trig_row_after on rem2;
\! env PGOPTIONS='' psql -p ${PG_PORT} contrib_regression -c 'drop trigger loc2_trig_row_before_insert on loc2;'
-- drop trigger loc2_trig_row_before_insert on loc2;
delete from rem2;
-- test COPY FROM with foreign table created in the same transaction
\! env PGOPTIONS='' psql -p ${PG_PORT} contrib_regression -c 'create table loc3 (f1 int, f2 text);'
-- create table loc3 (f1 int, f2 text);
begin;
create foreign table rem3 (f1 int, f2 text)
server pgserver options(table_name 'loc3');
copy rem3 from stdin;
1 foo
2 bar
\.
commit;
select * from rem3;
drop foreign table rem3;
\! env PGOPTIONS='' psql -p ${PG_PORT} contrib_regression -c 'drop table loc3;'
-- drop table loc3;
-- ===================================================================
-- test IMPORT FOREIGN SCHEMA
-- ===================================================================
\! env PGOPTIONS='' psql -p ${PG_PORT} contrib_regression -c 'CREATE SCHEMA import_source;'
\! env PGOPTIONS='' psql -p ${PG_PORT} contrib_regression -c 'CREATE TABLE import_source.t1 (c1 int, c2 varchar NOT NULL);'
\! env PGOPTIONS='' psql -p ${PG_PORT} contrib_regression -c 'CREATE TABLE import_source.t2 (c1 int default 42, c2 varchar NULL, c3 text collate "POSIX");'
\! env PGOPTIONS='' psql -p ${PG_PORT} contrib_regression -c 'CREATE TYPE typ1 AS (m1 int, m2 varchar);'
\! env PGOPTIONS='' psql -p ${PG_PORT} contrib_regression -c 'CREATE TABLE import_source.t3 (c1 timestamptz default now(), c2 typ1);'
\! env PGOPTIONS='' psql -p ${PG_PORT} contrib_regression -c 'CREATE TABLE import_source."x 4" (c1 float8, "C 2" text, c3 varchar(42));'
\! env PGOPTIONS='' psql -p ${PG_PORT} contrib_regression -c 'CREATE TABLE import_source."x 5" (c1 float8);'
\! env PGOPTIONS='' psql -p ${PG_PORT} contrib_regression -c 'ALTER TABLE import_source."x 5" DROP COLUMN c1;'
\! env PGOPTIONS='' psql -p ${PG_PORT} contrib_regression -c 'CREATE TABLE import_source."x 6" (c1 int, c2 int generated always as (c1 * 2) stored);'
\! env PGOPTIONS='' psql -p ${PG_PORT} contrib_regression -c 'CREATE TABLE import_source.t4 (c1 int) PARTITION BY RANGE (c1);'
\! env PGOPTIONS='' psql -p ${PG_PORT} contrib_regression -c 'CREATE TABLE import_source.t4_part PARTITION OF import_source.t4 FOR VALUES FROM (1) TO (100);'
-- CREATE SCHEMA import_source;
-- CREATE TABLE import_source.t1 (c1 int, c2 varchar NOT NULL);
-- CREATE TABLE import_source.t2 (c1 int default 42, c2 varchar NULL, c3 text collate "POSIX");
-- CREATE TYPE typ1 AS (m1 int, m2 varchar);
-- CREATE TABLE import_source.t3 (c1 timestamptz default now(), c2 typ1);
-- CREATE TABLE import_source."x 4" (c1 float8, "C 2" text, c3 varchar(42));
-- CREATE TABLE import_source."x 5" (c1 float8);
-- ALTER TABLE import_source."x 5" DROP COLUMN c1;
-- CREATE TABLE import_source."x 6" (c1 int, c2 int generated always as (c1 * 2) stored);
-- CREATE TABLE import_source.t4 (c1 int) PARTITION BY RANGE (c1);
-- CREATE TABLE import_source.t4_part PARTITION OF import_source.t4
-- FOR VALUES FROM (1) TO (100);
CREATE TYPE typ1 AS (m1 int, m2 varchar);
CREATE SCHEMA import_dest1;
IMPORT FOREIGN SCHEMA import_source FROM SERVER pgserver INTO import_dest1;
\det+ import_dest1.*
\d import_dest1.*
-- Options
CREATE SCHEMA import_dest2;
IMPORT FOREIGN SCHEMA import_source FROM SERVER pgserver INTO import_dest2
OPTIONS (import_default 'true');
\det+ import_dest2.*
\d import_dest2.*
CREATE SCHEMA import_dest3;
IMPORT FOREIGN SCHEMA import_source FROM SERVER pgserver INTO import_dest3
OPTIONS (import_collate 'false', import_generated 'false', import_not_null 'false');
\det+ import_dest3.*
\d import_dest3.*
-- Check LIMIT TO and EXCEPT
CREATE SCHEMA import_dest4;
IMPORT FOREIGN SCHEMA import_source LIMIT TO (t1, nonesuch)
FROM SERVER pgserver INTO import_dest4;
\det+ import_dest4.*
IMPORT FOREIGN SCHEMA import_source EXCEPT (t1, "x 4", nonesuch)
FROM SERVER pgserver INTO import_dest4;
\det+ import_dest4.*
-- Assorted error cases
IMPORT FOREIGN SCHEMA import_source FROM SERVER pgserver INTO import_dest4;
IMPORT FOREIGN SCHEMA nonesuch FROM SERVER pgserver INTO import_dest4;
IMPORT FOREIGN SCHEMA nonesuch FROM SERVER pgserver INTO notthere;
IMPORT FOREIGN SCHEMA nonesuch FROM SERVER nowhere INTO notthere;
-- Check case of a type present only on the remote server.
-- We can fake this by dropping the type locally in our transaction.
\! env PGOPTIONS='' psql -p ${PG_PORT} contrib_regression -c 'CREATE TYPE "Colors" AS ENUM ($$red$$, $$green$$, $$blue$$);'
\! env PGOPTIONS='' psql -p ${PG_PORT} contrib_regression -c 'CREATE TABLE import_source.t5 (c1 int, c2 text collate "C", "Col" "Colors");'
CREATE TYPE "Colors" AS ENUM ('red', 'green', 'blue');
-- CREATE TABLE import_source.t5 (c1 int, c2 text collate "C", "Col" "Colors");
CREATE SCHEMA import_dest5;
BEGIN;
DROP TYPE "Colors" CASCADE;
IMPORT FOREIGN SCHEMA import_source LIMIT TO (t5)
FROM SERVER pgserver INTO import_dest5; -- ERROR
ROLLBACK;
BEGIN;
CREATE SERVER fetch101 FOREIGN DATA WRAPPER postgres_fdw OPTIONS( fetch_size '101' );
SELECT count(*)
FROM pg_foreign_server
WHERE srvname = 'fetch101'
AND srvoptions @> array['fetch_size=101'];
ALTER SERVER fetch101 OPTIONS( SET fetch_size '202' );
SELECT count(*)
FROM pg_foreign_server
WHERE srvname = 'fetch101'
AND srvoptions @> array['fetch_size=101'];
SELECT count(*)
FROM pg_foreign_server
WHERE srvname = 'fetch101'
AND srvoptions @> array['fetch_size=202'];
CREATE FOREIGN TABLE table30000 ( x int ) SERVER fetch101 OPTIONS ( fetch_size '30000' );
SELECT COUNT(*)
FROM pg_foreign_table
WHERE ftrelid = 'table30000'::regclass
AND ftoptions @> array['fetch_size=30000'];
ALTER FOREIGN TABLE table30000 OPTIONS ( SET fetch_size '60000');
SELECT COUNT(*)
FROM pg_foreign_table
WHERE ftrelid = 'table30000'::regclass
AND ftoptions @> array['fetch_size=30000'];
SELECT COUNT(*)
FROM pg_foreign_table
WHERE ftrelid = 'table30000'::regclass
AND ftoptions @> array['fetch_size=60000'];
ROLLBACK;
-- ===================================================================
-- test partitionwise joins
-- ===================================================================
SET enable_partitionwise_join=on;
\! env PGOPTIONS='' psql -p ${PG_PORT} contrib_regression -c 'CREATE TABLE fprt1 (a int, b int, c varchar) PARTITION BY RANGE(a);'
\! env PGOPTIONS='' psql -p ${PG_PORT} contrib_regression -c 'CREATE TABLE fprt1_p1 (LIKE fprt1);'
\! env PGOPTIONS='' psql -p ${PG_PORT} contrib_regression -c 'CREATE TABLE fprt1_p2 (LIKE fprt1);'
\! env PGOPTIONS='' psql -p ${PG_PORT} contrib_regression -c 'ALTER TABLE fprt1_p1 SET (autovacuum_enabled = 'false');'
\! env PGOPTIONS='' psql -p ${PG_PORT} contrib_regression -c 'ALTER TABLE fprt1_p2 SET (autovacuum_enabled = 'false');'
\! env PGOPTIONS='' psql -p ${PG_PORT} contrib_regression -c 'INSERT INTO fprt1_p1 SELECT i, i, to_char(i/50, $$FM0000$$) FROM generate_series(0, 249, 2) i;'
\! env PGOPTIONS='' psql -p ${PG_PORT} contrib_regression -c 'INSERT INTO fprt1_p2 SELECT i, i, to_char(i/50, $$FM0000$$) FROM generate_series(250, 499, 2) i;'
CREATE TABLE fprt1 (a int, b int, c varchar) PARTITION BY RANGE(a);
-- CREATE TABLE fprt1_p1 (LIKE fprt1);
-- CREATE TABLE fprt1_p2 (LIKE fprt1);
-- ALTER TABLE fprt1_p1 SET (autovacuum_enabled = 'false');
-- ALTER TABLE fprt1_p2 SET (autovacuum_enabled = 'false');
-- INSERT INTO fprt1_p1 SELECT i, i, to_char(i/50, 'FM0000') FROM generate_series(0, 249, 2) i;
-- INSERT INTO fprt1_p2 SELECT i, i, to_char(i/50, 'FM0000') FROM generate_series(250, 499, 2) i;
CREATE FOREIGN TABLE ftprt1_p1 PARTITION OF fprt1 FOR VALUES FROM (0) TO (250)
SERVER pgserver OPTIONS (table_name 'fprt1_p1', use_remote_estimate 'true');
CREATE FOREIGN TABLE ftprt1_p2 PARTITION OF fprt1 FOR VALUES FROM (250) TO (500)
SERVER pgserver OPTIONS (TABLE_NAME 'fprt1_p2');
\! env PGOPTIONS='' psql -p ${PG_PORT} contrib_regression -c 'ANALYZE fprt1;'
\! env PGOPTIONS='' psql -p ${PG_PORT} contrib_regression -c 'ANALYZE fprt1_p1;'
\! env PGOPTIONS='' psql -p ${PG_PORT} contrib_regression -c 'ANALYZE fprt1_p2;'
ANALYZE fprt1;
-- ANALYZE fprt1_p1;
-- ANALYZE fprt1_p2;
\! env PGOPTIONS='' psql -p ${PG_PORT} contrib_regression -c 'CREATE TABLE fprt2 (a int, b int, c varchar) PARTITION BY RANGE(b);'
\! env PGOPTIONS='' psql -p ${PG_PORT} contrib_regression -c 'CREATE TABLE fprt2_p1 (LIKE fprt2);'
\! env PGOPTIONS='' psql -p ${PG_PORT} contrib_regression -c 'CREATE TABLE fprt2_p2 (LIKE fprt2);'
\! env PGOPTIONS='' psql -p ${PG_PORT} contrib_regression -c 'ALTER TABLE fprt2_p1 SET (autovacuum_enabled = 'false');'
\! env PGOPTIONS='' psql -p ${PG_PORT} contrib_regression -c 'ALTER TABLE fprt2_p2 SET (autovacuum_enabled = 'false');'
\! env PGOPTIONS='' psql -p ${PG_PORT} contrib_regression -c 'INSERT INTO fprt2_p1 SELECT i, i, to_char(i/50, $$FM0000$$) FROM generate_series(0, 249, 3) i;'
\! env PGOPTIONS='' psql -p ${PG_PORT} contrib_regression -c 'INSERT INTO fprt2_p2 SELECT i, i, to_char(i/50, $$FM0000$$) FROM generate_series(250, 499, 3) i;'
CREATE TABLE fprt2 (a int, b int, c varchar) PARTITION BY RANGE(b);
-- CREATE TABLE fprt2_p1 (LIKE fprt2);
-- CREATE TABLE fprt2_p2 (LIKE fprt2);
-- ALTER TABLE fprt2_p1 SET (autovacuum_enabled = 'false');
-- ALTER TABLE fprt2_p2 SET (autovacuum_enabled = 'false');
-- INSERT INTO fprt2_p1 SELECT i, i, to_char(i/50, 'FM0000') FROM generate_series(0, 249, 3) i;
-- INSERT INTO fprt2_p2 SELECT i, i, to_char(i/50, 'FM0000') FROM generate_series(250, 499, 3) i;
CREATE FOREIGN TABLE ftprt2_p1 (b int, c varchar, a int)
SERVER pgserver OPTIONS (table_name 'fprt2_p1', use_remote_estimate 'true');
ALTER TABLE fprt2 ATTACH PARTITION ftprt2_p1 FOR VALUES FROM (0) TO (250);
CREATE FOREIGN TABLE ftprt2_p2 PARTITION OF fprt2 FOR VALUES FROM (250) TO (500)
SERVER pgserver OPTIONS (table_name 'fprt2_p2', use_remote_estimate 'true');
\! env PGOPTIONS='' psql -p ${PG_PORT} contrib_regression -c 'ANALYZE fprt2;'
\! env PGOPTIONS='' psql -p ${PG_PORT} contrib_regression -c 'ANALYZE fprt2_p1;'
\! env PGOPTIONS='' psql -p ${PG_PORT} contrib_regression -c 'ANALYZE fprt2_p2;'
ANALYZE fprt2;
-- ANALYZE fprt2_p1;
-- ANALYZE fprt2_p2;
-- inner join three tables
EXPLAIN (COSTS OFF)
SELECT t1.a,t2.b,t3.c FROM fprt1 t1 INNER JOIN fprt2 t2 ON (t1.a = t2.b) INNER JOIN fprt1 t3 ON (t2.b = t3.a) WHERE t1.a % 25 =0 ORDER BY 1,2,3;
SELECT t1.a,t2.b,t3.c FROM fprt1 t1 INNER JOIN fprt2 t2 ON (t1.a = t2.b) INNER JOIN fprt1 t3 ON (t2.b = t3.a) WHERE t1.a % 25 =0 ORDER BY 1,2,3;
-- left outer join + nullable clause
EXPLAIN (VERBOSE, COSTS OFF)
SELECT t1.a,t2.b,t2.c FROM fprt1 t1 LEFT JOIN (SELECT * FROM fprt2 WHERE a < 10) t2 ON (t1.a = t2.b and t1.b = t2.a) WHERE t1.a < 10 ORDER BY 1,2,3;
SELECT t1.a,t2.b,t2.c FROM fprt1 t1 LEFT JOIN (SELECT * FROM fprt2 WHERE a < 10) t2 ON (t1.a = t2.b and t1.b = t2.a) WHERE t1.a < 10 ORDER BY 1,2,3;
-- with whole-row reference; partitionwise join does not apply
EXPLAIN (COSTS OFF)
SELECT t1.wr, t2.wr FROM (SELECT t1 wr, a FROM fprt1 t1 WHERE t1.a % 25 = 0) t1 FULL JOIN (SELECT t2 wr, b FROM fprt2 t2 WHERE t2.b % 25 = 0) t2 ON (t1.a = t2.b) ORDER BY 1,2;
SELECT t1.wr, t2.wr FROM (SELECT t1 wr, a FROM fprt1 t1 WHERE t1.a % 25 = 0) t1 FULL JOIN (SELECT t2 wr, b FROM fprt2 t2 WHERE t2.b % 25 = 0) t2 ON (t1.a = t2.b) ORDER BY 1,2;
-- join with lateral reference
EXPLAIN (COSTS OFF)
SELECT t1.a,t1.b FROM fprt1 t1, LATERAL (SELECT t2.a, t2.b FROM fprt2 t2 WHERE t1.a = t2.b AND t1.b = t2.a) q WHERE t1.a%25 = 0 ORDER BY 1,2;
SELECT t1.a,t1.b FROM fprt1 t1, LATERAL (SELECT t2.a, t2.b FROM fprt2 t2 WHERE t1.a = t2.b AND t1.b = t2.a) q WHERE t1.a%25 = 0 ORDER BY 1,2;
-- with PHVs, partitionwise join selected but no join pushdown
EXPLAIN (COSTS OFF)
SELECT t1.a, t1.phv, t2.b, t2.phv FROM (SELECT 't1_phv' phv, * FROM fprt1 WHERE a % 25 = 0) t1 FULL JOIN (SELECT 't2_phv' phv, * FROM fprt2 WHERE b % 25 = 0) t2 ON (t1.a = t2.b) ORDER BY t1.a, t2.b;
SELECT t1.a, t1.phv, t2.b, t2.phv FROM (SELECT 't1_phv' phv, * FROM fprt1 WHERE a % 25 = 0) t1 FULL JOIN (SELECT 't2_phv' phv, * FROM fprt2 WHERE b % 25 = 0) t2 ON (t1.a = t2.b) ORDER BY t1.a, t2.b;
-- test FOR UPDATE; partitionwise join does not apply
EXPLAIN (COSTS OFF)
SELECT t1.a, t2.b FROM fprt1 t1 INNER JOIN fprt2 t2 ON (t1.a = t2.b) WHERE t1.a % 25 = 0 ORDER BY 1,2 FOR UPDATE OF t1;
SELECT t1.a, t2.b FROM fprt1 t1 INNER JOIN fprt2 t2 ON (t1.a = t2.b) WHERE t1.a % 25 = 0 ORDER BY 1,2 FOR UPDATE OF t1;
RESET enable_partitionwise_join;
-- ===================================================================
-- test partitionwise aggregates
-- ===================================================================
CREATE TABLE pagg_tab (a int, b int, c text) PARTITION BY RANGE(a);
\! env PGOPTIONS='' psql -p ${PG_PORT} contrib_regression -c 'CREATE TABLE pagg_tab (a int, b int, c text) PARTITION BY RANGE(a);'
\! env PGOPTIONS='' psql -p ${PG_PORT} contrib_regression -c 'CREATE TABLE pagg_tab_p1 (LIKE pagg_tab);'
\! env PGOPTIONS='' psql -p ${PG_PORT} contrib_regression -c 'CREATE TABLE pagg_tab_p2 (LIKE pagg_tab);'
\! env PGOPTIONS='' psql -p ${PG_PORT} contrib_regression -c 'CREATE TABLE pagg_tab_p3 (LIKE pagg_tab);'
\! env PGOPTIONS='' psql -p ${PG_PORT} contrib_regression -c 'INSERT INTO pagg_tab_p1 SELECT i % 30, i % 50, to_char(i/30, $$FM0000$$) FROM generate_series(1, 3000) i WHERE (i % 30) < 10;'
\! env PGOPTIONS='' psql -p ${PG_PORT} contrib_regression -c 'INSERT INTO pagg_tab_p2 SELECT i % 30, i % 50, to_char(i/30, $$FM0000$$) FROM generate_series(1, 3000) i WHERE (i % 30) < 20 and (i % 30) >= 10;'
\! env PGOPTIONS='' psql -p ${PG_PORT} contrib_regression -c 'INSERT INTO pagg_tab_p3 SELECT i % 30, i % 50, to_char(i/30, $$FM0000$$) FROM generate_series(1, 3000) i WHERE (i % 30) < 30 and (i % 30) >= 20;'
-- CREATE TABLE pagg_tab_p1 (LIKE pagg_tab);
-- CREATE TABLE pagg_tab_p2 (LIKE pagg_tab);
-- CREATE TABLE pagg_tab_p3 (LIKE pagg_tab);
-- INSERT INTO pagg_tab_p1 SELECT i % 30, i % 50, to_char(i/30, 'FM0000') FROM generate_series(1, 3000) i WHERE (i % 30) < 10;
-- INSERT INTO pagg_tab_p2 SELECT i % 30, i % 50, to_char(i/30, 'FM0000') FROM generate_series(1, 3000) i WHERE (i % 30) < 20 and (i % 30) >= 10;
-- INSERT INTO pagg_tab_p3 SELECT i % 30, i % 50, to_char(i/30, 'FM0000') FROM generate_series(1, 3000) i WHERE (i % 30) < 30 and (i % 30) >= 20;
-- Create foreign partitions
CREATE FOREIGN TABLE fpagg_tab_p1 PARTITION OF pagg_tab FOR VALUES FROM (0) TO (10) SERVER pgserver OPTIONS (table_name 'pagg_tab_p1');
CREATE FOREIGN TABLE fpagg_tab_p2 PARTITION OF pagg_tab FOR VALUES FROM (10) TO (20) SERVER pgserver OPTIONS (table_name 'pagg_tab_p2');;
CREATE FOREIGN TABLE fpagg_tab_p3 PARTITION OF pagg_tab FOR VALUES FROM (20) TO (30) SERVER pgserver OPTIONS (table_name 'pagg_tab_p3');;
ANALYZE pagg_tab;
ANALYZE fpagg_tab_p1;
ANALYZE fpagg_tab_p2;
ANALYZE fpagg_tab_p3;
-- When GROUP BY clause matches with PARTITION KEY.
-- Plan with partitionwise aggregates is disabled
SET enable_partitionwise_aggregate TO false;
EXPLAIN (COSTS OFF)
SELECT a, sum(b), min(b), count(*) FROM pagg_tab GROUP BY a HAVING avg(b) < 22 ORDER BY 1;
-- Plan with partitionwise aggregates is enabled
SET enable_partitionwise_aggregate TO true;
EXPLAIN (COSTS OFF)
SELECT a, sum(b), min(b), count(*) FROM pagg_tab GROUP BY a HAVING avg(b) < 22 ORDER BY 1;
SELECT a, sum(b), min(b), count(*) FROM pagg_tab GROUP BY a HAVING avg(b) < 22 ORDER BY 1;
-- Check with whole-row reference
-- Should have all the columns in the target list for the given relation
EXPLAIN (VERBOSE, COSTS OFF)
SELECT a, count(t1) FROM pagg_tab t1 GROUP BY a HAVING avg(b) < 22 ORDER BY 1;
SELECT a, count(t1) FROM pagg_tab t1 GROUP BY a HAVING avg(b) < 22 ORDER BY 1;
-- When GROUP BY clause does not match with PARTITION KEY.
EXPLAIN (COSTS OFF)
SELECT b, avg(a), max(a), count(*) FROM pagg_tab GROUP BY b HAVING sum(a) < 700 ORDER BY 1;
SELECT b, avg(a), max(a), count(*) FROM pagg_tab GROUP BY b HAVING sum(a) < 700 ORDER BY 1;
-- Clean-up
RESET enable_partitionwise_aggregate;
-- ===================================================================
-- test connection invalidation cases
-- ===================================================================
-- This test case is for closing the connection in pgfdw_xact_callback
BEGIN;
-- Connection xact depth becomes 1 i.e. the connection is in midst of the xact.
SELECT 1 FROM ft1 LIMIT 1;
-- Connection is not closed at the end of the alter statement in
-- pgfdw_inval_callback. That's because the connection is in midst of this
-- xact, it is just marked as invalid.
ALTER SERVER pgserver OPTIONS (ADD use_remote_estimate 'off');
-- The invalid connection gets closed in pgfdw_xact_callback during commit.
COMMIT;