diff -U3 /tmp/cirrus-ci-build/contrib/postgres_fdw/expected/postgres_fdw.out /tmp/cirrus-ci-build/build/testrun/postgres_fdw-running/regress/results/postgres_fdw.out --- /tmp/cirrus-ci-build/contrib/postgres_fdw/expected/postgres_fdw.out 2024-04-04 07:35:40.176836000 +0000 +++ /tmp/cirrus-ci-build/build/testrun/postgres_fdw-running/regress/results/postgres_fdw.out 2024-04-04 07:39:10.745690000 +0000 @@ -6236,5996 +6236,7 @@ -- Test UPDATE/DELETE with WHERE or JOIN/ON conditions containing -- user-defined operators/functions ALTER SERVER loopback 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 - QUERY PLAN ----------------------------------------------------------------------------------------------------------- - Update on public.ft2 - Output: c1, c2, c3, c4, c5, c6, c7, c8 - Remote SQL: UPDATE "S 1"."T 1" SET c3 = $2 WHERE ctid = $1 RETURNING "C 1", c2, c3, c4, c5, c6, c7, c8 - -> Foreign Scan on public.ft2 - Output: 'bar'::text, ctid, ft2.* - Filter: (postgres_fdw_abs(ft2.c1) > 2000) - Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8, ctid FROM "S 1"."T 1" FOR UPDATE -(7 rows) - -UPDATE ft2 SET c3 = 'bar' WHERE postgres_fdw_abs(c1) > 2000 RETURNING *; - c1 | c2 | c3 | c4 | c5 | c6 | c7 | c8 -------+----+-----+----+----+----+------------+---- - 2001 | 1 | bar | | | | ft2 | - 2002 | 2 | bar | | | | ft2 | - 2003 | 3 | bar | | | | ft2 | - 2004 | 4 | bar | | | | ft2 | - 2005 | 5 | bar | | | | ft2 | - 2006 | 6 | bar | | | | ft2 | - 2007 | 7 | bar | | | | ft2 | - 2008 | 8 | bar | | | | ft2 | - 2009 | 9 | bar | | | | ft2 | - 2010 | 0 | bar | | | | ft2 | -(10 rows) - -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 - QUERY PLAN ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- - Update on public.ft2 - Output: ft2.c1, ft2.c2, ft2.c3, ft2.c4, ft2.c5, ft2.c6, ft2.c7, ft2.c8, ft4.c1, ft4.c2, ft4.c3, ft5.c1, ft5.c2, ft5.c3 - Remote SQL: UPDATE "S 1"."T 1" SET c3 = $2 WHERE ctid = $1 RETURNING "C 1", c2, c3, c4, c5, c6, c7, c8 - -> Nested Loop - Output: 'baz'::text, ft2.ctid, ft2.*, ft4.*, ft5.*, ft4.c1, ft4.c2, ft4.c3, ft5.c1, ft5.c2, ft5.c3 - Join Filter: (ft2.c2 === ft4.c1) - -> Foreign Scan on public.ft2 - Output: ft2.ctid, ft2.*, ft2.c2 - Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8, ctid FROM "S 1"."T 1" WHERE (("C 1" > 2000)) FOR UPDATE - -> Foreign Scan - Output: ft4.*, ft4.c1, ft4.c2, ft4.c3, ft5.*, ft5.c1, ft5.c2, ft5.c3 - Relations: (public.ft4) INNER JOIN (public.ft5) - Remote SQL: SELECT CASE WHEN (r2.*)::text IS NOT NULL THEN ROW(r2.c1, r2.c2, r2.c3) END, r2.c1, r2.c2, r2.c3, CASE WHEN (r3.*)::text IS NOT NULL THEN ROW(r3.c1, r3.c2, r3.c3) END, r3.c1, r3.c2, r3.c3 FROM ("S 1"."T 3" r2 INNER JOIN "S 1"."T 4" r3 ON (((r2.c1 = r3.c1)))) - -> Hash Join - Output: ft4.*, ft4.c1, ft4.c2, ft4.c3, ft5.*, ft5.c1, ft5.c2, ft5.c3 - Hash Cond: (ft4.c1 = ft5.c1) - -> Foreign Scan on public.ft4 - Output: ft4.*, ft4.c1, ft4.c2, ft4.c3 - Remote SQL: SELECT c1, c2, c3 FROM "S 1"."T 3" - -> Hash - Output: ft5.*, ft5.c1, ft5.c2, ft5.c3 - -> Foreign Scan on public.ft5 - Output: ft5.*, ft5.c1, ft5.c2, ft5.c3 - Remote SQL: SELECT c1, c2, c3 FROM "S 1"."T 4" -(24 rows) - -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.*; - c1 | c2 | c3 | c4 | c5 | c6 | c7 | c8 | c1 | c2 | c3 | c1 | c2 | c3 -------+----+-----+----+----+----+------------+----+----+----+--------+----+----+-------- - 2006 | 6 | baz | | | | ft2 | | 6 | 7 | AAA006 | 6 | 7 | AAA006 -(1 row) - -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 - QUERY PLAN ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- - Delete on public.ft2 - Output: ft2.c1, ft2.c2, ft2.c3 - Remote SQL: DELETE FROM "S 1"."T 1" WHERE ctid = $1 RETURNING "C 1", c2, c3 - -> Foreign Scan - Output: ft2.ctid, ft4.*, ft5.* - Filter: (ft4.c1 === ft5.c1) - Relations: ((public.ft2) INNER JOIN (public.ft4)) INNER JOIN (public.ft5) - Remote SQL: SELECT r1.ctid, CASE WHEN (r2.*)::text IS NOT NULL THEN ROW(r2.c1, r2.c2, r2.c3) END, CASE WHEN (r3.*)::text IS NOT NULL THEN ROW(r3.c1, r3.c2, r3.c3) END, r2.c1, r3.c1 FROM (("S 1"."T 1" r1 INNER JOIN "S 1"."T 3" r2 ON (((r1.c2 = r2.c1)) AND ((r1."C 1" > 2000)))) INNER JOIN "S 1"."T 4" r3 ON (TRUE)) FOR UPDATE OF r1 - -> Nested Loop - Output: ft2.ctid, ft4.*, ft5.*, ft4.c1, ft5.c1 - -> Nested Loop - Output: ft2.ctid, ft4.*, ft4.c1 - Join Filter: (ft2.c2 = ft4.c1) - -> Foreign Scan on public.ft2 - Output: ft2.ctid, ft2.c2 - Remote SQL: SELECT c2, ctid FROM "S 1"."T 1" WHERE (("C 1" > 2000)) FOR UPDATE - -> Foreign Scan on public.ft4 - Output: ft4.*, ft4.c1 - Remote SQL: SELECT c1, c2, c3 FROM "S 1"."T 3" - -> Foreign Scan on public.ft5 - Output: ft5.*, ft5.c1 - Remote SQL: SELECT c1, c2, c3 FROM "S 1"."T 4" -(22 rows) - -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; - c1 | c2 | c3 -------+----+----- - 2006 | 6 | baz -(1 row) - -DELETE FROM ft2 WHERE ft2.c1 > 2000; -ALTER SERVER loopback 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; -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 *; - c1 | c2 | c3 | c4 | c5 | c6 | c7 | c8 -------+-----+-----------------+----+----+----+------------+---- - 1208 | 818 | fff_trig_update | | | | ft2 | -(1 row) - -INSERT INTO ft2 (c1,c2,c3,c6) VALUES (1218, 818, 'ggg', '(--;') RETURNING *; - c1 | c2 | c3 | c4 | c5 | c6 | c7 | c8 -------+-----+-----------------+----+----+------+------------+---- - 1218 | 818 | ggg_trig_update | | | (--; | ft2 | -(1 row) - -UPDATE ft2 SET c2 = c2 + 600 WHERE c1 % 10 = 8 AND c1 < 1200 RETURNING *; - c1 | c2 | c3 | c4 | c5 | c6 | c7 | c8 -------+-----+------------------------+------------------------------+--------------------------+----+------------+----- - 8 | 608 | 00008_trig_update | Fri Jan 09 00:00:00 1970 PST | Fri Jan 09 00:00:00 1970 | 8 | 8 | foo - 18 | 608 | 00018_trig_update | Mon Jan 19 00:00:00 1970 PST | Mon Jan 19 00:00:00 1970 | 8 | 8 | foo - 28 | 608 | 00028_trig_update | Thu Jan 29 00:00:00 1970 PST | Thu Jan 29 00:00:00 1970 | 8 | 8 | foo - 38 | 608 | 00038_trig_update | Sun Feb 08 00:00:00 1970 PST | Sun Feb 08 00:00:00 1970 | 8 | 8 | foo - 48 | 608 | 00048_trig_update | Wed Feb 18 00:00:00 1970 PST | Wed Feb 18 00:00:00 1970 | 8 | 8 | foo - 58 | 608 | 00058_trig_update | Sat Feb 28 00:00:00 1970 PST | Sat Feb 28 00:00:00 1970 | 8 | 8 | foo - 68 | 608 | 00068_trig_update | Tue Mar 10 00:00:00 1970 PST | Tue Mar 10 00:00:00 1970 | 8 | 8 | foo - 78 | 608 | 00078_trig_update | Fri Mar 20 00:00:00 1970 PST | Fri Mar 20 00:00:00 1970 | 8 | 8 | foo - 88 | 608 | 00088_trig_update | Mon Mar 30 00:00:00 1970 PST | Mon Mar 30 00:00:00 1970 | 8 | 8 | foo - 98 | 608 | 00098_trig_update | Thu Apr 09 00:00:00 1970 PST | Thu Apr 09 00:00:00 1970 | 8 | 8 | foo - 108 | 608 | 00108_trig_update | Fri Jan 09 00:00:00 1970 PST | Fri Jan 09 00:00:00 1970 | 8 | 8 | foo - 118 | 608 | 00118_trig_update | Mon Jan 19 00:00:00 1970 PST | Mon Jan 19 00:00:00 1970 | 8 | 8 | foo - 128 | 608 | 00128_trig_update | Thu Jan 29 00:00:00 1970 PST | Thu Jan 29 00:00:00 1970 | 8 | 8 | foo - 138 | 608 | 00138_trig_update | Sun Feb 08 00:00:00 1970 PST | Sun Feb 08 00:00:00 1970 | 8 | 8 | foo - 148 | 608 | 00148_trig_update | Wed Feb 18 00:00:00 1970 PST | Wed Feb 18 00:00:00 1970 | 8 | 8 | foo - 158 | 608 | 00158_trig_update | Sat Feb 28 00:00:00 1970 PST | Sat Feb 28 00:00:00 1970 | 8 | 8 | foo - 168 | 608 | 00168_trig_update | Tue Mar 10 00:00:00 1970 PST | Tue Mar 10 00:00:00 1970 | 8 | 8 | foo - 178 | 608 | 00178_trig_update | Fri Mar 20 00:00:00 1970 PST | Fri Mar 20 00:00:00 1970 | 8 | 8 | foo - 188 | 608 | 00188_trig_update | Mon Mar 30 00:00:00 1970 PST | Mon Mar 30 00:00:00 1970 | 8 | 8 | foo - 198 | 608 | 00198_trig_update | Thu Apr 09 00:00:00 1970 PST | Thu Apr 09 00:00:00 1970 | 8 | 8 | foo - 208 | 608 | 00208_trig_update | Fri Jan 09 00:00:00 1970 PST | Fri Jan 09 00:00:00 1970 | 8 | 8 | foo - 218 | 608 | 00218_trig_update | Mon Jan 19 00:00:00 1970 PST | Mon Jan 19 00:00:00 1970 | 8 | 8 | foo - 228 | 608 | 00228_trig_update | Thu Jan 29 00:00:00 1970 PST | Thu Jan 29 00:00:00 1970 | 8 | 8 | foo - 238 | 608 | 00238_trig_update | Sun Feb 08 00:00:00 1970 PST | Sun Feb 08 00:00:00 1970 | 8 | 8 | foo - 248 | 608 | 00248_trig_update | Wed Feb 18 00:00:00 1970 PST | Wed Feb 18 00:00:00 1970 | 8 | 8 | foo - 258 | 608 | 00258_trig_update | Sat Feb 28 00:00:00 1970 PST | Sat Feb 28 00:00:00 1970 | 8 | 8 | foo - 268 | 608 | 00268_trig_update | Tue Mar 10 00:00:00 1970 PST | Tue Mar 10 00:00:00 1970 | 8 | 8 | foo - 278 | 608 | 00278_trig_update | Fri Mar 20 00:00:00 1970 PST | Fri Mar 20 00:00:00 1970 | 8 | 8 | foo - 288 | 608 | 00288_trig_update | Mon Mar 30 00:00:00 1970 PST | Mon Mar 30 00:00:00 1970 | 8 | 8 | foo - 298 | 608 | 00298_trig_update | Thu Apr 09 00:00:00 1970 PST | Thu Apr 09 00:00:00 1970 | 8 | 8 | foo - 308 | 608 | 00308_trig_update | Fri Jan 09 00:00:00 1970 PST | Fri Jan 09 00:00:00 1970 | 8 | 8 | foo - 318 | 608 | 00318_trig_update | Mon Jan 19 00:00:00 1970 PST | Mon Jan 19 00:00:00 1970 | 8 | 8 | foo - 328 | 608 | 00328_trig_update | Thu Jan 29 00:00:00 1970 PST | Thu Jan 29 00:00:00 1970 | 8 | 8 | foo - 338 | 608 | 00338_trig_update | Sun Feb 08 00:00:00 1970 PST | Sun Feb 08 00:00:00 1970 | 8 | 8 | foo - 348 | 608 | 00348_trig_update | Wed Feb 18 00:00:00 1970 PST | Wed Feb 18 00:00:00 1970 | 8 | 8 | foo - 358 | 608 | 00358_trig_update | Sat Feb 28 00:00:00 1970 PST | Sat Feb 28 00:00:00 1970 | 8 | 8 | foo - 368 | 608 | 00368_trig_update | Tue Mar 10 00:00:00 1970 PST | Tue Mar 10 00:00:00 1970 | 8 | 8 | foo - 378 | 608 | 00378_trig_update | Fri Mar 20 00:00:00 1970 PST | Fri Mar 20 00:00:00 1970 | 8 | 8 | foo - 388 | 608 | 00388_trig_update | Mon Mar 30 00:00:00 1970 PST | Mon Mar 30 00:00:00 1970 | 8 | 8 | foo - 398 | 608 | 00398_trig_update | Thu Apr 09 00:00:00 1970 PST | Thu Apr 09 00:00:00 1970 | 8 | 8 | foo - 408 | 608 | 00408_trig_update | Fri Jan 09 00:00:00 1970 PST | Fri Jan 09 00:00:00 1970 | 8 | 8 | foo - 418 | 608 | 00418_trig_update | Mon Jan 19 00:00:00 1970 PST | Mon Jan 19 00:00:00 1970 | 8 | 8 | foo - 428 | 608 | 00428_trig_update | Thu Jan 29 00:00:00 1970 PST | Thu Jan 29 00:00:00 1970 | 8 | 8 | foo - 438 | 608 | 00438_trig_update | Sun Feb 08 00:00:00 1970 PST | Sun Feb 08 00:00:00 1970 | 8 | 8 | foo - 448 | 608 | 00448_trig_update | Wed Feb 18 00:00:00 1970 PST | Wed Feb 18 00:00:00 1970 | 8 | 8 | foo - 458 | 608 | 00458_trig_update | Sat Feb 28 00:00:00 1970 PST | Sat Feb 28 00:00:00 1970 | 8 | 8 | foo - 468 | 608 | 00468_trig_update | Tue Mar 10 00:00:00 1970 PST | Tue Mar 10 00:00:00 1970 | 8 | 8 | foo - 478 | 608 | 00478_trig_update | Fri Mar 20 00:00:00 1970 PST | Fri Mar 20 00:00:00 1970 | 8 | 8 | foo - 488 | 608 | 00488_trig_update | Mon Mar 30 00:00:00 1970 PST | Mon Mar 30 00:00:00 1970 | 8 | 8 | foo - 498 | 608 | 00498_trig_update | Thu Apr 09 00:00:00 1970 PST | Thu Apr 09 00:00:00 1970 | 8 | 8 | foo - 508 | 608 | 00508_trig_update | Fri Jan 09 00:00:00 1970 PST | Fri Jan 09 00:00:00 1970 | 8 | 8 | foo - 518 | 608 | 00518_trig_update | Mon Jan 19 00:00:00 1970 PST | Mon Jan 19 00:00:00 1970 | 8 | 8 | foo - 528 | 608 | 00528_trig_update | Thu Jan 29 00:00:00 1970 PST | Thu Jan 29 00:00:00 1970 | 8 | 8 | foo - 538 | 608 | 00538_trig_update | Sun Feb 08 00:00:00 1970 PST | Sun Feb 08 00:00:00 1970 | 8 | 8 | foo - 548 | 608 | 00548_trig_update | Wed Feb 18 00:00:00 1970 PST | Wed Feb 18 00:00:00 1970 | 8 | 8 | foo - 558 | 608 | 00558_trig_update | Sat Feb 28 00:00:00 1970 PST | Sat Feb 28 00:00:00 1970 | 8 | 8 | foo - 568 | 608 | 00568_trig_update | Tue Mar 10 00:00:00 1970 PST | Tue Mar 10 00:00:00 1970 | 8 | 8 | foo - 578 | 608 | 00578_trig_update | Fri Mar 20 00:00:00 1970 PST | Fri Mar 20 00:00:00 1970 | 8 | 8 | foo - 588 | 608 | 00588_trig_update | Mon Mar 30 00:00:00 1970 PST | Mon Mar 30 00:00:00 1970 | 8 | 8 | foo - 598 | 608 | 00598_trig_update | Thu Apr 09 00:00:00 1970 PST | Thu Apr 09 00:00:00 1970 | 8 | 8 | foo - 608 | 608 | 00608_trig_update | Fri Jan 09 00:00:00 1970 PST | Fri Jan 09 00:00:00 1970 | 8 | 8 | foo - 618 | 608 | 00618_trig_update | Mon Jan 19 00:00:00 1970 PST | Mon Jan 19 00:00:00 1970 | 8 | 8 | foo - 628 | 608 | 00628_trig_update | Thu Jan 29 00:00:00 1970 PST | Thu Jan 29 00:00:00 1970 | 8 | 8 | foo - 638 | 608 | 00638_trig_update | Sun Feb 08 00:00:00 1970 PST | Sun Feb 08 00:00:00 1970 | 8 | 8 | foo - 648 | 608 | 00648_trig_update | Wed Feb 18 00:00:00 1970 PST | Wed Feb 18 00:00:00 1970 | 8 | 8 | foo - 658 | 608 | 00658_trig_update | Sat Feb 28 00:00:00 1970 PST | Sat Feb 28 00:00:00 1970 | 8 | 8 | foo - 668 | 608 | 00668_trig_update | Tue Mar 10 00:00:00 1970 PST | Tue Mar 10 00:00:00 1970 | 8 | 8 | foo - 678 | 608 | 00678_trig_update | Fri Mar 20 00:00:00 1970 PST | Fri Mar 20 00:00:00 1970 | 8 | 8 | foo - 688 | 608 | 00688_trig_update | Mon Mar 30 00:00:00 1970 PST | Mon Mar 30 00:00:00 1970 | 8 | 8 | foo - 698 | 608 | 00698_trig_update | Thu Apr 09 00:00:00 1970 PST | Thu Apr 09 00:00:00 1970 | 8 | 8 | foo - 708 | 608 | 00708_trig_update | Fri Jan 09 00:00:00 1970 PST | Fri Jan 09 00:00:00 1970 | 8 | 8 | foo - 718 | 608 | 00718_trig_update | Mon Jan 19 00:00:00 1970 PST | Mon Jan 19 00:00:00 1970 | 8 | 8 | foo - 728 | 608 | 00728_trig_update | Thu Jan 29 00:00:00 1970 PST | Thu Jan 29 00:00:00 1970 | 8 | 8 | foo - 738 | 608 | 00738_trig_update | Sun Feb 08 00:00:00 1970 PST | Sun Feb 08 00:00:00 1970 | 8 | 8 | foo - 748 | 608 | 00748_trig_update | Wed Feb 18 00:00:00 1970 PST | Wed Feb 18 00:00:00 1970 | 8 | 8 | foo - 758 | 608 | 00758_trig_update | Sat Feb 28 00:00:00 1970 PST | Sat Feb 28 00:00:00 1970 | 8 | 8 | foo - 768 | 608 | 00768_trig_update | Tue Mar 10 00:00:00 1970 PST | Tue Mar 10 00:00:00 1970 | 8 | 8 | foo - 778 | 608 | 00778_trig_update | Fri Mar 20 00:00:00 1970 PST | Fri Mar 20 00:00:00 1970 | 8 | 8 | foo - 788 | 608 | 00788_trig_update | Mon Mar 30 00:00:00 1970 PST | Mon Mar 30 00:00:00 1970 | 8 | 8 | foo - 798 | 608 | 00798_trig_update | Thu Apr 09 00:00:00 1970 PST | Thu Apr 09 00:00:00 1970 | 8 | 8 | foo - 808 | 608 | 00808_trig_update | Fri Jan 09 00:00:00 1970 PST | Fri Jan 09 00:00:00 1970 | 8 | 8 | foo - 818 | 608 | 00818_trig_update | Mon Jan 19 00:00:00 1970 PST | Mon Jan 19 00:00:00 1970 | 8 | 8 | foo - 828 | 608 | 00828_trig_update | Thu Jan 29 00:00:00 1970 PST | Thu Jan 29 00:00:00 1970 | 8 | 8 | foo - 838 | 608 | 00838_trig_update | Sun Feb 08 00:00:00 1970 PST | Sun Feb 08 00:00:00 1970 | 8 | 8 | foo - 848 | 608 | 00848_trig_update | Wed Feb 18 00:00:00 1970 PST | Wed Feb 18 00:00:00 1970 | 8 | 8 | foo - 858 | 608 | 00858_trig_update | Sat Feb 28 00:00:00 1970 PST | Sat Feb 28 00:00:00 1970 | 8 | 8 | foo - 868 | 608 | 00868_trig_update | Tue Mar 10 00:00:00 1970 PST | Tue Mar 10 00:00:00 1970 | 8 | 8 | foo - 878 | 608 | 00878_trig_update | Fri Mar 20 00:00:00 1970 PST | Fri Mar 20 00:00:00 1970 | 8 | 8 | foo - 888 | 608 | 00888_trig_update | Mon Mar 30 00:00:00 1970 PST | Mon Mar 30 00:00:00 1970 | 8 | 8 | foo - 898 | 608 | 00898_trig_update | Thu Apr 09 00:00:00 1970 PST | Thu Apr 09 00:00:00 1970 | 8 | 8 | foo - 908 | 608 | 00908_trig_update | Fri Jan 09 00:00:00 1970 PST | Fri Jan 09 00:00:00 1970 | 8 | 8 | foo - 918 | 608 | 00918_trig_update | Mon Jan 19 00:00:00 1970 PST | Mon Jan 19 00:00:00 1970 | 8 | 8 | foo - 928 | 608 | 00928_trig_update | Thu Jan 29 00:00:00 1970 PST | Thu Jan 29 00:00:00 1970 | 8 | 8 | foo - 938 | 608 | 00938_trig_update | Sun Feb 08 00:00:00 1970 PST | Sun Feb 08 00:00:00 1970 | 8 | 8 | foo - 948 | 608 | 00948_trig_update | Wed Feb 18 00:00:00 1970 PST | Wed Feb 18 00:00:00 1970 | 8 | 8 | foo - 958 | 608 | 00958_trig_update | Sat Feb 28 00:00:00 1970 PST | Sat Feb 28 00:00:00 1970 | 8 | 8 | foo - 968 | 608 | 00968_trig_update | Tue Mar 10 00:00:00 1970 PST | Tue Mar 10 00:00:00 1970 | 8 | 8 | foo - 978 | 608 | 00978_trig_update | Fri Mar 20 00:00:00 1970 PST | Fri Mar 20 00:00:00 1970 | 8 | 8 | foo - 988 | 608 | 00988_trig_update | Mon Mar 30 00:00:00 1970 PST | Mon Mar 30 00:00:00 1970 | 8 | 8 | foo - 998 | 608 | 00998_trig_update | Thu Apr 09 00:00:00 1970 PST | Thu Apr 09 00:00:00 1970 | 8 | 8 | foo - 1008 | 708 | 0000800008_trig_update | | | | ft2 | - 1018 | 708 | 0001800018_trig_update | | | | ft2 | -(102 rows) - --- Test errors thrown on remote side during update -ALTER TABLE "S 1"."T 1" ADD CONSTRAINT c2positive CHECK (c2 >= 0); -INSERT INTO ft1(c1, c2) VALUES(11, 12); -- duplicate key -ERROR: duplicate key value violates unique constraint "t1_pkey" -DETAIL: Key ("C 1")=(11) already exists. -CONTEXT: remote SQL command: INSERT INTO "S 1"."T 1"("C 1", c2, c3, c4, c5, c6, c7, c8) VALUES ($1, $2, $3, $4, $5, $6, $7, $8) -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 -ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification -INSERT INTO ft1(c1, c2) VALUES(11, 12) ON CONFLICT (c1, c2) DO UPDATE SET c3 = 'ffg'; -- unsupported -ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification -INSERT INTO ft1(c1, c2) VALUES(1111, -2); -- c2positive -ERROR: new row for relation "T 1" violates check constraint "c2positive" -DETAIL: Failing row contains (1111, -2, null, null, null, null, ft1 , null). -CONTEXT: remote SQL command: INSERT INTO "S 1"."T 1"("C 1", c2, c3, c4, c5, c6, c7, c8) VALUES ($1, $2, $3, $4, $5, $6, $7, $8) -UPDATE ft1 SET c2 = -c2 WHERE c1 = 1; -- c2positive -ERROR: new row for relation "T 1" violates check constraint "c2positive" -DETAIL: Failing row contains (1, -1, 00001_trig_update, 1970-01-02 08:00:00+00, 1970-01-02 00:00:00, 1, 1 , foo). -CONTEXT: remote SQL command: UPDATE "S 1"."T 1" SET c2 = (- c2) WHERE (("C 1" = 1)) --- Test savepoint/rollback behavior -select c2, count(*) from ft2 where c2 < 500 group by 1 order by 1; - c2 | count ------+------- - 0 | 100 - 1 | 100 - 4 | 100 - 6 | 100 - 100 | 2 - 101 | 2 - 104 | 2 - 106 | 2 - 201 | 1 - 204 | 1 - 303 | 100 - 403 | 2 - 407 | 100 -(13 rows) - -select c2, count(*) from "S 1"."T 1" where c2 < 500 group by 1 order by 1; - c2 | count ------+------- - 0 | 100 - 1 | 100 - 4 | 100 - 6 | 100 - 100 | 2 - 101 | 2 - 104 | 2 - 106 | 2 - 201 | 1 - 204 | 1 - 303 | 100 - 403 | 2 - 407 | 100 -(13 rows) - -begin; -update ft2 set c2 = 42 where c2 = 0; -select c2, count(*) from ft2 where c2 < 500 group by 1 order by 1; - c2 | count ------+------- - 1 | 100 - 4 | 100 - 6 | 100 - 42 | 100 - 100 | 2 - 101 | 2 - 104 | 2 - 106 | 2 - 201 | 1 - 204 | 1 - 303 | 100 - 403 | 2 - 407 | 100 -(13 rows) - -savepoint s1; -update ft2 set c2 = 44 where c2 = 4; -select c2, count(*) from ft2 where c2 < 500 group by 1 order by 1; - c2 | count ------+------- - 1 | 100 - 6 | 100 - 42 | 100 - 44 | 100 - 100 | 2 - 101 | 2 - 104 | 2 - 106 | 2 - 201 | 1 - 204 | 1 - 303 | 100 - 403 | 2 - 407 | 100 -(13 rows) - -release savepoint s1; -select c2, count(*) from ft2 where c2 < 500 group by 1 order by 1; - c2 | count ------+------- - 1 | 100 - 6 | 100 - 42 | 100 - 44 | 100 - 100 | 2 - 101 | 2 - 104 | 2 - 106 | 2 - 201 | 1 - 204 | 1 - 303 | 100 - 403 | 2 - 407 | 100 -(13 rows) - -savepoint s2; -update ft2 set c2 = 46 where c2 = 6; -select c2, count(*) from ft2 where c2 < 500 group by 1 order by 1; - c2 | count ------+------- - 1 | 100 - 42 | 100 - 44 | 100 - 46 | 100 - 100 | 2 - 101 | 2 - 104 | 2 - 106 | 2 - 201 | 1 - 204 | 1 - 303 | 100 - 403 | 2 - 407 | 100 -(13 rows) - -rollback to savepoint s2; -select c2, count(*) from ft2 where c2 < 500 group by 1 order by 1; - c2 | count ------+------- - 1 | 100 - 6 | 100 - 42 | 100 - 44 | 100 - 100 | 2 - 101 | 2 - 104 | 2 - 106 | 2 - 201 | 1 - 204 | 1 - 303 | 100 - 403 | 2 - 407 | 100 -(13 rows) - -release savepoint s2; -select c2, count(*) from ft2 where c2 < 500 group by 1 order by 1; - c2 | count ------+------- - 1 | 100 - 6 | 100 - 42 | 100 - 44 | 100 - 100 | 2 - 101 | 2 - 104 | 2 - 106 | 2 - 201 | 1 - 204 | 1 - 303 | 100 - 403 | 2 - 407 | 100 -(13 rows) - -savepoint s3; -update ft2 set c2 = -2 where c2 = 42 and c1 = 10; -- fail on remote side -ERROR: new row for relation "T 1" violates check constraint "c2positive" -DETAIL: Failing row contains (10, -2, 00010_trig_update_trig_update, 1970-01-11 08:00:00+00, 1970-01-11 00:00:00, 0, 0 , foo). -CONTEXT: remote SQL command: UPDATE "S 1"."T 1" SET c2 = (-2) WHERE ((c2 = 42)) AND (("C 1" = 10)) -rollback to savepoint s3; -select c2, count(*) from ft2 where c2 < 500 group by 1 order by 1; - c2 | count ------+------- - 1 | 100 - 6 | 100 - 42 | 100 - 44 | 100 - 100 | 2 - 101 | 2 - 104 | 2 - 106 | 2 - 201 | 1 - 204 | 1 - 303 | 100 - 403 | 2 - 407 | 100 -(13 rows) - -release savepoint s3; -select c2, count(*) from ft2 where c2 < 500 group by 1 order by 1; - c2 | count ------+------- - 1 | 100 - 6 | 100 - 42 | 100 - 44 | 100 - 100 | 2 - 101 | 2 - 104 | 2 - 106 | 2 - 201 | 1 - 204 | 1 - 303 | 100 - 403 | 2 - 407 | 100 -(13 rows) - --- none of the above is committed yet remotely -select c2, count(*) from "S 1"."T 1" where c2 < 500 group by 1 order by 1; - c2 | count ------+------- - 0 | 100 - 1 | 100 - 4 | 100 - 6 | 100 - 100 | 2 - 101 | 2 - 104 | 2 - 106 | 2 - 201 | 1 - 204 | 1 - 303 | 100 - 403 | 2 - 407 | 100 -(13 rows) - -commit; -select c2, count(*) from ft2 where c2 < 500 group by 1 order by 1; - c2 | count ------+------- - 1 | 100 - 6 | 100 - 42 | 100 - 44 | 100 - 100 | 2 - 101 | 2 - 104 | 2 - 106 | 2 - 201 | 1 - 204 | 1 - 303 | 100 - 403 | 2 - 407 | 100 -(13 rows) - -select c2, count(*) from "S 1"."T 1" where c2 < 500 group by 1 order by 1; - c2 | count ------+------- - 1 | 100 - 6 | 100 - 42 | 100 - 44 | 100 - 100 | 2 - 101 | 2 - 104 | 2 - 106 | 2 - 201 | 1 - 204 | 1 - 303 | 100 - 403 | 2 - 407 | 100 -(13 rows) - -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; - QUERY PLAN ---------------------------------------------------------------------------------------------------------------------------------------------------------------- - Foreign Scan on public.ft1 - Output: c1, c2, c3, c4, c5, c6, c7, c8 - Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8 FROM "S 1"."T 1" ORDER BY c6 DESC NULLS LAST, "C 1" ASC NULLS LAST LIMIT 10::bigint OFFSET 795::bigint -(3 rows) - -SELECT * FROM ft1 ORDER BY c6 DESC NULLS LAST, c1 OFFSET 795 LIMIT 10; - c1 | c2 | c3 | c4 | c5 | c6 | c7 | c8 -------+-----+--------------------+------------------------------+--------------------------+------+------------+----- - 960 | 42 | 00960_trig_update | Mon Mar 02 00:00:00 1970 PST | Mon Mar 02 00:00:00 1970 | 0 | 0 | foo - 970 | 42 | 00970_trig_update | Thu Mar 12 00:00:00 1970 PST | Thu Mar 12 00:00:00 1970 | 0 | 0 | foo - 980 | 42 | 00980_trig_update | Sun Mar 22 00:00:00 1970 PST | Sun Mar 22 00:00:00 1970 | 0 | 0 | foo - 990 | 42 | 00990_trig_update | Wed Apr 01 00:00:00 1970 PST | Wed Apr 01 00:00:00 1970 | 0 | 0 | foo - 1000 | 42 | 01000_trig_update | Thu Jan 01 00:00:00 1970 PST | Thu Jan 01 00:00:00 1970 | 0 | 0 | foo - 1218 | 818 | ggg_trig_update | | | (--; | ft2 | - 1001 | 101 | 0000100001 | | | | ft2 | - 1003 | 403 | 0000300003_update3 | | | | ft2 | - 1004 | 104 | 0000400004 | | | | ft2 | - 1006 | 106 | 0000600006 | | | | ft2 | -(10 rows) - --- ORDER BY DESC NULLS FIRST options -EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM ft1 ORDER BY c6 DESC NULLS FIRST, c1 OFFSET 15 LIMIT 10; - QUERY PLAN ---------------------------------------------------------------------------------------------------------------------------------------------------------------- - Foreign Scan on public.ft1 - Output: c1, c2, c3, c4, c5, c6, c7, c8 - Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8 FROM "S 1"."T 1" ORDER BY c6 DESC NULLS FIRST, "C 1" ASC NULLS LAST LIMIT 10::bigint OFFSET 15::bigint -(3 rows) - -SELECT * FROM ft1 ORDER BY c6 DESC NULLS FIRST, c1 OFFSET 15 LIMIT 10; - c1 | c2 | c3 | c4 | c5 | c6 | c7 | c8 -------+-----+-----------------+------------------------------+--------------------------+----+------------+----- - 1020 | 100 | 0002000020 | | | | ft2 | - 1101 | 201 | aaa | | | | ft2 | - 1103 | 503 | ccc_update3 | | | | ft2 | - 1104 | 204 | ddd | | | | ft2 | - 1208 | 818 | fff_trig_update | | | | ft2 | - 9 | 509 | 00009_update9 | Sat Jan 10 00:00:00 1970 PST | Sat Jan 10 00:00:00 1970 | 9 | ft2 | foo - 19 | 509 | 00019_update9 | Tue Jan 20 00:00:00 1970 PST | Tue Jan 20 00:00:00 1970 | 9 | ft2 | foo - 29 | 509 | 00029_update9 | Fri Jan 30 00:00:00 1970 PST | Fri Jan 30 00:00:00 1970 | 9 | ft2 | foo - 39 | 509 | 00039_update9 | Mon Feb 09 00:00:00 1970 PST | Mon Feb 09 00:00:00 1970 | 9 | ft2 | foo - 49 | 509 | 00049_update9 | Thu Feb 19 00:00:00 1970 PST | Thu Feb 19 00:00:00 1970 | 9 | ft2 | foo -(10 rows) - --- ORDER BY ASC NULLS FIRST options -EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM ft1 ORDER BY c6 ASC NULLS FIRST, c1 OFFSET 15 LIMIT 10; - QUERY PLAN --------------------------------------------------------------------------------------------------------------------------------------------------------------- - Foreign Scan on public.ft1 - Output: c1, c2, c3, c4, c5, c6, c7, c8 - Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8 FROM "S 1"."T 1" ORDER BY c6 ASC NULLS FIRST, "C 1" ASC NULLS LAST LIMIT 10::bigint OFFSET 15::bigint -(3 rows) - -SELECT * FROM ft1 ORDER BY c6 ASC NULLS FIRST, c1 OFFSET 15 LIMIT 10; - c1 | c2 | c3 | c4 | c5 | c6 | c7 | c8 -------+-----+-------------------+------------------------------+--------------------------+------+------------+----- - 1020 | 100 | 0002000020 | | | | ft2 | - 1101 | 201 | aaa | | | | ft2 | - 1103 | 503 | ccc_update3 | | | | ft2 | - 1104 | 204 | ddd | | | | ft2 | - 1208 | 818 | fff_trig_update | | | | ft2 | - 1218 | 818 | ggg_trig_update | | | (--; | ft2 | - 10 | 42 | 00010_trig_update | Sun Jan 11 00:00:00 1970 PST | Sun Jan 11 00:00:00 1970 | 0 | 0 | foo - 20 | 42 | 00020_trig_update | Wed Jan 21 00:00:00 1970 PST | Wed Jan 21 00:00:00 1970 | 0 | 0 | foo - 30 | 42 | 00030_trig_update | Sat Jan 31 00:00:00 1970 PST | Sat Jan 31 00:00:00 1970 | 0 | 0 | foo - 40 | 42 | 00040_trig_update | Tue Feb 10 00:00:00 1970 PST | Tue Feb 10 00:00:00 1970 | 0 | 0 | foo -(10 rows) - --- =================================================================== --- test check constraints --- =================================================================== --- Consistent check constraints provide consistent results -ALTER FOREIGN TABLE ft1 ADD CONSTRAINT ft1_c2positive CHECK (c2 >= 0); -EXPLAIN (VERBOSE, COSTS OFF) SELECT count(*) FROM ft1 WHERE c2 < 0; - QUERY PLAN ------------------------------------------------------------------ - Foreign Scan - Output: (count(*)) - Relations: Aggregate on (public.ft1) - Remote SQL: SELECT count(*) FROM "S 1"."T 1" WHERE ((c2 < 0)) -(4 rows) - -SELECT count(*) FROM ft1 WHERE c2 < 0; - count -------- - 0 -(1 row) - -SET constraint_exclusion = 'on'; -EXPLAIN (VERBOSE, COSTS OFF) SELECT count(*) FROM ft1 WHERE c2 < 0; - QUERY PLAN --------------------------------- - Aggregate - Output: count(*) - -> Result - One-Time Filter: false -(4 rows) - -SELECT count(*) FROM ft1 WHERE c2 < 0; - count -------- - 0 -(1 row) - -RESET constraint_exclusion; --- check constraint is enforced on the remote side, not locally -INSERT INTO ft1(c1, c2) VALUES(1111, -2); -- c2positive -ERROR: new row for relation "T 1" violates check constraint "c2positive" -DETAIL: Failing row contains (1111, -2, null, null, null, null, ft1 , null). -CONTEXT: remote SQL command: INSERT INTO "S 1"."T 1"("C 1", c2, c3, c4, c5, c6, c7, c8) VALUES ($1, $2, $3, $4, $5, $6, $7, $8) -UPDATE ft1 SET c2 = -c2 WHERE c1 = 1; -- c2positive -ERROR: new row for relation "T 1" violates check constraint "c2positive" -DETAIL: Failing row contains (1, -1, 00001_trig_update, 1970-01-02 08:00:00+00, 1970-01-02 00:00:00, 1, 1 , foo). -CONTEXT: remote SQL command: UPDATE "S 1"."T 1" SET c2 = (- c2) WHERE (("C 1" = 1)) -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; - QUERY PLAN ------------------------------------------------------------------- - Foreign Scan - Output: (count(*)) - Relations: Aggregate on (public.ft1) - Remote SQL: SELECT count(*) FROM "S 1"."T 1" WHERE ((c2 >= 0)) -(4 rows) - -SELECT count(*) FROM ft1 WHERE c2 >= 0; - count -------- - 821 -(1 row) - -SET constraint_exclusion = 'on'; -EXPLAIN (VERBOSE, COSTS OFF) SELECT count(*) FROM ft1 WHERE c2 >= 0; - QUERY PLAN --------------------------------- - Aggregate - Output: count(*) - -> Result - One-Time Filter: false -(4 rows) - -SELECT count(*) FROM ft1 WHERE c2 >= 0; - count -------- - 0 -(1 row) - -RESET constraint_exclusion; --- 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 --- =================================================================== -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 loopback OPTIONS (table_name 'base_tbl'); -CREATE VIEW rw_view AS SELECT * FROM foreign_tbl - WHERE a < b WITH CHECK OPTION; -\d+ rw_view - View "public.rw_view" - Column | Type | Collation | Nullable | Default | Storage | Description ---------+---------+-----------+----------+---------+---------+------------- - a | integer | | | | plain | - b | integer | | | | plain | -View definition: - SELECT a, - b - FROM foreign_tbl - WHERE a < b; -Options: check_option=cascaded - -EXPLAIN (VERBOSE, COSTS OFF) -INSERT INTO rw_view VALUES (0, 5); - QUERY PLAN --------------------------------------------------------------------------------- - Insert on public.foreign_tbl - Remote SQL: INSERT INTO public.base_tbl(a, b) VALUES ($1, $2) RETURNING a, b - Batch Size: 1 - -> Result - Output: 0, 5 -(5 rows) - -INSERT INTO rw_view VALUES (0, 5); -- should fail -ERROR: new row violates check option for view "rw_view" -DETAIL: Failing row contains (10, 5). -EXPLAIN (VERBOSE, COSTS OFF) -INSERT INTO rw_view VALUES (0, 15); - QUERY PLAN --------------------------------------------------------------------------------- - Insert on public.foreign_tbl - Remote SQL: INSERT INTO public.base_tbl(a, b) VALUES ($1, $2) RETURNING a, b - Batch Size: 1 - -> Result - Output: 0, 15 -(5 rows) - -INSERT INTO rw_view VALUES (0, 15); -- ok -SELECT * FROM foreign_tbl; - a | b -----+---- - 10 | 15 -(1 row) - -EXPLAIN (VERBOSE, COSTS OFF) -UPDATE rw_view SET b = b + 5; - QUERY PLAN ---------------------------------------------------------------------------------------- - Update on public.foreign_tbl - Remote SQL: UPDATE public.base_tbl SET b = $2 WHERE ctid = $1 RETURNING a, b - -> Foreign Scan on public.foreign_tbl - Output: (foreign_tbl.b + 5), foreign_tbl.ctid, foreign_tbl.* - Remote SQL: SELECT a, b, ctid FROM public.base_tbl WHERE ((a < b)) FOR UPDATE -(5 rows) - -UPDATE rw_view SET b = b + 5; -- should fail -ERROR: new row violates check option for view "rw_view" -DETAIL: Failing row contains (20, 20). -EXPLAIN (VERBOSE, COSTS OFF) -UPDATE rw_view SET b = b + 15; - QUERY PLAN ---------------------------------------------------------------------------------------- - Update on public.foreign_tbl - Remote SQL: UPDATE public.base_tbl SET b = $2 WHERE ctid = $1 RETURNING a, b - -> Foreign Scan on public.foreign_tbl - Output: (foreign_tbl.b + 15), foreign_tbl.ctid, foreign_tbl.* - Remote SQL: SELECT a, b, ctid FROM public.base_tbl WHERE ((a < b)) FOR UPDATE -(5 rows) - -UPDATE rw_view SET b = b + 15; -- ok -SELECT * FROM foreign_tbl; - a | b -----+---- - 20 | 30 -(1 row) - --- We don't allow batch insert when there are any WCO constraints -ALTER SERVER loopback OPTIONS (ADD batch_size '10'); -EXPLAIN (VERBOSE, COSTS OFF) -INSERT INTO rw_view VALUES (0, 15), (0, 5); - QUERY PLAN --------------------------------------------------------------------------------- - Insert on public.foreign_tbl - Remote SQL: INSERT INTO public.base_tbl(a, b) VALUES ($1, $2) RETURNING a, b - Batch Size: 1 - -> Values Scan on "*VALUES*" - Output: "*VALUES*".column1, "*VALUES*".column2 -(5 rows) - -INSERT INTO rw_view VALUES (0, 15), (0, 5); -- should fail -ERROR: new row violates check option for view "rw_view" -DETAIL: Failing row contains (10, 5). -SELECT * FROM foreign_tbl; - a | b -----+---- - 20 | 30 -(1 row) - -ALTER SERVER loopback OPTIONS (DROP batch_size); -DROP FOREIGN TABLE foreign_tbl CASCADE; -NOTICE: drop cascades to view rw_view -DROP TRIGGER row_before_insupd_trigger ON base_tbl; -DROP TABLE base_tbl; --- test WCO for partitions -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 loopback 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); --- Detach and re-attach once, to stress the concurrent detach case. -ALTER TABLE parent_tbl DETACH PARTITION foreign_tbl CONCURRENTLY; -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 - View "public.rw_view" - Column | Type | Collation | Nullable | Default | Storage | Description ---------+---------+-----------+----------+---------+---------+------------- - a | integer | | | | plain | - b | integer | | | | plain | -View definition: - SELECT a, - b - FROM parent_tbl - WHERE a < b; -Options: check_option=cascaded - -EXPLAIN (VERBOSE, COSTS OFF) -INSERT INTO rw_view VALUES (0, 5); - QUERY PLAN ------------------------------ - Insert on public.parent_tbl - -> Result - Output: 0, 5 -(3 rows) - -INSERT INTO rw_view VALUES (0, 5); -- should fail -ERROR: new row violates check option for view "rw_view" -DETAIL: Failing row contains (10, 5). -EXPLAIN (VERBOSE, COSTS OFF) -INSERT INTO rw_view VALUES (0, 15); - QUERY PLAN ------------------------------ - Insert on public.parent_tbl - -> Result - Output: 0, 15 -(3 rows) - -INSERT INTO rw_view VALUES (0, 15); -- ok -SELECT * FROM foreign_tbl; - a | b -----+---- - 10 | 15 -(1 row) - -EXPLAIN (VERBOSE, COSTS OFF) -UPDATE rw_view SET b = b + 5; - QUERY PLAN ------------------------------------------------------------------------------------------------- - Update on public.parent_tbl - Foreign Update on public.foreign_tbl parent_tbl_1 - Remote SQL: UPDATE public.child_tbl SET b = $2 WHERE ctid = $1 RETURNING a, b - -> Foreign Scan on public.foreign_tbl parent_tbl_1 - Output: (parent_tbl_1.b + 5), parent_tbl_1.tableoid, parent_tbl_1.ctid, parent_tbl_1.* - Remote SQL: SELECT a, b, ctid FROM public.child_tbl WHERE ((a < b)) FOR UPDATE -(6 rows) - -UPDATE rw_view SET b = b + 5; -- should fail -ERROR: new row violates check option for view "rw_view" -DETAIL: Failing row contains (20, 20). -EXPLAIN (VERBOSE, COSTS OFF) -UPDATE rw_view SET b = b + 15; - QUERY PLAN -------------------------------------------------------------------------------------------------- - Update on public.parent_tbl - Foreign Update on public.foreign_tbl parent_tbl_1 - Remote SQL: UPDATE public.child_tbl SET b = $2 WHERE ctid = $1 RETURNING a, b - -> Foreign Scan on public.foreign_tbl parent_tbl_1 - Output: (parent_tbl_1.b + 15), parent_tbl_1.tableoid, parent_tbl_1.ctid, parent_tbl_1.* - Remote SQL: SELECT a, b, ctid FROM public.child_tbl WHERE ((a < b)) FOR UPDATE -(6 rows) - -UPDATE rw_view SET b = b + 15; -- ok -SELECT * FROM foreign_tbl; - a | b -----+---- - 20 | 30 -(1 row) - --- We don't allow batch insert when there are any WCO constraints -ALTER SERVER loopback OPTIONS (ADD batch_size '10'); -EXPLAIN (VERBOSE, COSTS OFF) -INSERT INTO rw_view VALUES (0, 15), (0, 5); - QUERY PLAN --------------------------------------------------------- - Insert on public.parent_tbl - -> Values Scan on "*VALUES*" - Output: "*VALUES*".column1, "*VALUES*".column2 -(3 rows) - -INSERT INTO rw_view VALUES (0, 15), (0, 5); -- should fail -ERROR: new row violates check option for view "rw_view" -DETAIL: Failing row contains (10, 5). -SELECT * FROM foreign_tbl; - a | b -----+---- - 20 | 30 -(1 row) - -ALTER SERVER loopback OPTIONS (DROP batch_size); -DROP FOREIGN TABLE foreign_tbl CASCADE; -DROP TRIGGER row_before_insupd_trigger ON child_tbl; -DROP TABLE parent_tbl CASCADE; -NOTICE: drop cascades to view rw_view -DROP FUNCTION row_before_insupd_trigfunc; --- Try a more complex permutation of WCO where there are multiple levels of --- partitioned tables with columns not all in the same order -CREATE TABLE parent_tbl (a int, b text, c numeric) PARTITION BY RANGE(a); -CREATE TABLE sub_parent (c numeric, a int, b text) PARTITION BY RANGE(a); -ALTER TABLE parent_tbl ATTACH PARTITION sub_parent FOR VALUES FROM (1) TO (10); -CREATE TABLE child_local (b text, c numeric, a int); -CREATE FOREIGN TABLE child_foreign (b text, c numeric, a int) - SERVER loopback OPTIONS (table_name 'child_local'); -ALTER TABLE sub_parent ATTACH PARTITION child_foreign FOR VALUES FROM (1) TO (10); -CREATE VIEW rw_view AS SELECT * FROM parent_tbl WHERE a < 5 WITH CHECK OPTION; -INSERT INTO parent_tbl (a) VALUES(1),(5); -EXPLAIN (VERBOSE, COSTS OFF) -UPDATE rw_view SET b = 'text', c = 123.456; - QUERY PLAN -------------------------------------------------------------------------------------------------- - Update on public.parent_tbl - Foreign Update on public.child_foreign parent_tbl_1 - Remote SQL: UPDATE public.child_local SET b = $2, c = $3 WHERE ctid = $1 RETURNING a - -> Foreign Scan on public.child_foreign parent_tbl_1 - Output: 'text'::text, 123.456, parent_tbl_1.tableoid, parent_tbl_1.ctid, parent_tbl_1.* - Remote SQL: SELECT b, c, a, ctid FROM public.child_local WHERE ((a < 5)) FOR UPDATE -(6 rows) - -UPDATE rw_view SET b = 'text', c = 123.456; -SELECT * FROM parent_tbl ORDER BY a; - a | b | c ----+------+--------- - 1 | text | 123.456 - 5 | | -(2 rows) - -DROP VIEW rw_view; -DROP TABLE child_local; -DROP FOREIGN TABLE child_foreign; -DROP TABLE sub_parent; -DROP TABLE parent_tbl; --- =================================================================== --- test serial columns (ie, sequence-based defaults) --- =================================================================== -create table loc1 (f1 serial, f2 text); -alter table loc1 set (autovacuum_enabled = 'false'); -create foreign table rem1 (f1 serial, f2 text) - server loopback options(table_name 'loc1'); -select pg_catalog.setval('rem1_f1_seq', 10, false); - setval --------- - 10 -(1 row) - -insert into loc1(f2) values('hi'); -insert into rem1(f2) values('hi remote'); -insert into loc1(f2) values('bye'); -insert into rem1(f2) values('bye remote'); -select * from loc1; - f1 | f2 -----+------------ - 1 | hi - 10 | hi remote - 2 | bye - 11 | bye remote -(4 rows) - -select * from rem1; - f1 | f2 -----+------------ - 1 | hi - 10 | hi remote - 2 | bye - 11 | bye remote -(4 rows) - --- =================================================================== --- test generated columns --- =================================================================== -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 loopback options(table_name 'gloc1'); -explain (verbose, costs off) -insert into grem1 (a) values (1), (2); - QUERY PLAN -------------------------------------------------------------------- - Insert on public.grem1 - Remote SQL: INSERT INTO public.gloc1(a, b) VALUES ($1, DEFAULT) - Batch Size: 1 - -> Values Scan on "*VALUES*" - Output: "*VALUES*".column1, NULL::integer -(5 rows) - -insert into grem1 (a) values (1), (2); -explain (verbose, costs off) -update grem1 set a = 22 where a = 2; - QUERY PLAN ------------------------------------------------------------------------------------- - Update on public.grem1 - Remote SQL: UPDATE public.gloc1 SET a = $2, b = DEFAULT WHERE ctid = $1 - -> Foreign Scan on public.grem1 - Output: 22, ctid, grem1.* - Remote SQL: SELECT a, b, ctid FROM public.gloc1 WHERE ((a = 2)) FOR UPDATE -(5 rows) - -update grem1 set a = 22 where a = 2; -select * from gloc1; - a | b -----+---- - 1 | 2 - 22 | 44 -(2 rows) - -select * from grem1; - a | b -----+---- - 1 | 2 - 22 | 44 -(2 rows) - -delete from grem1; --- test copy from -copy grem1 from stdin; -select * from gloc1; - a | b ----+--- - 1 | 2 - 2 | 4 -(2 rows) - -select * from grem1; - a | b ----+--- - 1 | 2 - 2 | 4 -(2 rows) - -delete from grem1; --- test batch insert -alter server loopback options (add batch_size '10'); -explain (verbose, costs off) -insert into grem1 (a) values (1), (2); - QUERY PLAN -------------------------------------------------------------------- - Insert on public.grem1 - Remote SQL: INSERT INTO public.gloc1(a, b) VALUES ($1, DEFAULT) - Batch Size: 10 - -> Values Scan on "*VALUES*" - Output: "*VALUES*".column1, NULL::integer -(5 rows) - -insert into grem1 (a) values (1), (2); -select * from gloc1; - a | b ----+--- - 1 | 2 - 2 | 4 -(2 rows) - -select * from grem1; - a | b ----+--- - 1 | 2 - 2 | 4 -(2 rows) - -delete from grem1; --- batch insert with foreign partitions. --- This schema uses two partitions, one local and one remote with a modulo --- to loop across all of them in batches. -create table tab_batch_local (id int, data text); -insert into tab_batch_local select i, 'test'|| i from generate_series(1, 45) i; -create table tab_batch_sharded (id int, data text) partition by hash(id); -create table tab_batch_sharded_p0 partition of tab_batch_sharded - for values with (modulus 2, remainder 0); -create table tab_batch_sharded_p1_remote (id int, data text); -create foreign table tab_batch_sharded_p1 partition of tab_batch_sharded - for values with (modulus 2, remainder 1) - server loopback options (table_name 'tab_batch_sharded_p1_remote'); -insert into tab_batch_sharded select * from tab_batch_local; -select count(*) from tab_batch_sharded; - count -------- - 45 -(1 row) - -drop table tab_batch_local; -drop table tab_batch_sharded; -drop table tab_batch_sharded_p1_remote; -alter server loopback options (drop batch_size); --- =================================================================== --- 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;$$; -CREATE TRIGGER trig_stmt_before BEFORE DELETE OR INSERT OR UPDATE OR TRUNCATE ON rem1 - FOR EACH STATEMENT EXECUTE PROCEDURE trigger_func(); -CREATE TRIGGER trig_stmt_after AFTER DELETE OR INSERT OR UPDATE OR TRUNCATE 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; -NOTICE: trigger_func() called: action = DELETE, when = BEFORE, level = STATEMENT -NOTICE: trig_row_before(23, skidoo) BEFORE ROW DELETE ON rem1 -NOTICE: OLD: (1,hi) -NOTICE: trig_row_before(23, skidoo) BEFORE ROW DELETE ON rem1 -NOTICE: OLD: (10,"hi remote") -NOTICE: trig_row_before(23, skidoo) BEFORE ROW DELETE ON rem1 -NOTICE: OLD: (2,bye) -NOTICE: trig_row_before(23, skidoo) BEFORE ROW DELETE ON rem1 -NOTICE: OLD: (11,"bye remote") -NOTICE: trig_row_after(23, skidoo) AFTER ROW DELETE ON rem1 -NOTICE: OLD: (1,hi) -NOTICE: trig_row_after(23, skidoo) AFTER ROW DELETE ON rem1 -NOTICE: OLD: (10,"hi remote") -NOTICE: trig_row_after(23, skidoo) AFTER ROW DELETE ON rem1 -NOTICE: OLD: (2,bye) -NOTICE: trig_row_after(23, skidoo) AFTER ROW DELETE ON rem1 -NOTICE: OLD: (11,"bye remote") -NOTICE: trigger_func() called: action = DELETE, when = AFTER, level = STATEMENT -insert into rem1 values(1,'insert'); -NOTICE: trigger_func() called: action = INSERT, when = BEFORE, level = STATEMENT -NOTICE: trig_row_before(23, skidoo) BEFORE ROW INSERT ON rem1 -NOTICE: NEW: (1,insert) -NOTICE: trig_row_after(23, skidoo) AFTER ROW INSERT ON rem1 -NOTICE: NEW: (1,insert) -NOTICE: trigger_func() called: action = INSERT, when = AFTER, level = STATEMENT -update rem1 set f2 = 'update' where f1 = 1; -NOTICE: trigger_func() called: action = UPDATE, when = BEFORE, level = STATEMENT -NOTICE: trig_row_before(23, skidoo) BEFORE ROW UPDATE ON rem1 -NOTICE: OLD: (1,insert),NEW: (1,update) -NOTICE: trig_row_after(23, skidoo) AFTER ROW UPDATE ON rem1 -NOTICE: OLD: (1,insert),NEW: (1,update) -NOTICE: trigger_func() called: action = UPDATE, when = AFTER, level = STATEMENT -update rem1 set f2 = f2 || f2; -NOTICE: trigger_func() called: action = UPDATE, when = BEFORE, level = STATEMENT -NOTICE: trig_row_before(23, skidoo) BEFORE ROW UPDATE ON rem1 -NOTICE: OLD: (1,update),NEW: (1,updateupdate) -NOTICE: trig_row_after(23, skidoo) AFTER ROW UPDATE ON rem1 -NOTICE: OLD: (1,update),NEW: (1,updateupdate) -NOTICE: trigger_func() called: action = UPDATE, when = AFTER, level = STATEMENT -truncate rem1; -NOTICE: trigger_func() called: action = TRUNCATE, when = BEFORE, level = STATEMENT -NOTICE: trigger_func() called: action = TRUNCATE, when = AFTER, level = STATEMENT --- cleanup -DROP TRIGGER trig_row_before ON rem1; -DROP TRIGGER trig_row_after ON rem1; -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'); -NOTICE: trig_row_after1(23, skidoo) AFTER ROW INSERT ON rem1 -NOTICE: NEW: (1,insert) -NOTICE: trig_row_after2(23, skidoo) AFTER ROW INSERT ON rem1 -NOTICE: NEW: (1,insert) -update rem1 set f2 = 'update' where f1 = 1; -NOTICE: trig_row_after1(23, skidoo) AFTER ROW UPDATE ON rem1 -NOTICE: OLD: (1,insert),NEW: (1,update) -NOTICE: trig_row_after2(23, skidoo) AFTER ROW UPDATE ON rem1 -NOTICE: OLD: (1,insert),NEW: (1,update) -update rem1 set f2 = f2 || f2; -NOTICE: trig_row_after1(23, skidoo) AFTER ROW UPDATE ON rem1 -NOTICE: OLD: (1,update),NEW: (1,updateupdate) -NOTICE: trig_row_after2(23, skidoo) AFTER ROW UPDATE ON rem1 -NOTICE: OLD: (1,update),NEW: (1,updateupdate) -delete from rem1; -NOTICE: trig_row_after1(23, skidoo) AFTER ROW DELETE ON rem1 -NOTICE: OLD: (1,updateupdate) -NOTICE: trig_row_after2(23, skidoo) AFTER ROW DELETE ON rem1 -NOTICE: OLD: (1,updateupdate) --- 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'); -NOTICE: trig_row_before_insupd(23, skidoo) BEFORE ROW INSERT ON rem1 -NOTICE: NEW: (2,update) -NOTICE: trig_row_after_insupd(23, skidoo) AFTER ROW INSERT ON rem1 -NOTICE: NEW: (2,update) -UPDATE rem1 set f2 = 'update update' where f1 = '2'; -NOTICE: trig_row_before_insupd(23, skidoo) BEFORE ROW UPDATE ON rem1 -NOTICE: OLD: (2,update),NEW: (2,"update update") -NOTICE: trig_row_after_insupd(23, skidoo) AFTER ROW UPDATE ON rem1 -NOTICE: OLD: (2,update),NEW: (2,"update update") -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; -NOTICE: trig_row_before_delete(23, skidoo) BEFORE ROW DELETE ON rem1 -NOTICE: OLD: (2,"update update") -NOTICE: trig_row_after_delete(23, skidoo) AFTER ROW DELETE ON rem1 -NOTICE: OLD: (2,"update update") --- 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'); -SELECT * from loc1; - f1 | f2 -----+-------------------- - 1 | insert triggered ! -(1 row) - -INSERT INTO rem1 values(2, 'insert') RETURNING f2; - f2 --------------------- - insert triggered ! -(1 row) - -SELECT * from loc1; - f1 | f2 -----+-------------------- - 1 | insert triggered ! - 2 | insert triggered ! -(2 rows) - -UPDATE rem1 set f2 = ''; -SELECT * from loc1; - f1 | f2 -----+-------------- - 1 | triggered ! - 2 | triggered ! -(2 rows) - -UPDATE rem1 set f2 = 'skidoo' RETURNING f2; - f2 --------------------- - skidoo triggered ! - skidoo triggered ! -(2 rows) - -SELECT * from loc1; - f1 | f2 -----+-------------------- - 1 | skidoo triggered ! - 2 | skidoo triggered ! -(2 rows) - -EXPLAIN (verbose, costs off) -UPDATE rem1 set f1 = 10; -- all columns should be transmitted - QUERY PLAN ------------------------------------------------------------------------ - Update on public.rem1 - Remote SQL: UPDATE public.loc1 SET f1 = $2, f2 = $3 WHERE ctid = $1 - -> Foreign Scan on public.rem1 - Output: 10, ctid, rem1.* - Remote SQL: SELECT f1, f2, ctid FROM public.loc1 FOR UPDATE -(5 rows) - -UPDATE rem1 set f1 = 10; -SELECT * from loc1; - f1 | f2 -----+-------------------------------- - 10 | skidoo triggered ! triggered ! - 10 | skidoo triggered ! triggered ! -(2 rows) - -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'); -SELECT * from loc1; - f1 | f2 -----+-------------------------------- - 1 | insert triggered ! triggered ! -(1 row) - -INSERT INTO rem1 values(2, 'insert') RETURNING f2; - f2 --------------------------------- - insert triggered ! triggered ! -(1 row) - -SELECT * from loc1; - f1 | f2 -----+-------------------------------- - 1 | insert triggered ! triggered ! - 2 | insert triggered ! triggered ! -(2 rows) - -UPDATE rem1 set f2 = ''; -SELECT * from loc1; - f1 | f2 -----+-------------------------- - 1 | triggered ! triggered ! - 2 | triggered ! triggered ! -(2 rows) - -UPDATE rem1 set f2 = 'skidoo' RETURNING f2; - f2 --------------------------------- - skidoo triggered ! triggered ! - skidoo triggered ! triggered ! -(2 rows) - -SELECT * from loc1; - f1 | f2 -----+-------------------------------- - 1 | skidoo triggered ! triggered ! - 2 | skidoo triggered ! triggered ! -(2 rows) - -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'); -SELECT * from loc1; - f1 | f2 -----+------ - 1 | test -(1 row) - -UPDATE rem1 SET f2 = 'test2'; -SELECT * from loc1; - f1 | f2 -----+------ - 1 | test -(1 row) - -DELETE from rem1; -SELECT * from loc1; - f1 | f2 -----+------ - 1 | test -(1 row) - -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'); -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'); -NOTICE: trig_row_before(23, skidoo) BEFORE ROW INSERT ON rem1 -NOTICE: NEW: (12,test) -NOTICE: trig_row_after(23, skidoo) AFTER ROW INSERT ON rem1 -NOTICE: NEW: (12,"test triggered !") -UPDATE rem1 SET f2 = 'testo'; -NOTICE: trig_row_before(23, skidoo) BEFORE ROW UPDATE ON rem1 -NOTICE: OLD: (12,"test triggered !"),NEW: (12,testo) -NOTICE: trig_row_after(23, skidoo) AFTER ROW UPDATE ON rem1 -NOTICE: OLD: (12,"test triggered !"),NEW: (12,"testo triggered !") --- Test returning a system attribute -INSERT INTO rem1(f2) VALUES ('test') RETURNING ctid; -NOTICE: trig_row_before(23, skidoo) BEFORE ROW INSERT ON rem1 -NOTICE: NEW: (13,test) -NOTICE: trig_row_after(23, skidoo) AFTER ROW INSERT ON rem1 -NOTICE: NEW: (13,"test triggered !") - ctid --------- - (0,25) -(1 row) - --- cleanup -DROP TRIGGER trig_row_before ON rem1; -DROP TRIGGER trig_row_after ON rem1; -DROP TRIGGER trig_local_before ON loc1; --- Test direct foreign table modification functionality -EXPLAIN (verbose, costs off) -DELETE FROM rem1; -- can be pushed down - QUERY PLAN ---------------------------------------------- - Delete on public.rem1 - -> Foreign Delete on public.rem1 - Remote SQL: DELETE FROM public.loc1 -(3 rows) - -EXPLAIN (verbose, costs off) -DELETE FROM rem1 WHERE false; -- currently can't be pushed down - QUERY PLAN -------------------------------------------------------- - Delete on public.rem1 - Remote SQL: DELETE FROM public.loc1 WHERE ctid = $1 - -> Result - Output: ctid - One-Time Filter: false -(5 rows) - --- Test with statement-level triggers -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 - QUERY PLAN ----------------------------------------------------------- - Update on public.rem1 - -> Foreign Update on public.rem1 - Remote SQL: UPDATE public.loc1 SET f2 = ''::text -(3 rows) - -EXPLAIN (verbose, costs off) -DELETE FROM rem1; -- can be pushed down - QUERY PLAN ---------------------------------------------- - Delete on public.rem1 - -> Foreign Delete on public.rem1 - Remote SQL: DELETE FROM public.loc1 -(3 rows) - -DROP TRIGGER trig_stmt_before ON rem1; -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 - QUERY PLAN ----------------------------------------------------------- - Update on public.rem1 - -> Foreign Update on public.rem1 - Remote SQL: UPDATE public.loc1 SET f2 = ''::text -(3 rows) - -EXPLAIN (verbose, costs off) -DELETE FROM rem1; -- can be pushed down - QUERY PLAN ---------------------------------------------- - Delete on public.rem1 - -> Foreign Delete on public.rem1 - Remote SQL: DELETE FROM public.loc1 -(3 rows) - -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 - QUERY PLAN ----------------------------------------------------------- - Update on public.rem1 - -> Foreign Update on public.rem1 - Remote SQL: UPDATE public.loc1 SET f2 = ''::text -(3 rows) - -EXPLAIN (verbose, costs off) -DELETE FROM rem1; -- can be pushed down - QUERY PLAN ---------------------------------------------- - Delete on public.rem1 - -> Foreign Delete on public.rem1 - Remote SQL: DELETE FROM public.loc1 -(3 rows) - -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 - QUERY PLAN ----------------------------------------------------------- - Update on public.rem1 - -> Foreign Update on public.rem1 - Remote SQL: UPDATE public.loc1 SET f2 = ''::text -(3 rows) - -EXPLAIN (verbose, costs off) -DELETE FROM rem1; -- can be pushed down - QUERY PLAN ---------------------------------------------- - Delete on public.rem1 - -> Foreign Delete on public.rem1 - Remote SQL: DELETE FROM public.loc1 -(3 rows) - -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 - QUERY PLAN ------------------------------------------------------------------------ - Update on public.rem1 - Remote SQL: UPDATE public.loc1 SET f1 = $2, f2 = $3 WHERE ctid = $1 - -> Foreign Scan on public.rem1 - Output: ''::text, ctid, rem1.* - Remote SQL: SELECT f1, f2, ctid FROM public.loc1 FOR UPDATE -(5 rows) - -EXPLAIN (verbose, costs off) -DELETE FROM rem1; -- can be pushed down - QUERY PLAN ---------------------------------------------- - Delete on public.rem1 - -> Foreign Delete on public.rem1 - Remote SQL: DELETE FROM public.loc1 -(3 rows) - -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 - QUERY PLAN -------------------------------------------------------------------------------- - Update on public.rem1 - Remote SQL: UPDATE public.loc1 SET f2 = $2 WHERE ctid = $1 RETURNING f1, f2 - -> Foreign Scan on public.rem1 - Output: ''::text, ctid, rem1.* - Remote SQL: SELECT f1, f2, ctid FROM public.loc1 FOR UPDATE -(5 rows) - -EXPLAIN (verbose, costs off) -DELETE FROM rem1; -- can be pushed down - QUERY PLAN ---------------------------------------------- - Delete on public.rem1 - -> Foreign Delete on public.rem1 - Remote SQL: DELETE FROM public.loc1 -(3 rows) - -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 - QUERY PLAN ----------------------------------------------------------- - Update on public.rem1 - -> Foreign Update on public.rem1 - Remote SQL: UPDATE public.loc1 SET f2 = ''::text -(3 rows) - -EXPLAIN (verbose, costs off) -DELETE FROM rem1; -- can't be pushed down - QUERY PLAN ---------------------------------------------------------------------- - Delete on public.rem1 - Remote SQL: DELETE FROM public.loc1 WHERE ctid = $1 - -> Foreign Scan on public.rem1 - Output: ctid, rem1.* - Remote SQL: SELECT f1, f2, ctid FROM public.loc1 FOR UPDATE -(5 rows) - -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 - QUERY PLAN ----------------------------------------------------------- - Update on public.rem1 - -> Foreign Update on public.rem1 - Remote SQL: UPDATE public.loc1 SET f2 = ''::text -(3 rows) - -EXPLAIN (verbose, costs off) -DELETE FROM rem1; -- can't be pushed down - QUERY PLAN ------------------------------------------------------------------------- - Delete on public.rem1 - Remote SQL: DELETE FROM public.loc1 WHERE ctid = $1 RETURNING f1, f2 - -> Foreign Scan on public.rem1 - Output: ctid, rem1.* - Remote SQL: SELECT f1, f2, ctid FROM public.loc1 FOR UPDATE -(5 rows) - -DROP TRIGGER trig_row_after_delete ON rem1; --- =================================================================== --- test inheritance features --- =================================================================== -CREATE TABLE a (aa TEXT); -CREATE TABLE loct (aa TEXT, bb TEXT); -ALTER TABLE a SET (autovacuum_enabled = 'false'); -ALTER TABLE loct SET (autovacuum_enabled = 'false'); -CREATE FOREIGN TABLE b (bb TEXT) INHERITS (a) - SERVER loopback 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; - tableoid | aa -----------+------- - a | aaa - a | aaaa - a | aaaaa - b | bbb - b | bbbb - b | bbbbb -(6 rows) - -SELECT tableoid::regclass, * FROM b; - tableoid | aa | bb -----------+-------+---- - b | bbb | - b | bbbb | - b | bbbbb | -(3 rows) - -SELECT tableoid::regclass, * FROM ONLY a; - tableoid | aa -----------+------- - a | aaa - a | aaaa - a | aaaaa -(3 rows) - -UPDATE a SET aa = 'zzzzzz' WHERE aa LIKE 'aaaa%'; -SELECT tableoid::regclass, * FROM a; - tableoid | aa -----------+-------- - a | aaa - a | zzzzzz - a | zzzzzz - b | bbb - b | bbbb - b | bbbbb -(6 rows) - -SELECT tableoid::regclass, * FROM b; - tableoid | aa | bb -----------+-------+---- - b | bbb | - b | bbbb | - b | bbbbb | -(3 rows) - -SELECT tableoid::regclass, * FROM ONLY a; - tableoid | aa -----------+-------- - a | aaa - a | zzzzzz - a | zzzzzz -(3 rows) - -UPDATE b SET aa = 'new'; -SELECT tableoid::regclass, * FROM a; - tableoid | aa -----------+-------- - a | aaa - a | zzzzzz - a | zzzzzz - b | new - b | new - b | new -(6 rows) - -SELECT tableoid::regclass, * FROM b; - tableoid | aa | bb -----------+-----+---- - b | new | - b | new | - b | new | -(3 rows) - -SELECT tableoid::regclass, * FROM ONLY a; - tableoid | aa -----------+-------- - a | aaa - a | zzzzzz - a | zzzzzz -(3 rows) - -UPDATE a SET aa = 'newtoo'; -SELECT tableoid::regclass, * FROM a; - tableoid | aa -----------+-------- - a | newtoo - a | newtoo - a | newtoo - b | newtoo - b | newtoo - b | newtoo -(6 rows) - -SELECT tableoid::regclass, * FROM b; - tableoid | aa | bb -----------+--------+---- - b | newtoo | - b | newtoo | - b | newtoo | -(3 rows) - -SELECT tableoid::regclass, * FROM ONLY a; - tableoid | aa -----------+-------- - a | newtoo - a | newtoo - a | newtoo -(3 rows) - -DELETE FROM a; -SELECT tableoid::regclass, * FROM a; - tableoid | aa -----------+---- -(0 rows) - -SELECT tableoid::regclass, * FROM b; - tableoid | aa | bb -----------+----+---- -(0 rows) - -SELECT tableoid::regclass, * FROM ONLY a; - tableoid | aa -----------+---- -(0 rows) - -DROP TABLE a CASCADE; -NOTICE: drop cascades to foreign table b -DROP TABLE loct; --- Check SELECT FOR UPDATE/SHARE with an inherited source table -create table loct1 (f1 int, f2 int, f3 int); -create table loct2 (f1 int, f2 int, f3 int); -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 loopback options (table_name 'loct1'); -create table bar (f1 int, f2 int); -create foreign table bar2 (f3 int) inherits (bar) - server loopback 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; - QUERY PLAN ----------------------------------------------------------------------------------------------- - LockRows - Output: bar.f1, bar.f2, bar.ctid, foo.ctid, bar.*, bar.tableoid, foo.*, foo.tableoid - -> Hash Join - Output: bar.f1, bar.f2, bar.ctid, foo.ctid, bar.*, bar.tableoid, foo.*, foo.tableoid - Inner Unique: true - Hash Cond: (bar.f1 = foo.f1) - -> Append - -> Seq Scan on public.bar bar_1 - Output: bar_1.f1, bar_1.f2, bar_1.ctid, bar_1.*, bar_1.tableoid - -> Foreign Scan on public.bar2 bar_2 - Output: bar_2.f1, bar_2.f2, bar_2.ctid, bar_2.*, bar_2.tableoid - Remote SQL: SELECT f1, f2, f3, ctid FROM public.loct2 FOR UPDATE - -> Hash - Output: foo.ctid, foo.f1, foo.*, foo.tableoid - -> HashAggregate - Output: foo.ctid, foo.f1, foo.*, foo.tableoid - Group Key: foo.f1 - -> Append - -> Seq Scan on public.foo foo_1 - Output: foo_1.ctid, foo_1.f1, foo_1.*, foo_1.tableoid - -> Foreign Scan on public.foo2 foo_2 - Output: foo_2.ctid, foo_2.f1, foo_2.*, foo_2.tableoid - Remote SQL: SELECT f1, f2, f3, ctid FROM public.loct1 -(23 rows) - -select * from bar where f1 in (select f1 from foo) for update; - f1 | f2 -----+---- - 1 | 11 - 2 | 22 - 3 | 33 - 4 | 44 -(4 rows) - -explain (verbose, costs off) -select * from bar where f1 in (select f1 from foo) for share; - QUERY PLAN ----------------------------------------------------------------------------------------------- - LockRows - Output: bar.f1, bar.f2, bar.ctid, foo.ctid, bar.*, bar.tableoid, foo.*, foo.tableoid - -> Hash Join - Output: bar.f1, bar.f2, bar.ctid, foo.ctid, bar.*, bar.tableoid, foo.*, foo.tableoid - Inner Unique: true - Hash Cond: (bar.f1 = foo.f1) - -> Append - -> Seq Scan on public.bar bar_1 - Output: bar_1.f1, bar_1.f2, bar_1.ctid, bar_1.*, bar_1.tableoid - -> Foreign Scan on public.bar2 bar_2 - Output: bar_2.f1, bar_2.f2, bar_2.ctid, bar_2.*, bar_2.tableoid - Remote SQL: SELECT f1, f2, f3, ctid FROM public.loct2 FOR SHARE - -> Hash - Output: foo.ctid, foo.f1, foo.*, foo.tableoid - -> HashAggregate - Output: foo.ctid, foo.f1, foo.*, foo.tableoid - Group Key: foo.f1 - -> Append - -> Seq Scan on public.foo foo_1 - Output: foo_1.ctid, foo_1.f1, foo_1.*, foo_1.tableoid - -> Foreign Scan on public.foo2 foo_2 - Output: foo_2.ctid, foo_2.f1, foo_2.*, foo_2.tableoid - Remote SQL: SELECT f1, f2, f3, ctid FROM public.loct1 -(23 rows) - -select * from bar where f1 in (select f1 from foo) for share; - f1 | f2 -----+---- - 1 | 11 - 2 | 22 - 3 | 33 - 4 | 44 -(4 rows) - --- 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'); -NOTICE: moving and merging column "f3" with inherited definition -DETAIL: User-specified column moved to the position of the inherited column. -explain (verbose, costs off) -select * from bar where f1 in (select f1 from foo2) for share; - QUERY PLAN --------------------------------------------------------------------------------------- - LockRows - Output: bar.f1, bar.f2, bar.ctid, foo2.*, bar.*, bar.tableoid, foo2.tableoid - -> Hash Join - Output: bar.f1, bar.f2, bar.ctid, foo2.*, bar.*, bar.tableoid, foo2.tableoid - Inner Unique: true - Hash Cond: (bar.f1 = foo2.f1) - -> Append - -> Seq Scan on public.bar bar_1 - Output: bar_1.f1, bar_1.f2, bar_1.ctid, bar_1.*, bar_1.tableoid - -> Foreign Scan on public.bar2 bar_2 - Output: bar_2.f1, bar_2.f2, bar_2.ctid, bar_2.*, bar_2.tableoid - Remote SQL: SELECT f1, f2, f3, ctid FROM public.loct2 FOR SHARE - -> Hash - Output: foo2.*, foo2.f1, foo2.tableoid - -> HashAggregate - Output: foo2.*, foo2.f1, foo2.tableoid - Group Key: foo2.f1 - -> Append - -> Foreign Scan on public.foo2 foo2_1 - Output: foo2_1.*, foo2_1.f1, foo2_1.tableoid - Remote SQL: SELECT f1, f2, f3 FROM public.loct1 - -> Foreign Scan on public.foo2child foo2_2 - Output: foo2_2.*, foo2_2.f1, foo2_2.tableoid - Remote SQL: SELECT f1, f2, f3 FROM public.loct4 -(24 rows) - -select * from bar where f1 in (select f1 from foo2) for share; - f1 | f2 -----+---- - 2 | 22 - 4 | 44 -(2 rows) - -drop foreign table foo2child; --- And with a local child relation of the foreign table parent -create table foo2child (f3 int) inherits (foo2); -NOTICE: moving and merging column "f3" with inherited definition -DETAIL: User-specified column moved to the position of the inherited column. -explain (verbose, costs off) -select * from bar where f1 in (select f1 from foo2) for share; - QUERY PLAN -------------------------------------------------------------------------------------------------- - LockRows - Output: bar.f1, bar.f2, bar.ctid, foo2.*, bar.*, bar.tableoid, foo2.ctid, foo2.tableoid - -> Hash Join - Output: bar.f1, bar.f2, bar.ctid, foo2.*, bar.*, bar.tableoid, foo2.ctid, foo2.tableoid - Inner Unique: true - Hash Cond: (bar.f1 = foo2.f1) - -> Append - -> Seq Scan on public.bar bar_1 - Output: bar_1.f1, bar_1.f2, bar_1.ctid, bar_1.*, bar_1.tableoid - -> Foreign Scan on public.bar2 bar_2 - Output: bar_2.f1, bar_2.f2, bar_2.ctid, bar_2.*, bar_2.tableoid - Remote SQL: SELECT f1, f2, f3, ctid FROM public.loct2 FOR SHARE - -> Hash - Output: foo2.*, foo2.f1, foo2.ctid, foo2.tableoid - -> HashAggregate - Output: foo2.*, foo2.f1, foo2.ctid, foo2.tableoid - Group Key: foo2.f1 - -> Append - -> Foreign Scan on public.foo2 foo2_1 - Output: foo2_1.*, foo2_1.f1, foo2_1.ctid, foo2_1.tableoid - Remote SQL: SELECT f1, f2, f3, ctid FROM public.loct1 - -> Seq Scan on public.foo2child foo2_2 - Output: foo2_2.*, foo2_2.f1, foo2_2.ctid, foo2_2.tableoid -(23 rows) - -select * from bar where f1 in (select f1 from foo2) for share; - f1 | f2 -----+---- - 2 | 22 - 4 | 44 -(2 rows) - -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); - QUERY PLAN -------------------------------------------------------------------------------------------------------- - Update on public.bar - Update on public.bar bar_1 - Foreign Update on public.bar2 bar_2 - Remote SQL: UPDATE public.loct2 SET f2 = $2 WHERE ctid = $1 - -> Hash Join - Output: (bar.f2 + 100), foo.ctid, bar.tableoid, bar.ctid, (NULL::record), foo.*, foo.tableoid - Inner Unique: true - Hash Cond: (bar.f1 = foo.f1) - -> Append - -> Seq Scan on public.bar bar_1 - Output: bar_1.f2, bar_1.f1, bar_1.tableoid, bar_1.ctid, NULL::record - -> Foreign Scan on public.bar2 bar_2 - Output: bar_2.f2, bar_2.f1, bar_2.tableoid, bar_2.ctid, bar_2.* - Remote SQL: SELECT f1, f2, f3, ctid FROM public.loct2 FOR UPDATE - -> Hash - Output: foo.ctid, foo.f1, foo.*, foo.tableoid - -> HashAggregate - Output: foo.ctid, foo.f1, foo.*, foo.tableoid - Group Key: foo.f1 - -> Append - -> Seq Scan on public.foo foo_1 - Output: foo_1.ctid, foo_1.f1, foo_1.*, foo_1.tableoid - -> Foreign Scan on public.foo2 foo_2 - Output: foo_2.ctid, foo_2.f1, foo_2.*, foo_2.tableoid - Remote SQL: SELECT f1, f2, f3, ctid FROM public.loct1 -(25 rows) - -update bar set f2 = f2 + 100 where f1 in (select f1 from foo); -select tableoid::regclass, * from bar order by 1,2; - tableoid | f1 | f2 -----------+----+----- - bar | 1 | 111 - bar | 2 | 122 - bar | 6 | 66 - bar2 | 3 | 133 - bar2 | 4 | 144 - bar2 | 7 | 77 -(6 rows) - --- 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; - QUERY PLAN ------------------------------------------------------------------------------------------------- - Update on public.bar - Update on public.bar bar_1 - Foreign Update on public.bar2 bar_2 - Remote SQL: UPDATE public.loct2 SET f2 = $2 WHERE ctid = $1 - -> Merge Join - Output: (bar.f2 + 100), (ROW(foo.f1)), bar.tableoid, bar.ctid, (NULL::record) - Merge Cond: (bar.f1 = foo.f1) - -> Sort - Output: bar.f2, bar.f1, bar.tableoid, bar.ctid, (NULL::record) - Sort Key: bar.f1 - -> Append - -> Seq Scan on public.bar bar_1 - Output: bar_1.f2, bar_1.f1, bar_1.tableoid, bar_1.ctid, NULL::record - -> Foreign Scan on public.bar2 bar_2 - Output: bar_2.f2, bar_2.f1, bar_2.tableoid, bar_2.ctid, bar_2.* - Remote SQL: SELECT f1, f2, f3, ctid FROM public.loct2 FOR UPDATE - -> Sort - Output: (ROW(foo.f1)), foo.f1 - Sort Key: foo.f1 - -> Append - -> Seq Scan on public.foo - Output: ROW(foo.f1), foo.f1 - -> Foreign Scan on public.foo2 foo_1 - Output: ROW(foo_1.f1), foo_1.f1 - Remote SQL: SELECT f1 FROM public.loct1 - -> Seq Scan on public.foo foo_2 - Output: ROW((foo_2.f1 + 3)), (foo_2.f1 + 3) - -> Foreign Scan on public.foo2 foo_3 - Output: ROW((foo_3.f1 + 3)), (foo_3.f1 + 3) - Remote SQL: SELECT f1 FROM public.loct1 -(30 rows) - -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; - tableoid | f1 | f2 -----------+----+----- - bar | 1 | 211 - bar | 2 | 222 - bar | 6 | 166 - bar2 | 3 | 233 - bar2 | 4 | 244 - bar2 | 7 | 177 -(6 rows) - --- Test forcing the remote server to produce sorted data for a merge join, --- but the foreign table is an inheritance child. -truncate table loct1; -truncate table only foo; -\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; -alter foreign table foo2 options (use_remote_estimate 'true'); -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; - QUERY PLAN --------------------------------------------------------------------------------------------------- - Limit - Output: foo.f1, loct1.f1, foo.f2 - -> Sort - Output: foo.f1, loct1.f1, foo.f2 - Sort Key: foo.f2 - -> Merge Join - Output: foo.f1, loct1.f1, foo.f2 - Merge Cond: (foo.f1 = loct1.f1) - -> Merge Append - Sort Key: foo.f1 - -> Index Scan using i_foo_f1 on public.foo foo_1 - Output: foo_1.f1, foo_1.f2 - -> Foreign Scan on public.foo2 foo_2 - Output: foo_2.f1, foo_2.f2 - Remote SQL: SELECT f1, f2 FROM public.loct1 ORDER BY f1 ASC NULLS LAST - -> Index Only Scan using i_loct1_f1 on public.loct1 - Output: loct1.f1 -(17 rows) - -select foo.f1, loct1.f1 from foo join loct1 on (foo.f1 = loct1.f1) order by foo.f2 offset 10 limit 10; - f1 | f1 -----+---- - 20 | 20 - 22 | 22 - 24 | 24 - 26 | 26 - 28 | 28 - 30 | 30 - 32 | 32 - 34 | 34 - 36 | 36 - 38 | 38 -(10 rows) - --- 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; - QUERY PLAN --------------------------------------------------------------------------------------------------- - Limit - Output: foo.f1, loct1.f1, foo.f2 - -> Sort - Output: foo.f1, loct1.f1, foo.f2 - Sort Key: foo.f2 - -> Merge Left Join - Output: foo.f1, loct1.f1, foo.f2 - Merge Cond: (foo.f1 = loct1.f1) - -> Merge Append - Sort Key: foo.f1 - -> Index Scan using i_foo_f1 on public.foo foo_1 - Output: foo_1.f1, foo_1.f2 - -> Foreign Scan on public.foo2 foo_2 - Output: foo_2.f1, foo_2.f2 - Remote SQL: SELECT f1, f2 FROM public.loct1 ORDER BY f1 ASC NULLS LAST - -> Index Only Scan using i_loct1_f1 on public.loct1 - Output: loct1.f1 -(17 rows) - -select foo.f1, loct1.f1 from foo left join loct1 on (foo.f1 = loct1.f1) order by foo.f2 offset 10 limit 10; - f1 | f1 -----+---- - 10 | 10 - 11 | - 12 | 12 - 13 | - 14 | 14 - 15 | - 16 | 16 - 17 | - 18 | 18 - 19 | -(10 rows) - -RESET enable_hashjoin; -RESET enable_nestloop; --- Test that WHERE CURRENT OF is not supported -begin; -declare c cursor for select * from bar where f1 = 7; -fetch from c; - f1 | f2 -----+----- - 7 | 177 -(1 row) - -update bar set f2 = null where current of c; -ERROR: WHERE CURRENT OF is not supported for this table type -rollback; -explain (verbose, costs off) -delete from foo where f1 < 5 returning *; - QUERY PLAN --------------------------------------------------------------------------------------- - Delete on public.foo - Output: foo_1.f1, foo_1.f2 - Delete on public.foo foo_1 - Foreign Delete on public.foo2 foo_2 - -> Append - -> Index Scan using i_foo_f1 on public.foo foo_1 - Output: foo_1.tableoid, foo_1.ctid - Index Cond: (foo_1.f1 < 5) - -> Foreign Delete on public.foo2 foo_2 - Remote SQL: DELETE FROM public.loct1 WHERE ((f1 < 5)) RETURNING f1, f2 -(10 rows) - -delete from foo where f1 < 5 returning *; - f1 | f2 -----+---- - 1 | 1 - 3 | 3 - 0 | 0 - 2 | 2 - 4 | 4 -(5 rows) - -explain (verbose, costs off) -update bar set f2 = f2 + 100 returning *; - QUERY PLAN ------------------------------------------------------------------------------------------- - Update on public.bar - Output: bar_1.f1, bar_1.f2 - Update on public.bar bar_1 - Foreign Update on public.bar2 bar_2 - -> Result - Output: (bar.f2 + 100), bar.tableoid, bar.ctid, (NULL::record) - -> Append - -> Seq Scan on public.bar bar_1 - Output: bar_1.f2, bar_1.tableoid, bar_1.ctid, NULL::record - -> Foreign Update on public.bar2 bar_2 - Remote SQL: UPDATE public.loct2 SET f2 = (f2 + 100) RETURNING f1, f2 -(11 rows) - -update bar set f2 = f2 + 100 returning *; - f1 | f2 -----+----- - 1 | 311 - 2 | 322 - 6 | 266 - 3 | 333 - 4 | 344 - 7 | 277 -(6 rows) - --- 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'); -explain (verbose, costs off) -update bar set f2 = f2 + 100; - QUERY PLAN --------------------------------------------------------------------------------------------------------- - Update on public.bar - Update on public.bar bar_1 - Foreign Update on public.bar2 bar_2 - Remote SQL: UPDATE public.loct2 SET f1 = $2, f2 = $3, f3 = $4 WHERE ctid = $1 RETURNING f1, f2, f3 - -> Result - Output: (bar.f2 + 100), bar.tableoid, bar.ctid, (NULL::record) - -> Append - -> Seq Scan on public.bar bar_1 - Output: bar_1.f2, bar_1.tableoid, bar_1.ctid, NULL::record - -> Foreign Scan on public.bar2 bar_2 - Output: bar_2.f2, bar_2.tableoid, bar_2.ctid, bar_2.* - Remote SQL: SELECT f1, f2, f3, ctid FROM public.loct2 FOR UPDATE -(12 rows) - -update bar set f2 = f2 + 100; -NOTICE: trig_row_before(23, skidoo) BEFORE ROW UPDATE ON bar2 -NOTICE: OLD: (3,333,33),NEW: (3,433,33) -NOTICE: trig_row_before(23, skidoo) BEFORE ROW UPDATE ON bar2 -NOTICE: OLD: (4,344,44),NEW: (4,444,44) -NOTICE: trig_row_before(23, skidoo) BEFORE ROW UPDATE ON bar2 -NOTICE: OLD: (7,277,77),NEW: (7,377,77) -NOTICE: trig_row_after(23, skidoo) AFTER ROW UPDATE ON bar2 -NOTICE: OLD: (3,333,33),NEW: (3,433,33) -NOTICE: trig_row_after(23, skidoo) AFTER ROW UPDATE ON bar2 -NOTICE: OLD: (4,344,44),NEW: (4,444,44) -NOTICE: trig_row_after(23, skidoo) AFTER ROW UPDATE ON bar2 -NOTICE: OLD: (7,277,77),NEW: (7,377,77) -explain (verbose, costs off) -delete from bar where f2 < 400; - QUERY PLAN ---------------------------------------------------------------------------------------------------- - Delete on public.bar - Delete on public.bar bar_1 - Foreign Delete on public.bar2 bar_2 - Remote SQL: DELETE FROM public.loct2 WHERE ctid = $1 RETURNING f1, f2, f3 - -> Append - -> Seq Scan on public.bar bar_1 - Output: bar_1.tableoid, bar_1.ctid, NULL::record - Filter: (bar_1.f2 < 400) - -> Foreign Scan on public.bar2 bar_2 - Output: bar_2.tableoid, bar_2.ctid, bar_2.* - Remote SQL: SELECT f1, f2, f3, ctid FROM public.loct2 WHERE ((f2 < 400)) FOR UPDATE -(11 rows) - -delete from bar where f2 < 400; -NOTICE: trig_row_before(23, skidoo) BEFORE ROW DELETE ON bar2 -NOTICE: OLD: (7,377,77) -NOTICE: trig_row_after(23, skidoo) AFTER ROW DELETE ON bar2 -NOTICE: OLD: (7,377,77) --- cleanup -drop table foo cascade; -NOTICE: drop cascades to foreign table foo2 -drop table bar cascade; -NOTICE: drop cascades to foreign table bar2 -drop table loct1; -drop table loct2; --- Test pushing down UPDATE/DELETE joins to the remote server -create table parent (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 loopback options (table_name 'loct1'); -create foreign table remt2 (a int, b text) - server loopback 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; -explain (verbose, costs off) -update parent set b = parent.b || remt2.b from remt2 where parent.a = remt2.a returning *; - QUERY PLAN ----------------------------------------------------------------------------------------------------------------- - Update on public.parent - Output: parent_1.a, parent_1.b, remt2.a, remt2.b - Update on public.parent parent_1 - Foreign Update on public.remt1 parent_2 - Remote SQL: UPDATE public.loct1 SET b = $2 WHERE ctid = $1 RETURNING a, b - -> Nested Loop - Output: (parent.b || remt2.b), remt2.*, remt2.a, remt2.b, parent.tableoid, parent.ctid, (NULL::record) - Join Filter: (parent.a = remt2.a) - -> Append - -> Seq Scan on public.parent parent_1 - Output: parent_1.b, parent_1.a, parent_1.tableoid, parent_1.ctid, NULL::record - -> Foreign Scan on public.remt1 parent_2 - Output: parent_2.b, parent_2.a, parent_2.tableoid, parent_2.ctid, parent_2.* - Remote SQL: SELECT a, b, ctid FROM public.loct1 FOR UPDATE - -> Materialize - Output: remt2.b, remt2.*, remt2.a - -> Foreign Scan on public.remt2 - Output: remt2.b, remt2.*, remt2.a - Remote SQL: SELECT a, b FROM public.loct2 -(19 rows) - -update parent set b = parent.b || remt2.b from remt2 where parent.a = remt2.a returning *; - a | b | a | b ----+--------+---+----- - 1 | foofoo | 1 | foo - 2 | barbar | 2 | bar -(2 rows) - -explain (verbose, costs off) -delete from parent using remt2 where parent.a = remt2.a returning parent; - QUERY PLAN ------------------------------------------------------------------------------ - Delete on public.parent - Output: parent_1.* - Delete on public.parent parent_1 - Foreign Delete on public.remt1 parent_2 - Remote SQL: DELETE FROM public.loct1 WHERE ctid = $1 RETURNING a, b - -> Nested Loop - Output: remt2.*, parent.tableoid, parent.ctid - Join Filter: (parent.a = remt2.a) - -> Append - -> Seq Scan on public.parent parent_1 - Output: parent_1.a, parent_1.tableoid, parent_1.ctid - -> Foreign Scan on public.remt1 parent_2 - Output: parent_2.a, parent_2.tableoid, parent_2.ctid - Remote SQL: SELECT a, ctid FROM public.loct1 FOR UPDATE - -> Materialize - Output: remt2.*, remt2.a - -> Foreign Scan on public.remt2 - Output: remt2.*, remt2.a - Remote SQL: SELECT a, b FROM public.loct2 -(19 rows) - -delete from parent using remt2 where parent.a = remt2.a returning parent; - parent ------------- - (1,foofoo) - (2,barbar) -(2 rows) - --- cleanup -drop foreign table remt1; -drop foreign table remt2; -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); -create table loct1 (a int check (a in (1)), b text); -create foreign table remp1 (a int check (a in (1)), b text) server loopback options (table_name 'loct1'); -create table loct2 (a int check (a in (2)), b text); -create foreign table remp2 (b text, a int check (a in (2))) server loopback 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 *; - a | b ----+----- - 1 | bar -(1 row) - -insert into itrtest values (2, 'baz'); -insert into itrtest values (2, 'qux') returning *; - a | b ----+----- - 2 | qux -(1 row) - -insert into itrtest values (1, 'test1'), (2, 'test2') returning *; - a | b ----+------- - 1 | test1 - 2 | test2 -(2 rows) - -select tableoid::regclass, * FROM itrtest; - tableoid | a | b -----------+---+------- - remp1 | 1 | foo - remp1 | 1 | bar - remp1 | 1 | test1 - remp2 | 2 | baz - remp2 | 2 | qux - remp2 | 2 | test2 -(6 rows) - -select tableoid::regclass, * FROM remp1; - tableoid | a | b -----------+---+------- - remp1 | 1 | foo - remp1 | 1 | bar - remp1 | 1 | test1 -(3 rows) - -select tableoid::regclass, * FROM remp2; - tableoid | b | a -----------+-------+--- - remp2 | baz | 2 - remp2 | qux | 2 - remp2 | test2 | 2 -(3 rows) - -delete from itrtest; --- MERGE ought to fail cleanly -merge into itrtest using (select 1, 'foo') as source on (true) - when matched then do nothing; -ERROR: cannot execute MERGE on relation "remp1" -DETAIL: This operation is not supported for foreign tables. -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 *; - a | b ----+----- - 1 | foo -(1 row) - -insert into itrtest values (1, 'foo') on conflict do nothing returning *; - a | b ----+--- -(0 rows) - --- But other cases are not supported -insert into itrtest values (1, 'bar') on conflict (a) do nothing; -ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification -insert into itrtest values (1, 'bar') on conflict (a) do update set b = excluded.b; -ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification -select tableoid::regclass, * FROM itrtest; - tableoid | a | b -----------+---+----- - remp1 | 1 | foo -(1 row) - -delete from itrtest; -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; -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 *; - a | b ----+----------------- - 1 | foo triggered ! -(1 row) - -insert into itrtest values (2, 'qux') returning *; - a | b ----+----------------- - 2 | qux triggered ! -(1 row) - -insert into itrtest values (1, 'test1'), (2, 'test2') returning *; - a | b ----+------------------- - 1 | test1 triggered ! - 2 | test2 triggered ! -(2 rows) - -with result as (insert into itrtest values (1, 'test1'), (2, 'test2') returning *) select * from result; - a | b ----+------------------- - 1 | test1 triggered ! - 2 | test2 triggered ! -(2 rows) - -drop trigger loct1_br_insert_trigger on loct1; -drop trigger loct2_br_insert_trigger on loct2; -drop table itrtest; -drop table loct1; -drop table loct2; --- Test update tuple routing -create table utrtest (a int, b text) partition by list (a); -create table loct (a int check (a in (1)), b text); -create foreign table remp (a int check (a in (1)), b text) server loopback 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; - tableoid | a | b -----------+---+----- - remp | 1 | foo - locp | 2 | qux -(2 rows) - -select tableoid::regclass, * FROM remp; - tableoid | a | b -----------+---+----- - remp | 1 | foo -(1 row) - -select tableoid::regclass, * FROM locp; - tableoid | a | b -----------+---+----- - locp | 2 | qux -(1 row) - --- 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 *; -ERROR: new row for relation "loct" violates check constraint "loct_a_check" -DETAIL: Failing row contains (2, foo). -CONTEXT: remote SQL command: UPDATE public.loct SET a = 2 WHERE ((b = 'foo')) RETURNING a, b --- But the reverse is allowed -update utrtest set a = 1 where b = 'qux' returning *; -ERROR: cannot route tuples into foreign table to be updated "remp" -select tableoid::regclass, * FROM utrtest; - tableoid | a | b -----------+---+----- - remp | 1 | foo - locp | 2 | qux -(2 rows) - -select tableoid::regclass, * FROM remp; - tableoid | a | b -----------+---+----- - remp | 1 | foo -(1 row) - -select tableoid::regclass, * FROM locp; - tableoid | a | b -----------+---+----- - locp | 2 | qux -(1 row) - --- 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 -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 *; - QUERY PLAN ----------------------------------------------------------------------------------------------------- - Update on public.utrtest - Output: utrtest_1.a, utrtest_1.b - Foreign Update on public.remp utrtest_1 - Update on public.locp utrtest_2 - -> Append - -> Foreign Update on public.remp utrtest_1 - Remote SQL: UPDATE public.loct SET a = 1 WHERE (((a = 1) OR (a = 2))) RETURNING a, b - -> Seq Scan on public.locp utrtest_2 - Output: 1, utrtest_2.tableoid, utrtest_2.ctid, NULL::record - Filter: ((utrtest_2.a = 1) OR (utrtest_2.a = 2)) -(10 rows) - --- The new values are concatenated with ' triggered !' -update utrtest set a = 1 where a = 1 or a = 2 returning *; -ERROR: cannot route tuples into foreign table to be updated "remp" -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 *; - QUERY PLAN -------------------------------------------------------- - Update on public.utrtest - Output: utrtest_1.a, utrtest_1.b - Update on public.locp utrtest_1 - -> Seq Scan on public.locp utrtest_1 - Output: 1, utrtest_1.tableoid, utrtest_1.ctid - Filter: (utrtest_1.a = 2) -(6 rows) - --- The new values are concatenated with ' triggered !' -update utrtest set a = 1 where a = 2 returning *; - a | b ----+----------------- - 1 | qux triggered ! -(1 row) - -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 *; - QUERY PLAN ---------------------------------------------------------------------------- - Update on public.utrtest - Output: utrtest_1.a, utrtest_1.b - Foreign Update on public.remp utrtest_1 - Update on public.locp utrtest_2 - -> Append - -> Foreign Update on public.remp utrtest_1 - Remote SQL: UPDATE public.loct SET a = 1 RETURNING a, b - -> Seq Scan on public.locp utrtest_2 - Output: 1, utrtest_2.tableoid, utrtest_2.ctid, NULL::record -(9 rows) - -update utrtest set a = 1 returning *; -ERROR: cannot route tuples into foreign table to be updated "remp" -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 *; - QUERY PLAN ------------------------------------------------------------------------------------------------- - Update on public.utrtest - Output: utrtest_1.a, utrtest_1.b, "*VALUES*".column1 - Foreign Update on public.remp utrtest_1 - Remote SQL: UPDATE public.loct SET a = $2 WHERE ctid = $1 RETURNING a, b - Update on public.locp utrtest_2 - -> Hash Join - Output: 1, "*VALUES*".*, "*VALUES*".column1, utrtest.tableoid, utrtest.ctid, utrtest.* - Hash Cond: (utrtest.a = "*VALUES*".column1) - -> Append - -> Foreign Scan on public.remp utrtest_1 - Output: utrtest_1.a, utrtest_1.tableoid, utrtest_1.ctid, utrtest_1.* - Remote SQL: SELECT a, b, ctid FROM public.loct FOR UPDATE - -> Seq Scan on public.locp utrtest_2 - Output: utrtest_2.a, utrtest_2.tableoid, utrtest_2.ctid, NULL::record - -> Hash - Output: "*VALUES*".*, "*VALUES*".column1 - -> Values Scan on "*VALUES*" - Output: "*VALUES*".*, "*VALUES*".column1 -(18 rows) - -update utrtest set a = 1 from (values (1), (2)) s(x) where a = s.x returning *; -ERROR: cannot route tuples into foreign table to be updated "remp" --- 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; -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 loopback 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 *; - QUERY PLAN ---------------------------------------------------------------------------- - Update on public.utrtest - Output: utrtest_1.a, utrtest_1.b - Update on public.locp utrtest_1 - Foreign Update on public.remp utrtest_2 - -> Append - -> Seq Scan on public.locp utrtest_1 - Output: 3, utrtest_1.tableoid, utrtest_1.ctid, NULL::record - -> Foreign Update on public.remp utrtest_2 - Remote SQL: UPDATE public.loct SET a = 3 RETURNING a, b -(9 rows) - -update utrtest set a = 3 returning *; -- ERROR -ERROR: cannot route tuples into foreign table to be updated "remp" --- 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 *; - QUERY PLAN ------------------------------------------------------------------------------------------------------ - Update on public.utrtest - Output: utrtest_1.a, utrtest_1.b, "*VALUES*".column1 - Update on public.locp utrtest_1 - Foreign Update on public.remp utrtest_2 - Remote SQL: UPDATE public.loct SET a = $2 WHERE ctid = $1 RETURNING a, b - -> Hash Join - Output: 3, "*VALUES*".*, "*VALUES*".column1, utrtest.tableoid, utrtest.ctid, (NULL::record) - Hash Cond: (utrtest.a = "*VALUES*".column1) - -> Append - -> Seq Scan on public.locp utrtest_1 - Output: utrtest_1.a, utrtest_1.tableoid, utrtest_1.ctid, NULL::record - -> Foreign Scan on public.remp utrtest_2 - Output: utrtest_2.a, utrtest_2.tableoid, utrtest_2.ctid, utrtest_2.* - Remote SQL: SELECT a, b, ctid FROM public.loct FOR UPDATE - -> Hash - Output: "*VALUES*".*, "*VALUES*".column1 - -> Values Scan on "*VALUES*" - Output: "*VALUES*".*, "*VALUES*".column1 -(18 rows) - -update utrtest set a = 3 from (values (2), (3)) s(x) where a = s.x returning *; -- ERROR -ERROR: cannot route tuples into foreign table to be updated "remp" -drop table utrtest; -drop table loct; --- Test copy tuple routing -create table ctrtest (a int, b text) partition by list (a); -create table loct1 (a int check (a in (1)), b text); -create foreign table remp1 (a int check (a in (1)), b text) server loopback options (table_name 'loct1'); -create table loct2 (a int check (a in (2)), b text); -create foreign table remp2 (b text, a int check (a in (2))) server loopback 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; -select tableoid::regclass, * FROM ctrtest; - tableoid | a | b -----------+---+----- - remp1 | 1 | foo - remp2 | 2 | qux -(2 rows) - -select tableoid::regclass, * FROM remp1; - tableoid | a | b -----------+---+----- - remp1 | 1 | foo -(1 row) - -select tableoid::regclass, * FROM remp2; - tableoid | b | a -----------+-----+--- - remp2 | qux | 2 -(1 row) - --- Copying into foreign partitions directly should work as well -copy remp1 from stdin; -select tableoid::regclass, * FROM remp1; - tableoid | a | b -----------+---+----- - remp1 | 1 | foo - remp1 | 1 | bar -(2 rows) - -delete from ctrtest; --- Test copy tuple routing with the batch_size option enabled -alter server loopback options (add batch_size '2'); -copy ctrtest from stdin; -select tableoid::regclass, * FROM ctrtest; - tableoid | a | b -----------+---+------- - remp1 | 1 | foo - remp1 | 1 | bar - remp1 | 1 | test1 - remp2 | 2 | baz - remp2 | 2 | qux - remp2 | 2 | test2 -(6 rows) - -select tableoid::regclass, * FROM remp1; - tableoid | a | b -----------+---+------- - remp1 | 1 | foo - remp1 | 1 | bar - remp1 | 1 | test1 -(3 rows) - -select tableoid::regclass, * FROM remp2; - tableoid | b | a -----------+-------+--- - remp2 | baz | 2 - remp2 | qux | 2 - remp2 | test2 | 2 -(3 rows) - -delete from ctrtest; -alter server loopback options (drop batch_size); -drop table ctrtest; -drop table loct1; -drop table loct2; --- =================================================================== --- test COPY FROM --- =================================================================== -create table loc2 (f1 int, f2 text); -alter table loc2 set (autovacuum_enabled = 'false'); -create foreign table rem2 (f1 int, f2 text) server loopback options(table_name 'loc2'); --- Test basic functionality -copy rem2 from stdin; -select * from rem2; - f1 | f2 -----+----- - 1 | foo - 2 | bar -(2 rows) - -delete from rem2; --- Test check constraints -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; -copy rem2 from stdin; -- ERROR -ERROR: new row for relation "loc2" violates check constraint "loc2_f1positive" -DETAIL: Failing row contains (-1, xyzzy). -CONTEXT: remote SQL command: INSERT INTO public.loc2(f1, f2) VALUES ($1, $2) -COPY rem2, line 1: "-1 xyzzy" -select * from rem2; - f1 | f2 -----+----- - 1 | foo - 2 | bar -(2 rows) - -alter foreign table rem2 drop constraint rem2_f1positive; -alter table loc2 drop constraint loc2_f1positive; -delete from rem2; --- 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; -NOTICE: trigger_func() called: action = INSERT, when = BEFORE, level = STATEMENT -NOTICE: trig_row_before(23, skidoo) BEFORE ROW INSERT ON rem2 -NOTICE: NEW: (1,foo) -NOTICE: trig_row_before(23, skidoo) BEFORE ROW INSERT ON rem2 -NOTICE: NEW: (2,bar) -NOTICE: trig_row_after(23, skidoo) AFTER ROW INSERT ON rem2 -NOTICE: NEW: (1,foo) -NOTICE: trig_row_after(23, skidoo) AFTER ROW INSERT ON rem2 -NOTICE: NEW: (2,bar) -NOTICE: trigger_func() called: action = INSERT, when = AFTER, level = STATEMENT -select * from rem2; - f1 | f2 -----+----- - 1 | foo - 2 | bar -(2 rows) - -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; -select * from rem2; - f1 | f2 -----+----------------- - 1 | foo triggered ! - 2 | bar triggered ! -(2 rows) - -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; -select * from rem2; - f1 | f2 -----+---- -(0 rows) - -drop trigger trig_null on rem2; -delete from rem2; --- Test remote triggers -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; -select * from rem2; - f1 | f2 -----+----------------- - 1 | foo triggered ! - 2 | bar triggered ! -(2 rows) - -drop trigger trig_row_before_insert on loc2; -delete from rem2; -create trigger trig_null before insert on loc2 - for each row execute procedure trig_null(); --- Nothing happens -copy rem2 from stdin; -select * from rem2; - f1 | f2 -----+---- -(0 rows) - -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'); -create trigger loc2_trig_row_before_insert before insert on loc2 - for each row execute procedure trig_row_before_insupdate(); -copy rem2 from stdin; -NOTICE: rem2_trig_row_before(23, skidoo) BEFORE ROW INSERT ON rem2 -NOTICE: NEW: (1,foo) -NOTICE: rem2_trig_row_before(23, skidoo) BEFORE ROW INSERT ON rem2 -NOTICE: NEW: (2,bar) -NOTICE: rem2_trig_row_after(23, skidoo) AFTER ROW INSERT ON rem2 -NOTICE: NEW: (1,"foo triggered !") -NOTICE: rem2_trig_row_after(23, skidoo) AFTER ROW INSERT ON rem2 -NOTICE: NEW: (2,"bar triggered !") -select * from rem2; - f1 | f2 -----+----------------- - 1 | foo triggered ! - 2 | bar triggered ! -(2 rows) - -drop trigger rem2_trig_row_before on rem2; -drop trigger rem2_trig_row_after on rem2; -drop trigger loc2_trig_row_before_insert on loc2; -delete from rem2; --- test COPY FROM with foreign table created in the same transaction -create table loc3 (f1 int, f2 text); -begin; -create foreign table rem3 (f1 int, f2 text) - server loopback options(table_name 'loc3'); -copy rem3 from stdin; -commit; -select * from rem3; - f1 | f2 -----+----- - 1 | foo - 2 | bar -(2 rows) - -drop foreign table rem3; -drop table loc3; --- Test COPY FROM with the batch_size option enabled -alter server loopback options (add batch_size '2'); --- Test basic functionality -copy rem2 from stdin; -select * from rem2; - f1 | f2 -----+----- - 1 | foo - 2 | bar - 3 | baz -(3 rows) - -delete from rem2; --- Test check constraints -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; -copy rem2 from stdin; -- ERROR -ERROR: new row for relation "loc2" violates check constraint "loc2_f1positive" -DETAIL: Failing row contains (-1, xyzzy). -CONTEXT: remote SQL command: INSERT INTO public.loc2(f1, f2) VALUES ($1, $2) -COPY rem2 -select * from rem2; - f1 | f2 -----+----- - 1 | foo - 2 | bar - 3 | baz -(3 rows) - -alter foreign table rem2 drop constraint rem2_f1positive; -alter table loc2 drop constraint loc2_f1positive; -delete from rem2; --- Test remote triggers -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; -select * from rem2; - f1 | f2 -----+----------------- - 1 | foo triggered ! - 2 | bar triggered ! - 3 | baz triggered ! -(3 rows) - -drop trigger trig_row_before_insert on loc2; -delete from rem2; -create trigger trig_null before insert on loc2 - for each row execute procedure trig_null(); --- Nothing happens -copy rem2 from stdin; -select * from rem2; - f1 | f2 -----+---- -(0 rows) - -drop trigger trig_null on loc2; -delete from rem2; --- Check with zero-column foreign table; batch insert will be disabled -alter table loc2 drop column f1; -alter table loc2 drop column f2; -alter table rem2 drop column f1; -alter table rem2 drop column f2; -copy rem2 from stdin; -select * from rem2; --- -(3 rows) - -delete from rem2; -alter server loopback options (drop batch_size); --- =================================================================== --- test for TRUNCATE --- =================================================================== -CREATE TABLE tru_rtable0 (id int primary key); -CREATE FOREIGN TABLE tru_ftable (id int) - SERVER loopback OPTIONS (table_name 'tru_rtable0'); -INSERT INTO tru_rtable0 (SELECT x FROM generate_series(1,10) x); -CREATE TABLE tru_ptable (id int) PARTITION BY HASH(id); -CREATE TABLE tru_ptable__p0 PARTITION OF tru_ptable - FOR VALUES WITH (MODULUS 2, REMAINDER 0); -CREATE TABLE tru_rtable1 (id int primary key); -CREATE FOREIGN TABLE tru_ftable__p1 PARTITION OF tru_ptable - FOR VALUES WITH (MODULUS 2, REMAINDER 1) - SERVER loopback OPTIONS (table_name 'tru_rtable1'); -INSERT INTO tru_ptable (SELECT x FROM generate_series(11,20) x); -CREATE TABLE tru_pk_table(id int primary key); -CREATE TABLE tru_fk_table(fkey int references tru_pk_table(id)); -INSERT INTO tru_pk_table (SELECT x FROM generate_series(1,10) x); -INSERT INTO tru_fk_table (SELECT x % 10 + 1 FROM generate_series(5,25) x); -CREATE FOREIGN TABLE tru_pk_ftable (id int) - SERVER loopback OPTIONS (table_name 'tru_pk_table'); -CREATE TABLE tru_rtable_parent (id int); -CREATE TABLE tru_rtable_child (id int); -CREATE FOREIGN TABLE tru_ftable_parent (id int) - SERVER loopback OPTIONS (table_name 'tru_rtable_parent'); -CREATE FOREIGN TABLE tru_ftable_child () INHERITS (tru_ftable_parent) - SERVER loopback OPTIONS (table_name 'tru_rtable_child'); -INSERT INTO tru_rtable_parent (SELECT x FROM generate_series(1,8) x); -INSERT INTO tru_rtable_child (SELECT x FROM generate_series(10, 18) x); --- normal truncate -SELECT sum(id) FROM tru_ftable; -- 55 - sum ------ - 55 -(1 row) - -TRUNCATE tru_ftable; -SELECT count(*) FROM tru_rtable0; -- 0 - count -------- - 0 -(1 row) - -SELECT count(*) FROM tru_ftable; -- 0 - count -------- - 0 -(1 row) - --- 'truncatable' option -ALTER SERVER loopback OPTIONS (ADD truncatable 'false'); -TRUNCATE tru_ftable; -- error -ERROR: foreign table "tru_ftable" does not allow truncates -ALTER FOREIGN TABLE tru_ftable OPTIONS (ADD truncatable 'true'); -TRUNCATE tru_ftable; -- accepted -ALTER FOREIGN TABLE tru_ftable OPTIONS (SET truncatable 'false'); -TRUNCATE tru_ftable; -- error -ERROR: foreign table "tru_ftable" does not allow truncates -ALTER SERVER loopback OPTIONS (DROP truncatable); -ALTER FOREIGN TABLE tru_ftable OPTIONS (SET truncatable 'false'); -TRUNCATE tru_ftable; -- error -ERROR: foreign table "tru_ftable" does not allow truncates -ALTER FOREIGN TABLE tru_ftable OPTIONS (SET truncatable 'true'); -TRUNCATE tru_ftable; -- accepted --- partitioned table with both local and foreign tables as partitions -SELECT sum(id) FROM tru_ptable; -- 155 - sum ------ - 155 -(1 row) - -TRUNCATE tru_ptable; -SELECT count(*) FROM tru_ptable; -- 0 - count -------- - 0 -(1 row) - -SELECT count(*) FROM tru_ptable__p0; -- 0 - count -------- - 0 -(1 row) - -SELECT count(*) FROM tru_ftable__p1; -- 0 - count -------- - 0 -(1 row) - -SELECT count(*) FROM tru_rtable1; -- 0 - count -------- - 0 -(1 row) - --- 'CASCADE' option -SELECT sum(id) FROM tru_pk_ftable; -- 55 - sum ------ - 55 -(1 row) - -TRUNCATE tru_pk_ftable; -- failed by FK reference -ERROR: cannot truncate a table referenced in a foreign key constraint -DETAIL: Table "tru_fk_table" references "tru_pk_table". -HINT: Truncate table "tru_fk_table" at the same time, or use TRUNCATE ... CASCADE. -CONTEXT: remote SQL command: TRUNCATE public.tru_pk_table CONTINUE IDENTITY RESTRICT -TRUNCATE tru_pk_ftable CASCADE; -SELECT count(*) FROM tru_pk_ftable; -- 0 - count -------- - 0 -(1 row) - -SELECT count(*) FROM tru_fk_table; -- also truncated,0 - count -------- - 0 -(1 row) - --- truncate two tables at a command -INSERT INTO tru_ftable (SELECT x FROM generate_series(1,8) x); -INSERT INTO tru_pk_ftable (SELECT x FROM generate_series(3,10) x); -SELECT count(*) from tru_ftable; -- 8 - count -------- - 8 -(1 row) - -SELECT count(*) from tru_pk_ftable; -- 8 - count -------- - 8 -(1 row) - -TRUNCATE tru_ftable, tru_pk_ftable CASCADE; -SELECT count(*) from tru_ftable; -- 0 - count -------- - 0 -(1 row) - -SELECT count(*) from tru_pk_ftable; -- 0 - count -------- - 0 -(1 row) - --- truncate with ONLY clause --- Since ONLY is specified, the table tru_ftable_child that inherits --- tru_ftable_parent locally is not truncated. -TRUNCATE ONLY tru_ftable_parent; -SELECT sum(id) FROM tru_ftable_parent; -- 126 - sum ------ - 126 -(1 row) - -TRUNCATE tru_ftable_parent; -SELECT count(*) FROM tru_ftable_parent; -- 0 - count -------- - 0 -(1 row) - --- in case when remote table has inherited children -CREATE TABLE tru_rtable0_child () INHERITS (tru_rtable0); -INSERT INTO tru_rtable0 (SELECT x FROM generate_series(5,9) x); -INSERT INTO tru_rtable0_child (SELECT x FROM generate_series(10,14) x); -SELECT sum(id) FROM tru_ftable; -- 95 - sum ------ - 95 -(1 row) - --- Both parent and child tables in the foreign server are truncated --- even though ONLY is specified because ONLY has no effect --- when truncating a foreign table. -TRUNCATE ONLY tru_ftable; -SELECT count(*) FROM tru_ftable; -- 0 - count -------- - 0 -(1 row) - -INSERT INTO tru_rtable0 (SELECT x FROM generate_series(21,25) x); -INSERT INTO tru_rtable0_child (SELECT x FROM generate_series(26,30) x); -SELECT sum(id) FROM tru_ftable; -- 255 - sum ------ - 255 -(1 row) - -TRUNCATE tru_ftable; -- truncate both of parent and child -SELECT count(*) FROM tru_ftable; -- 0 - count -------- - 0 -(1 row) - --- cleanup -DROP FOREIGN TABLE tru_ftable_parent, tru_ftable_child, tru_pk_ftable,tru_ftable__p1,tru_ftable; -DROP TABLE tru_rtable0, tru_rtable1, tru_ptable, tru_ptable__p0, tru_pk_table, tru_fk_table, -tru_rtable_parent,tru_rtable_child, tru_rtable0_child; --- =================================================================== --- test IMPORT FOREIGN SCHEMA --- =================================================================== -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 TABLE import_source.t4_part2 PARTITION OF import_source.t4 - FOR VALUES FROM (100) TO (200); -CREATE SCHEMA import_dest1; -IMPORT FOREIGN SCHEMA import_source FROM SERVER loopback INTO import_dest1; -\det+ import_dest1.* - List of foreign tables - Schema | Table | Server | FDW options | Description ---------------+-------+----------+-------------------------------------------------+------------- - import_dest1 | t1 | loopback | (schema_name 'import_source', table_name 't1') | - import_dest1 | t2 | loopback | (schema_name 'import_source', table_name 't2') | - import_dest1 | t3 | loopback | (schema_name 'import_source', table_name 't3') | - import_dest1 | t4 | loopback | (schema_name 'import_source', table_name 't4') | - import_dest1 | x 4 | loopback | (schema_name 'import_source', table_name 'x 4') | - import_dest1 | x 5 | loopback | (schema_name 'import_source', table_name 'x 5') | - import_dest1 | x 6 | loopback | (schema_name 'import_source', table_name 'x 6') | -(7 rows) - -\d import_dest1.* - Foreign table "import_dest1.t1" - Column | Type | Collation | Nullable | Default | FDW options ---------+-------------------+-----------+----------+---------+-------------------- - c1 | integer | | | | (column_name 'c1') - c2 | character varying | | not null | | (column_name 'c2') -Server: loopback -FDW options: (schema_name 'import_source', table_name 't1') - - Foreign table "import_dest1.t2" - Column | Type | Collation | Nullable | Default | FDW options ---------+-------------------+-----------+----------+---------+-------------------- - c1 | integer | | | | (column_name 'c1') - c2 | character varying | | | | (column_name 'c2') - c3 | text | POSIX | | | (column_name 'c3') -Server: loopback -FDW options: (schema_name 'import_source', table_name 't2') - - Foreign table "import_dest1.t3" - Column | Type | Collation | Nullable | Default | FDW options ---------+--------------------------+-----------+----------+---------+-------------------- - c1 | timestamp with time zone | | | | (column_name 'c1') - c2 | typ1 | | | | (column_name 'c2') -Server: loopback -FDW options: (schema_name 'import_source', table_name 't3') - - Foreign table "import_dest1.t4" - Column | Type | Collation | Nullable | Default | FDW options ---------+---------+-----------+----------+---------+-------------------- - c1 | integer | | | | (column_name 'c1') -Server: loopback -FDW options: (schema_name 'import_source', table_name 't4') - - Foreign table "import_dest1.x 4" - Column | Type | Collation | Nullable | Default | FDW options ---------+-----------------------+-----------+----------+---------+--------------------- - c1 | double precision | | | | (column_name 'c1') - C 2 | text | | | | (column_name 'C 2') - c3 | character varying(42) | | | | (column_name 'c3') -Server: loopback -FDW options: (schema_name 'import_source', table_name 'x 4') - - Foreign table "import_dest1.x 5" - Column | Type | Collation | Nullable | Default | FDW options ---------+------+-----------+----------+---------+------------- -Server: loopback -FDW options: (schema_name 'import_source', table_name 'x 5') - - Foreign table "import_dest1.x 6" - Column | Type | Collation | Nullable | Default | FDW options ---------+---------+-----------+----------+-------------------------------------+-------------------- - c1 | integer | | | | (column_name 'c1') - c2 | integer | | | generated always as (c1 * 2) stored | (column_name 'c2') -Server: loopback -FDW options: (schema_name 'import_source', table_name 'x 6') - --- Options -CREATE SCHEMA import_dest2; -IMPORT FOREIGN SCHEMA import_source FROM SERVER loopback INTO import_dest2 - OPTIONS (import_default 'true'); -\det+ import_dest2.* - List of foreign tables - Schema | Table | Server | FDW options | Description ---------------+-------+----------+-------------------------------------------------+------------- - import_dest2 | t1 | loopback | (schema_name 'import_source', table_name 't1') | - import_dest2 | t2 | loopback | (schema_name 'import_source', table_name 't2') | - import_dest2 | t3 | loopback | (schema_name 'import_source', table_name 't3') | - import_dest2 | t4 | loopback | (schema_name 'import_source', table_name 't4') | - import_dest2 | x 4 | loopback | (schema_name 'import_source', table_name 'x 4') | - import_dest2 | x 5 | loopback | (schema_name 'import_source', table_name 'x 5') | - import_dest2 | x 6 | loopback | (schema_name 'import_source', table_name 'x 6') | -(7 rows) - -\d import_dest2.* - Foreign table "import_dest2.t1" - Column | Type | Collation | Nullable | Default | FDW options ---------+-------------------+-----------+----------+---------+-------------------- - c1 | integer | | | | (column_name 'c1') - c2 | character varying | | not null | | (column_name 'c2') -Server: loopback -FDW options: (schema_name 'import_source', table_name 't1') - - Foreign table "import_dest2.t2" - Column | Type | Collation | Nullable | Default | FDW options ---------+-------------------+-----------+----------+---------+-------------------- - c1 | integer | | | 42 | (column_name 'c1') - c2 | character varying | | | | (column_name 'c2') - c3 | text | POSIX | | | (column_name 'c3') -Server: loopback -FDW options: (schema_name 'import_source', table_name 't2') - - Foreign table "import_dest2.t3" - Column | Type | Collation | Nullable | Default | FDW options ---------+--------------------------+-----------+----------+---------+-------------------- - c1 | timestamp with time zone | | | now() | (column_name 'c1') - c2 | typ1 | | | | (column_name 'c2') -Server: loopback -FDW options: (schema_name 'import_source', table_name 't3') - - Foreign table "import_dest2.t4" - Column | Type | Collation | Nullable | Default | FDW options ---------+---------+-----------+----------+---------+-------------------- - c1 | integer | | | | (column_name 'c1') -Server: loopback -FDW options: (schema_name 'import_source', table_name 't4') - - Foreign table "import_dest2.x 4" - Column | Type | Collation | Nullable | Default | FDW options ---------+-----------------------+-----------+----------+---------+--------------------- - c1 | double precision | | | | (column_name 'c1') - C 2 | text | | | | (column_name 'C 2') - c3 | character varying(42) | | | | (column_name 'c3') -Server: loopback -FDW options: (schema_name 'import_source', table_name 'x 4') - - Foreign table "import_dest2.x 5" - Column | Type | Collation | Nullable | Default | FDW options ---------+------+-----------+----------+---------+------------- -Server: loopback -FDW options: (schema_name 'import_source', table_name 'x 5') - - Foreign table "import_dest2.x 6" - Column | Type | Collation | Nullable | Default | FDW options ---------+---------+-----------+----------+-------------------------------------+-------------------- - c1 | integer | | | | (column_name 'c1') - c2 | integer | | | generated always as (c1 * 2) stored | (column_name 'c2') -Server: loopback -FDW options: (schema_name 'import_source', table_name 'x 6') - -CREATE SCHEMA import_dest3; -IMPORT FOREIGN SCHEMA import_source FROM SERVER loopback INTO import_dest3 - OPTIONS (import_collate 'false', import_generated 'false', import_not_null 'false'); -\det+ import_dest3.* - List of foreign tables - Schema | Table | Server | FDW options | Description ---------------+-------+----------+-------------------------------------------------+------------- - import_dest3 | t1 | loopback | (schema_name 'import_source', table_name 't1') | - import_dest3 | t2 | loopback | (schema_name 'import_source', table_name 't2') | - import_dest3 | t3 | loopback | (schema_name 'import_source', table_name 't3') | - import_dest3 | t4 | loopback | (schema_name 'import_source', table_name 't4') | - import_dest3 | x 4 | loopback | (schema_name 'import_source', table_name 'x 4') | - import_dest3 | x 5 | loopback | (schema_name 'import_source', table_name 'x 5') | - import_dest3 | x 6 | loopback | (schema_name 'import_source', table_name 'x 6') | -(7 rows) - -\d import_dest3.* - Foreign table "import_dest3.t1" - Column | Type | Collation | Nullable | Default | FDW options ---------+-------------------+-----------+----------+---------+-------------------- - c1 | integer | | | | (column_name 'c1') - c2 | character varying | | | | (column_name 'c2') -Server: loopback -FDW options: (schema_name 'import_source', table_name 't1') - - Foreign table "import_dest3.t2" - Column | Type | Collation | Nullable | Default | FDW options ---------+-------------------+-----------+----------+---------+-------------------- - c1 | integer | | | | (column_name 'c1') - c2 | character varying | | | | (column_name 'c2') - c3 | text | | | | (column_name 'c3') -Server: loopback -FDW options: (schema_name 'import_source', table_name 't2') - - Foreign table "import_dest3.t3" - Column | Type | Collation | Nullable | Default | FDW options ---------+--------------------------+-----------+----------+---------+-------------------- - c1 | timestamp with time zone | | | | (column_name 'c1') - c2 | typ1 | | | | (column_name 'c2') -Server: loopback -FDW options: (schema_name 'import_source', table_name 't3') - - Foreign table "import_dest3.t4" - Column | Type | Collation | Nullable | Default | FDW options ---------+---------+-----------+----------+---------+-------------------- - c1 | integer | | | | (column_name 'c1') -Server: loopback -FDW options: (schema_name 'import_source', table_name 't4') - - Foreign table "import_dest3.x 4" - Column | Type | Collation | Nullable | Default | FDW options ---------+-----------------------+-----------+----------+---------+--------------------- - c1 | double precision | | | | (column_name 'c1') - C 2 | text | | | | (column_name 'C 2') - c3 | character varying(42) | | | | (column_name 'c3') -Server: loopback -FDW options: (schema_name 'import_source', table_name 'x 4') - - Foreign table "import_dest3.x 5" - Column | Type | Collation | Nullable | Default | FDW options ---------+------+-----------+----------+---------+------------- -Server: loopback -FDW options: (schema_name 'import_source', table_name 'x 5') - - Foreign table "import_dest3.x 6" - Column | Type | Collation | Nullable | Default | FDW options ---------+---------+-----------+----------+---------+-------------------- - c1 | integer | | | | (column_name 'c1') - c2 | integer | | | | (column_name 'c2') -Server: loopback -FDW options: (schema_name 'import_source', table_name 'x 6') - --- Check LIMIT TO and EXCEPT -CREATE SCHEMA import_dest4; -IMPORT FOREIGN SCHEMA import_source LIMIT TO (t1, nonesuch, t4_part) - FROM SERVER loopback INTO import_dest4; -\det+ import_dest4.* - List of foreign tables - Schema | Table | Server | FDW options | Description ---------------+---------+----------+-----------------------------------------------------+------------- - import_dest4 | t1 | loopback | (schema_name 'import_source', table_name 't1') | - import_dest4 | t4_part | loopback | (schema_name 'import_source', table_name 't4_part') | -(2 rows) - -IMPORT FOREIGN SCHEMA import_source EXCEPT (t1, "x 4", nonesuch, t4_part) - FROM SERVER loopback INTO import_dest4; -\det+ import_dest4.* - List of foreign tables - Schema | Table | Server | FDW options | Description ---------------+---------+----------+-----------------------------------------------------+------------- - import_dest4 | t1 | loopback | (schema_name 'import_source', table_name 't1') | - import_dest4 | t2 | loopback | (schema_name 'import_source', table_name 't2') | - import_dest4 | t3 | loopback | (schema_name 'import_source', table_name 't3') | - import_dest4 | t4 | loopback | (schema_name 'import_source', table_name 't4') | - import_dest4 | t4_part | loopback | (schema_name 'import_source', table_name 't4_part') | - import_dest4 | x 5 | loopback | (schema_name 'import_source', table_name 'x 5') | - import_dest4 | x 6 | loopback | (schema_name 'import_source', table_name 'x 6') | -(7 rows) - --- Assorted error cases -IMPORT FOREIGN SCHEMA import_source FROM SERVER loopback INTO import_dest4; -ERROR: relation "t1" already exists -CONTEXT: importing foreign table "t1" -IMPORT FOREIGN SCHEMA nonesuch FROM SERVER loopback INTO import_dest4; -ERROR: schema "nonesuch" is not present on foreign server "loopback" -IMPORT FOREIGN SCHEMA nonesuch FROM SERVER loopback INTO notthere; -ERROR: schema "notthere" does not exist -IMPORT FOREIGN SCHEMA nonesuch FROM SERVER nowhere INTO notthere; -ERROR: server "nowhere" does not exist --- Check case of a type present only on the remote server. --- We can fake this by dropping the type locally in our transaction. -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; -NOTICE: drop cascades to column Col of table import_source.t5 -IMPORT FOREIGN SCHEMA import_source LIMIT TO (t5) - FROM SERVER loopback INTO import_dest5; -- ERROR -ERROR: type "public.Colors" does not exist -LINE 4: "Col" public."Colors" OPTIONS (column_name 'Col') - ^ -QUERY: CREATE FOREIGN TABLE t5 ( - c1 integer OPTIONS (column_name 'c1'), - c2 text OPTIONS (column_name 'c2') COLLATE pg_catalog."C", - "Col" public."Colors" OPTIONS (column_name 'Col') -) SERVER loopback -OPTIONS (schema_name 'import_source', table_name 't5'); -CONTEXT: importing foreign table "t5" -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']; - count -------- - 1 -(1 row) - -ALTER SERVER fetch101 OPTIONS( SET fetch_size '202' ); -SELECT count(*) -FROM pg_foreign_server -WHERE srvname = 'fetch101' -AND srvoptions @> array['fetch_size=101']; - count -------- - 0 -(1 row) - -SELECT count(*) -FROM pg_foreign_server -WHERE srvname = 'fetch101' -AND srvoptions @> array['fetch_size=202']; - count -------- - 1 -(1 row) - -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']; - count -------- - 1 -(1 row) - -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']; - count -------- - 0 -(1 row) - -SELECT COUNT(*) -FROM pg_foreign_table -WHERE ftrelid = 'table30000'::regclass -AND ftoptions @> array['fetch_size=60000']; - count -------- - 1 -(1 row) - -ROLLBACK; --- =================================================================== --- test partitionwise joins --- =================================================================== -SET enable_partitionwise_join=on; -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 loopback OPTIONS (table_name 'fprt1_p1', use_remote_estimate 'true'); -CREATE FOREIGN TABLE ftprt1_p2 PARTITION OF fprt1 FOR VALUES FROM (250) TO (500) - SERVER loopback OPTIONS (TABLE_NAME 'fprt1_p2'); -ANALYZE fprt1; -ANALYZE fprt1_p1; -ANALYZE fprt1_p2; -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 loopback 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 loopback OPTIONS (table_name 'fprt2_p2', use_remote_estimate 'true'); -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; - QUERY PLAN ------------------------------------------------------------------------------------------------------ - Sort - Sort Key: t1.a, t3.c - -> Append - -> Foreign Scan - Relations: ((ftprt1_p1 t1_1) INNER JOIN (ftprt2_p1 t2_1)) INNER JOIN (ftprt1_p1 t3_1) - -> Foreign Scan - Relations: ((ftprt1_p2 t1_2) INNER JOIN (ftprt2_p2 t2_2)) INNER JOIN (ftprt1_p2 t3_2) -(7 rows) - -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; - a | b | c ------+-----+------ - 0 | 0 | 0000 - 150 | 150 | 0003 - 250 | 250 | 0005 - 400 | 400 | 0008 -(4 rows) - --- 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; - QUERY PLAN ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- - Foreign Scan - Output: t1.a, fprt2.b, fprt2.c - Relations: (public.ftprt1_p1 t1) LEFT JOIN (public.ftprt2_p1 fprt2) - Remote SQL: SELECT r5.a, r6.b, r6.c FROM (public.fprt1_p1 r5 LEFT JOIN public.fprt2_p1 r6 ON (((r5.a = r6.b)) AND ((r5.b = r6.a)) AND ((r6.a < 10)))) WHERE ((r5.a < 10)) ORDER BY r5.a ASC NULLS LAST, r6.b ASC NULLS LAST, r6.c ASC NULLS LAST -(4 rows) - -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; - a | b | c ----+---+------ - 0 | 0 | 0000 - 2 | | - 4 | | - 6 | 6 | 0000 - 8 | | -(5 rows) - --- 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; - QUERY PLAN --------------------------------------------------------- - Sort - Sort Key: ((t1.*)::fprt1), ((t2.*)::fprt2) - -> Hash Full Join - Hash Cond: (t1.a = t2.b) - -> Append - -> Foreign Scan on ftprt1_p1 t1_1 - -> Foreign Scan on ftprt1_p2 t1_2 - -> Hash - -> Append - -> Foreign Scan on ftprt2_p1 t2_1 - -> Foreign Scan on ftprt2_p2 t2_2 -(11 rows) - -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; - wr | wr -----------------+---------------- - (0,0,0000) | (0,0,0000) - (50,50,0001) | - (100,100,0002) | - (150,150,0003) | (150,150,0003) - (200,200,0004) | - (250,250,0005) | (250,250,0005) - (300,300,0006) | - (350,350,0007) | - (400,400,0008) | (400,400,0008) - (450,450,0009) | - | (75,75,0001) - | (225,225,0004) - | (325,325,0006) - | (475,475,0009) -(14 rows) - --- 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; - QUERY PLAN ------------------------------------------------------------------------ - Sort - Sort Key: t1.a, t1.b - -> Append - -> Foreign Scan - Relations: (ftprt1_p1 t1_1) INNER JOIN (ftprt2_p1 t2_1) - -> Foreign Scan - Relations: (ftprt1_p2 t1_2) INNER JOIN (ftprt2_p2 t2_2) -(7 rows) - -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; - a | b ------+----- - 0 | 0 - 150 | 150 - 250 | 250 - 400 | 400 -(4 rows) - --- 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; - QUERY PLAN ------------------------------------------------------------ - Sort - Sort Key: fprt1.a, fprt2.b - -> Append - -> Hash Full Join - Hash Cond: (fprt1_1.a = fprt2_1.b) - -> Foreign Scan on ftprt1_p1 fprt1_1 - -> Hash - -> Foreign Scan on ftprt2_p1 fprt2_1 - -> Hash Full Join - Hash Cond: (fprt1_2.a = fprt2_2.b) - -> Foreign Scan on ftprt1_p2 fprt1_2 - -> Hash - -> Foreign Scan on ftprt2_p2 fprt2_2 -(13 rows) - -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; - a | phv | b | phv ------+--------+-----+-------- - 0 | t1_phv | 0 | t2_phv - 50 | t1_phv | | - 100 | t1_phv | | - 150 | t1_phv | 150 | t2_phv - 200 | t1_phv | | - 250 | t1_phv | 250 | t2_phv - 300 | t1_phv | | - 350 | t1_phv | | - 400 | t1_phv | 400 | t2_phv - 450 | t1_phv | | - | | 75 | t2_phv - | | 225 | t2_phv - | | 325 | t2_phv - | | 475 | t2_phv -(14 rows) - --- 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; - QUERY PLAN --------------------------------------------------------- - LockRows - -> Nested Loop - Join Filter: (t1.a = t2.b) - -> Append - -> Foreign Scan on ftprt1_p1 t1_1 - -> Foreign Scan on ftprt1_p2 t1_2 - -> Materialize - -> Append - -> Foreign Scan on ftprt2_p1 t2_1 - -> Foreign Scan on ftprt2_p2 t2_2 -(10 rows) - -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; - a | b ------+----- - 0 | 0 - 150 | 150 - 250 | 250 - 400 | 400 -(4 rows) - -RESET enable_partitionwise_join; --- =================================================================== --- test partitionwise aggregates --- =================================================================== -CREATE TABLE pagg_tab (a int, b int, c text) PARTITION BY RANGE(a); -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 loopback OPTIONS (table_name 'pagg_tab_p1'); -CREATE FOREIGN TABLE fpagg_tab_p2 PARTITION OF pagg_tab FOR VALUES FROM (10) TO (20) SERVER loopback OPTIONS (table_name 'pagg_tab_p2'); -CREATE FOREIGN TABLE fpagg_tab_p3 PARTITION OF pagg_tab FOR VALUES FROM (20) TO (30) SERVER loopback 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; - QUERY PLAN ------------------------------------------------------ - GroupAggregate - Group Key: pagg_tab.a - Filter: (avg(pagg_tab.b) < '22'::numeric) - -> Append - -> Foreign Scan on fpagg_tab_p1 pagg_tab_1 - -> Foreign Scan on fpagg_tab_p2 pagg_tab_2 - -> Foreign Scan on fpagg_tab_p3 pagg_tab_3 -(7 rows) - --- 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; - QUERY PLAN ------------------------------------------------------------------ - Sort - Sort Key: pagg_tab.a - -> Append - -> Foreign Scan - Relations: Aggregate on (fpagg_tab_p1 pagg_tab) - -> Foreign Scan - Relations: Aggregate on (fpagg_tab_p2 pagg_tab_1) - -> Foreign Scan - Relations: Aggregate on (fpagg_tab_p3 pagg_tab_2) -(9 rows) - -SELECT a, sum(b), min(b), count(*) FROM pagg_tab GROUP BY a HAVING avg(b) < 22 ORDER BY 1; - a | sum | min | count -----+------+-----+------- - 0 | 2000 | 0 | 100 - 1 | 2100 | 1 | 100 - 10 | 2000 | 0 | 100 - 11 | 2100 | 1 | 100 - 20 | 2000 | 0 | 100 - 21 | 2100 | 1 | 100 -(6 rows) - --- 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; - QUERY PLAN --------------------------------------------------------------------------------------------- - Merge Append - Sort Key: t1.a - -> GroupAggregate - Output: t1.a, count(((t1.*)::pagg_tab)) - Group Key: t1.a - Filter: (avg(t1.b) < '22'::numeric) - -> Foreign Scan on public.fpagg_tab_p1 t1 - Output: t1.a, t1.*, t1.b - Remote SQL: SELECT a, b, c FROM public.pagg_tab_p1 ORDER BY a ASC NULLS LAST - -> GroupAggregate - Output: t1_1.a, count(((t1_1.*)::pagg_tab)) - Group Key: t1_1.a - Filter: (avg(t1_1.b) < '22'::numeric) - -> Foreign Scan on public.fpagg_tab_p2 t1_1 - Output: t1_1.a, t1_1.*, t1_1.b - Remote SQL: SELECT a, b, c FROM public.pagg_tab_p2 ORDER BY a ASC NULLS LAST - -> GroupAggregate - Output: t1_2.a, count(((t1_2.*)::pagg_tab)) - Group Key: t1_2.a - Filter: (avg(t1_2.b) < '22'::numeric) - -> Foreign Scan on public.fpagg_tab_p3 t1_2 - Output: t1_2.a, t1_2.*, t1_2.b - Remote SQL: SELECT a, b, c FROM public.pagg_tab_p3 ORDER BY a ASC NULLS LAST -(23 rows) - -SELECT a, count(t1) FROM pagg_tab t1 GROUP BY a HAVING avg(b) < 22 ORDER BY 1; - a | count -----+------- - 0 | 100 - 1 | 100 - 10 | 100 - 11 | 100 - 20 | 100 - 21 | 100 -(6 rows) - --- 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; - QUERY PLAN ------------------------------------------------------------ - Finalize GroupAggregate - Group Key: pagg_tab.b - Filter: (sum(pagg_tab.a) < 700) - -> Merge Append - Sort Key: pagg_tab.b - -> Partial GroupAggregate - Group Key: pagg_tab.b - -> Foreign Scan on fpagg_tab_p1 pagg_tab - -> Partial GroupAggregate - Group Key: pagg_tab_1.b - -> Foreign Scan on fpagg_tab_p2 pagg_tab_1 - -> Partial GroupAggregate - Group Key: pagg_tab_2.b - -> Foreign Scan on fpagg_tab_p3 pagg_tab_2 -(14 rows) - --- =================================================================== --- access rights and superuser --- =================================================================== --- Non-superuser cannot create a FDW without a password in the connstr -CREATE ROLE regress_nosuper NOSUPERUSER; -GRANT USAGE ON FOREIGN DATA WRAPPER postgres_fdw TO regress_nosuper; -SET ROLE regress_nosuper; -SHOW is_superuser; - is_superuser --------------- - off -(1 row) - --- This will be OK, we can create the FDW -DO $d$ - BEGIN - EXECUTE $$CREATE SERVER loopback_nopw FOREIGN DATA WRAPPER postgres_fdw - OPTIONS (dbname '$$||current_database()||$$', - port '$$||current_setting('port')||$$' - )$$; - END; -$d$; --- But creation of user mappings for non-superusers should fail -CREATE USER MAPPING FOR public SERVER loopback_nopw; -CREATE USER MAPPING FOR CURRENT_USER SERVER loopback_nopw; -CREATE FOREIGN TABLE pg_temp.ft1_nopw ( - 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 loopback_nopw OPTIONS (schema_name 'public', table_name 'ft1'); -SELECT 1 FROM ft1_nopw LIMIT 1; -ERROR: password or GSSAPI delegated credentials required -DETAIL: Non-superusers must delegate GSSAPI credentials or provide a password in the user mapping. --- If we add a password to the connstr it'll fail, because we don't allow passwords --- in connstrs only in user mappings. -ALTER SERVER loopback_nopw OPTIONS (ADD password 'dummypw'); -ERROR: invalid option "password" -HINT: Perhaps you meant the option "passfile". --- If we add a password for our user mapping instead, we should get a different --- error because the password wasn't actually *used* when we run with trust auth. --- --- This won't work with installcheck, but neither will most of the FDW checks. -ALTER USER MAPPING FOR CURRENT_USER SERVER loopback_nopw OPTIONS (ADD password 'dummypw'); -SELECT 1 FROM ft1_nopw LIMIT 1; -ERROR: password or GSSAPI delegated credentials required -DETAIL: Non-superuser cannot connect if the server does not request a password or use GSSAPI with delegated credentials. -HINT: Target server's authentication method must be changed or password_required=false set in the user mapping attributes. --- Unpriv user cannot make the mapping passwordless -ALTER USER MAPPING FOR CURRENT_USER SERVER loopback_nopw OPTIONS (ADD password_required 'false'); -ERROR: password_required=false is superuser-only -HINT: User mappings with the password_required option set to false may only be created or modified by the superuser. -SELECT 1 FROM ft1_nopw LIMIT 1; -ERROR: password or GSSAPI delegated credentials required -DETAIL: Non-superuser cannot connect if the server does not request a password or use GSSAPI with delegated credentials. -HINT: Target server's authentication method must be changed or password_required=false set in the user mapping attributes. -RESET ROLE; --- But the superuser can -ALTER USER MAPPING FOR regress_nosuper SERVER loopback_nopw OPTIONS (ADD password_required 'false'); -SET ROLE regress_nosuper; --- Should finally work now -SELECT 1 FROM ft1_nopw LIMIT 1; - ?column? ----------- - 1 -(1 row) - --- unpriv user also cannot set sslcert / sslkey on the user mapping --- first set password_required so we see the right error messages -ALTER USER MAPPING FOR CURRENT_USER SERVER loopback_nopw OPTIONS (SET password_required 'true'); -ALTER USER MAPPING FOR CURRENT_USER SERVER loopback_nopw OPTIONS (ADD sslcert 'foo.crt'); -ERROR: sslcert and sslkey are superuser-only -HINT: User mappings with the sslcert or sslkey options set may only be created or modified by the superuser. -ALTER USER MAPPING FOR CURRENT_USER SERVER loopback_nopw OPTIONS (ADD sslkey 'foo.key'); -ERROR: sslcert and sslkey are superuser-only -HINT: User mappings with the sslcert or sslkey options set may only be created or modified by the superuser. --- We're done with the role named after a specific user and need to check the --- changes to the public mapping. -DROP USER MAPPING FOR CURRENT_USER SERVER loopback_nopw; --- This will fail again as it'll resolve the user mapping for public, which --- lacks password_required=false -SELECT 1 FROM ft1_nopw LIMIT 1; -ERROR: password or GSSAPI delegated credentials required -DETAIL: Non-superusers must delegate GSSAPI credentials or provide a password in the user mapping. -RESET ROLE; --- The user mapping for public is passwordless and lacks the password_required=false --- mapping option, but will work because the current user is a superuser. -SELECT 1 FROM ft1_nopw LIMIT 1; - ?column? ----------- - 1 -(1 row) - --- cleanup -DROP USER MAPPING FOR public SERVER loopback_nopw; -DROP OWNED BY regress_nosuper; -DROP ROLE regress_nosuper; --- Clean-up -RESET enable_partitionwise_aggregate; --- Two-phase transactions are not supported. -BEGIN; -SELECT count(*) FROM ft1; - count -------- - 822 -(1 row) - --- error here -PREPARE TRANSACTION 'fdw_tpc'; -ERROR: cannot PREPARE a transaction that has operated on postgres_fdw foreign tables -ROLLBACK; -WARNING: there is no transaction in progress --- =================================================================== --- reestablish new connection --- =================================================================== --- Change application_name of remote connection to special one --- so that we can easily terminate the connection later. -ALTER SERVER loopback OPTIONS (application_name 'fdw_retry_check'); --- Make sure we have a remote connection. -SELECT 1 FROM ft1 LIMIT 1; - ?column? ----------- - 1 -(1 row) - --- Terminate the remote connection and wait for the termination to complete. --- (If a cache flush happens, the remote connection might have already been --- dropped; so code this step in a way that doesn't fail if no connection.) -DO $$ BEGIN -PERFORM pg_terminate_backend(pid, 180000) FROM pg_stat_activity - WHERE application_name = 'fdw_retry_check'; -END $$; --- This query should detect the broken connection when starting new remote --- transaction, reestablish new connection, and then succeed. -BEGIN; -SELECT 1 FROM ft1 LIMIT 1; - ?column? ----------- - 1 -(1 row) - --- If we detect the broken connection when starting a new remote --- subtransaction, we should fail instead of establishing a new connection. --- Terminate the remote connection and wait for the termination to complete. -DO $$ BEGIN -PERFORM pg_terminate_backend(pid, 180000) FROM pg_stat_activity - WHERE application_name = 'fdw_retry_check'; -END $$; -SAVEPOINT s; --- The text of the error might vary across platforms, so only show SQLSTATE. -\set VERBOSITY sqlstate -SELECT 1 FROM ft1 LIMIT 1; -- should fail -ERROR: 08006 -\set VERBOSITY default -COMMIT; --- ============================================================================= --- test connection invalidation cases and postgres_fdw_get_connections function --- ============================================================================= --- Let's ensure to close all the existing cached connections. -SELECT 1 FROM postgres_fdw_disconnect_all(); - ?column? ----------- - 1 -(1 row) - --- No cached connections, so no records should be output. -SELECT server_name FROM postgres_fdw_get_connections() ORDER BY 1; - server_name -------------- -(0 rows) - --- 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; - ?column? ----------- - 1 -(1 row) - -SELECT 1 FROM ft7 LIMIT 1; - ?column? ----------- - 1 -(1 row) - --- List all the existing cached connections. loopback and loopback3 should be --- output. -SELECT server_name FROM postgres_fdw_get_connections() ORDER BY 1; - server_name -------------- - loopback - loopback3 -(2 rows) - --- Connections are not closed at the end of the alter and drop statements. --- That's because the connections are in midst of this xact, --- they are just marked as invalid in pgfdw_inval_callback. -ALTER SERVER loopback OPTIONS (ADD use_remote_estimate 'off'); -DROP SERVER loopback3 CASCADE; -NOTICE: drop cascades to 2 other objects -DETAIL: drop cascades to user mapping for public on server loopback3 -drop cascades to foreign table ft7 --- List all the existing cached connections. loopback and loopback3 --- should be output as invalid connections. Also the server name for --- loopback3 should be NULL because the server was dropped. -SELECT * FROM postgres_fdw_get_connections() ORDER BY 1; - server_name | valid --------------+------- - loopback | f - | f -(2 rows) - --- The invalid connections get closed in pgfdw_xact_callback during commit. -COMMIT; --- All cached connections were closed while committing above xact, so no --- records should be output. -SELECT server_name FROM postgres_fdw_get_connections() ORDER BY 1; - server_name -------------- -(0 rows) - --- ======================================================================= --- test postgres_fdw_disconnect and postgres_fdw_disconnect_all functions --- ======================================================================= -BEGIN; --- Ensure to cache loopback connection. -SELECT 1 FROM ft1 LIMIT 1; - ?column? ----------- - 1 -(1 row) - --- Ensure to cache loopback2 connection. -SELECT 1 FROM ft6 LIMIT 1; - ?column? ----------- - 1 -(1 row) - --- List all the existing cached connections. loopback and loopback2 should be --- output. -SELECT server_name FROM postgres_fdw_get_connections() ORDER BY 1; - server_name -------------- - loopback - loopback2 -(2 rows) - --- Issue a warning and return false as loopback connection is still in use and --- can not be closed. -SELECT postgres_fdw_disconnect('loopback'); -WARNING: cannot close connection for server "loopback" because it is still in use - postgres_fdw_disconnect -------------------------- - f -(1 row) - --- List all the existing cached connections. loopback and loopback2 should be --- output. -SELECT server_name FROM postgres_fdw_get_connections() ORDER BY 1; - server_name -------------- - loopback - loopback2 -(2 rows) - --- Return false as connections are still in use, warnings are issued. --- But disable warnings temporarily because the order of them is not stable. -SET client_min_messages = 'ERROR'; -SELECT postgres_fdw_disconnect_all(); - postgres_fdw_disconnect_all ------------------------------ - f -(1 row) - -RESET client_min_messages; -COMMIT; --- Ensure that loopback2 connection is closed. -SELECT 1 FROM postgres_fdw_disconnect('loopback2'); - ?column? ----------- - 1 -(1 row) - -SELECT server_name FROM postgres_fdw_get_connections() WHERE server_name = 'loopback2'; - server_name -------------- -(0 rows) - --- Return false as loopback2 connection is closed already. -SELECT postgres_fdw_disconnect('loopback2'); - postgres_fdw_disconnect -------------------------- - f -(1 row) - --- Return an error as there is no foreign server with given name. -SELECT postgres_fdw_disconnect('unknownserver'); -ERROR: server "unknownserver" does not exist --- Let's ensure to close all the existing cached connections. -SELECT 1 FROM postgres_fdw_disconnect_all(); - ?column? ----------- - 1 -(1 row) - --- No cached connections, so no records should be output. -SELECT server_name FROM postgres_fdw_get_connections() ORDER BY 1; - server_name -------------- -(0 rows) - --- ============================================================================= --- test case for having multiple cached connections for a foreign server --- ============================================================================= -CREATE ROLE regress_multi_conn_user1 SUPERUSER; -CREATE ROLE regress_multi_conn_user2 SUPERUSER; -CREATE USER MAPPING FOR regress_multi_conn_user1 SERVER loopback; -CREATE USER MAPPING FOR regress_multi_conn_user2 SERVER loopback; -BEGIN; --- Will cache loopback connection with user mapping for regress_multi_conn_user1 -SET ROLE regress_multi_conn_user1; -SELECT 1 FROM ft1 LIMIT 1; - ?column? ----------- - 1 -(1 row) - -RESET ROLE; --- Will cache loopback connection with user mapping for regress_multi_conn_user2 -SET ROLE regress_multi_conn_user2; -SELECT 1 FROM ft1 LIMIT 1; - ?column? ----------- - 1 -(1 row) - -RESET ROLE; --- Should output two connections for loopback server -SELECT server_name FROM postgres_fdw_get_connections() ORDER BY 1; - server_name -------------- - loopback - loopback -(2 rows) - -COMMIT; --- Let's ensure to close all the existing cached connections. -SELECT 1 FROM postgres_fdw_disconnect_all(); - ?column? ----------- - 1 -(1 row) - --- No cached connections, so no records should be output. -SELECT server_name FROM postgres_fdw_get_connections() ORDER BY 1; - server_name -------------- -(0 rows) - --- Clean up -DROP USER MAPPING FOR regress_multi_conn_user1 SERVER loopback; -DROP USER MAPPING FOR regress_multi_conn_user2 SERVER loopback; -DROP ROLE regress_multi_conn_user1; -DROP ROLE regress_multi_conn_user2; --- =================================================================== --- Test foreign server level option keep_connections --- =================================================================== --- By default, the connections associated with foreign server are cached i.e. --- keep_connections option is on. Set it to off. -ALTER SERVER loopback OPTIONS (keep_connections 'off'); --- connection to loopback server is closed at the end of xact --- as keep_connections was set to off. -SELECT 1 FROM ft1 LIMIT 1; - ?column? ----------- - 1 -(1 row) - --- No cached connections, so no records should be output. -SELECT server_name FROM postgres_fdw_get_connections() ORDER BY 1; - server_name -------------- -(0 rows) - -ALTER SERVER loopback OPTIONS (SET keep_connections 'on'); --- =================================================================== --- batch insert --- =================================================================== -BEGIN; -CREATE SERVER batch10 FOREIGN DATA WRAPPER postgres_fdw OPTIONS( batch_size '10' ); -SELECT count(*) -FROM pg_foreign_server -WHERE srvname = 'batch10' -AND srvoptions @> array['batch_size=10']; - count -------- - 1 -(1 row) - -ALTER SERVER batch10 OPTIONS( SET batch_size '20' ); -SELECT count(*) -FROM pg_foreign_server -WHERE srvname = 'batch10' -AND srvoptions @> array['batch_size=10']; - count -------- - 0 -(1 row) - -SELECT count(*) -FROM pg_foreign_server -WHERE srvname = 'batch10' -AND srvoptions @> array['batch_size=20']; - count -------- - 1 -(1 row) - -CREATE FOREIGN TABLE table30 ( x int ) SERVER batch10 OPTIONS ( batch_size '30' ); -SELECT COUNT(*) -FROM pg_foreign_table -WHERE ftrelid = 'table30'::regclass -AND ftoptions @> array['batch_size=30']; - count -------- - 1 -(1 row) - -ALTER FOREIGN TABLE table30 OPTIONS ( SET batch_size '40'); -SELECT COUNT(*) -FROM pg_foreign_table -WHERE ftrelid = 'table30'::regclass -AND ftoptions @> array['batch_size=30']; - count -------- - 0 -(1 row) - -SELECT COUNT(*) -FROM pg_foreign_table -WHERE ftrelid = 'table30'::regclass -AND ftoptions @> array['batch_size=40']; - count -------- - 1 -(1 row) - -ROLLBACK; -CREATE TABLE batch_table ( x int ); -CREATE FOREIGN TABLE ftable ( x int ) SERVER loopback OPTIONS ( table_name 'batch_table', batch_size '10' ); -EXPLAIN (VERBOSE, COSTS OFF) INSERT INTO ftable SELECT * FROM generate_series(1, 10) i; - QUERY PLAN -------------------------------------------------------------- - Insert on public.ftable - Remote SQL: INSERT INTO public.batch_table(x) VALUES ($1) - Batch Size: 10 - -> Function Scan on pg_catalog.generate_series i - Output: i.i - Function Call: generate_series(1, 10) -(6 rows) - -INSERT INTO ftable SELECT * FROM generate_series(1, 10) i; -INSERT INTO ftable SELECT * FROM generate_series(11, 31) i; -INSERT INTO ftable VALUES (32); -INSERT INTO ftable VALUES (33), (34); -SELECT COUNT(*) FROM ftable; - count -------- - 34 -(1 row) - -TRUNCATE batch_table; -DROP FOREIGN TABLE ftable; --- Disable batch insert -CREATE FOREIGN TABLE ftable ( x int ) SERVER loopback OPTIONS ( table_name 'batch_table', batch_size '1' ); -EXPLAIN (VERBOSE, COSTS OFF) INSERT INTO ftable VALUES (1), (2); - QUERY PLAN -------------------------------------------------------------- - Insert on public.ftable - Remote SQL: INSERT INTO public.batch_table(x) VALUES ($1) - Batch Size: 1 - -> Values Scan on "*VALUES*" - Output: "*VALUES*".column1 -(5 rows) - -INSERT INTO ftable VALUES (1), (2); -SELECT COUNT(*) FROM ftable; - count -------- - 2 -(1 row) - --- Disable batch inserting into foreign tables with BEFORE ROW INSERT triggers --- even if the batch_size option is enabled. -ALTER FOREIGN TABLE ftable OPTIONS ( SET batch_size '10' ); -CREATE TRIGGER trig_row_before BEFORE INSERT ON ftable -FOR EACH ROW EXECUTE PROCEDURE trigger_data(23,'skidoo'); -EXPLAIN (VERBOSE, COSTS OFF) INSERT INTO ftable VALUES (3), (4); - QUERY PLAN -------------------------------------------------------------- - Insert on public.ftable - Remote SQL: INSERT INTO public.batch_table(x) VALUES ($1) - Batch Size: 1 - -> Values Scan on "*VALUES*" - Output: "*VALUES*".column1 -(5 rows) - -INSERT INTO ftable VALUES (3), (4); -NOTICE: trig_row_before(23, skidoo) BEFORE ROW INSERT ON ftable -NOTICE: NEW: (3) -NOTICE: trig_row_before(23, skidoo) BEFORE ROW INSERT ON ftable -NOTICE: NEW: (4) -SELECT COUNT(*) FROM ftable; - count -------- - 4 -(1 row) - --- Clean up -DROP TRIGGER trig_row_before ON ftable; -DROP FOREIGN TABLE ftable; -DROP TABLE batch_table; --- Use partitioning -CREATE TABLE batch_table ( x int ) PARTITION BY HASH (x); -CREATE TABLE batch_table_p0 (LIKE batch_table); -CREATE FOREIGN TABLE batch_table_p0f - PARTITION OF batch_table - FOR VALUES WITH (MODULUS 3, REMAINDER 0) - SERVER loopback - OPTIONS (table_name 'batch_table_p0', batch_size '10'); -CREATE TABLE batch_table_p1 (LIKE batch_table); -CREATE FOREIGN TABLE batch_table_p1f - PARTITION OF batch_table - FOR VALUES WITH (MODULUS 3, REMAINDER 1) - SERVER loopback - OPTIONS (table_name 'batch_table_p1', batch_size '1'); -CREATE TABLE batch_table_p2 - PARTITION OF batch_table - FOR VALUES WITH (MODULUS 3, REMAINDER 2); -INSERT INTO batch_table SELECT * FROM generate_series(1, 66) i; -SELECT COUNT(*) FROM batch_table; - count -------- - 66 -(1 row) - --- Clean up -DROP TABLE batch_table; -DROP TABLE batch_table_p0; -DROP TABLE batch_table_p1; --- Check that batched mode also works for some inserts made during --- cross-partition updates -CREATE TABLE batch_cp_upd_test (a int) PARTITION BY LIST (a); -CREATE TABLE batch_cp_upd_test1 (LIKE batch_cp_upd_test); -CREATE FOREIGN TABLE batch_cp_upd_test1_f - PARTITION OF batch_cp_upd_test - FOR VALUES IN (1) - SERVER loopback - OPTIONS (table_name 'batch_cp_upd_test1', batch_size '10'); -CREATE TABLE batch_cp_upd_test2 PARTITION OF batch_cp_upd_test - FOR VALUES IN (2); -CREATE TABLE batch_cp_upd_test3 (LIKE batch_cp_upd_test); -CREATE FOREIGN TABLE batch_cp_upd_test3_f - PARTITION OF batch_cp_upd_test - FOR VALUES IN (3) - SERVER loopback - OPTIONS (table_name 'batch_cp_upd_test3', batch_size '1'); --- Create statement triggers on remote tables that "log" any INSERTs --- performed on them. -CREATE TABLE cmdlog (cmd text); -CREATE FUNCTION log_stmt() RETURNS TRIGGER LANGUAGE plpgsql AS $$ - BEGIN INSERT INTO public.cmdlog VALUES (TG_OP || ' on ' || TG_RELNAME); RETURN NULL; END; -$$; -CREATE TRIGGER stmt_trig AFTER INSERT ON batch_cp_upd_test1 - FOR EACH STATEMENT EXECUTE FUNCTION log_stmt(); -CREATE TRIGGER stmt_trig AFTER INSERT ON batch_cp_upd_test3 - FOR EACH STATEMENT EXECUTE FUNCTION log_stmt(); --- This update moves rows from the local partition 'batch_cp_upd_test2' to the --- foreign partition 'batch_cp_upd_test1', one that has insert batching --- enabled, so a single INSERT for both rows. -INSERT INTO batch_cp_upd_test VALUES (2), (2); -UPDATE batch_cp_upd_test t SET a = 1 FROM (VALUES (1), (2)) s(a) WHERE t.a = s.a AND s.a = 2; --- This one moves rows from the local partition 'batch_cp_upd_test2' to the --- foreign partition 'batch_cp_upd_test2', one that has insert batching --- disabled, so separate INSERTs for the two rows. -INSERT INTO batch_cp_upd_test VALUES (2), (2); -UPDATE batch_cp_upd_test t SET a = 3 FROM (VALUES (1), (2)) s(a) WHERE t.a = s.a AND s.a = 2; -SELECT tableoid::regclass, * FROM batch_cp_upd_test ORDER BY 1; - tableoid | a -----------------------+--- - batch_cp_upd_test1_f | 1 - batch_cp_upd_test1_f | 1 - batch_cp_upd_test3_f | 3 - batch_cp_upd_test3_f | 3 -(4 rows) - --- Should see 1 INSERT on batch_cp_upd_test1 and 2 on batch_cp_upd_test3 as --- described above. -SELECT * FROM cmdlog ORDER BY 1; - cmd ------------------------------- - INSERT on batch_cp_upd_test1 - INSERT on batch_cp_upd_test3 - INSERT on batch_cp_upd_test3 -(3 rows) - --- Clean up -DROP TABLE batch_cp_upd_test; -DROP TABLE batch_cp_upd_test1; -DROP TABLE batch_cp_upd_test3; -DROP TABLE cmdlog; -DROP FUNCTION log_stmt(); --- Use partitioning -ALTER SERVER loopback OPTIONS (ADD batch_size '10'); -CREATE TABLE batch_table ( x int, field1 text, field2 text) PARTITION BY HASH (x); -CREATE TABLE batch_table_p0 (LIKE batch_table); -ALTER TABLE batch_table_p0 ADD CONSTRAINT p0_pkey PRIMARY KEY (x); -CREATE FOREIGN TABLE batch_table_p0f - PARTITION OF batch_table - FOR VALUES WITH (MODULUS 2, REMAINDER 0) - SERVER loopback - OPTIONS (table_name 'batch_table_p0'); -CREATE TABLE batch_table_p1 (LIKE batch_table); -ALTER TABLE batch_table_p1 ADD CONSTRAINT p1_pkey PRIMARY KEY (x); -CREATE FOREIGN TABLE batch_table_p1f - PARTITION OF batch_table - FOR VALUES WITH (MODULUS 2, REMAINDER 1) - SERVER loopback - OPTIONS (table_name 'batch_table_p1'); -INSERT INTO batch_table SELECT i, 'test'||i, 'test'|| i FROM generate_series(1, 50) i; -SELECT COUNT(*) FROM batch_table; - count -------- - 50 -(1 row) - -SELECT * FROM batch_table ORDER BY x; - x | field1 | field2 -----+--------+-------- - 1 | test1 | test1 - 2 | test2 | test2 - 3 | test3 | test3 - 4 | test4 | test4 - 5 | test5 | test5 - 6 | test6 | test6 - 7 | test7 | test7 - 8 | test8 | test8 - 9 | test9 | test9 - 10 | test10 | test10 - 11 | test11 | test11 - 12 | test12 | test12 - 13 | test13 | test13 - 14 | test14 | test14 - 15 | test15 | test15 - 16 | test16 | test16 - 17 | test17 | test17 - 18 | test18 | test18 - 19 | test19 | test19 - 20 | test20 | test20 - 21 | test21 | test21 - 22 | test22 | test22 - 23 | test23 | test23 - 24 | test24 | test24 - 25 | test25 | test25 - 26 | test26 | test26 - 27 | test27 | test27 - 28 | test28 | test28 - 29 | test29 | test29 - 30 | test30 | test30 - 31 | test31 | test31 - 32 | test32 | test32 - 33 | test33 | test33 - 34 | test34 | test34 - 35 | test35 | test35 - 36 | test36 | test36 - 37 | test37 | test37 - 38 | test38 | test38 - 39 | test39 | test39 - 40 | test40 | test40 - 41 | test41 | test41 - 42 | test42 | test42 - 43 | test43 | test43 - 44 | test44 | test44 - 45 | test45 | test45 - 46 | test46 | test46 - 47 | test47 | test47 - 48 | test48 | test48 - 49 | test49 | test49 - 50 | test50 | test50 -(50 rows) - --- Clean up -DROP TABLE batch_table; -DROP TABLE batch_table_p0; -DROP TABLE batch_table_p1; -ALTER SERVER loopback OPTIONS (DROP batch_size); --- Test that pending inserts are handled properly when needed -CREATE TABLE batch_table (a text, b int); -CREATE FOREIGN TABLE ftable (a text, b int) - SERVER loopback - OPTIONS (table_name 'batch_table', batch_size '2'); -CREATE TABLE ltable (a text, b int); -CREATE FUNCTION ftable_rowcount_trigf() RETURNS trigger LANGUAGE plpgsql AS -$$ -begin - raise notice '%: there are % rows in ftable', - TG_NAME, (SELECT count(*) FROM ftable); - if TG_OP = 'DELETE' then - return OLD; - else - return NEW; - end if; -end; -$$; -CREATE TRIGGER ftable_rowcount_trigger -BEFORE INSERT OR UPDATE OR DELETE ON ltable -FOR EACH ROW EXECUTE PROCEDURE ftable_rowcount_trigf(); -WITH t AS ( - INSERT INTO ltable VALUES ('AAA', 42), ('BBB', 42) RETURNING * -) -INSERT INTO ftable SELECT * FROM t; -NOTICE: ftable_rowcount_trigger: there are 0 rows in ftable -NOTICE: ftable_rowcount_trigger: there are 1 rows in ftable -SELECT * FROM ltable; - a | b ------+---- - AAA | 42 - BBB | 42 -(2 rows) - -SELECT * FROM ftable; - a | b ------+---- - AAA | 42 - BBB | 42 -(2 rows) - -DELETE FROM ftable; -WITH t AS ( - UPDATE ltable SET b = b + 100 RETURNING * -) -INSERT INTO ftable SELECT * FROM t; -NOTICE: ftable_rowcount_trigger: there are 0 rows in ftable -NOTICE: ftable_rowcount_trigger: there are 1 rows in ftable -SELECT * FROM ltable; - a | b ------+----- - AAA | 142 - BBB | 142 -(2 rows) - -SELECT * FROM ftable; - a | b ------+----- - AAA | 142 - BBB | 142 -(2 rows) - -DELETE FROM ftable; -WITH t AS ( - DELETE FROM ltable RETURNING * -) -INSERT INTO ftable SELECT * FROM t; -NOTICE: ftable_rowcount_trigger: there are 0 rows in ftable -NOTICE: ftable_rowcount_trigger: there are 1 rows in ftable -SELECT * FROM ltable; - a | b ----+--- -(0 rows) - -SELECT * FROM ftable; - a | b ------+----- - AAA | 142 - BBB | 142 -(2 rows) - -DELETE FROM ftable; --- Clean up -DROP FOREIGN TABLE ftable; -DROP TABLE batch_table; -DROP TRIGGER ftable_rowcount_trigger ON ltable; -DROP TABLE ltable; -CREATE TABLE parent (a text, b int) PARTITION BY LIST (a); -CREATE TABLE batch_table (a text, b int); -CREATE FOREIGN TABLE ftable - PARTITION OF parent - FOR VALUES IN ('AAA') - SERVER loopback - OPTIONS (table_name 'batch_table', batch_size '2'); -CREATE TABLE ltable - PARTITION OF parent - FOR VALUES IN ('BBB'); -CREATE TRIGGER ftable_rowcount_trigger -BEFORE INSERT ON ltable -FOR EACH ROW EXECUTE PROCEDURE ftable_rowcount_trigf(); -INSERT INTO parent VALUES ('AAA', 42), ('BBB', 42), ('AAA', 42), ('BBB', 42); -NOTICE: ftable_rowcount_trigger: there are 1 rows in ftable -NOTICE: ftable_rowcount_trigger: there are 2 rows in ftable -SELECT tableoid::regclass, * FROM parent; - tableoid | a | b -----------+-----+---- - ftable | AAA | 42 - ftable | AAA | 42 - ltable | BBB | 42 - ltable | BBB | 42 -(4 rows) - --- Clean up -DROP FOREIGN TABLE ftable; -DROP TABLE batch_table; -DROP TRIGGER ftable_rowcount_trigger ON ltable; -DROP TABLE ltable; -DROP TABLE parent; -DROP FUNCTION ftable_rowcount_trigf; --- =================================================================== --- test asynchronous execution --- =================================================================== -ALTER SERVER loopback OPTIONS (DROP extensions); -ALTER SERVER loopback OPTIONS (ADD async_capable 'true'); -ALTER SERVER loopback2 OPTIONS (ADD async_capable 'true'); -CREATE TABLE async_pt (a int, b int, c text) PARTITION BY RANGE (a); -CREATE TABLE base_tbl1 (a int, b int, c text); -CREATE TABLE base_tbl2 (a int, b int, c text); -CREATE FOREIGN TABLE async_p1 PARTITION OF async_pt FOR VALUES FROM (1000) TO (2000) - SERVER loopback OPTIONS (table_name 'base_tbl1'); -CREATE FOREIGN TABLE async_p2 PARTITION OF async_pt FOR VALUES FROM (2000) TO (3000) - SERVER loopback2 OPTIONS (table_name 'base_tbl2'); -INSERT INTO async_p1 SELECT 1000 + i, i, to_char(i, 'FM0000') FROM generate_series(0, 999, 5) i; -INSERT INTO async_p2 SELECT 2000 + i, i, to_char(i, 'FM0000') FROM generate_series(0, 999, 5) i; -ANALYZE async_pt; --- simple queries -CREATE TABLE result_tbl (a int, b int, c text); -EXPLAIN (VERBOSE, COSTS OFF) -INSERT INTO result_tbl SELECT * FROM async_pt WHERE b % 100 = 0; - QUERY PLAN ----------------------------------------------------------------------------------------- - Insert on public.result_tbl - -> Append - -> Async Foreign Scan on public.async_p1 async_pt_1 - Output: async_pt_1.a, async_pt_1.b, async_pt_1.c - Remote SQL: SELECT a, b, c FROM public.base_tbl1 WHERE (((b % 100) = 0)) - -> Async Foreign Scan on public.async_p2 async_pt_2 - Output: async_pt_2.a, async_pt_2.b, async_pt_2.c - Remote SQL: SELECT a, b, c FROM public.base_tbl2 WHERE (((b % 100) = 0)) -(8 rows) - -INSERT INTO result_tbl SELECT * FROM async_pt WHERE b % 100 = 0; -SELECT * FROM result_tbl ORDER BY a; - a | b | c -------+-----+------ - 1000 | 0 | 0000 - 1100 | 100 | 0100 - 1200 | 200 | 0200 - 1300 | 300 | 0300 - 1400 | 400 | 0400 - 1500 | 500 | 0500 - 1600 | 600 | 0600 - 1700 | 700 | 0700 - 1800 | 800 | 0800 - 1900 | 900 | 0900 - 2000 | 0 | 0000 - 2100 | 100 | 0100 - 2200 | 200 | 0200 - 2300 | 300 | 0300 - 2400 | 400 | 0400 - 2500 | 500 | 0500 - 2600 | 600 | 0600 - 2700 | 700 | 0700 - 2800 | 800 | 0800 - 2900 | 900 | 0900 -(20 rows) - -DELETE FROM result_tbl; -EXPLAIN (VERBOSE, COSTS OFF) -INSERT INTO result_tbl SELECT * FROM async_pt WHERE b === 505; - QUERY PLAN ----------------------------------------------------------------- - Insert on public.result_tbl - -> Append - -> Async Foreign Scan on public.async_p1 async_pt_1 - Output: async_pt_1.a, async_pt_1.b, async_pt_1.c - Filter: (async_pt_1.b === 505) - Remote SQL: SELECT a, b, c FROM public.base_tbl1 - -> Async Foreign Scan on public.async_p2 async_pt_2 - Output: async_pt_2.a, async_pt_2.b, async_pt_2.c - Filter: (async_pt_2.b === 505) - Remote SQL: SELECT a, b, c FROM public.base_tbl2 -(10 rows) - -INSERT INTO result_tbl SELECT * FROM async_pt WHERE b === 505; -SELECT * FROM result_tbl ORDER BY a; - a | b | c -------+-----+------ - 1505 | 505 | 0505 - 2505 | 505 | 0505 -(2 rows) - -DELETE FROM result_tbl; -EXPLAIN (VERBOSE, COSTS OFF) -INSERT INTO result_tbl SELECT a, b, 'AAA' || c FROM async_pt WHERE b === 505; - QUERY PLAN ---------------------------------------------------------------------------------- - Insert on public.result_tbl - -> Append - -> Async Foreign Scan on public.async_p1 async_pt_1 - Output: async_pt_1.a, async_pt_1.b, ('AAA'::text || async_pt_1.c) - Filter: (async_pt_1.b === 505) - Remote SQL: SELECT a, b, c FROM public.base_tbl1 - -> Async Foreign Scan on public.async_p2 async_pt_2 - Output: async_pt_2.a, async_pt_2.b, ('AAA'::text || async_pt_2.c) - Filter: (async_pt_2.b === 505) - Remote SQL: SELECT a, b, c FROM public.base_tbl2 -(10 rows) - -INSERT INTO result_tbl SELECT a, b, 'AAA' || c FROM async_pt WHERE b === 505; -SELECT * FROM result_tbl ORDER BY a; - a | b | c -------+-----+--------- - 1505 | 505 | AAA0505 - 2505 | 505 | AAA0505 -(2 rows) - -DELETE FROM result_tbl; --- Test error handling, if accessing one of the foreign partitions errors out -CREATE FOREIGN TABLE async_p_broken PARTITION OF async_pt FOR VALUES FROM (10000) TO (10001) - SERVER loopback OPTIONS (table_name 'non_existent_table'); -SELECT * FROM async_pt; -ERROR: relation "public.non_existent_table" does not exist -CONTEXT: remote SQL command: SELECT a, b, c FROM public.non_existent_table -DROP FOREIGN TABLE async_p_broken; --- Check case where multiple partitions use the same connection -CREATE TABLE base_tbl3 (a int, b int, c text); -CREATE FOREIGN TABLE async_p3 PARTITION OF async_pt FOR VALUES FROM (3000) TO (4000) - SERVER loopback2 OPTIONS (table_name 'base_tbl3'); -INSERT INTO async_p3 SELECT 3000 + i, i, to_char(i, 'FM0000') FROM generate_series(0, 999, 5) i; -ANALYZE async_pt; -EXPLAIN (VERBOSE, COSTS OFF) -INSERT INTO result_tbl SELECT * FROM async_pt WHERE b === 505; - QUERY PLAN ----------------------------------------------------------------- - Insert on public.result_tbl - -> Append - -> Async Foreign Scan on public.async_p1 async_pt_1 - Output: async_pt_1.a, async_pt_1.b, async_pt_1.c - Filter: (async_pt_1.b === 505) - Remote SQL: SELECT a, b, c FROM public.base_tbl1 - -> Async Foreign Scan on public.async_p2 async_pt_2 - Output: async_pt_2.a, async_pt_2.b, async_pt_2.c - Filter: (async_pt_2.b === 505) - Remote SQL: SELECT a, b, c FROM public.base_tbl2 - -> Async Foreign Scan on public.async_p3 async_pt_3 - Output: async_pt_3.a, async_pt_3.b, async_pt_3.c - Filter: (async_pt_3.b === 505) - Remote SQL: SELECT a, b, c FROM public.base_tbl3 -(14 rows) - -INSERT INTO result_tbl SELECT * FROM async_pt WHERE b === 505; -SELECT * FROM result_tbl ORDER BY a; - a | b | c -------+-----+------ - 1505 | 505 | 0505 - 2505 | 505 | 0505 - 3505 | 505 | 0505 -(3 rows) - -DELETE FROM result_tbl; -DROP FOREIGN TABLE async_p3; -DROP TABLE base_tbl3; --- Check case where the partitioned table has local/remote partitions -CREATE TABLE async_p3 PARTITION OF async_pt FOR VALUES FROM (3000) TO (4000); -INSERT INTO async_p3 SELECT 3000 + i, i, to_char(i, 'FM0000') FROM generate_series(0, 999, 5) i; -ANALYZE async_pt; -EXPLAIN (VERBOSE, COSTS OFF) -INSERT INTO result_tbl SELECT * FROM async_pt WHERE b === 505; - QUERY PLAN ----------------------------------------------------------------- - Insert on public.result_tbl - -> Append - -> Async Foreign Scan on public.async_p1 async_pt_1 - Output: async_pt_1.a, async_pt_1.b, async_pt_1.c - Filter: (async_pt_1.b === 505) - Remote SQL: SELECT a, b, c FROM public.base_tbl1 - -> Async Foreign Scan on public.async_p2 async_pt_2 - Output: async_pt_2.a, async_pt_2.b, async_pt_2.c - Filter: (async_pt_2.b === 505) - Remote SQL: SELECT a, b, c FROM public.base_tbl2 - -> Seq Scan on public.async_p3 async_pt_3 - Output: async_pt_3.a, async_pt_3.b, async_pt_3.c - Filter: (async_pt_3.b === 505) -(13 rows) - -INSERT INTO result_tbl SELECT * FROM async_pt WHERE b === 505; -SELECT * FROM result_tbl ORDER BY a; - a | b | c -------+-----+------ - 1505 | 505 | 0505 - 2505 | 505 | 0505 - 3505 | 505 | 0505 -(3 rows) - -DELETE FROM result_tbl; --- partitionwise joins -SET enable_partitionwise_join TO true; -CREATE TABLE join_tbl (a1 int, b1 int, c1 text, a2 int, b2 int, c2 text); -EXPLAIN (VERBOSE, COSTS OFF) -INSERT INTO join_tbl SELECT * FROM async_pt t1, async_pt t2 WHERE t1.a = t2.a AND t1.b = t2.b AND t1.b % 100 = 0; - QUERY PLAN -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- - Insert on public.join_tbl - -> Append - -> Async Foreign Scan - Output: t1_1.a, t1_1.b, t1_1.c, t2_1.a, t2_1.b, t2_1.c - Relations: (public.async_p1 t1_1) INNER JOIN (public.async_p1 t2_1) - Remote SQL: SELECT r5.a, r5.b, r5.c, r8.a, r8.b, r8.c FROM (public.base_tbl1 r5 INNER JOIN public.base_tbl1 r8 ON (((r5.a = r8.a)) AND ((r5.b = r8.b)) AND (((r5.b % 100) = 0)))) - -> Async Foreign Scan - Output: t1_2.a, t1_2.b, t1_2.c, t2_2.a, t2_2.b, t2_2.c - Relations: (public.async_p2 t1_2) INNER JOIN (public.async_p2 t2_2) - Remote SQL: SELECT r6.a, r6.b, r6.c, r9.a, r9.b, r9.c FROM (public.base_tbl2 r6 INNER JOIN public.base_tbl2 r9 ON (((r6.a = r9.a)) AND ((r6.b = r9.b)) AND (((r6.b % 100) = 0)))) - -> Hash Join - Output: t1_3.a, t1_3.b, t1_3.c, t2_3.a, t2_3.b, t2_3.c - Hash Cond: ((t2_3.a = t1_3.a) AND (t2_3.b = t1_3.b)) - -> Seq Scan on public.async_p3 t2_3 - Output: t2_3.a, t2_3.b, t2_3.c - -> Hash - Output: t1_3.a, t1_3.b, t1_3.c - -> Seq Scan on public.async_p3 t1_3 - Output: t1_3.a, t1_3.b, t1_3.c - Filter: ((t1_3.b % 100) = 0) -(20 rows) - -INSERT INTO join_tbl SELECT * FROM async_pt t1, async_pt t2 WHERE t1.a = t2.a AND t1.b = t2.b AND t1.b % 100 = 0; -SELECT * FROM join_tbl ORDER BY a1; - a1 | b1 | c1 | a2 | b2 | c2 -------+-----+------+------+-----+------ - 1000 | 0 | 0000 | 1000 | 0 | 0000 - 1100 | 100 | 0100 | 1100 | 100 | 0100 - 1200 | 200 | 0200 | 1200 | 200 | 0200 - 1300 | 300 | 0300 | 1300 | 300 | 0300 - 1400 | 400 | 0400 | 1400 | 400 | 0400 - 1500 | 500 | 0500 | 1500 | 500 | 0500 - 1600 | 600 | 0600 | 1600 | 600 | 0600 - 1700 | 700 | 0700 | 1700 | 700 | 0700 - 1800 | 800 | 0800 | 1800 | 800 | 0800 - 1900 | 900 | 0900 | 1900 | 900 | 0900 - 2000 | 0 | 0000 | 2000 | 0 | 0000 - 2100 | 100 | 0100 | 2100 | 100 | 0100 - 2200 | 200 | 0200 | 2200 | 200 | 0200 - 2300 | 300 | 0300 | 2300 | 300 | 0300 - 2400 | 400 | 0400 | 2400 | 400 | 0400 - 2500 | 500 | 0500 | 2500 | 500 | 0500 - 2600 | 600 | 0600 | 2600 | 600 | 0600 - 2700 | 700 | 0700 | 2700 | 700 | 0700 - 2800 | 800 | 0800 | 2800 | 800 | 0800 - 2900 | 900 | 0900 | 2900 | 900 | 0900 - 3000 | 0 | 0000 | 3000 | 0 | 0000 - 3100 | 100 | 0100 | 3100 | 100 | 0100 - 3200 | 200 | 0200 | 3200 | 200 | 0200 - 3300 | 300 | 0300 | 3300 | 300 | 0300 - 3400 | 400 | 0400 | 3400 | 400 | 0400 - 3500 | 500 | 0500 | 3500 | 500 | 0500 - 3600 | 600 | 0600 | 3600 | 600 | 0600 - 3700 | 700 | 0700 | 3700 | 700 | 0700 - 3800 | 800 | 0800 | 3800 | 800 | 0800 - 3900 | 900 | 0900 | 3900 | 900 | 0900 -(30 rows) - -DELETE FROM join_tbl; -EXPLAIN (VERBOSE, COSTS OFF) -INSERT INTO join_tbl SELECT t1.a, t1.b, 'AAA' || t1.c, t2.a, t2.b, 'AAA' || t2.c FROM async_pt t1, async_pt t2 WHERE t1.a = t2.a AND t1.b = t2.b AND t1.b % 100 = 0; - QUERY PLAN -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- - Insert on public.join_tbl - -> Append - -> Async Foreign Scan - Output: t1_1.a, t1_1.b, ('AAA'::text || t1_1.c), t2_1.a, t2_1.b, ('AAA'::text || t2_1.c) - Relations: (public.async_p1 t1_1) INNER JOIN (public.async_p1 t2_1) - Remote SQL: SELECT r5.a, r5.b, r5.c, r8.a, r8.b, r8.c FROM (public.base_tbl1 r5 INNER JOIN public.base_tbl1 r8 ON (((r5.a = r8.a)) AND ((r5.b = r8.b)) AND (((r5.b % 100) = 0)))) - -> Async Foreign Scan - Output: t1_2.a, t1_2.b, ('AAA'::text || t1_2.c), t2_2.a, t2_2.b, ('AAA'::text || t2_2.c) - Relations: (public.async_p2 t1_2) INNER JOIN (public.async_p2 t2_2) - Remote SQL: SELECT r6.a, r6.b, r6.c, r9.a, r9.b, r9.c FROM (public.base_tbl2 r6 INNER JOIN public.base_tbl2 r9 ON (((r6.a = r9.a)) AND ((r6.b = r9.b)) AND (((r6.b % 100) = 0)))) - -> Hash Join - Output: t1_3.a, t1_3.b, ('AAA'::text || t1_3.c), t2_3.a, t2_3.b, ('AAA'::text || t2_3.c) - Hash Cond: ((t2_3.a = t1_3.a) AND (t2_3.b = t1_3.b)) - -> Seq Scan on public.async_p3 t2_3 - Output: t2_3.a, t2_3.b, t2_3.c - -> Hash - Output: t1_3.a, t1_3.b, t1_3.c - -> Seq Scan on public.async_p3 t1_3 - Output: t1_3.a, t1_3.b, t1_3.c - Filter: ((t1_3.b % 100) = 0) -(20 rows) - -INSERT INTO join_tbl SELECT t1.a, t1.b, 'AAA' || t1.c, t2.a, t2.b, 'AAA' || t2.c FROM async_pt t1, async_pt t2 WHERE t1.a = t2.a AND t1.b = t2.b AND t1.b % 100 = 0; -SELECT * FROM join_tbl ORDER BY a1; - a1 | b1 | c1 | a2 | b2 | c2 -------+-----+---------+------+-----+--------- - 1000 | 0 | AAA0000 | 1000 | 0 | AAA0000 - 1100 | 100 | AAA0100 | 1100 | 100 | AAA0100 - 1200 | 200 | AAA0200 | 1200 | 200 | AAA0200 - 1300 | 300 | AAA0300 | 1300 | 300 | AAA0300 - 1400 | 400 | AAA0400 | 1400 | 400 | AAA0400 - 1500 | 500 | AAA0500 | 1500 | 500 | AAA0500 - 1600 | 600 | AAA0600 | 1600 | 600 | AAA0600 - 1700 | 700 | AAA0700 | 1700 | 700 | AAA0700 - 1800 | 800 | AAA0800 | 1800 | 800 | AAA0800 - 1900 | 900 | AAA0900 | 1900 | 900 | AAA0900 - 2000 | 0 | AAA0000 | 2000 | 0 | AAA0000 - 2100 | 100 | AAA0100 | 2100 | 100 | AAA0100 - 2200 | 200 | AAA0200 | 2200 | 200 | AAA0200 - 2300 | 300 | AAA0300 | 2300 | 300 | AAA0300 - 2400 | 400 | AAA0400 | 2400 | 400 | AAA0400 - 2500 | 500 | AAA0500 | 2500 | 500 | AAA0500 - 2600 | 600 | AAA0600 | 2600 | 600 | AAA0600 - 2700 | 700 | AAA0700 | 2700 | 700 | AAA0700 - 2800 | 800 | AAA0800 | 2800 | 800 | AAA0800 - 2900 | 900 | AAA0900 | 2900 | 900 | AAA0900 - 3000 | 0 | AAA0000 | 3000 | 0 | AAA0000 - 3100 | 100 | AAA0100 | 3100 | 100 | AAA0100 - 3200 | 200 | AAA0200 | 3200 | 200 | AAA0200 - 3300 | 300 | AAA0300 | 3300 | 300 | AAA0300 - 3400 | 400 | AAA0400 | 3400 | 400 | AAA0400 - 3500 | 500 | AAA0500 | 3500 | 500 | AAA0500 - 3600 | 600 | AAA0600 | 3600 | 600 | AAA0600 - 3700 | 700 | AAA0700 | 3700 | 700 | AAA0700 - 3800 | 800 | AAA0800 | 3800 | 800 | AAA0800 - 3900 | 900 | AAA0900 | 3900 | 900 | AAA0900 -(30 rows) - -DELETE FROM join_tbl; -RESET enable_partitionwise_join; --- Test rescan of an async Append node with do_exec_prune=false -SET enable_hashjoin TO false; -EXPLAIN (VERBOSE, COSTS OFF) -INSERT INTO join_tbl SELECT * FROM async_p1 t1, async_pt t2 WHERE t1.a = t2.a AND t1.b = t2.b AND t1.b % 100 = 0; - QUERY PLAN ----------------------------------------------------------------------------------------- - Insert on public.join_tbl - -> Nested Loop - Output: t1.a, t1.b, t1.c, t2.a, t2.b, t2.c - Join Filter: ((t1.a = t2.a) AND (t1.b = t2.b)) - -> Foreign Scan on public.async_p1 t1 - Output: t1.a, t1.b, t1.c - Remote SQL: SELECT a, b, c FROM public.base_tbl1 WHERE (((b % 100) = 0)) - -> Append - -> Async Foreign Scan on public.async_p1 t2_1 - Output: t2_1.a, t2_1.b, t2_1.c - Remote SQL: SELECT a, b, c FROM public.base_tbl1 - -> Async Foreign Scan on public.async_p2 t2_2 - Output: t2_2.a, t2_2.b, t2_2.c - Remote SQL: SELECT a, b, c FROM public.base_tbl2 - -> Seq Scan on public.async_p3 t2_3 - Output: t2_3.a, t2_3.b, t2_3.c -(16 rows) - -INSERT INTO join_tbl SELECT * FROM async_p1 t1, async_pt t2 WHERE t1.a = t2.a AND t1.b = t2.b AND t1.b % 100 = 0; -SELECT * FROM join_tbl ORDER BY a1; - a1 | b1 | c1 | a2 | b2 | c2 -------+-----+------+------+-----+------ - 1000 | 0 | 0000 | 1000 | 0 | 0000 - 1100 | 100 | 0100 | 1100 | 100 | 0100 - 1200 | 200 | 0200 | 1200 | 200 | 0200 - 1300 | 300 | 0300 | 1300 | 300 | 0300 - 1400 | 400 | 0400 | 1400 | 400 | 0400 - 1500 | 500 | 0500 | 1500 | 500 | 0500 - 1600 | 600 | 0600 | 1600 | 600 | 0600 - 1700 | 700 | 0700 | 1700 | 700 | 0700 - 1800 | 800 | 0800 | 1800 | 800 | 0800 - 1900 | 900 | 0900 | 1900 | 900 | 0900 -(10 rows) - -DELETE FROM join_tbl; -RESET enable_hashjoin; --- Test interaction of async execution with plan-time partition pruning -EXPLAIN (VERBOSE, COSTS OFF) -SELECT * FROM async_pt WHERE a < 3000; - QUERY PLAN ------------------------------------------------------------------------------ - Append - -> Async Foreign Scan on public.async_p1 async_pt_1 - Output: async_pt_1.a, async_pt_1.b, async_pt_1.c - Remote SQL: SELECT a, b, c FROM public.base_tbl1 WHERE ((a < 3000)) - -> Async Foreign Scan on public.async_p2 async_pt_2 - Output: async_pt_2.a, async_pt_2.b, async_pt_2.c - Remote SQL: SELECT a, b, c FROM public.base_tbl2 WHERE ((a < 3000)) -(7 rows) - -EXPLAIN (VERBOSE, COSTS OFF) -SELECT * FROM async_pt WHERE a < 2000; - QUERY PLAN ------------------------------------------------------------------------ - Foreign Scan on public.async_p1 async_pt - Output: async_pt.a, async_pt.b, async_pt.c - Remote SQL: SELECT a, b, c FROM public.base_tbl1 WHERE ((a < 2000)) -(3 rows) - --- Test interaction of async execution with run-time partition pruning -SET plan_cache_mode TO force_generic_plan; -PREPARE async_pt_query (int, int) AS - INSERT INTO result_tbl SELECT * FROM async_pt WHERE a < $1 AND b === $2; -EXPLAIN (VERBOSE, COSTS OFF) -EXECUTE async_pt_query (3000, 505); - QUERY PLAN ------------------------------------------------------------------------------------------- - Insert on public.result_tbl - -> Append - Subplans Removed: 1 - -> Async Foreign Scan on public.async_p1 async_pt_1 - Output: async_pt_1.a, async_pt_1.b, async_pt_1.c - Filter: (async_pt_1.b === $2) - Remote SQL: SELECT a, b, c FROM public.base_tbl1 WHERE ((a < $1::integer)) - -> Async Foreign Scan on public.async_p2 async_pt_2 - Output: async_pt_2.a, async_pt_2.b, async_pt_2.c - Filter: (async_pt_2.b === $2) - Remote SQL: SELECT a, b, c FROM public.base_tbl2 WHERE ((a < $1::integer)) -(11 rows) - -EXECUTE async_pt_query (3000, 505); -SELECT * FROM result_tbl ORDER BY a; - a | b | c -------+-----+------ - 1505 | 505 | 0505 - 2505 | 505 | 0505 -(2 rows) - -DELETE FROM result_tbl; -EXPLAIN (VERBOSE, COSTS OFF) -EXECUTE async_pt_query (2000, 505); - QUERY PLAN ------------------------------------------------------------------------------------------- - Insert on public.result_tbl - -> Append - Subplans Removed: 2 - -> Async Foreign Scan on public.async_p1 async_pt_1 - Output: async_pt_1.a, async_pt_1.b, async_pt_1.c - Filter: (async_pt_1.b === $2) - Remote SQL: SELECT a, b, c FROM public.base_tbl1 WHERE ((a < $1::integer)) -(7 rows) - -EXECUTE async_pt_query (2000, 505); -SELECT * FROM result_tbl ORDER BY a; - a | b | c -------+-----+------ - 1505 | 505 | 0505 -(1 row) - -DELETE FROM result_tbl; -RESET plan_cache_mode; -CREATE TABLE local_tbl(a int, b int, c text); -INSERT INTO local_tbl VALUES (1505, 505, 'foo'), (2505, 505, 'bar'); -ANALYZE local_tbl; -CREATE INDEX base_tbl1_idx ON base_tbl1 (a); -CREATE INDEX base_tbl2_idx ON base_tbl2 (a); -CREATE INDEX async_p3_idx ON async_p3 (a); -ANALYZE base_tbl1; -ANALYZE base_tbl2; -ANALYZE async_p3; -ALTER FOREIGN TABLE async_p1 OPTIONS (use_remote_estimate 'true'); -ALTER FOREIGN TABLE async_p2 OPTIONS (use_remote_estimate 'true'); -EXPLAIN (VERBOSE, COSTS OFF) -SELECT * FROM local_tbl, async_pt WHERE local_tbl.a = async_pt.a AND local_tbl.c = 'bar'; - QUERY PLAN ------------------------------------------------------------------------------------------- - Nested Loop - Output: local_tbl.a, local_tbl.b, local_tbl.c, async_pt.a, async_pt.b, async_pt.c - -> Seq Scan on public.local_tbl - Output: local_tbl.a, local_tbl.b, local_tbl.c - Filter: (local_tbl.c = 'bar'::text) - -> Append - -> Async Foreign Scan on public.async_p1 async_pt_1 - Output: async_pt_1.a, async_pt_1.b, async_pt_1.c - Remote SQL: SELECT a, b, c FROM public.base_tbl1 WHERE ((a = $1::integer)) - -> Async Foreign Scan on public.async_p2 async_pt_2 - Output: async_pt_2.a, async_pt_2.b, async_pt_2.c - Remote SQL: SELECT a, b, c FROM public.base_tbl2 WHERE ((a = $1::integer)) - -> Seq Scan on public.async_p3 async_pt_3 - Output: async_pt_3.a, async_pt_3.b, async_pt_3.c - Filter: (async_pt_3.a = local_tbl.a) -(15 rows) - -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) -SELECT * FROM local_tbl, async_pt WHERE local_tbl.a = async_pt.a AND local_tbl.c = 'bar'; - QUERY PLAN -------------------------------------------------------------------------------- - Nested Loop (actual rows=1 loops=1) - -> Seq Scan on local_tbl (actual rows=1 loops=1) - Filter: (c = 'bar'::text) - Rows Removed by Filter: 1 - -> Append (actual rows=1 loops=1) - -> Async Foreign Scan on async_p1 async_pt_1 (never executed) - -> Async Foreign Scan on async_p2 async_pt_2 (actual rows=1 loops=1) - -> Seq Scan on async_p3 async_pt_3 (never executed) - Filter: (a = local_tbl.a) -(9 rows) - -SELECT * FROM local_tbl, async_pt WHERE local_tbl.a = async_pt.a AND local_tbl.c = 'bar'; - a | b | c | a | b | c -------+-----+-----+------+-----+------ - 2505 | 505 | bar | 2505 | 505 | 0505 -(1 row) - -ALTER FOREIGN TABLE async_p1 OPTIONS (DROP use_remote_estimate); -ALTER FOREIGN TABLE async_p2 OPTIONS (DROP use_remote_estimate); -DROP TABLE local_tbl; -DROP INDEX base_tbl1_idx; -DROP INDEX base_tbl2_idx; -DROP INDEX async_p3_idx; --- UNION queries -SET enable_sort TO off; -SET enable_incremental_sort TO off; --- Adjust fdw_startup_cost so that we get an unordered path in the Append. -ALTER SERVER loopback2 OPTIONS (ADD fdw_startup_cost '0.00'); -EXPLAIN (VERBOSE, COSTS OFF) -INSERT INTO result_tbl -(SELECT a, b, 'AAA' || c FROM async_p1 ORDER BY a LIMIT 10) -UNION -(SELECT a, b, 'AAA' || c FROM async_p2 WHERE b < 10); - QUERY PLAN ------------------------------------------------------------------------------------------------------------------ - Insert on public.result_tbl - -> HashAggregate - Output: async_p1.a, async_p1.b, (('AAA'::text || async_p1.c)) - Group Key: async_p1.a, async_p1.b, (('AAA'::text || async_p1.c)) - -> Append - -> Async Foreign Scan on public.async_p1 - Output: async_p1.a, async_p1.b, ('AAA'::text || async_p1.c) - Remote SQL: SELECT a, b, c FROM public.base_tbl1 ORDER BY a ASC NULLS LAST LIMIT 10::bigint - -> Async Foreign Scan on public.async_p2 - Output: async_p2.a, async_p2.b, ('AAA'::text || async_p2.c) - Remote SQL: SELECT a, b, c FROM public.base_tbl2 WHERE ((b < 10)) -(11 rows) - -INSERT INTO result_tbl -(SELECT a, b, 'AAA' || c FROM async_p1 ORDER BY a LIMIT 10) -UNION -(SELECT a, b, 'AAA' || c FROM async_p2 WHERE b < 10); -SELECT * FROM result_tbl ORDER BY a; - a | b | c -------+----+--------- - 1000 | 0 | AAA0000 - 1005 | 5 | AAA0005 - 1010 | 10 | AAA0010 - 1015 | 15 | AAA0015 - 1020 | 20 | AAA0020 - 1025 | 25 | AAA0025 - 1030 | 30 | AAA0030 - 1035 | 35 | AAA0035 - 1040 | 40 | AAA0040 - 1045 | 45 | AAA0045 - 2000 | 0 | AAA0000 - 2005 | 5 | AAA0005 -(12 rows) - -DELETE FROM result_tbl; -EXPLAIN (VERBOSE, COSTS OFF) -INSERT INTO result_tbl -(SELECT a, b, 'AAA' || c FROM async_p1 ORDER BY a LIMIT 10) -UNION ALL -(SELECT a, b, 'AAA' || c FROM async_p2 WHERE b < 10); - QUERY PLAN ------------------------------------------------------------------------------------------------------------ - Insert on public.result_tbl - -> Append - -> Async Foreign Scan on public.async_p1 - Output: async_p1.a, async_p1.b, ('AAA'::text || async_p1.c) - Remote SQL: SELECT a, b, c FROM public.base_tbl1 ORDER BY a ASC NULLS LAST LIMIT 10::bigint - -> Async Foreign Scan on public.async_p2 - Output: async_p2.a, async_p2.b, ('AAA'::text || async_p2.c) - Remote SQL: SELECT a, b, c FROM public.base_tbl2 WHERE ((b < 10)) -(8 rows) - -INSERT INTO result_tbl -(SELECT a, b, 'AAA' || c FROM async_p1 ORDER BY a LIMIT 10) -UNION ALL -(SELECT a, b, 'AAA' || c FROM async_p2 WHERE b < 10); -SELECT * FROM result_tbl ORDER BY a; - a | b | c -------+----+--------- - 1000 | 0 | AAA0000 - 1005 | 5 | AAA0005 - 1010 | 10 | AAA0010 - 1015 | 15 | AAA0015 - 1020 | 20 | AAA0020 - 1025 | 25 | AAA0025 - 1030 | 30 | AAA0030 - 1035 | 35 | AAA0035 - 1040 | 40 | AAA0040 - 1045 | 45 | AAA0045 - 2000 | 0 | AAA0000 - 2005 | 5 | AAA0005 -(12 rows) - -DELETE FROM result_tbl; -RESET enable_incremental_sort; -RESET enable_sort; -ALTER SERVER loopback2 OPTIONS (DROP fdw_startup_cost); --- Disable async execution if we use gating Result nodes for pseudoconstant --- quals -EXPLAIN (VERBOSE, COSTS OFF) -SELECT * FROM async_pt WHERE CURRENT_USER = SESSION_USER; - QUERY PLAN ----------------------------------------------------------------- - Append - -> Result - Output: async_pt_1.a, async_pt_1.b, async_pt_1.c - One-Time Filter: (CURRENT_USER = SESSION_USER) - -> Foreign Scan on public.async_p1 async_pt_1 - Output: async_pt_1.a, async_pt_1.b, async_pt_1.c - Remote SQL: SELECT a, b, c FROM public.base_tbl1 - -> Result - Output: async_pt_2.a, async_pt_2.b, async_pt_2.c - One-Time Filter: (CURRENT_USER = SESSION_USER) - -> Foreign Scan on public.async_p2 async_pt_2 - Output: async_pt_2.a, async_pt_2.b, async_pt_2.c - Remote SQL: SELECT a, b, c FROM public.base_tbl2 - -> Result - Output: async_pt_3.a, async_pt_3.b, async_pt_3.c - One-Time Filter: (CURRENT_USER = SESSION_USER) - -> Seq Scan on public.async_p3 async_pt_3 - Output: async_pt_3.a, async_pt_3.b, async_pt_3.c -(18 rows) - -EXPLAIN (VERBOSE, COSTS OFF) -(SELECT * FROM async_p1 WHERE CURRENT_USER = SESSION_USER) -UNION ALL -(SELECT * FROM async_p2 WHERE CURRENT_USER = SESSION_USER); - QUERY PLAN ----------------------------------------------------------------- - Append - -> Result - Output: async_p1.a, async_p1.b, async_p1.c - One-Time Filter: (CURRENT_USER = SESSION_USER) - -> Foreign Scan on public.async_p1 - Output: async_p1.a, async_p1.b, async_p1.c - Remote SQL: SELECT a, b, c FROM public.base_tbl1 - -> Result - Output: async_p2.a, async_p2.b, async_p2.c - One-Time Filter: (CURRENT_USER = SESSION_USER) - -> Foreign Scan on public.async_p2 - Output: async_p2.a, async_p2.b, async_p2.c - Remote SQL: SELECT a, b, c FROM public.base_tbl2 -(13 rows) - -EXPLAIN (VERBOSE, COSTS OFF) -SELECT * FROM ((SELECT * FROM async_p1 WHERE b < 10) UNION ALL (SELECT * FROM async_p2 WHERE b < 10)) s WHERE CURRENT_USER = SESSION_USER; - QUERY PLAN ---------------------------------------------------------------------------------- - Append - -> Result - Output: async_p1.a, async_p1.b, async_p1.c - One-Time Filter: (CURRENT_USER = SESSION_USER) - -> Foreign Scan on public.async_p1 - Output: async_p1.a, async_p1.b, async_p1.c - Remote SQL: SELECT a, b, c FROM public.base_tbl1 WHERE ((b < 10)) - -> Result - Output: async_p2.a, async_p2.b, async_p2.c - One-Time Filter: (CURRENT_USER = SESSION_USER) - -> Foreign Scan on public.async_p2 - Output: async_p2.a, async_p2.b, async_p2.c - Remote SQL: SELECT a, b, c FROM public.base_tbl2 WHERE ((b < 10)) -(13 rows) - --- Test that pending requests are processed properly -SET enable_mergejoin TO false; -SET enable_hashjoin TO false; -EXPLAIN (VERBOSE, COSTS OFF) -SELECT * FROM async_pt t1, async_p2 t2 WHERE t1.a = t2.a AND t1.b === 505; - QUERY PLAN ----------------------------------------------------------------- - Nested Loop - Output: t1.a, t1.b, t1.c, t2.a, t2.b, t2.c - Join Filter: (t1.a = t2.a) - -> Append - -> Async Foreign Scan on public.async_p1 t1_1 - Output: t1_1.a, t1_1.b, t1_1.c - Filter: (t1_1.b === 505) - Remote SQL: SELECT a, b, c FROM public.base_tbl1 - -> Async Foreign Scan on public.async_p2 t1_2 - Output: t1_2.a, t1_2.b, t1_2.c - Filter: (t1_2.b === 505) - Remote SQL: SELECT a, b, c FROM public.base_tbl2 - -> Seq Scan on public.async_p3 t1_3 - Output: t1_3.a, t1_3.b, t1_3.c - Filter: (t1_3.b === 505) - -> Materialize - Output: t2.a, t2.b, t2.c - -> Foreign Scan on public.async_p2 t2 - Output: t2.a, t2.b, t2.c - Remote SQL: SELECT a, b, c FROM public.base_tbl2 -(20 rows) - -SELECT * FROM async_pt t1, async_p2 t2 WHERE t1.a = t2.a AND t1.b === 505; - a | b | c | a | b | c -------+-----+------+------+-----+------ - 2505 | 505 | 0505 | 2505 | 505 | 0505 -(1 row) - -CREATE TABLE local_tbl (a int, b int, c text); -INSERT INTO local_tbl VALUES (1505, 505, 'foo'); -ANALYZE local_tbl; -EXPLAIN (VERBOSE, COSTS OFF) -SELECT * FROM local_tbl t1 LEFT JOIN (SELECT *, (SELECT count(*) FROM async_pt WHERE a < 3000) FROM async_pt WHERE a < 3000) t2 ON t1.a = t2.a; - QUERY PLAN ----------------------------------------------------------------------------------------- - Nested Loop Left Join - Output: t1.a, t1.b, t1.c, async_pt.a, async_pt.b, async_pt.c, ((InitPlan 1).col1) - Join Filter: (t1.a = async_pt.a) - InitPlan 1 - -> Aggregate - Output: count(*) - -> Append - -> Async Foreign Scan on public.async_p1 async_pt_4 - Remote SQL: SELECT NULL FROM public.base_tbl1 WHERE ((a < 3000)) - -> Async Foreign Scan on public.async_p2 async_pt_5 - Remote SQL: SELECT NULL FROM public.base_tbl2 WHERE ((a < 3000)) - -> Seq Scan on public.local_tbl t1 - Output: t1.a, t1.b, t1.c - -> Append - -> Async Foreign Scan on public.async_p1 async_pt_1 - Output: async_pt_1.a, async_pt_1.b, async_pt_1.c, (InitPlan 1).col1 - Remote SQL: SELECT a, b, c FROM public.base_tbl1 WHERE ((a < 3000)) - -> Async Foreign Scan on public.async_p2 async_pt_2 - Output: async_pt_2.a, async_pt_2.b, async_pt_2.c, (InitPlan 1).col1 - Remote SQL: SELECT a, b, c FROM public.base_tbl2 WHERE ((a < 3000)) -(20 rows) - -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) -SELECT * FROM local_tbl t1 LEFT JOIN (SELECT *, (SELECT count(*) FROM async_pt WHERE a < 3000) FROM async_pt WHERE a < 3000) t2 ON t1.a = t2.a; - QUERY PLAN ------------------------------------------------------------------------------------------ - Nested Loop Left Join (actual rows=1 loops=1) - Join Filter: (t1.a = async_pt.a) - Rows Removed by Join Filter: 399 - InitPlan 1 - -> Aggregate (actual rows=1 loops=1) - -> Append (actual rows=400 loops=1) - -> Async Foreign Scan on async_p1 async_pt_4 (actual rows=200 loops=1) - -> Async Foreign Scan on async_p2 async_pt_5 (actual rows=200 loops=1) - -> Seq Scan on local_tbl t1 (actual rows=1 loops=1) - -> Append (actual rows=400 loops=1) - -> Async Foreign Scan on async_p1 async_pt_1 (actual rows=200 loops=1) - -> Async Foreign Scan on async_p2 async_pt_2 (actual rows=200 loops=1) -(12 rows) - -SELECT * FROM local_tbl t1 LEFT JOIN (SELECT *, (SELECT count(*) FROM async_pt WHERE a < 3000) FROM async_pt WHERE a < 3000) t2 ON t1.a = t2.a; - a | b | c | a | b | c | count -------+-----+-----+------+-----+------+------- - 1505 | 505 | foo | 1505 | 505 | 0505 | 400 -(1 row) - -EXPLAIN (VERBOSE, COSTS OFF) -SELECT * FROM async_pt t1 WHERE t1.b === 505 LIMIT 1; - QUERY PLAN ----------------------------------------------------------------- - Limit - Output: t1.a, t1.b, t1.c - -> Append - -> Async Foreign Scan on public.async_p1 t1_1 - Output: t1_1.a, t1_1.b, t1_1.c - Filter: (t1_1.b === 505) - Remote SQL: SELECT a, b, c FROM public.base_tbl1 - -> Async Foreign Scan on public.async_p2 t1_2 - Output: t1_2.a, t1_2.b, t1_2.c - Filter: (t1_2.b === 505) - Remote SQL: SELECT a, b, c FROM public.base_tbl2 - -> Seq Scan on public.async_p3 t1_3 - Output: t1_3.a, t1_3.b, t1_3.c - Filter: (t1_3.b === 505) -(14 rows) - -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) -SELECT * FROM async_pt t1 WHERE t1.b === 505 LIMIT 1; - QUERY PLAN -------------------------------------------------------------------------- - Limit (actual rows=1 loops=1) - -> Append (actual rows=1 loops=1) - -> Async Foreign Scan on async_p1 t1_1 (actual rows=0 loops=1) - Filter: (b === 505) - -> Async Foreign Scan on async_p2 t1_2 (actual rows=0 loops=1) - Filter: (b === 505) - -> Seq Scan on async_p3 t1_3 (actual rows=1 loops=1) - Filter: (b === 505) - Rows Removed by Filter: 101 -(9 rows) - -SELECT * FROM async_pt t1 WHERE t1.b === 505 LIMIT 1; - a | b | c -------+-----+------ - 3505 | 505 | 0505 -(1 row) - --- Check with foreign modify -CREATE TABLE base_tbl3 (a int, b int, c text); -CREATE FOREIGN TABLE remote_tbl (a int, b int, c text) - SERVER loopback OPTIONS (table_name 'base_tbl3'); -INSERT INTO remote_tbl VALUES (2505, 505, 'bar'); -CREATE TABLE base_tbl4 (a int, b int, c text); -CREATE FOREIGN TABLE insert_tbl (a int, b int, c text) - SERVER loopback OPTIONS (table_name 'base_tbl4'); -EXPLAIN (VERBOSE, COSTS OFF) -INSERT INTO insert_tbl (SELECT * FROM local_tbl UNION ALL SELECT * FROM remote_tbl); - QUERY PLAN -------------------------------------------------------------------------- - Insert on public.insert_tbl - Remote SQL: INSERT INTO public.base_tbl4(a, b, c) VALUES ($1, $2, $3) - Batch Size: 1 - -> Append - -> Seq Scan on public.local_tbl - Output: local_tbl.a, local_tbl.b, local_tbl.c - -> Async Foreign Scan on public.remote_tbl - Output: remote_tbl.a, remote_tbl.b, remote_tbl.c - Remote SQL: SELECT a, b, c FROM public.base_tbl3 -(9 rows) - -INSERT INTO insert_tbl (SELECT * FROM local_tbl UNION ALL SELECT * FROM remote_tbl); -SELECT * FROM insert_tbl ORDER BY a; - a | b | c -------+-----+----- - 1505 | 505 | foo - 2505 | 505 | bar -(2 rows) - --- Check with direct modify -EXPLAIN (VERBOSE, COSTS OFF) -WITH t AS (UPDATE remote_tbl SET c = c || c RETURNING *) -INSERT INTO join_tbl SELECT * FROM async_pt LEFT JOIN t ON (async_pt.a = t.a AND async_pt.b = t.b) WHERE async_pt.b === 505; - QUERY PLAN ----------------------------------------------------------------------------------------- - Insert on public.join_tbl - CTE t - -> Update on public.remote_tbl - Output: remote_tbl.a, remote_tbl.b, remote_tbl.c - -> Foreign Update on public.remote_tbl - Remote SQL: UPDATE public.base_tbl3 SET c = (c || c) RETURNING a, b, c - -> Nested Loop Left Join - Output: async_pt.a, async_pt.b, async_pt.c, t.a, t.b, t.c - Join Filter: ((async_pt.a = t.a) AND (async_pt.b = t.b)) - -> Append - -> Async Foreign Scan on public.async_p1 async_pt_1 - Output: async_pt_1.a, async_pt_1.b, async_pt_1.c - Filter: (async_pt_1.b === 505) - Remote SQL: SELECT a, b, c FROM public.base_tbl1 - -> Async Foreign Scan on public.async_p2 async_pt_2 - Output: async_pt_2.a, async_pt_2.b, async_pt_2.c - Filter: (async_pt_2.b === 505) - Remote SQL: SELECT a, b, c FROM public.base_tbl2 - -> Seq Scan on public.async_p3 async_pt_3 - Output: async_pt_3.a, async_pt_3.b, async_pt_3.c - Filter: (async_pt_3.b === 505) - -> CTE Scan on t - Output: t.a, t.b, t.c -(23 rows) - -WITH t AS (UPDATE remote_tbl SET c = c || c RETURNING *) -INSERT INTO join_tbl SELECT * FROM async_pt LEFT JOIN t ON (async_pt.a = t.a AND async_pt.b = t.b) WHERE async_pt.b === 505; -SELECT * FROM join_tbl ORDER BY a1; - a1 | b1 | c1 | a2 | b2 | c2 -------+-----+------+------+-----+-------- - 1505 | 505 | 0505 | | | - 2505 | 505 | 0505 | 2505 | 505 | barbar - 3505 | 505 | 0505 | | | -(3 rows) - -DELETE FROM join_tbl; -DROP TABLE local_tbl; -DROP FOREIGN TABLE remote_tbl; -DROP FOREIGN TABLE insert_tbl; -DROP TABLE base_tbl3; -DROP TABLE base_tbl4; -RESET enable_mergejoin; -RESET enable_hashjoin; --- Test that UPDATE/DELETE with inherited target works with async_capable enabled -EXPLAIN (VERBOSE, COSTS OFF) -UPDATE async_pt SET c = c || c WHERE b = 0 RETURNING *; - QUERY PLAN ----------------------------------------------------------------------------------------------------------- - Update on public.async_pt - Output: async_pt_1.a, async_pt_1.b, async_pt_1.c - Foreign Update on public.async_p1 async_pt_1 - Foreign Update on public.async_p2 async_pt_2 - Update on public.async_p3 async_pt_3 - -> Append - -> Foreign Update on public.async_p1 async_pt_1 - Remote SQL: UPDATE public.base_tbl1 SET c = (c || c) WHERE ((b = 0)) RETURNING a, b, c - -> Foreign Update on public.async_p2 async_pt_2 - Remote SQL: UPDATE public.base_tbl2 SET c = (c || c) WHERE ((b = 0)) RETURNING a, b, c - -> Seq Scan on public.async_p3 async_pt_3 - Output: (async_pt_3.c || async_pt_3.c), async_pt_3.tableoid, async_pt_3.ctid, NULL::record - Filter: (async_pt_3.b = 0) -(13 rows) - -UPDATE async_pt SET c = c || c WHERE b = 0 RETURNING *; - a | b | c -------+---+---------- - 1000 | 0 | 00000000 - 2000 | 0 | 00000000 - 3000 | 0 | 00000000 -(3 rows) - -EXPLAIN (VERBOSE, COSTS OFF) -DELETE FROM async_pt WHERE b = 0 RETURNING *; - QUERY PLAN ------------------------------------------------------------------------------------------- - Delete on public.async_pt - Output: async_pt_1.a, async_pt_1.b, async_pt_1.c - Foreign Delete on public.async_p1 async_pt_1 - Foreign Delete on public.async_p2 async_pt_2 - Delete on public.async_p3 async_pt_3 - -> Append - -> Foreign Delete on public.async_p1 async_pt_1 - Remote SQL: DELETE FROM public.base_tbl1 WHERE ((b = 0)) RETURNING a, b, c - -> Foreign Delete on public.async_p2 async_pt_2 - Remote SQL: DELETE FROM public.base_tbl2 WHERE ((b = 0)) RETURNING a, b, c - -> Seq Scan on public.async_p3 async_pt_3 - Output: async_pt_3.tableoid, async_pt_3.ctid - Filter: (async_pt_3.b = 0) -(13 rows) - -DELETE FROM async_pt WHERE b = 0 RETURNING *; - a | b | c -------+---+---------- - 1000 | 0 | 00000000 - 2000 | 0 | 00000000 - 3000 | 0 | 00000000 -(3 rows) - --- Check EXPLAIN ANALYZE for a query that scans empty partitions asynchronously -DELETE FROM async_p1; -DELETE FROM async_p2; -DELETE FROM async_p3; -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) -SELECT * FROM async_pt; - QUERY PLAN -------------------------------------------------------------------------- - Append (actual rows=0 loops=1) - -> Async Foreign Scan on async_p1 async_pt_1 (actual rows=0 loops=1) - -> Async Foreign Scan on async_p2 async_pt_2 (actual rows=0 loops=1) - -> Seq Scan on async_p3 async_pt_3 (actual rows=0 loops=1) -(4 rows) - --- Clean up -DROP TABLE async_pt; -DROP TABLE base_tbl1; -DROP TABLE base_tbl2; -DROP TABLE result_tbl; -DROP TABLE join_tbl; --- Test that an asynchronous fetch is processed before restarting the scan in --- ReScanForeignScan -CREATE TABLE base_tbl (a int, b int); -INSERT INTO base_tbl VALUES (1, 11), (2, 22), (3, 33); -CREATE FOREIGN TABLE foreign_tbl (b int) - SERVER loopback OPTIONS (table_name 'base_tbl'); -CREATE FOREIGN TABLE foreign_tbl2 () INHERITS (foreign_tbl) - SERVER loopback OPTIONS (table_name 'base_tbl'); -EXPLAIN (VERBOSE, COSTS OFF) -SELECT a FROM base_tbl WHERE (a, random() > 0) IN (SELECT a, random() > 0 FROM foreign_tbl); - QUERY PLAN ---------------------------------------------------------------------------------------------------------------- - Seq Scan on public.base_tbl - Output: base_tbl.a - Filter: (ANY ((base_tbl.a = (SubPlan 1).col1) AND ((random() > '0'::double precision) = (SubPlan 1).col2))) - SubPlan 1 - -> Result - Output: base_tbl.a, (random() > '0'::double precision) - -> Append - -> Async Foreign Scan on public.foreign_tbl foreign_tbl_1 - Remote SQL: SELECT NULL FROM public.base_tbl - -> Async Foreign Scan on public.foreign_tbl2 foreign_tbl_2 - Remote SQL: SELECT NULL FROM public.base_tbl -(11 rows) - -SELECT a FROM base_tbl WHERE (a, random() > 0) IN (SELECT a, random() > 0 FROM foreign_tbl); - a ---- - 1 - 2 - 3 -(3 rows) - --- Clean up -DROP FOREIGN TABLE foreign_tbl CASCADE; -NOTICE: drop cascades to foreign table foreign_tbl2 -DROP TABLE base_tbl; -ALTER SERVER loopback OPTIONS (DROP async_capable); -ALTER SERVER loopback2 OPTIONS (DROP async_capable); --- =================================================================== --- test invalid server, foreign table and foreign data wrapper options --- =================================================================== --- Invalid fdw_startup_cost option -CREATE SERVER inv_scst FOREIGN DATA WRAPPER postgres_fdw - OPTIONS(fdw_startup_cost '100$%$#$#'); -ERROR: invalid value for floating point option "fdw_startup_cost": 100$%$#$# --- Invalid fdw_tuple_cost option -CREATE SERVER inv_scst FOREIGN DATA WRAPPER postgres_fdw - OPTIONS(fdw_tuple_cost '100$%$#$#'); -ERROR: invalid value for floating point option "fdw_tuple_cost": 100$%$#$# --- Invalid fetch_size option -CREATE FOREIGN TABLE inv_fsz (c1 int ) - SERVER loopback OPTIONS (fetch_size '100$%$#$#'); -ERROR: invalid value for integer option "fetch_size": 100$%$#$# --- Invalid batch_size option -CREATE FOREIGN TABLE inv_bsz (c1 int ) - SERVER loopback OPTIONS (batch_size '100$%$#$#'); -ERROR: invalid value for integer option "batch_size": 100$%$#$# --- No option is allowed to be specified at foreign data wrapper level -ALTER FOREIGN DATA WRAPPER postgres_fdw OPTIONS (nonexistent 'fdw'); -ERROR: invalid option "nonexistent" -HINT: There are no valid options in this context. --- =================================================================== --- test postgres_fdw.application_name GUC --- =================================================================== --- To avoid race conditions in checking the remote session's application_name, --- use this view to make the remote session itself read its application_name. -CREATE VIEW my_application_name AS - SELECT application_name FROM pg_stat_activity WHERE pid = pg_backend_pid(); -CREATE FOREIGN TABLE remote_application_name (application_name text) - SERVER loopback2 - OPTIONS (schema_name 'public', table_name 'my_application_name'); -SELECT count(*) FROM remote_application_name; - count -------- - 1 -(1 row) - --- Specify escape sequences in application_name option of a server --- object so as to test that they are replaced with status information --- expectedly. Note that we are also relying on ALTER SERVER to force --- the remote session to be restarted with its new application name. --- --- Since pg_stat_activity.application_name may be truncated to less than --- NAMEDATALEN characters, note that substring() needs to be used --- at the condition of test query to make sure that the string consisting --- of database name and process ID is also less than that. -ALTER SERVER loopback2 OPTIONS (application_name 'fdw_%d%p'); -SELECT count(*) FROM remote_application_name - WHERE application_name = - substring('fdw_' || current_database() || pg_backend_pid() for - current_setting('max_identifier_length')::int); - count -------- - 1 -(1 row) - --- postgres_fdw.application_name overrides application_name option --- of a server object if both settings are present. -ALTER SERVER loopback2 OPTIONS (SET application_name 'fdw_wrong'); -SET postgres_fdw.application_name TO 'fdw_%a%u%%'; -SELECT count(*) FROM remote_application_name - WHERE application_name = - substring('fdw_' || current_setting('application_name') || - CURRENT_USER || '%' for current_setting('max_identifier_length')::int); - count -------- - 1 -(1 row) - -RESET postgres_fdw.application_name; --- Test %c (session ID) and %C (cluster name) escape sequences. -ALTER SERVER loopback2 OPTIONS (SET application_name 'fdw_%C%c'); -SELECT count(*) FROM remote_application_name - WHERE application_name = - substring('fdw_' || current_setting('cluster_name') || - to_hex(trunc(EXTRACT(EPOCH FROM (SELECT backend_start FROM - pg_stat_get_activity(pg_backend_pid()))))::integer) || '.' || - to_hex(pg_backend_pid()) - for current_setting('max_identifier_length')::int); - count -------- - 1 -(1 row) - --- Clean up. -DROP FOREIGN TABLE remote_application_name; -DROP VIEW my_application_name; --- =================================================================== --- test parallel commit and parallel abort --- =================================================================== -ALTER SERVER loopback OPTIONS (ADD parallel_commit 'true'); -ALTER SERVER loopback OPTIONS (ADD parallel_abort 'true'); -ALTER SERVER loopback2 OPTIONS (ADD parallel_commit 'true'); -ALTER SERVER loopback2 OPTIONS (ADD parallel_abort 'true'); -CREATE TABLE ploc1 (f1 int, f2 text); -CREATE FOREIGN TABLE prem1 (f1 int, f2 text) - SERVER loopback OPTIONS (table_name 'ploc1'); -CREATE TABLE ploc2 (f1 int, f2 text); -CREATE FOREIGN TABLE prem2 (f1 int, f2 text) - SERVER loopback2 OPTIONS (table_name 'ploc2'); -BEGIN; -INSERT INTO prem1 VALUES (101, 'foo'); -INSERT INTO prem2 VALUES (201, 'bar'); -COMMIT; -SELECT * FROM prem1; - f1 | f2 ------+----- - 101 | foo -(1 row) - -SELECT * FROM prem2; - f1 | f2 ------+----- - 201 | bar -(1 row) - -BEGIN; -SAVEPOINT s; -INSERT INTO prem1 VALUES (102, 'foofoo'); -INSERT INTO prem2 VALUES (202, 'barbar'); -RELEASE SAVEPOINT s; -COMMIT; -SELECT * FROM prem1; - f1 | f2 ------+-------- - 101 | foo - 102 | foofoo -(2 rows) - -SELECT * FROM prem2; - f1 | f2 ------+-------- - 201 | bar - 202 | barbar -(2 rows) - --- This tests executing DEALLOCATE ALL against foreign servers in parallel --- during pre-commit -BEGIN; -SAVEPOINT s; -INSERT INTO prem1 VALUES (103, 'baz'); -INSERT INTO prem2 VALUES (203, 'qux'); -ROLLBACK TO SAVEPOINT s; -RELEASE SAVEPOINT s; -INSERT INTO prem1 VALUES (104, 'bazbaz'); -INSERT INTO prem2 VALUES (204, 'quxqux'); -COMMIT; -SELECT * FROM prem1; - f1 | f2 ------+-------- - 101 | foo - 102 | foofoo - 104 | bazbaz -(3 rows) - -SELECT * FROM prem2; - f1 | f2 ------+-------- - 201 | bar - 202 | barbar - 204 | quxqux -(3 rows) - -BEGIN; -INSERT INTO prem1 VALUES (105, 'test1'); -INSERT INTO prem2 VALUES (205, 'test2'); -ABORT; -SELECT * FROM prem1; - f1 | f2 ------+-------- - 101 | foo - 102 | foofoo - 104 | bazbaz -(3 rows) - -SELECT * FROM prem2; - f1 | f2 ------+-------- - 201 | bar - 202 | barbar - 204 | quxqux -(3 rows) - --- This tests executing DEALLOCATE ALL against foreign servers in parallel --- during post-abort -BEGIN; -SAVEPOINT s; -INSERT INTO prem1 VALUES (105, 'test1'); -INSERT INTO prem2 VALUES (205, 'test2'); -ROLLBACK TO SAVEPOINT s; -RELEASE SAVEPOINT s; -INSERT INTO prem1 VALUES (105, 'test1'); -INSERT INTO prem2 VALUES (205, 'test2'); -ABORT; -SELECT * FROM prem1; - f1 | f2 ------+-------- - 101 | foo - 102 | foofoo - 104 | bazbaz -(3 rows) - -SELECT * FROM prem2; - f1 | f2 ------+-------- - 201 | bar - 202 | barbar - 204 | quxqux -(3 rows) - -ALTER SERVER loopback OPTIONS (DROP parallel_commit); -ALTER SERVER loopback OPTIONS (DROP parallel_abort); -ALTER SERVER loopback2 OPTIONS (DROP parallel_commit); -ALTER SERVER loopback2 OPTIONS (DROP parallel_abort); --- =================================================================== --- test for ANALYZE sampling --- =================================================================== -CREATE TABLE analyze_table (id int, a text, b bigint); -CREATE FOREIGN TABLE analyze_ftable (id int, a text, b bigint) - SERVER loopback OPTIONS (table_name 'analyze_rtable1'); -INSERT INTO analyze_table (SELECT x FROM generate_series(1,1000) x); -ANALYZE analyze_table; -SET default_statistics_target = 10; -ANALYZE analyze_table; -ALTER SERVER loopback OPTIONS (analyze_sampling 'invalid'); -ERROR: invalid value for string option "analyze_sampling": invalid -ALTER SERVER loopback OPTIONS (analyze_sampling 'auto'); -ANALYZE analyze_table; -ALTER SERVER loopback OPTIONS (SET analyze_sampling 'system'); -ANALYZE analyze_table; -ALTER SERVER loopback OPTIONS (SET analyze_sampling 'bernoulli'); -ANALYZE analyze_table; -ALTER SERVER loopback OPTIONS (SET analyze_sampling 'random'); -ANALYZE analyze_table; -ALTER SERVER loopback OPTIONS (SET analyze_sampling 'off'); -ANALYZE analyze_table; --- cleanup -DROP FOREIGN TABLE analyze_ftable; -DROP TABLE analyze_table; +server closed the connection unexpectedly + This probably means the server terminated abnormally + before or while processing the request. +connection to server was lost