diff options
Diffstat (limited to 'src/test/regress')
| -rw-r--r-- | src/test/regress/expected/collate.icu.utf8.out | 81 | ||||
| -rw-r--r-- | src/test/regress/expected/eager_aggregate.out | 1712 | ||||
| -rw-r--r-- | src/test/regress/expected/join.out | 12 | ||||
| -rw-r--r-- | src/test/regress/expected/opr_sanity.out | 2 | ||||
| -rw-r--r-- | src/test/regress/expected/partition_aggregate.out | 2 | ||||
| -rw-r--r-- | src/test/regress/expected/psql.out | 6 | ||||
| -rw-r--r-- | src/test/regress/expected/publication.out | 570 | ||||
| -rw-r--r-- | src/test/regress/expected/rules.out | 14 | ||||
| -rw-r--r-- | src/test/regress/expected/sysviews.out | 3 | ||||
| -rw-r--r-- | src/test/regress/expected/vacuum.out | 6 | ||||
| -rw-r--r-- | src/test/regress/expected/window.out | 16 | ||||
| -rw-r--r-- | src/test/regress/parallel_schedule | 2 | ||||
| -rw-r--r-- | src/test/regress/sql/collate.icu.utf8.sql | 13 | ||||
| -rw-r--r-- | src/test/regress/sql/eager_aggregate.sql | 377 | ||||
| -rw-r--r-- | src/test/regress/sql/opr_sanity.sql | 2 | ||||
| -rw-r--r-- | src/test/regress/sql/partition_aggregate.sql | 2 | ||||
| -rw-r--r-- | src/test/regress/sql/publication.sql | 46 | ||||
| -rw-r--r-- | src/test/regress/sql/window.sql | 13 |
18 files changed, 2605 insertions, 274 deletions
diff --git a/src/test/regress/expected/collate.icu.utf8.out b/src/test/regress/expected/collate.icu.utf8.out index 69805d4b9ec..05d8b3b369e 100644 --- a/src/test/regress/expected/collate.icu.utf8.out +++ b/src/test/regress/expected/collate.icu.utf8.out @@ -2437,11 +2437,11 @@ SELECT c collate "C", count(c) FROM pagg_tab3 GROUP BY c collate "C" ORDER BY 1; SET enable_partitionwise_join TO false; EXPLAIN (COSTS OFF) SELECT t1.c, count(t2.c) FROM pagg_tab3 t1 JOIN pagg_tab3 t2 ON t1.c = t2.c GROUP BY 1 ORDER BY t1.c COLLATE "C"; - QUERY PLAN -------------------------------------------------------------- + QUERY PLAN +------------------------------------------------------------------- Sort Sort Key: t1.c COLLATE "C" - -> HashAggregate + -> Finalize HashAggregate Group Key: t1.c -> Hash Join Hash Cond: (t1.c = t2.c) @@ -2449,10 +2449,12 @@ SELECT t1.c, count(t2.c) FROM pagg_tab3 t1 JOIN pagg_tab3 t2 ON t1.c = t2.c GROU -> Seq Scan on pagg_tab3_p2 t1_1 -> Seq Scan on pagg_tab3_p1 t1_2 -> Hash - -> Append - -> Seq Scan on pagg_tab3_p2 t2_1 - -> Seq Scan on pagg_tab3_p1 t2_2 -(13 rows) + -> Partial HashAggregate + Group Key: t2.c + -> Append + -> Seq Scan on pagg_tab3_p2 t2_1 + -> Seq Scan on pagg_tab3_p1 t2_2 +(15 rows) SELECT t1.c, count(t2.c) FROM pagg_tab3 t1 JOIN pagg_tab3 t2 ON t1.c = t2.c GROUP BY 1 ORDER BY t1.c COLLATE "C"; c | count @@ -2464,11 +2466,11 @@ SELECT t1.c, count(t2.c) FROM pagg_tab3 t1 JOIN pagg_tab3 t2 ON t1.c = t2.c GROU SET enable_partitionwise_join TO true; EXPLAIN (COSTS OFF) SELECT t1.c, count(t2.c) FROM pagg_tab3 t1 JOIN pagg_tab3 t2 ON t1.c = t2.c GROUP BY 1 ORDER BY t1.c COLLATE "C"; - QUERY PLAN -------------------------------------------------------------- + QUERY PLAN +------------------------------------------------------------------- Sort Sort Key: t1.c COLLATE "C" - -> HashAggregate + -> Finalize HashAggregate Group Key: t1.c -> Hash Join Hash Cond: (t1.c = t2.c) @@ -2476,10 +2478,12 @@ SELECT t1.c, count(t2.c) FROM pagg_tab3 t1 JOIN pagg_tab3 t2 ON t1.c = t2.c GROU -> Seq Scan on pagg_tab3_p2 t1_1 -> Seq Scan on pagg_tab3_p1 t1_2 -> Hash - -> Append - -> Seq Scan on pagg_tab3_p2 t2_1 - -> Seq Scan on pagg_tab3_p1 t2_2 -(13 rows) + -> Partial HashAggregate + Group Key: t2.c + -> Append + -> Seq Scan on pagg_tab3_p2 t2_1 + -> Seq Scan on pagg_tab3_p1 t2_2 +(15 rows) SELECT t1.c, count(t2.c) FROM pagg_tab3 t1 JOIN pagg_tab3 t2 ON t1.c = t2.c GROUP BY 1 ORDER BY t1.c COLLATE "C"; c | count @@ -2690,6 +2694,55 @@ SELECT * FROM t5 ORDER BY c ASC, a ASC; 3 | d1 | d1 (3 rows) +-- Check that DEFAULT expressions in SQL/JSON functions use the same collation +-- as the RETURNING type. Mismatched collations should raise an error. +CREATE DOMAIN d1 AS text COLLATE case_insensitive; +CREATE DOMAIN d2 AS text COLLATE "C"; +SELECT JSON_VALUE('{"a": "A"}', '$.a' RETURNING d1 DEFAULT ('C' COLLATE "C") COLLATE case_insensitive ON EMPTY) = 'a'; -- true + ?column? +---------- + t +(1 row) + +SELECT JSON_VALUE('{"a": "A"}', '$.a' RETURNING d1 DEFAULT 'C' ON EMPTY) = 'a'; -- true + ?column? +---------- + t +(1 row) + +SELECT JSON_VALUE('{"a": "A"}', '$.a' RETURNING d1 DEFAULT 'C'::d2 ON EMPTY) = 'a'; -- error +ERROR: the collation of DEFAULT expression conflicts with RETURNING clause +LINE 1: ...ON_VALUE('{"a": "A"}', '$.a' RETURNING d1 DEFAULT 'C'::d2 ON... + ^ +DETAIL: "C" versus "case_insensitive" +SELECT JSON_VALUE('{"a": "A"}', '$.a' RETURNING d1 DEFAULT 'C' COLLATE "C" ON EMPTY) = 'a'; -- error +ERROR: the collation of DEFAULT expression conflicts with RETURNING clause +LINE 1: ...ON_VALUE('{"a": "A"}', '$.a' RETURNING d1 DEFAULT 'C' COLLAT... + ^ +DETAIL: "C" versus "case_insensitive" +SELECT JSON_VALUE('{"a": "A"}', '$.c' RETURNING d1 DEFAULT 'A' ON EMPTY) = 'a'; -- true + ?column? +---------- + t +(1 row) + +SELECT JSON_VALUE('{"a": "A"}', '$.c' RETURNING d1 DEFAULT 'A' COLLATE case_insensitive ON EMPTY) = 'a'; -- true + ?column? +---------- + t +(1 row) + +SELECT JSON_VALUE('{"a": "A"}', '$.c' RETURNING d1 DEFAULT 'A'::d2 ON EMPTY) = 'a'; -- error +ERROR: the collation of DEFAULT expression conflicts with RETURNING clause +LINE 1: ...ON_VALUE('{"a": "A"}', '$.c' RETURNING d1 DEFAULT 'A'::d2 ON... + ^ +DETAIL: "C" versus "case_insensitive" +SELECT JSON_VALUE('{"a": "A"}', '$.c' RETURNING d1 DEFAULT 'A' COLLATE "C" ON EMPTY) = 'a'; -- error +ERROR: the collation of DEFAULT expression conflicts with RETURNING clause +LINE 1: ...ON_VALUE('{"a": "A"}', '$.c' RETURNING d1 DEFAULT 'A' COLLAT... + ^ +DETAIL: "C" versus "case_insensitive" +DROP DOMAIN d1, d2; -- cleanup RESET search_path; SET client_min_messages TO warning; diff --git a/src/test/regress/expected/eager_aggregate.out b/src/test/regress/expected/eager_aggregate.out new file mode 100644 index 00000000000..5ac966186f7 --- /dev/null +++ b/src/test/regress/expected/eager_aggregate.out @@ -0,0 +1,1712 @@ +-- +-- EAGER AGGREGATION +-- Test we can push aggregation down below join +-- +CREATE TABLE eager_agg_t1 (a int, b int, c double precision); +CREATE TABLE eager_agg_t2 (a int, b int, c double precision); +CREATE TABLE eager_agg_t3 (a int, b int, c double precision); +INSERT INTO eager_agg_t1 SELECT i, i, i FROM generate_series(1, 1000) i; +INSERT INTO eager_agg_t2 SELECT i, i%10, i FROM generate_series(1, 1000) i; +INSERT INTO eager_agg_t3 SELECT i%10, i%10, i FROM generate_series(1, 1000) i; +ANALYZE eager_agg_t1; +ANALYZE eager_agg_t2; +ANALYZE eager_agg_t3; +-- +-- Test eager aggregation over base rel +-- +-- Perform scan of a table, aggregate the result, join it to the other table +-- and finalize the aggregation. +EXPLAIN (VERBOSE, COSTS OFF) +SELECT t1.a, avg(t2.c) + FROM eager_agg_t1 t1 + JOIN eager_agg_t2 t2 ON t1.b = t2.b +GROUP BY t1.a ORDER BY t1.a; + QUERY PLAN +------------------------------------------------------------------ + Finalize GroupAggregate + Output: t1.a, avg(t2.c) + Group Key: t1.a + -> Sort + Output: t1.a, (PARTIAL avg(t2.c)) + Sort Key: t1.a + -> Hash Join + Output: t1.a, (PARTIAL avg(t2.c)) + Hash Cond: (t1.b = t2.b) + -> Seq Scan on public.eager_agg_t1 t1 + Output: t1.a, t1.b, t1.c + -> Hash + Output: t2.b, (PARTIAL avg(t2.c)) + -> Partial HashAggregate + Output: t2.b, PARTIAL avg(t2.c) + Group Key: t2.b + -> Seq Scan on public.eager_agg_t2 t2 + Output: t2.a, t2.b, t2.c +(18 rows) + +SELECT t1.a, avg(t2.c) + FROM eager_agg_t1 t1 + JOIN eager_agg_t2 t2 ON t1.b = t2.b +GROUP BY t1.a ORDER BY t1.a; + a | avg +---+----- + 1 | 496 + 2 | 497 + 3 | 498 + 4 | 499 + 5 | 500 + 6 | 501 + 7 | 502 + 8 | 503 + 9 | 504 +(9 rows) + +-- Produce results with sorting aggregation +SET enable_hashagg TO off; +EXPLAIN (VERBOSE, COSTS OFF) +SELECT t1.a, avg(t2.c) + FROM eager_agg_t1 t1 + JOIN eager_agg_t2 t2 ON t1.b = t2.b +GROUP BY t1.a ORDER BY t1.a; + QUERY PLAN +------------------------------------------------------------------------ + Finalize GroupAggregate + Output: t1.a, avg(t2.c) + Group Key: t1.a + -> Sort + Output: t1.a, (PARTIAL avg(t2.c)) + Sort Key: t1.a + -> Hash Join + Output: t1.a, (PARTIAL avg(t2.c)) + Hash Cond: (t1.b = t2.b) + -> Seq Scan on public.eager_agg_t1 t1 + Output: t1.a, t1.b, t1.c + -> Hash + Output: t2.b, (PARTIAL avg(t2.c)) + -> Partial GroupAggregate + Output: t2.b, PARTIAL avg(t2.c) + Group Key: t2.b + -> Sort + Output: t2.c, t2.b + Sort Key: t2.b + -> Seq Scan on public.eager_agg_t2 t2 + Output: t2.c, t2.b +(21 rows) + +SELECT t1.a, avg(t2.c) + FROM eager_agg_t1 t1 + JOIN eager_agg_t2 t2 ON t1.b = t2.b +GROUP BY t1.a ORDER BY t1.a; + a | avg +---+----- + 1 | 496 + 2 | 497 + 3 | 498 + 4 | 499 + 5 | 500 + 6 | 501 + 7 | 502 + 8 | 503 + 9 | 504 +(9 rows) + +RESET enable_hashagg; +-- +-- Test eager aggregation over join rel +-- +-- Perform join of tables, aggregate the result, join it to the other table +-- and finalize the aggregation. +EXPLAIN (VERBOSE, COSTS OFF) +SELECT t1.a, avg(t2.c + t3.c) + FROM eager_agg_t1 t1 + JOIN eager_agg_t2 t2 ON t1.b = t2.b + JOIN eager_agg_t3 t3 ON t2.a = t3.a +GROUP BY t1.a ORDER BY t1.a; + QUERY PLAN +------------------------------------------------------------------------------ + Finalize GroupAggregate + Output: t1.a, avg((t2.c + t3.c)) + Group Key: t1.a + -> Sort + Output: t1.a, (PARTIAL avg((t2.c + t3.c))) + Sort Key: t1.a + -> Hash Join + Output: t1.a, (PARTIAL avg((t2.c + t3.c))) + Hash Cond: (t1.b = t2.b) + -> Seq Scan on public.eager_agg_t1 t1 + Output: t1.a, t1.b, t1.c + -> Hash + Output: t2.b, (PARTIAL avg((t2.c + t3.c))) + -> Partial HashAggregate + Output: t2.b, PARTIAL avg((t2.c + t3.c)) + Group Key: t2.b + -> Hash Join + Output: t2.c, t2.b, t3.c + Hash Cond: (t3.a = t2.a) + -> Seq Scan on public.eager_agg_t3 t3 + Output: t3.a, t3.b, t3.c + -> Hash + Output: t2.c, t2.b, t2.a + -> Seq Scan on public.eager_agg_t2 t2 + Output: t2.c, t2.b, t2.a +(25 rows) + +SELECT t1.a, avg(t2.c + t3.c) + FROM eager_agg_t1 t1 + JOIN eager_agg_t2 t2 ON t1.b = t2.b + JOIN eager_agg_t3 t3 ON t2.a = t3.a +GROUP BY t1.a ORDER BY t1.a; + a | avg +---+----- + 1 | 497 + 2 | 499 + 3 | 501 + 4 | 503 + 5 | 505 + 6 | 507 + 7 | 509 + 8 | 511 + 9 | 513 +(9 rows) + +-- Produce results with sorting aggregation +SET enable_hashagg TO off; +EXPLAIN (VERBOSE, COSTS OFF) +SELECT t1.a, avg(t2.c + t3.c) + FROM eager_agg_t1 t1 + JOIN eager_agg_t2 t2 ON t1.b = t2.b + JOIN eager_agg_t3 t3 ON t2.a = t3.a +GROUP BY t1.a ORDER BY t1.a; + QUERY PLAN +------------------------------------------------------------------------------------ + Finalize GroupAggregate + Output: t1.a, avg((t2.c + t3.c)) + Group Key: t1.a + -> Sort + Output: t1.a, (PARTIAL avg((t2.c + t3.c))) + Sort Key: t1.a + -> Hash Join + Output: t1.a, (PARTIAL avg((t2.c + t3.c))) + Hash Cond: (t1.b = t2.b) + -> Seq Scan on public.eager_agg_t1 t1 + Output: t1.a, t1.b, t1.c + -> Hash + Output: t2.b, (PARTIAL avg((t2.c + t3.c))) + -> Partial GroupAggregate + Output: t2.b, PARTIAL avg((t2.c + t3.c)) + Group Key: t2.b + -> Sort + Output: t2.c, t2.b, t3.c + Sort Key: t2.b + -> Hash Join + Output: t2.c, t2.b, t3.c + Hash Cond: (t3.a = t2.a) + -> Seq Scan on public.eager_agg_t3 t3 + Output: t3.a, t3.b, t3.c + -> Hash + Output: t2.c, t2.b, t2.a + -> Seq Scan on public.eager_agg_t2 t2 + Output: t2.c, t2.b, t2.a +(28 rows) + +SELECT t1.a, avg(t2.c + t3.c) + FROM eager_agg_t1 t1 + JOIN eager_agg_t2 t2 ON t1.b = t2.b + JOIN eager_agg_t3 t3 ON t2.a = t3.a +GROUP BY t1.a ORDER BY t1.a; + a | avg +---+----- + 1 | 497 + 2 | 499 + 3 | 501 + 4 | 503 + 5 | 505 + 6 | 507 + 7 | 509 + 8 | 511 + 9 | 513 +(9 rows) + +RESET enable_hashagg; +-- +-- Test that eager aggregation works for outer join +-- +-- Ensure aggregation can be pushed down to the non-nullable side +EXPLAIN (VERBOSE, COSTS OFF) +SELECT t1.a, avg(t2.c) + FROM eager_agg_t1 t1 + RIGHT JOIN eager_agg_t2 t2 ON t1.b = t2.b +GROUP BY t1.a ORDER BY t1.a; + QUERY PLAN +------------------------------------------------------------------ + Finalize GroupAggregate + Output: t1.a, avg(t2.c) + Group Key: t1.a + -> Sort + Output: t1.a, (PARTIAL avg(t2.c)) + Sort Key: t1.a + -> Hash Right Join + Output: t1.a, (PARTIAL avg(t2.c)) + Hash Cond: (t1.b = t2.b) + -> Seq Scan on public.eager_agg_t1 t1 + Output: t1.a, t1.b, t1.c + -> Hash + Output: t2.b, (PARTIAL avg(t2.c)) + -> Partial HashAggregate + Output: t2.b, PARTIAL avg(t2.c) + Group Key: t2.b + -> Seq Scan on public.eager_agg_t2 t2 + Output: t2.a, t2.b, t2.c +(18 rows) + +SELECT t1.a, avg(t2.c) + FROM eager_agg_t1 t1 + RIGHT JOIN eager_agg_t2 t2 ON t1.b = t2.b +GROUP BY t1.a ORDER BY t1.a; + a | avg +---+----- + 1 | 496 + 2 | 497 + 3 | 498 + 4 | 499 + 5 | 500 + 6 | 501 + 7 | 502 + 8 | 503 + 9 | 504 + | 505 +(10 rows) + +-- Ensure aggregation cannot be pushed down to the nullable side +EXPLAIN (VERBOSE, COSTS OFF) +SELECT t2.b, avg(t2.c) + FROM eager_agg_t1 t1 + LEFT JOIN eager_agg_t2 t2 ON t1.b = t2.b +GROUP BY t2.b ORDER BY t2.b; + QUERY PLAN +------------------------------------------------------------ + Sort + Output: t2.b, (avg(t2.c)) + Sort Key: t2.b + -> HashAggregate + Output: t2.b, avg(t2.c) + Group Key: t2.b + -> Hash Right Join + Output: t2.b, t2.c + Hash Cond: (t2.b = t1.b) + -> Seq Scan on public.eager_agg_t2 t2 + Output: t2.a, t2.b, t2.c + -> Hash + Output: t1.b + -> Seq Scan on public.eager_agg_t1 t1 + Output: t1.b +(15 rows) + +SELECT t2.b, avg(t2.c) + FROM eager_agg_t1 t1 + LEFT JOIN eager_agg_t2 t2 ON t1.b = t2.b +GROUP BY t2.b ORDER BY t2.b; + b | avg +---+----- + 1 | 496 + 2 | 497 + 3 | 498 + 4 | 499 + 5 | 500 + 6 | 501 + 7 | 502 + 8 | 503 + 9 | 504 + | +(10 rows) + +-- +-- Test that eager aggregation works for parallel plans +-- +SET parallel_setup_cost=0; +SET parallel_tuple_cost=0; +SET min_parallel_table_scan_size=0; +SET max_parallel_workers_per_gather=4; +EXPLAIN (VERBOSE, COSTS OFF) +SELECT t1.a, avg(t2.c) + FROM eager_agg_t1 t1 + JOIN eager_agg_t2 t2 ON t1.b = t2.b +GROUP BY t1.a ORDER BY t1.a; + QUERY PLAN +--------------------------------------------------------------------------------- + Finalize GroupAggregate + Output: t1.a, avg(t2.c) + Group Key: t1.a + -> Gather Merge + Output: t1.a, (PARTIAL avg(t2.c)) + Workers Planned: 2 + -> Sort + Output: t1.a, (PARTIAL avg(t2.c)) + Sort Key: t1.a + -> Parallel Hash Join + Output: t1.a, (PARTIAL avg(t2.c)) + Hash Cond: (t1.b = t2.b) + -> Parallel Seq Scan on public.eager_agg_t1 t1 + Output: t1.a, t1.b, t1.c + -> Parallel Hash + Output: t2.b, (PARTIAL avg(t2.c)) + -> Partial HashAggregate + Output: t2.b, PARTIAL avg(t2.c) + Group Key: t2.b + -> Parallel Seq Scan on public.eager_agg_t2 t2 + Output: t2.a, t2.b, t2.c +(21 rows) + +SELECT t1.a, avg(t2.c) + FROM eager_agg_t1 t1 + JOIN eager_agg_t2 t2 ON t1.b = t2.b +GROUP BY t1.a ORDER BY t1.a; + a | avg +---+----- + 1 | 496 + 2 | 497 + 3 | 498 + 4 | 499 + 5 | 500 + 6 | 501 + 7 | 502 + 8 | 503 + 9 | 504 +(9 rows) + +RESET parallel_setup_cost; +RESET parallel_tuple_cost; +RESET min_parallel_table_scan_size; +RESET max_parallel_workers_per_gather; +-- +-- Test eager aggregation with GEQO +-- +SET geqo = on; +SET geqo_threshold = 2; +EXPLAIN (VERBOSE, COSTS OFF) +SELECT t1.a, avg(t2.c) + FROM eager_agg_t1 t1 + JOIN eager_agg_t2 t2 ON t1.b = t2.b +GROUP BY t1.a ORDER BY t1.a; + QUERY PLAN +------------------------------------------------------------------ + Finalize GroupAggregate + Output: t1.a, avg(t2.c) + Group Key: t1.a + -> Sort + Output: t1.a, (PARTIAL avg(t2.c)) + Sort Key: t1.a + -> Hash Join + Output: t1.a, (PARTIAL avg(t2.c)) + Hash Cond: (t1.b = t2.b) + -> Seq Scan on public.eager_agg_t1 t1 + Output: t1.a, t1.b, t1.c + -> Hash + Output: t2.b, (PARTIAL avg(t2.c)) + -> Partial HashAggregate + Output: t2.b, PARTIAL avg(t2.c) + Group Key: t2.b + -> Seq Scan on public.eager_agg_t2 t2 + Output: t2.a, t2.b, t2.c +(18 rows) + +SELECT t1.a, avg(t2.c) + FROM eager_agg_t1 t1 + JOIN eager_agg_t2 t2 ON t1.b = t2.b +GROUP BY t1.a ORDER BY t1.a; + a | avg +---+----- + 1 | 496 + 2 | 497 + 3 | 498 + 4 | 499 + 5 | 500 + 6 | 501 + 7 | 502 + 8 | 503 + 9 | 504 +(9 rows) + +RESET geqo; +RESET geqo_threshold; +DROP TABLE eager_agg_t1; +DROP TABLE eager_agg_t2; +DROP TABLE eager_agg_t3; +-- +-- Test eager aggregation for partitionwise join +-- +-- Enable partitionwise aggregate, which by default is disabled. +SET enable_partitionwise_aggregate TO true; +-- Enable partitionwise join, which by default is disabled. +SET enable_partitionwise_join TO true; +CREATE TABLE eager_agg_tab1(x int, y int) PARTITION BY RANGE(x); +CREATE TABLE eager_agg_tab1_p1 PARTITION OF eager_agg_tab1 FOR VALUES FROM (0) TO (5); +CREATE TABLE eager_agg_tab1_p2 PARTITION OF eager_agg_tab1 FOR VALUES FROM (5) TO (10); +CREATE TABLE eager_agg_tab1_p3 PARTITION OF eager_agg_tab1 FOR VALUES FROM (10) TO (15); +CREATE TABLE eager_agg_tab2(x int, y int) PARTITION BY RANGE(y); +CREATE TABLE eager_agg_tab2_p1 PARTITION OF eager_agg_tab2 FOR VALUES FROM (0) TO (5); +CREATE TABLE eager_agg_tab2_p2 PARTITION OF eager_agg_tab2 FOR VALUES FROM (5) TO (10); +CREATE TABLE eager_agg_tab2_p3 PARTITION OF eager_agg_tab2 FOR VALUES FROM (10) TO (15); +INSERT INTO eager_agg_tab1 SELECT i % 15, i % 10 FROM generate_series(1, 1000) i; +INSERT INTO eager_agg_tab2 SELECT i % 10, i % 15 FROM generate_series(1, 1000) i; +ANALYZE eager_agg_tab1; +ANALYZE eager_agg_tab2; +-- When GROUP BY clause matches; full aggregation is performed for each +-- partition. +EXPLAIN (VERBOSE, COSTS OFF) +SELECT t1.x, sum(t1.y), count(*) + FROM eager_agg_tab1 t1 + JOIN eager_agg_tab2 t2 ON t1.x = t2.y +GROUP BY t1.x ORDER BY t1.x; + QUERY PLAN +--------------------------------------------------------------------------------------- + Sort + Output: t1.x, (sum(t1.y)), (count(*)) + Sort Key: t1.x + -> Append + -> Finalize HashAggregate + Output: t1.x, sum(t1.y), count(*) + Group Key: t1.x + -> Hash Join + Output: t1.x, (PARTIAL sum(t1.y)), (PARTIAL count(*)) + Hash Cond: (t2.y = t1.x) + -> Seq Scan on public.eager_agg_tab2_p1 t2 + Output: t2.y + -> Hash + Output: t1.x, (PARTIAL sum(t1.y)), (PARTIAL count(*)) + -> Partial HashAggregate + Output: t1.x, PARTIAL sum(t1.y), PARTIAL count(*) + Group Key: t1.x + -> Seq Scan on public.eager_agg_tab1_p1 t1 + Output: t1.x, t1.y + -> Finalize HashAggregate + Output: t1_1.x, sum(t1_1.y), count(*) + Group Key: t1_1.x + -> Hash Join + Output: t1_1.x, (PARTIAL sum(t1_1.y)), (PARTIAL count(*)) + Hash Cond: (t2_1.y = t1_1.x) + -> Seq Scan on public.eager_agg_tab2_p2 t2_1 + Output: t2_1.y + -> Hash + Output: t1_1.x, (PARTIAL sum(t1_1.y)), (PARTIAL count(*)) + -> Partial HashAggregate + Output: t1_1.x, PARTIAL sum(t1_1.y), PARTIAL count(*) + Group Key: t1_1.x + -> Seq Scan on public.eager_agg_tab1_p2 t1_1 + Output: t1_1.x, t1_1.y + -> Finalize HashAggregate + Output: t1_2.x, sum(t1_2.y), count(*) + Group Key: t1_2.x + -> Hash Join + Output: t1_2.x, (PARTIAL sum(t1_2.y)), (PARTIAL count(*)) + Hash Cond: (t2_2.y = t1_2.x) + -> Seq Scan on public.eager_agg_tab2_p3 t2_2 + Output: t2_2.y + -> Hash + Output: t1_2.x, (PARTIAL sum(t1_2.y)), (PARTIAL count(*)) + -> Partial HashAggregate + Output: t1_2.x, PARTIAL sum(t1_2.y), PARTIAL count(*) + Group Key: t1_2.x + -> Seq Scan on public.eager_agg_tab1_p3 t1_2 + Output: t1_2.x, t1_2.y +(49 rows) + +SELECT t1.x, sum(t1.y), count(*) + FROM eager_agg_tab1 t1 + JOIN eager_agg_tab2 t2 ON t1.x = t2.y +GROUP BY t1.x ORDER BY t1.x; + x | sum | count +----+-------+------- + 0 | 10890 | 4356 + 1 | 15544 | 4489 + 2 | 20033 | 4489 + 3 | 24522 | 4489 + 4 | 29011 | 4489 + 5 | 11390 | 4489 + 6 | 15879 | 4489 + 7 | 20368 | 4489 + 8 | 24857 | 4489 + 9 | 29346 | 4489 + 10 | 11055 | 4489 + 11 | 15246 | 4356 + 12 | 19602 | 4356 + 13 | 23958 | 4356 + 14 | 28314 | 4356 +(15 rows) + +-- GROUP BY having other matching key +EXPLAIN (VERBOSE, COSTS OFF) +SELECT t2.y, sum(t1.y), count(*) + FROM eager_agg_tab1 t1 + JOIN eager_agg_tab2 t2 ON t1.x = t2.y +GROUP BY t2.y ORDER BY t2.y; + QUERY PLAN +--------------------------------------------------------------------------------------- + Sort + Output: t2.y, (sum(t1.y)), (count(*)) + Sort Key: t2.y + -> Append + -> Finalize HashAggregate + Output: t2.y, sum(t1.y), count(*) + Group Key: t2.y + -> Hash Join + Output: t2.y, (PARTIAL sum(t1.y)), (PARTIAL count(*)) + Hash Cond: (t2.y = t1.x) + -> Seq Scan on public.eager_agg_tab2_p1 t2 + Output: t2.y + -> Hash + Output: t1.x, (PARTIAL sum(t1.y)), (PARTIAL count(*)) + -> Partial HashAggregate + Output: t1.x, PARTIAL sum(t1.y), PARTIAL count(*) + Group Key: t1.x + -> Seq Scan on public.eager_agg_tab1_p1 t1 + Output: t1.y, t1.x + -> Finalize HashAggregate + Output: t2_1.y, sum(t1_1.y), count(*) + Group Key: t2_1.y + -> Hash Join + Output: t2_1.y, (PARTIAL sum(t1_1.y)), (PARTIAL count(*)) + Hash Cond: (t2_1.y = t1_1.x) + -> Seq Scan on public.eager_agg_tab2_p2 t2_1 + Output: t2_1.y + -> Hash + Output: t1_1.x, (PARTIAL sum(t1_1.y)), (PARTIAL count(*)) + -> Partial HashAggregate + Output: t1_1.x, PARTIAL sum(t1_1.y), PARTIAL count(*) + Group Key: t1_1.x + -> Seq Scan on public.eager_agg_tab1_p2 t1_1 + Output: t1_1.y, t1_1.x + -> Finalize HashAggregate + Output: t2_2.y, sum(t1_2.y), count(*) + Group Key: t2_2.y + -> Hash Join + Output: t2_2.y, (PARTIAL sum(t1_2.y)), (PARTIAL count(*)) + Hash Cond: (t2_2.y = t1_2.x) + -> Seq Scan on public.eager_agg_tab2_p3 t2_2 + Output: t2_2.y + -> Hash + Output: t1_2.x, (PARTIAL sum(t1_2.y)), (PARTIAL count(*)) + -> Partial HashAggregate + Output: t1_2.x, PARTIAL sum(t1_2.y), PARTIAL count(*) + Group Key: t1_2.x + -> Seq Scan on public.eager_agg_tab1_p3 t1_2 + Output: t1_2.y, t1_2.x +(49 rows) + +SELECT t2.y, sum(t1.y), count(*) + FROM eager_agg_tab1 t1 + JOIN eager_agg_tab2 t2 ON t1.x = t2.y +GROUP BY t2.y ORDER BY t2.y; + y | sum | count +----+-------+------- + 0 | 10890 | 4356 + 1 | 15544 | 4489 + 2 | 20033 | 4489 + 3 | 24522 | 4489 + 4 | 29011 | 4489 + 5 | 11390 | 4489 + 6 | 15879 | 4489 + 7 | 20368 | 4489 + 8 | 24857 | 4489 + 9 | 29346 | 4489 + 10 | 11055 | 4489 + 11 | 15246 | 4356 + 12 | 19602 | 4356 + 13 | 23958 | 4356 + 14 | 28314 | 4356 +(15 rows) + +-- When GROUP BY clause does not match; partial aggregation is performed for +-- each partition. +EXPLAIN (VERBOSE, COSTS OFF) +SELECT t2.x, sum(t1.x), count(*) + FROM eager_agg_tab1 t1 + JOIN eager_agg_tab2 t2 ON t1.x = t2.y +GROUP BY t2.x HAVING avg(t1.x) > 5 ORDER BY t2.x; + QUERY PLAN +------------------------------------------------------------------------------------------------------------ + Sort + Output: t2.x, (sum(t1.x)), (count(*)) + Sort Key: t2.x + -> Finalize HashAggregate + Output: t2.x, sum(t1.x), count(*) + Group Key: t2.x + Filter: (avg(t1.x) > '5'::numeric) + -> Append + -> Hash Join + Output: t2.x, (PARTIAL sum(t1.x)), (PARTIAL count(*)), (PARTIAL avg(t1.x)) + Hash Cond: (t2.y = t1.x) + -> Seq Scan on public.eager_agg_tab2_p1 t2 + Output: t2.x, t2.y + -> Hash + Output: t1.x, (PARTIAL sum(t1.x)), (PARTIAL count(*)), (PARTIAL avg(t1.x)) + -> Partial HashAggregate + Output: t1.x, PARTIAL sum(t1.x), PARTIAL count(*), PARTIAL avg(t1.x) + Group Key: t1.x + -> Seq Scan on public.eager_agg_tab1_p1 t1 + Output: t1.x + -> Hash Join + Output: t2_1.x, (PARTIAL sum(t1_1.x)), (PARTIAL count(*)), (PARTIAL avg(t1_1.x)) + Hash Cond: (t2_1.y = t1_1.x) + -> Seq Scan on public.eager_agg_tab2_p2 t2_1 + Output: t2_1.x, t2_1.y + -> Hash + Output: t1_1.x, (PARTIAL sum(t1_1.x)), (PARTIAL count(*)), (PARTIAL avg(t1_1.x)) + -> Partial HashAggregate + Output: t1_1.x, PARTIAL sum(t1_1.x), PARTIAL count(*), PARTIAL avg(t1_1.x) + Group Key: t1_1.x + -> Seq Scan on public.eager_agg_tab1_p2 t1_1 + Output: t1_1.x + -> Hash Join + Output: t2_2.x, (PARTIAL sum(t1_2.x)), (PARTIAL count(*)), (PARTIAL avg(t1_2.x)) + Hash Cond: (t2_2.y = t1_2.x) + -> Seq Scan on public.eager_agg_tab2_p3 t2_2 + Output: t2_2.x, t2_2.y + -> Hash + Output: t1_2.x, (PARTIAL sum(t1_2.x)), (PARTIAL count(*)), (PARTIAL avg(t1_2.x)) + -> Partial HashAggregate + Output: t1_2.x, PARTIAL sum(t1_2.x), PARTIAL count(*), PARTIAL avg(t1_2.x) + Group Key: t1_2.x + -> Seq Scan on public.eager_agg_tab1_p3 t1_2 + Output: t1_2.x +(44 rows) + +SELECT t2.x, sum(t1.x), count(*) + FROM eager_agg_tab1 t1 + JOIN eager_agg_tab2 t2 ON t1.x = t2.y +GROUP BY t2.x HAVING avg(t1.x) > 5 ORDER BY t2.x; + x | sum | count +---+-------+------- + 0 | 33835 | 6667 + 1 | 39502 | 6667 + 2 | 46169 | 6667 + 3 | 52836 | 6667 + 4 | 59503 | 6667 + 5 | 33500 | 6667 + 6 | 39837 | 6667 + 7 | 46504 | 6667 + 8 | 53171 | 6667 + 9 | 59838 | 6667 +(10 rows) + +-- Check with eager aggregation over join rel +-- full aggregation +EXPLAIN (VERBOSE, COSTS OFF) +SELECT t1.x, sum(t2.y + t3.y) + FROM eager_agg_tab1 t1 + JOIN eager_agg_tab1 t2 ON t1.x = t2.x + JOIN eager_agg_tab1 t3 ON t2.x = t3.x +GROUP BY t1.x ORDER BY t1.x; + QUERY PLAN +------------------------------------------------------------------------------------------- + Sort + Output: t1.x, (sum((t2.y + t3.y))) + Sort Key: t1.x + -> Append + -> Finalize HashAggregate + Output: t1.x, sum((t2.y + t3.y)) + Group Key: t1.x + -> Hash Join + Output: t1.x, (PARTIAL sum((t2.y + t3.y))) + Hash Cond: (t1.x = t2.x) + -> Seq Scan on public.eager_agg_tab1_p1 t1 + Output: t1.x + -> Hash + Output: t2.x, t3.x, (PARTIAL sum((t2.y + t3.y))) + -> Partial HashAggregate + Output: t2.x, t3.x, PARTIAL sum((t2.y + t3.y)) + Group Key: t2.x + -> Hash Join + Output: t2.y, t2.x, t3.y, t3.x + Hash Cond: (t2.x = t3.x) + -> Seq Scan on public.eager_agg_tab1_p1 t2 + Output: t2.y, t2.x + -> Hash + Output: t3.y, t3.x + -> Seq Scan on public.eager_agg_tab1_p1 t3 + Output: t3.y, t3.x + -> Finalize HashAggregate + Output: t1_1.x, sum((t2_1.y + t3_1.y)) + Group Key: t1_1.x + -> Hash Join + Output: t1_1.x, (PARTIAL sum((t2_1.y + t3_1.y))) + Hash Cond: (t1_1.x = t2_1.x) + -> Seq Scan on public.eager_agg_tab1_p2 t1_1 + Output: t1_1.x + -> Hash + Output: t2_1.x, t3_1.x, (PARTIAL sum((t2_1.y + t3_1.y))) + -> Partial HashAggregate + Output: t2_1.x, t3_1.x, PARTIAL sum((t2_1.y + t3_1.y)) + Group Key: t2_1.x + -> Hash Join + Output: t2_1.y, t2_1.x, t3_1.y, t3_1.x + Hash Cond: (t2_1.x = t3_1.x) + -> Seq Scan on public.eager_agg_tab1_p2 t2_1 + Output: t2_1.y, t2_1.x + -> Hash + Output: t3_1.y, t3_1.x + -> Seq Scan on public.eager_agg_tab1_p2 t3_1 + Output: t3_1.y, t3_1.x + -> Finalize HashAggregate + Output: t1_2.x, sum((t2_2.y + t3_2.y)) + Group Key: t1_2.x + -> Hash Join + Output: t1_2.x, (PARTIAL sum((t2_2.y + t3_2.y))) + Hash Cond: (t1_2.x = t2_2.x) + -> Seq Scan on public.eager_agg_tab1_p3 t1_2 + Output: t1_2.x + -> Hash + Output: t2_2.x, t3_2.x, (PARTIAL sum((t2_2.y + t3_2.y))) + -> Partial HashAggregate + Output: t2_2.x, t3_2.x, PARTIAL sum((t2_2.y + t3_2.y)) + Group Key: t2_2.x + -> Hash Join + Output: t2_2.y, t2_2.x, t3_2.y, t3_2.x + Hash Cond: (t2_2.x = t3_2.x) + -> Seq Scan on public.eager_agg_tab1_p3 t2_2 + Output: t2_2.y, t2_2.x + -> Hash + Output: t3_2.y, t3_2.x + -> Seq Scan on public.eager_agg_tab1_p3 t3_2 + Output: t3_2.y, t3_2.x +(70 rows) + +SELECT t1.x, sum(t2.y + t3.y) + FROM eager_agg_tab1 t1 + JOIN eager_agg_tab1 t2 ON t1.x = t2.x + JOIN eager_agg_tab1 t3 ON t2.x = t3.x +GROUP BY t1.x ORDER BY t1.x; + x | sum +----+--------- + 0 | 1437480 + 1 | 2082896 + 2 | 2684422 + 3 | 3285948 + 4 | 3887474 + 5 | 1526260 + 6 | 2127786 + 7 | 2729312 + 8 | 3330838 + 9 | 3932364 + 10 | 1481370 + 11 | 2012472 + 12 | 2587464 + 13 | 3162456 + 14 | 3737448 +(15 rows) + +-- partial aggregation +SET enable_hashagg TO off; +SET max_parallel_workers_per_gather TO 0; +EXPLAIN (VERBOSE, COSTS OFF) +SELECT t3.y, sum(t2.y + t3.y) + FROM eager_agg_tab1 t1 + JOIN eager_agg_tab1 t2 ON t1.x = t2.x + JOIN eager_agg_tab1 t3 ON t2.x = t3.x +GROUP BY t3.y ORDER BY t3.y; + QUERY PLAN +------------------------------------------------------------------------------------------- + Finalize GroupAggregate + Output: t3.y, sum((t2.y + t3.y)) + Group Key: t3.y + -> Sort + Output: t3.y, (PARTIAL sum((t2.y + t3.y))) + Sort Key: t3.y + -> Append + -> Hash Join + Output: t3.y, (PARTIAL sum((t2.y + t3.y))) + Hash Cond: (t2.x = t1.x) + -> Partial GroupAggregate + Output: t2.x, t3.y, t3.x, PARTIAL sum((t2.y + t3.y)) + Group Key: t2.x, t3.y, t3.x + -> Incremental Sort + Output: t2.y, t2.x, t3.y, t3.x + Sort Key: t2.x, t3.y + Presorted Key: t2.x + -> Merge Join + Output: t2.y, t2.x, t3.y, t3.x + Merge Cond: (t2.x = t3.x) + -> Sort + Output: t2.y, t2.x + Sort Key: t2.x + -> Seq Scan on public.eager_agg_tab1_p1 t2 + Output: t2.y, t2.x + -> Sort + Output: t3.y, t3.x + Sort Key: t3.x + -> Seq Scan on public.eager_agg_tab1_p1 t3 + Output: t3.y, t3.x + -> Hash + Output: t1.x + -> Seq Scan on public.eager_agg_tab1_p1 t1 + Output: t1.x + -> Hash Join + Output: t3_1.y, (PARTIAL sum((t2_1.y + t3_1.y))) + Hash Cond: (t2_1.x = t1_1.x) + -> Partial GroupAggregate + Output: t2_1.x, t3_1.y, t3_1.x, PARTIAL sum((t2_1.y + t3_1.y)) + Group Key: t2_1.x, t3_1.y, t3_1.x + -> Incremental Sort + Output: t2_1.y, t2_1.x, t3_1.y, t3_1.x + Sort Key: t2_1.x, t3_1.y + Presorted Key: t2_1.x + -> Merge Join + Output: t2_1.y, t2_1.x, t3_1.y, t3_1.x + Merge Cond: (t2_1.x = t3_1.x) + -> Sort + Output: t2_1.y, t2_1.x + Sort Key: t2_1.x + -> Seq Scan on public.eager_agg_tab1_p2 t2_1 + Output: t2_1.y, t2_1.x + -> Sort + Output: t3_1.y, t3_1.x + Sort Key: t3_1.x + -> Seq Scan on public.eager_agg_tab1_p2 t3_1 + Output: t3_1.y, t3_1.x + -> Hash + Output: t1_1.x + -> Seq Scan on public.eager_agg_tab1_p2 t1_1 + Output: t1_1.x + -> Hash Join + Output: t3_2.y, (PARTIAL sum((t2_2.y + t3_2.y))) + Hash Cond: (t2_2.x = t1_2.x) + -> Partial GroupAggregate + Output: t2_2.x, t3_2.y, t3_2.x, PARTIAL sum((t2_2.y + t3_2.y)) + Group Key: t2_2.x, t3_2.y, t3_2.x + -> Incremental Sort + Output: t2_2.y, t2_2.x, t3_2.y, t3_2.x + Sort Key: t2_2.x, t3_2.y + Presorted Key: t2_2.x + -> Merge Join + Output: t2_2.y, t2_2.x, t3_2.y, t3_2.x + Merge Cond: (t2_2.x = t3_2.x) + -> Sort + Output: t2_2.y, t2_2.x + Sort Key: t2_2.x + -> Seq Scan on public.eager_agg_tab1_p3 t2_2 + Output: t2_2.y, t2_2.x + -> Sort + Output: t3_2.y, t3_2.x + Sort Key: t3_2.x + -> Seq Scan on public.eager_agg_tab1_p3 t3_2 + Output: t3_2.y, t3_2.x + -> Hash + Output: t1_2.x + -> Seq Scan on public.eager_agg_tab1_p3 t1_2 + Output: t1_2.x +(88 rows) + +SELECT t3.y, sum(t2.y + t3.y) + FROM eager_agg_tab1 t1 + JOIN eager_agg_tab1 t2 ON t1.x = t2.x + JOIN eager_agg_tab1 t3 ON t2.x = t3.x +GROUP BY t3.y ORDER BY t3.y; + y | sum +---+--------- + 0 | 1111110 + 1 | 2000132 + 2 | 2889154 + 3 | 3778176 + 4 | 4667198 + 5 | 3334000 + 6 | 4223022 + 7 | 5112044 + 8 | 6001066 + 9 | 6890088 +(10 rows) + +RESET enable_hashagg; +RESET max_parallel_workers_per_gather; +-- try that with GEQO too +SET geqo = on; +SET geqo_threshold = 2; +EXPLAIN (VERBOSE, COSTS OFF) +SELECT t1.x, sum(t1.y), count(*) + FROM eager_agg_tab1 t1 + JOIN eager_agg_tab2 t2 ON t1.x = t2.y +GROUP BY t1.x ORDER BY t1.x; + QUERY PLAN +--------------------------------------------------------------------------------------- + Sort + Output: t1.x, (sum(t1.y)), (count(*)) + Sort Key: t1.x + -> Append + -> Finalize HashAggregate + Output: t1.x, sum(t1.y), count(*) + Group Key: t1.x + -> Hash Join + Output: t1.x, (PARTIAL sum(t1.y)), (PARTIAL count(*)) + Hash Cond: (t2.y = t1.x) + -> Seq Scan on public.eager_agg_tab2_p1 t2 + Output: t2.y + -> Hash + Output: t1.x, (PARTIAL sum(t1.y)), (PARTIAL count(*)) + -> Partial HashAggregate + Output: t1.x, PARTIAL sum(t1.y), PARTIAL count(*) + Group Key: t1.x + -> Seq Scan on public.eager_agg_tab1_p1 t1 + Output: t1.x, t1.y + -> Finalize HashAggregate + Output: t1_1.x, sum(t1_1.y), count(*) + Group Key: t1_1.x + -> Hash Join + Output: t1_1.x, (PARTIAL sum(t1_1.y)), (PARTIAL count(*)) + Hash Cond: (t2_1.y = t1_1.x) + -> Seq Scan on public.eager_agg_tab2_p2 t2_1 + Output: t2_1.y + -> Hash + Output: t1_1.x, (PARTIAL sum(t1_1.y)), (PARTIAL count(*)) + -> Partial HashAggregate + Output: t1_1.x, PARTIAL sum(t1_1.y), PARTIAL count(*) + Group Key: t1_1.x + -> Seq Scan on public.eager_agg_tab1_p2 t1_1 + Output: t1_1.x, t1_1.y + -> Finalize HashAggregate + Output: t1_2.x, sum(t1_2.y), count(*) + Group Key: t1_2.x + -> Hash Join + Output: t1_2.x, (PARTIAL sum(t1_2.y)), (PARTIAL count(*)) + Hash Cond: (t2_2.y = t1_2.x) + -> Seq Scan on public.eager_agg_tab2_p3 t2_2 + Output: t2_2.y + -> Hash + Output: t1_2.x, (PARTIAL sum(t1_2.y)), (PARTIAL count(*)) + -> Partial HashAggregate + Output: t1_2.x, PARTIAL sum(t1_2.y), PARTIAL count(*) + Group Key: t1_2.x + -> Seq Scan on public.eager_agg_tab1_p3 t1_2 + Output: t1_2.x, t1_2.y +(49 rows) + +SELECT t1.x, sum(t1.y), count(*) + FROM eager_agg_tab1 t1 + JOIN eager_agg_tab2 t2 ON t1.x = t2.y +GROUP BY t1.x ORDER BY t1.x; + x | sum | count +----+-------+------- + 0 | 10890 | 4356 + 1 | 15544 | 4489 + 2 | 20033 | 4489 + 3 | 24522 | 4489 + 4 | 29011 | 4489 + 5 | 11390 | 4489 + 6 | 15879 | 4489 + 7 | 20368 | 4489 + 8 | 24857 | 4489 + 9 | 29346 | 4489 + 10 | 11055 | 4489 + 11 | 15246 | 4356 + 12 | 19602 | 4356 + 13 | 23958 | 4356 + 14 | 28314 | 4356 +(15 rows) + +RESET geqo; +RESET geqo_threshold; +DROP TABLE eager_agg_tab1; +DROP TABLE eager_agg_tab2; +-- +-- Test with multi-level partitioning scheme +-- +CREATE TABLE eager_agg_tab_ml(x int, y int) PARTITION BY RANGE(x); +CREATE TABLE eager_agg_tab_ml_p1 PARTITION OF eager_agg_tab_ml FOR VALUES FROM (0) TO (10); +CREATE TABLE eager_agg_tab_ml_p2 PARTITION OF eager_agg_tab_ml FOR VALUES FROM (10) TO (20) PARTITION BY RANGE(x); +CREATE TABLE eager_agg_tab_ml_p2_s1 PARTITION OF eager_agg_tab_ml_p2 FOR VALUES FROM (10) TO (15); +CREATE TABLE eager_agg_tab_ml_p2_s2 PARTITION OF eager_agg_tab_ml_p2 FOR VALUES FROM (15) TO (20); +CREATE TABLE eager_agg_tab_ml_p3 PARTITION OF eager_agg_tab_ml FOR VALUES FROM (20) TO (30) PARTITION BY RANGE(x); +CREATE TABLE eager_agg_tab_ml_p3_s1 PARTITION OF eager_agg_tab_ml_p3 FOR VALUES FROM (20) TO (25); +CREATE TABLE eager_agg_tab_ml_p3_s2 PARTITION OF eager_agg_tab_ml_p3 FOR VALUES FROM (25) TO (30); +INSERT INTO eager_agg_tab_ml SELECT i % 30, i % 30 FROM generate_series(1, 1000) i; +ANALYZE eager_agg_tab_ml; +-- When GROUP BY clause matches; full aggregation is performed for each +-- partition. +EXPLAIN (VERBOSE, COSTS OFF) +SELECT t1.x, sum(t2.y), count(*) + FROM eager_agg_tab_ml t1 + JOIN eager_agg_tab_ml t2 ON t1.x = t2.x +GROUP BY t1.x ORDER BY t1.x; + QUERY PLAN +--------------------------------------------------------------------------------------- + Sort + Output: t1.x, (sum(t2.y)), (count(*)) + Sort Key: t1.x + -> Append + -> Finalize HashAggregate + Output: t1.x, sum(t2.y), count(*) + Group Key: t1.x + -> Hash Join + Output: t1.x, (PARTIAL sum(t2.y)), (PARTIAL count(*)) + Hash Cond: (t1.x = t2.x) + -> Seq Scan on public.eager_agg_tab_ml_p1 t1 + Output: t1.x + -> Hash + Output: t2.x, (PARTIAL sum(t2.y)), (PARTIAL count(*)) + -> Partial HashAggregate + Output: t2.x, PARTIAL sum(t2.y), PARTIAL count(*) + Group Key: t2.x + -> Seq Scan on public.eager_agg_tab_ml_p1 t2 + Output: t2.y, t2.x + -> Finalize HashAggregate + Output: t1_1.x, sum(t2_1.y), count(*) + Group Key: t1_1.x + -> Hash Join + Output: t1_1.x, (PARTIAL sum(t2_1.y)), (PARTIAL count(*)) + Hash Cond: (t1_1.x = t2_1.x) + -> Seq Scan on public.eager_agg_tab_ml_p2_s1 t1_1 + Output: t1_1.x + -> Hash + Output: t2_1.x, (PARTIAL sum(t2_1.y)), (PARTIAL count(*)) + -> Partial HashAggregate + Output: t2_1.x, PARTIAL sum(t2_1.y), PARTIAL count(*) + Group Key: t2_1.x + -> Seq Scan on public.eager_agg_tab_ml_p2_s1 t2_1 + Output: t2_1.y, t2_1.x + -> Finalize HashAggregate + Output: t1_2.x, sum(t2_2.y), count(*) + Group Key: t1_2.x + -> Hash Join + Output: t1_2.x, (PARTIAL sum(t2_2.y)), (PARTIAL count(*)) + Hash Cond: (t1_2.x = t2_2.x) + -> Seq Scan on public.eager_agg_tab_ml_p2_s2 t1_2 + Output: t1_2.x + -> Hash + Output: t2_2.x, (PARTIAL sum(t2_2.y)), (PARTIAL count(*)) + -> Partial HashAggregate + Output: t2_2.x, PARTIAL sum(t2_2.y), PARTIAL count(*) + Group Key: t2_2.x + -> Seq Scan on public.eager_agg_tab_ml_p2_s2 t2_2 + Output: t2_2.y, t2_2.x + -> Finalize HashAggregate + Output: t1_3.x, sum(t2_3.y), count(*) + Group Key: t1_3.x + -> Hash Join + Output: t1_3.x, (PARTIAL sum(t2_3.y)), (PARTIAL count(*)) + Hash Cond: (t1_3.x = t2_3.x) + -> Seq Scan on public.eager_agg_tab_ml_p3_s1 t1_3 + Output: t1_3.x + -> Hash + Output: t2_3.x, (PARTIAL sum(t2_3.y)), (PARTIAL count(*)) + -> Partial HashAggregate + Output: t2_3.x, PARTIAL sum(t2_3.y), PARTIAL count(*) + Group Key: t2_3.x + -> Seq Scan on public.eager_agg_tab_ml_p3_s1 t2_3 + Output: t2_3.y, t2_3.x + -> Finalize HashAggregate + Output: t1_4.x, sum(t2_4.y), count(*) + Group Key: t1_4.x + -> Hash Join + Output: t1_4.x, (PARTIAL sum(t2_4.y)), (PARTIAL count(*)) + Hash Cond: (t1_4.x = t2_4.x) + -> Seq Scan on public.eager_agg_tab_ml_p3_s2 t1_4 + Output: t1_4.x + -> Hash + Output: t2_4.x, (PARTIAL sum(t2_4.y)), (PARTIAL count(*)) + -> Partial HashAggregate + Output: t2_4.x, PARTIAL sum(t2_4.y), PARTIAL count(*) + Group Key: t2_4.x + -> Seq Scan on public.eager_agg_tab_ml_p3_s2 t2_4 + Output: t2_4.y, t2_4.x +(79 rows) + +SELECT t1.x, sum(t2.y), count(*) + FROM eager_agg_tab_ml t1 + JOIN eager_agg_tab_ml t2 ON t1.x = t2.x +GROUP BY t1.x ORDER BY t1.x; + x | sum | count +----+-------+------- + 0 | 0 | 1089 + 1 | 1156 | 1156 + 2 | 2312 | 1156 + 3 | 3468 | 1156 + 4 | 4624 | 1156 + 5 | 5780 | 1156 + 6 | 6936 | 1156 + 7 | 8092 | 1156 + 8 | 9248 | 1156 + 9 | 10404 | 1156 + 10 | 11560 | 1156 + 11 | 11979 | 1089 + 12 | 13068 | 1089 + 13 | 14157 | 1089 + 14 | 15246 | 1089 + 15 | 16335 | 1089 + 16 | 17424 | 1089 + 17 | 18513 | 1089 + 18 | 19602 | 1089 + 19 | 20691 | 1089 + 20 | 21780 | 1089 + 21 | 22869 | 1089 + 22 | 23958 | 1089 + 23 | 25047 | 1089 + 24 | 26136 | 1089 + 25 | 27225 | 1089 + 26 | 28314 | 1089 + 27 | 29403 | 1089 + 28 | 30492 | 1089 + 29 | 31581 | 1089 +(30 rows) + +-- When GROUP BY clause does not match; partial aggregation is performed for +-- each partition. +EXPLAIN (VERBOSE, COSTS OFF) +SELECT t1.y, sum(t2.y), count(*) + FROM eager_agg_tab_ml t1 + JOIN eager_agg_tab_ml t2 ON t1.x = t2.x +GROUP BY t1.y ORDER BY t1.y; + QUERY PLAN +--------------------------------------------------------------------------------------- + Sort + Output: t1.y, (sum(t2.y)), (count(*)) + Sort Key: t1.y + -> Finalize HashAggregate + Output: t1.y, sum(t2.y), count(*) + Group Key: t1.y + -> Append + -> Hash Join + Output: t1.y, (PARTIAL sum(t2.y)), (PARTIAL count(*)) + Hash Cond: (t1.x = t2.x) + -> Seq Scan on public.eager_agg_tab_ml_p1 t1 + Output: t1.y, t1.x + -> Hash + Output: t2.x, (PARTIAL sum(t2.y)), (PARTIAL count(*)) + -> Partial HashAggregate + Output: t2.x, PARTIAL sum(t2.y), PARTIAL count(*) + Group Key: t2.x + -> Seq Scan on public.eager_agg_tab_ml_p1 t2 + Output: t2.y, t2.x + -> Hash Join + Output: t1_1.y, (PARTIAL sum(t2_1.y)), (PARTIAL count(*)) + Hash Cond: (t1_1.x = t2_1.x) + -> Seq Scan on public.eager_agg_tab_ml_p2_s1 t1_1 + Output: t1_1.y, t1_1.x + -> Hash + Output: t2_1.x, (PARTIAL sum(t2_1.y)), (PARTIAL count(*)) + -> Partial HashAggregate + Output: t2_1.x, PARTIAL sum(t2_1.y), PARTIAL count(*) + Group Key: t2_1.x + -> Seq Scan on public.eager_agg_tab_ml_p2_s1 t2_1 + Output: t2_1.y, t2_1.x + -> Hash Join + Output: t1_2.y, (PARTIAL sum(t2_2.y)), (PARTIAL count(*)) + Hash Cond: (t1_2.x = t2_2.x) + -> Seq Scan on public.eager_agg_tab_ml_p2_s2 t1_2 + Output: t1_2.y, t1_2.x + -> Hash + Output: t2_2.x, (PARTIAL sum(t2_2.y)), (PARTIAL count(*)) + -> Partial HashAggregate + Output: t2_2.x, PARTIAL sum(t2_2.y), PARTIAL count(*) + Group Key: t2_2.x + -> Seq Scan on public.eager_agg_tab_ml_p2_s2 t2_2 + Output: t2_2.y, t2_2.x + -> Hash Join + Output: t1_3.y, (PARTIAL sum(t2_3.y)), (PARTIAL count(*)) + Hash Cond: (t1_3.x = t2_3.x) + -> Seq Scan on public.eager_agg_tab_ml_p3_s1 t1_3 + Output: t1_3.y, t1_3.x + -> Hash + Output: t2_3.x, (PARTIAL sum(t2_3.y)), (PARTIAL count(*)) + -> Partial HashAggregate + Output: t2_3.x, PARTIAL sum(t2_3.y), PARTIAL count(*) + Group Key: t2_3.x + -> Seq Scan on public.eager_agg_tab_ml_p3_s1 t2_3 + Output: t2_3.y, t2_3.x + -> Hash Join + Output: t1_4.y, (PARTIAL sum(t2_4.y)), (PARTIAL count(*)) + Hash Cond: (t1_4.x = t2_4.x) + -> Seq Scan on public.eager_agg_tab_ml_p3_s2 t1_4 + Output: t1_4.y, t1_4.x + -> Hash + Output: t2_4.x, (PARTIAL sum(t2_4.y)), (PARTIAL count(*)) + -> Partial HashAggregate + Output: t2_4.x, PARTIAL sum(t2_4.y), PARTIAL count(*) + Group Key: t2_4.x + -> Seq Scan on public.eager_agg_tab_ml_p3_s2 t2_4 + Output: t2_4.y, t2_4.x +(67 rows) + +SELECT t1.y, sum(t2.y), count(*) + FROM eager_agg_tab_ml t1 + JOIN eager_agg_tab_ml t2 ON t1.x = t2.x +GROUP BY t1.y ORDER BY t1.y; + y | sum | count +----+-------+------- + 0 | 0 | 1089 + 1 | 1156 | 1156 + 2 | 2312 | 1156 + 3 | 3468 | 1156 + 4 | 4624 | 1156 + 5 | 5780 | 1156 + 6 | 6936 | 1156 + 7 | 8092 | 1156 + 8 | 9248 | 1156 + 9 | 10404 | 1156 + 10 | 11560 | 1156 + 11 | 11979 | 1089 + 12 | 13068 | 1089 + 13 | 14157 | 1089 + 14 | 15246 | 1089 + 15 | 16335 | 1089 + 16 | 17424 | 1089 + 17 | 18513 | 1089 + 18 | 19602 | 1089 + 19 | 20691 | 1089 + 20 | 21780 | 1089 + 21 | 22869 | 1089 + 22 | 23958 | 1089 + 23 | 25047 | 1089 + 24 | 26136 | 1089 + 25 | 27225 | 1089 + 26 | 28314 | 1089 + 27 | 29403 | 1089 + 28 | 30492 | 1089 + 29 | 31581 | 1089 +(30 rows) + +-- Check with eager aggregation over join rel +-- full aggregation +EXPLAIN (VERBOSE, COSTS OFF) +SELECT t1.x, sum(t2.y + t3.y), count(*) + FROM eager_agg_tab_ml t1 + JOIN eager_agg_tab_ml t2 ON t1.x = t2.x + JOIN eager_agg_tab_ml t3 ON t2.x = t3.x +GROUP BY t1.x ORDER BY t1.x; + QUERY PLAN +---------------------------------------------------------------------------------------------------------- + Sort + Output: t1.x, (sum((t2.y + t3.y))), (count(*)) + Sort Key: t1.x + -> Append + -> Finalize HashAggregate + Output: t1.x, sum((t2.y + t3.y)), count(*) + Group Key: t1.x + -> Hash Join + Output: t1.x, (PARTIAL sum((t2.y + t3.y))), (PARTIAL count(*)) + Hash Cond: (t1.x = t2.x) + -> Seq Scan on public.eager_agg_tab_ml_p1 t1 + Output: t1.x + -> Hash + Output: t2.x, t3.x, (PARTIAL sum((t2.y + t3.y))), (PARTIAL count(*)) + -> Partial HashAggregate + Output: t2.x, t3.x, PARTIAL sum((t2.y + t3.y)), PARTIAL count(*) + Group Key: t2.x + -> Hash Join + Output: t2.y, t2.x, t3.y, t3.x + Hash Cond: (t2.x = t3.x) + -> Seq Scan on public.eager_agg_tab_ml_p1 t2 + Output: t2.y, t2.x + -> Hash + Output: t3.y, t3.x + -> Seq Scan on public.eager_agg_tab_ml_p1 t3 + Output: t3.y, t3.x + -> Finalize HashAggregate + Output: t1_1.x, sum((t2_1.y + t3_1.y)), count(*) + Group Key: t1_1.x + -> Hash Join + Output: t1_1.x, (PARTIAL sum((t2_1.y + t3_1.y))), (PARTIAL count(*)) + Hash Cond: (t1_1.x = t2_1.x) + -> Seq Scan on public.eager_agg_tab_ml_p2_s1 t1_1 + Output: t1_1.x + -> Hash + Output: t2_1.x, t3_1.x, (PARTIAL sum((t2_1.y + t3_1.y))), (PARTIAL count(*)) + -> Partial HashAggregate + Output: t2_1.x, t3_1.x, PARTIAL sum((t2_1.y + t3_1.y)), PARTIAL count(*) + Group Key: t2_1.x + -> Hash Join + Output: t2_1.y, t2_1.x, t3_1.y, t3_1.x + Hash Cond: (t2_1.x = t3_1.x) + -> Seq Scan on public.eager_agg_tab_ml_p2_s1 t2_1 + Output: t2_1.y, t2_1.x + -> Hash + Output: t3_1.y, t3_1.x + -> Seq Scan on public.eager_agg_tab_ml_p2_s1 t3_1 + Output: t3_1.y, t3_1.x + -> Finalize HashAggregate + Output: t1_2.x, sum((t2_2.y + t3_2.y)), count(*) + Group Key: t1_2.x + -> Hash Join + Output: t1_2.x, (PARTIAL sum((t2_2.y + t3_2.y))), (PARTIAL count(*)) + Hash Cond: (t1_2.x = t2_2.x) + -> Seq Scan on public.eager_agg_tab_ml_p2_s2 t1_2 + Output: t1_2.x + -> Hash + Output: t2_2.x, t3_2.x, (PARTIAL sum((t2_2.y + t3_2.y))), (PARTIAL count(*)) + -> Partial HashAggregate + Output: t2_2.x, t3_2.x, PARTIAL sum((t2_2.y + t3_2.y)), PARTIAL count(*) + Group Key: t2_2.x + -> Hash Join + Output: t2_2.y, t2_2.x, t3_2.y, t3_2.x + Hash Cond: (t2_2.x = t3_2.x) + -> Seq Scan on public.eager_agg_tab_ml_p2_s2 t2_2 + Output: t2_2.y, t2_2.x + -> Hash + Output: t3_2.y, t3_2.x + -> Seq Scan on public.eager_agg_tab_ml_p2_s2 t3_2 + Output: t3_2.y, t3_2.x + -> Finalize HashAggregate + Output: t1_3.x, sum((t2_3.y + t3_3.y)), count(*) + Group Key: t1_3.x + -> Hash Join + Output: t1_3.x, (PARTIAL sum((t2_3.y + t3_3.y))), (PARTIAL count(*)) + Hash Cond: (t1_3.x = t2_3.x) + -> Seq Scan on public.eager_agg_tab_ml_p3_s1 t1_3 + Output: t1_3.x + -> Hash + Output: t2_3.x, t3_3.x, (PARTIAL sum((t2_3.y + t3_3.y))), (PARTIAL count(*)) + -> Partial HashAggregate + Output: t2_3.x, t3_3.x, PARTIAL sum((t2_3.y + t3_3.y)), PARTIAL count(*) + Group Key: t2_3.x + -> Hash Join + Output: t2_3.y, t2_3.x, t3_3.y, t3_3.x + Hash Cond: (t2_3.x = t3_3.x) + -> Seq Scan on public.eager_agg_tab_ml_p3_s1 t2_3 + Output: t2_3.y, t2_3.x + -> Hash + Output: t3_3.y, t3_3.x + -> Seq Scan on public.eager_agg_tab_ml_p3_s1 t3_3 + Output: t3_3.y, t3_3.x + -> Finalize HashAggregate + Output: t1_4.x, sum((t2_4.y + t3_4.y)), count(*) + Group Key: t1_4.x + -> Hash Join + Output: t1_4.x, (PARTIAL sum((t2_4.y + t3_4.y))), (PARTIAL count(*)) + Hash Cond: (t1_4.x = t2_4.x) + -> Seq Scan on public.eager_agg_tab_ml_p3_s2 t1_4 + Output: t1_4.x + -> Hash + Output: t2_4.x, t3_4.x, (PARTIAL sum((t2_4.y + t3_4.y))), (PARTIAL count(*)) + -> Partial HashAggregate + Output: t2_4.x, t3_4.x, PARTIAL sum((t2_4.y + t3_4.y)), PARTIAL count(*) + Group Key: t2_4.x + -> Hash Join + Output: t2_4.y, t2_4.x, t3_4.y, t3_4.x + Hash Cond: (t2_4.x = t3_4.x) + -> Seq Scan on public.eager_agg_tab_ml_p3_s2 t2_4 + Output: t2_4.y, t2_4.x + -> Hash + Output: t3_4.y, t3_4.x + -> Seq Scan on public.eager_agg_tab_ml_p3_s2 t3_4 + Output: t3_4.y, t3_4.x +(114 rows) + +SELECT t1.x, sum(t2.y + t3.y), count(*) + FROM eager_agg_tab_ml t1 + JOIN eager_agg_tab_ml t2 ON t1.x = t2.x + JOIN eager_agg_tab_ml t3 ON t2.x = t3.x +GROUP BY t1.x ORDER BY t1.x; + x | sum | count +----+---------+------- + 0 | 0 | 35937 + 1 | 78608 | 39304 + 2 | 157216 | 39304 + 3 | 235824 | 39304 + 4 | 314432 | 39304 + 5 | 393040 | 39304 + 6 | 471648 | 39304 + 7 | 550256 | 39304 + 8 | 628864 | 39304 + 9 | 707472 | 39304 + 10 | 786080 | 39304 + 11 | 790614 | 35937 + 12 | 862488 | 35937 + 13 | 934362 | 35937 + 14 | 1006236 | 35937 + 15 | 1078110 | 35937 + 16 | 1149984 | 35937 + 17 | 1221858 | 35937 + 18 | 1293732 | 35937 + 19 | 1365606 | 35937 + 20 | 1437480 | 35937 + 21 | 1509354 | 35937 + 22 | 1581228 | 35937 + 23 | 1653102 | 35937 + 24 | 1724976 | 35937 + 25 | 1796850 | 35937 + 26 | 1868724 | 35937 + 27 | 1940598 | 35937 + 28 | 2012472 | 35937 + 29 | 2084346 | 35937 +(30 rows) + +-- partial aggregation +EXPLAIN (VERBOSE, COSTS OFF) +SELECT t3.y, sum(t2.y + t3.y), count(*) + FROM eager_agg_tab_ml t1 + JOIN eager_agg_tab_ml t2 ON t1.x = t2.x + JOIN eager_agg_tab_ml t3 ON t2.x = t3.x +GROUP BY t3.y ORDER BY t3.y; + QUERY PLAN +------------------------------------------------------------------------------------------------------------------ + Sort + Output: t3.y, (sum((t2.y + t3.y))), (count(*)) + Sort Key: t3.y + -> Finalize HashAggregate + Output: t3.y, sum((t2.y + t3.y)), count(*) + Group Key: t3.y + -> Append + -> Hash Join + Output: t3.y, (PARTIAL sum((t2.y + t3.y))), (PARTIAL count(*)) + Hash Cond: (t1.x = t2.x) + -> Seq Scan on public.eager_agg_tab_ml_p1 t1 + Output: t1.x + -> Hash + Output: t2.x, t3.y, t3.x, (PARTIAL sum((t2.y + t3.y))), (PARTIAL count(*)) + -> Partial HashAggregate + Output: t2.x, t3.y, t3.x, PARTIAL sum((t2.y + t3.y)), PARTIAL count(*) + Group Key: t2.x, t3.y, t3.x + -> Hash Join + Output: t2.y, t2.x, t3.y, t3.x + Hash Cond: (t2.x = t3.x) + -> Seq Scan on public.eager_agg_tab_ml_p1 t2 + Output: t2.y, t2.x + -> Hash + Output: t3.y, t3.x + -> Seq Scan on public.eager_agg_tab_ml_p1 t3 + Output: t3.y, t3.x + -> Hash Join + Output: t3_1.y, (PARTIAL sum((t2_1.y + t3_1.y))), (PARTIAL count(*)) + Hash Cond: (t1_1.x = t2_1.x) + -> Seq Scan on public.eager_agg_tab_ml_p2_s1 t1_1 + Output: t1_1.x + -> Hash + Output: t2_1.x, t3_1.y, t3_1.x, (PARTIAL sum((t2_1.y + t3_1.y))), (PARTIAL count(*)) + -> Partial HashAggregate + Output: t2_1.x, t3_1.y, t3_1.x, PARTIAL sum((t2_1.y + t3_1.y)), PARTIAL count(*) + Group Key: t2_1.x, t3_1.y, t3_1.x + -> Hash Join + Output: t2_1.y, t2_1.x, t3_1.y, t3_1.x + Hash Cond: (t2_1.x = t3_1.x) + -> Seq Scan on public.eager_agg_tab_ml_p2_s1 t2_1 + Output: t2_1.y, t2_1.x + -> Hash + Output: t3_1.y, t3_1.x + -> Seq Scan on public.eager_agg_tab_ml_p2_s1 t3_1 + Output: t3_1.y, t3_1.x + -> Hash Join + Output: t3_2.y, (PARTIAL sum((t2_2.y + t3_2.y))), (PARTIAL count(*)) + Hash Cond: (t1_2.x = t2_2.x) + -> Seq Scan on public.eager_agg_tab_ml_p2_s2 t1_2 + Output: t1_2.x + -> Hash + Output: t2_2.x, t3_2.y, t3_2.x, (PARTIAL sum((t2_2.y + t3_2.y))), (PARTIAL count(*)) + -> Partial HashAggregate + Output: t2_2.x, t3_2.y, t3_2.x, PARTIAL sum((t2_2.y + t3_2.y)), PARTIAL count(*) + Group Key: t2_2.x, t3_2.y, t3_2.x + -> Hash Join + Output: t2_2.y, t2_2.x, t3_2.y, t3_2.x + Hash Cond: (t2_2.x = t3_2.x) + -> Seq Scan on public.eager_agg_tab_ml_p2_s2 t2_2 + Output: t2_2.y, t2_2.x + -> Hash + Output: t3_2.y, t3_2.x + -> Seq Scan on public.eager_agg_tab_ml_p2_s2 t3_2 + Output: t3_2.y, t3_2.x + -> Hash Join + Output: t3_3.y, (PARTIAL sum((t2_3.y + t3_3.y))), (PARTIAL count(*)) + Hash Cond: (t1_3.x = t2_3.x) + -> Seq Scan on public.eager_agg_tab_ml_p3_s1 t1_3 + Output: t1_3.x + -> Hash + Output: t2_3.x, t3_3.y, t3_3.x, (PARTIAL sum((t2_3.y + t3_3.y))), (PARTIAL count(*)) + -> Partial HashAggregate + Output: t2_3.x, t3_3.y, t3_3.x, PARTIAL sum((t2_3.y + t3_3.y)), PARTIAL count(*) + Group Key: t2_3.x, t3_3.y, t3_3.x + -> Hash Join + Output: t2_3.y, t2_3.x, t3_3.y, t3_3.x + Hash Cond: (t2_3.x = t3_3.x) + -> Seq Scan on public.eager_agg_tab_ml_p3_s1 t2_3 + Output: t2_3.y, t2_3.x + -> Hash + Output: t3_3.y, t3_3.x + -> Seq Scan on public.eager_agg_tab_ml_p3_s1 t3_3 + Output: t3_3.y, t3_3.x + -> Hash Join + Output: t3_4.y, (PARTIAL sum((t2_4.y + t3_4.y))), (PARTIAL count(*)) + Hash Cond: (t1_4.x = t2_4.x) + -> Seq Scan on public.eager_agg_tab_ml_p3_s2 t1_4 + Output: t1_4.x + -> Hash + Output: t2_4.x, t3_4.y, t3_4.x, (PARTIAL sum((t2_4.y + t3_4.y))), (PARTIAL count(*)) + -> Partial HashAggregate + Output: t2_4.x, t3_4.y, t3_4.x, PARTIAL sum((t2_4.y + t3_4.y)), PARTIAL count(*) + Group Key: t2_4.x, t3_4.y, t3_4.x + -> Hash Join + Output: t2_4.y, t2_4.x, t3_4.y, t3_4.x + Hash Cond: (t2_4.x = t3_4.x) + -> Seq Scan on public.eager_agg_tab_ml_p3_s2 t2_4 + Output: t2_4.y, t2_4.x + -> Hash + Output: t3_4.y, t3_4.x + -> Seq Scan on public.eager_agg_tab_ml_p3_s2 t3_4 + Output: t3_4.y, t3_4.x +(102 rows) + +SELECT t3.y, sum(t2.y + t3.y), count(*) + FROM eager_agg_tab_ml t1 + JOIN eager_agg_tab_ml t2 ON t1.x = t2.x + JOIN eager_agg_tab_ml t3 ON t2.x = t3.x +GROUP BY t3.y ORDER BY t3.y; + y | sum | count +----+---------+------- + 0 | 0 | 35937 + 1 | 78608 | 39304 + 2 | 157216 | 39304 + 3 | 235824 | 39304 + 4 | 314432 | 39304 + 5 | 393040 | 39304 + 6 | 471648 | 39304 + 7 | 550256 | 39304 + 8 | 628864 | 39304 + 9 | 707472 | 39304 + 10 | 786080 | 39304 + 11 | 790614 | 35937 + 12 | 862488 | 35937 + 13 | 934362 | 35937 + 14 | 1006236 | 35937 + 15 | 1078110 | 35937 + 16 | 1149984 | 35937 + 17 | 1221858 | 35937 + 18 | 1293732 | 35937 + 19 | 1365606 | 35937 + 20 | 1437480 | 35937 + 21 | 1509354 | 35937 + 22 | 1581228 | 35937 + 23 | 1653102 | 35937 + 24 | 1724976 | 35937 + 25 | 1796850 | 35937 + 26 | 1868724 | 35937 + 27 | 1940598 | 35937 + 28 | 2012472 | 35937 + 29 | 2084346 | 35937 +(30 rows) + +-- try that with GEQO too +SET geqo = on; +SET geqo_threshold = 2; +EXPLAIN (VERBOSE, COSTS OFF) +SELECT t1.x, sum(t2.y), count(*) + FROM eager_agg_tab_ml t1 + JOIN eager_agg_tab_ml t2 ON t1.x = t2.x +GROUP BY t1.x ORDER BY t1.x; + QUERY PLAN +--------------------------------------------------------------------------------------- + Sort + Output: t1.x, (sum(t2.y)), (count(*)) + Sort Key: t1.x + -> Append + -> Finalize HashAggregate + Output: t1.x, sum(t2.y), count(*) + Group Key: t1.x + -> Hash Join + Output: t1.x, (PARTIAL sum(t2.y)), (PARTIAL count(*)) + Hash Cond: (t1.x = t2.x) + -> Seq Scan on public.eager_agg_tab_ml_p1 t1 + Output: t1.x + -> Hash + Output: t2.x, (PARTIAL sum(t2.y)), (PARTIAL count(*)) + -> Partial HashAggregate + Output: t2.x, PARTIAL sum(t2.y), PARTIAL count(*) + Group Key: t2.x + -> Seq Scan on public.eager_agg_tab_ml_p1 t2 + Output: t2.y, t2.x + -> Finalize HashAggregate + Output: t1_1.x, sum(t2_1.y), count(*) + Group Key: t1_1.x + -> Hash Join + Output: t1_1.x, (PARTIAL sum(t2_1.y)), (PARTIAL count(*)) + Hash Cond: (t1_1.x = t2_1.x) + -> Seq Scan on public.eager_agg_tab_ml_p2_s1 t1_1 + Output: t1_1.x + -> Hash + Output: t2_1.x, (PARTIAL sum(t2_1.y)), (PARTIAL count(*)) + -> Partial HashAggregate + Output: t2_1.x, PARTIAL sum(t2_1.y), PARTIAL count(*) + Group Key: t2_1.x + -> Seq Scan on public.eager_agg_tab_ml_p2_s1 t2_1 + Output: t2_1.y, t2_1.x + -> Finalize HashAggregate + Output: t1_2.x, sum(t2_2.y), count(*) + Group Key: t1_2.x + -> Hash Join + Output: t1_2.x, (PARTIAL sum(t2_2.y)), (PARTIAL count(*)) + Hash Cond: (t1_2.x = t2_2.x) + -> Seq Scan on public.eager_agg_tab_ml_p2_s2 t1_2 + Output: t1_2.x + -> Hash + Output: t2_2.x, (PARTIAL sum(t2_2.y)), (PARTIAL count(*)) + -> Partial HashAggregate + Output: t2_2.x, PARTIAL sum(t2_2.y), PARTIAL count(*) + Group Key: t2_2.x + -> Seq Scan on public.eager_agg_tab_ml_p2_s2 t2_2 + Output: t2_2.y, t2_2.x + -> Finalize HashAggregate + Output: t1_3.x, sum(t2_3.y), count(*) + Group Key: t1_3.x + -> Hash Join + Output: t1_3.x, (PARTIAL sum(t2_3.y)), (PARTIAL count(*)) + Hash Cond: (t1_3.x = t2_3.x) + -> Seq Scan on public.eager_agg_tab_ml_p3_s1 t1_3 + Output: t1_3.x + -> Hash + Output: t2_3.x, (PARTIAL sum(t2_3.y)), (PARTIAL count(*)) + -> Partial HashAggregate + Output: t2_3.x, PARTIAL sum(t2_3.y), PARTIAL count(*) + Group Key: t2_3.x + -> Seq Scan on public.eager_agg_tab_ml_p3_s1 t2_3 + Output: t2_3.y, t2_3.x + -> Finalize HashAggregate + Output: t1_4.x, sum(t2_4.y), count(*) + Group Key: t1_4.x + -> Hash Join + Output: t1_4.x, (PARTIAL sum(t2_4.y)), (PARTIAL count(*)) + Hash Cond: (t1_4.x = t2_4.x) + -> Seq Scan on public.eager_agg_tab_ml_p3_s2 t1_4 + Output: t1_4.x + -> Hash + Output: t2_4.x, (PARTIAL sum(t2_4.y)), (PARTIAL count(*)) + -> Partial HashAggregate + Output: t2_4.x, PARTIAL sum(t2_4.y), PARTIAL count(*) + Group Key: t2_4.x + -> Seq Scan on public.eager_agg_tab_ml_p3_s2 t2_4 + Output: t2_4.y, t2_4.x +(79 rows) + +SELECT t1.x, sum(t2.y), count(*) + FROM eager_agg_tab_ml t1 + JOIN eager_agg_tab_ml t2 ON t1.x = t2.x +GROUP BY t1.x ORDER BY t1.x; + x | sum | count +----+-------+------- + 0 | 0 | 1089 + 1 | 1156 | 1156 + 2 | 2312 | 1156 + 3 | 3468 | 1156 + 4 | 4624 | 1156 + 5 | 5780 | 1156 + 6 | 6936 | 1156 + 7 | 8092 | 1156 + 8 | 9248 | 1156 + 9 | 10404 | 1156 + 10 | 11560 | 1156 + 11 | 11979 | 1089 + 12 | 13068 | 1089 + 13 | 14157 | 1089 + 14 | 15246 | 1089 + 15 | 16335 | 1089 + 16 | 17424 | 1089 + 17 | 18513 | 1089 + 18 | 19602 | 1089 + 19 | 20691 | 1089 + 20 | 21780 | 1089 + 21 | 22869 | 1089 + 22 | 23958 | 1089 + 23 | 25047 | 1089 + 24 | 26136 | 1089 + 25 | 27225 | 1089 + 26 | 28314 | 1089 + 27 | 29403 | 1089 + 28 | 30492 | 1089 + 29 | 31581 | 1089 +(30 rows) + +RESET geqo; +RESET geqo_threshold; +DROP TABLE eager_agg_tab_ml; diff --git a/src/test/regress/expected/join.out b/src/test/regress/expected/join.out index 14a6d7513aa..d10095de70f 100644 --- a/src/test/regress/expected/join.out +++ b/src/test/regress/expected/join.out @@ -2840,20 +2840,22 @@ select x.thousand, x.twothousand, count(*) from tenk1 x inner join tenk1 y on x.thousand = y.thousand group by x.thousand, x.twothousand order by x.thousand desc, x.twothousand; - QUERY PLAN ----------------------------------------------------------------------------------- - GroupAggregate + QUERY PLAN +---------------------------------------------------------------------------------------- + Finalize GroupAggregate Group Key: x.thousand, x.twothousand -> Incremental Sort Sort Key: x.thousand DESC, x.twothousand Presorted Key: x.thousand -> Merge Join Merge Cond: (y.thousand = x.thousand) - -> Index Only Scan Backward using tenk1_thous_tenthous on tenk1 y + -> Partial GroupAggregate + Group Key: y.thousand + -> Index Only Scan Backward using tenk1_thous_tenthous on tenk1 y -> Sort Sort Key: x.thousand DESC -> Seq Scan on tenk1 x -(11 rows) +(13 rows) reset enable_hashagg; reset enable_nestloop; diff --git a/src/test/regress/expected/opr_sanity.out b/src/test/regress/expected/opr_sanity.out index 20bf9ea9cdf..a357e1d0c0e 100644 --- a/src/test/regress/expected/opr_sanity.out +++ b/src/test/regress/expected/opr_sanity.out @@ -1470,7 +1470,7 @@ WHERE aggfnoid = 0 OR aggtransfn = 0 OR (aggkind = 'n' AND aggnumdirectargs > 0) OR aggfinalmodify NOT IN ('r', 's', 'w') OR aggmfinalmodify NOT IN ('r', 's', 'w') OR - aggtranstype = 0 OR aggtransspace < 0 OR aggmtransspace < 0; + aggtranstype = 0 OR aggmtransspace < 0; ctid | aggfnoid ------+---------- (0 rows) diff --git a/src/test/regress/expected/partition_aggregate.out b/src/test/regress/expected/partition_aggregate.out index cb12bf53719..fc84929a002 100644 --- a/src/test/regress/expected/partition_aggregate.out +++ b/src/test/regress/expected/partition_aggregate.out @@ -13,6 +13,8 @@ SET enable_partitionwise_join TO true; SET max_parallel_workers_per_gather TO 0; -- Disable incremental sort, which can influence selected plans due to fuzz factor. SET enable_incremental_sort TO off; +-- Disable eager aggregation, which can interfere with the generation of partitionwise aggregation. +SET enable_eager_aggregate TO off; -- -- Tests for list partitioned tables. -- diff --git a/src/test/regress/expected/psql.out b/src/test/regress/expected/psql.out index a79325e8a2f..fa8984ffe0d 100644 --- a/src/test/regress/expected/psql.out +++ b/src/test/regress/expected/psql.out @@ -6445,9 +6445,9 @@ List of schemas (0 rows) \dRp "no.such.publication" - List of publications - Name | Owner | All tables | Inserts | Updates | Deletes | Truncates | Generated columns | Via root -------+-------+------------+---------+---------+---------+-----------+-------------------+---------- + List of publications + Name | Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root +------+-------+------------+---------------+---------+---------+---------+-----------+-------------------+---------- (0 rows) \dRs "no.such.subscription" diff --git a/src/test/regress/expected/publication.out b/src/test/regress/expected/publication.out index 895ca87a0df..e72d1308967 100644 --- a/src/test/regress/expected/publication.out +++ b/src/test/regress/expected/publication.out @@ -40,20 +40,20 @@ CREATE PUBLICATION testpub_xxx WITH (publish_generated_columns); ERROR: invalid value for publication parameter "publish_generated_columns": "" DETAIL: Valid values are "none" and "stored". \dRp - List of publications - Name | Owner | All tables | Inserts | Updates | Deletes | Truncates | Generated columns | Via root ---------------------+--------------------------+------------+---------+---------+---------+-----------+-------------------+---------- - testpub_default | regress_publication_user | f | f | t | f | f | none | f - testpub_ins_trunct | regress_publication_user | f | t | f | f | f | none | f + List of publications + Name | Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root +--------------------+--------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+---------- + testpub_default | regress_publication_user | f | f | f | t | f | f | none | f + testpub_ins_trunct | regress_publication_user | f | f | t | f | f | f | none | f (2 rows) ALTER PUBLICATION testpub_default SET (publish = 'insert, update, delete'); \dRp - List of publications - Name | Owner | All tables | Inserts | Updates | Deletes | Truncates | Generated columns | Via root ---------------------+--------------------------+------------+---------+---------+---------+-----------+-------------------+---------- - testpub_default | regress_publication_user | f | t | t | t | f | none | f - testpub_ins_trunct | regress_publication_user | f | t | f | f | f | none | f + List of publications + Name | Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root +--------------------+--------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+---------- + testpub_default | regress_publication_user | f | f | t | t | t | f | none | f + testpub_ins_trunct | regress_publication_user | f | f | t | f | f | f | none | f (2 rows) --- adding tables @@ -70,15 +70,15 @@ CREATE TABLE testpub_tbl2 (id serial primary key, data text); -- fail - can't add to for all tables publication ALTER PUBLICATION testpub_foralltables ADD TABLE testpub_tbl2; ERROR: publication "testpub_foralltables" is defined as FOR ALL TABLES -DETAIL: Tables cannot be added to or dropped from FOR ALL TABLES publications. +DETAIL: Tables or sequences cannot be added to or dropped from FOR ALL TABLES publications. -- fail - can't drop from all tables publication ALTER PUBLICATION testpub_foralltables DROP TABLE testpub_tbl2; ERROR: publication "testpub_foralltables" is defined as FOR ALL TABLES -DETAIL: Tables cannot be added to or dropped from FOR ALL TABLES publications. +DETAIL: Tables or sequences cannot be added to or dropped from FOR ALL TABLES publications. -- fail - can't add to for all tables publication ALTER PUBLICATION testpub_foralltables SET TABLE pub_test.testpub_nopk; ERROR: publication "testpub_foralltables" is defined as FOR ALL TABLES -DETAIL: Tables cannot be added to or dropped from FOR ALL TABLES publications. +DETAIL: Tables or sequences cannot be added to or dropped from FOR ALL TABLES publications. -- fail - can't add schema to 'FOR ALL TABLES' publication ALTER PUBLICATION testpub_foralltables ADD TABLES IN SCHEMA pub_test; ERROR: publication "testpub_foralltables" is defined as FOR ALL TABLES @@ -97,10 +97,10 @@ RESET client_min_messages; -- should be able to add schema to 'FOR TABLE' publication ALTER PUBLICATION testpub_fortable ADD TABLES IN SCHEMA pub_test; \dRp+ testpub_fortable - Publication testpub_fortable - Owner | All tables | Inserts | Updates | Deletes | Truncates | Generated columns | Via root ---------------------------+------------+---------+---------+---------+-----------+-------------------+---------- - regress_publication_user | f | t | t | t | t | none | f + Publication testpub_fortable + Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root +--------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+---------- + regress_publication_user | f | f | t | t | t | t | none | f Tables: "public.testpub_tbl1" Tables from schemas: @@ -109,20 +109,20 @@ Tables from schemas: -- should be able to drop schema from 'FOR TABLE' publication ALTER PUBLICATION testpub_fortable DROP TABLES IN SCHEMA pub_test; \dRp+ testpub_fortable - Publication testpub_fortable - Owner | All tables | Inserts | Updates | Deletes | Truncates | Generated columns | Via root ---------------------------+------------+---------+---------+---------+-----------+-------------------+---------- - regress_publication_user | f | t | t | t | t | none | f + Publication testpub_fortable + Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root +--------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+---------- + regress_publication_user | f | f | t | t | t | t | none | f Tables: "public.testpub_tbl1" -- should be able to set schema to 'FOR TABLE' publication ALTER PUBLICATION testpub_fortable SET TABLES IN SCHEMA pub_test; \dRp+ testpub_fortable - Publication testpub_fortable - Owner | All tables | Inserts | Updates | Deletes | Truncates | Generated columns | Via root ---------------------------+------------+---------+---------+---------+-----------+-------------------+---------- - regress_publication_user | f | t | t | t | t | none | f + Publication testpub_fortable + Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root +--------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+---------- + regress_publication_user | f | f | t | t | t | t | none | f Tables from schemas: "pub_test" @@ -133,10 +133,10 @@ CREATE PUBLICATION testpub_forschema FOR TABLES IN SCHEMA pub_test; CREATE PUBLICATION testpub_for_tbl_schema FOR TABLES IN SCHEMA pub_test, TABLE pub_test.testpub_nopk; RESET client_min_messages; \dRp+ testpub_for_tbl_schema - Publication testpub_for_tbl_schema - Owner | All tables | Inserts | Updates | Deletes | Truncates | Generated columns | Via root ---------------------------+------------+---------+---------+---------+-----------+-------------------+---------- - regress_publication_user | f | t | t | t | t | none | f + Publication testpub_for_tbl_schema + Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root +--------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+---------- + regress_publication_user | f | f | t | t | t | t | none | f Tables: "pub_test.testpub_nopk" Tables from schemas: @@ -154,10 +154,10 @@ LINE 1: ...CATION testpub_parsertst FOR TABLES IN SCHEMA foo, test.foo; -- should be able to add a table of the same schema to the schema publication ALTER PUBLICATION testpub_forschema ADD TABLE pub_test.testpub_nopk; \dRp+ testpub_forschema - Publication testpub_forschema - Owner | All tables | Inserts | Updates | Deletes | Truncates | Generated columns | Via root ---------------------------+------------+---------+---------+---------+-----------+-------------------+---------- - regress_publication_user | f | t | t | t | t | none | f + Publication testpub_forschema + Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root +--------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+---------- + regress_publication_user | f | f | t | t | t | t | none | f Tables: "pub_test.testpub_nopk" Tables from schemas: @@ -166,10 +166,10 @@ Tables from schemas: -- should be able to drop the table ALTER PUBLICATION testpub_forschema DROP TABLE pub_test.testpub_nopk; \dRp+ testpub_forschema - Publication testpub_forschema - Owner | All tables | Inserts | Updates | Deletes | Truncates | Generated columns | Via root ---------------------------+------------+---------+---------+---------+-----------+-------------------+---------- - regress_publication_user | f | t | t | t | t | none | f + Publication testpub_forschema + Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root +--------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+---------- + regress_publication_user | f | f | t | t | t | t | none | f Tables from schemas: "pub_test" @@ -180,10 +180,10 @@ ERROR: relation "testpub_nopk" is not part of the publication -- should be able to set table to schema publication ALTER PUBLICATION testpub_forschema SET TABLE pub_test.testpub_nopk; \dRp+ testpub_forschema - Publication testpub_forschema - Owner | All tables | Inserts | Updates | Deletes | Truncates | Generated columns | Via root ---------------------------+------------+---------+---------+---------+-----------+-------------------+---------- - regress_publication_user | f | t | t | t | t | none | f + Publication testpub_forschema + Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root +--------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+---------- + regress_publication_user | f | f | t | t | t | t | none | f Tables: "pub_test.testpub_nopk" @@ -207,10 +207,10 @@ Not-null constraints: "testpub_tbl2_id_not_null" NOT NULL "id" \dRp+ testpub_foralltables - Publication testpub_foralltables - Owner | All tables | Inserts | Updates | Deletes | Truncates | Generated columns | Via root ---------------------------+------------+---------+---------+---------+-----------+-------------------+---------- - regress_publication_user | t | t | t | f | f | none | f + Publication testpub_foralltables + Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root +--------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+---------- + regress_publication_user | t | f | t | t | f | f | none | f (1 row) DROP TABLE testpub_tbl2; @@ -222,24 +222,110 @@ CREATE PUBLICATION testpub3 FOR TABLE testpub_tbl3; CREATE PUBLICATION testpub4 FOR TABLE ONLY testpub_tbl3; RESET client_min_messages; \dRp+ testpub3 - Publication testpub3 - Owner | All tables | Inserts | Updates | Deletes | Truncates | Generated columns | Via root ---------------------------+------------+---------+---------+---------+-----------+-------------------+---------- - regress_publication_user | f | t | t | t | t | none | f + Publication testpub3 + Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root +--------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+---------- + regress_publication_user | f | f | t | t | t | t | none | f Tables: "public.testpub_tbl3" "public.testpub_tbl3a" \dRp+ testpub4 - Publication testpub4 - Owner | All tables | Inserts | Updates | Deletes | Truncates | Generated columns | Via root ---------------------------+------------+---------+---------+---------+-----------+-------------------+---------- - regress_publication_user | f | t | t | t | t | none | f + Publication testpub4 + Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root +--------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+---------- + regress_publication_user | f | f | t | t | t | t | none | f Tables: "public.testpub_tbl3" DROP TABLE testpub_tbl3, testpub_tbl3a; DROP PUBLICATION testpub3, testpub4; +--- Tests for publications with SEQUENCES +CREATE SEQUENCE regress_pub_seq0; +CREATE SEQUENCE pub_test.regress_pub_seq1; +-- FOR ALL SEQUENCES +SET client_min_messages = 'ERROR'; +CREATE PUBLICATION regress_pub_forallsequences1 FOR ALL SEQUENCES; +RESET client_min_messages; +SELECT pubname, puballtables, puballsequences FROM pg_publication WHERE pubname = 'regress_pub_forallsequences1'; + pubname | puballtables | puballsequences +------------------------------+--------------+----------------- + regress_pub_forallsequences1 | f | t +(1 row) + +\d+ regress_pub_seq0 + Sequence "public.regress_pub_seq0" + Type | Start | Minimum | Maximum | Increment | Cycles? | Cache +--------+-------+---------+---------------------+-----------+---------+------- + bigint | 1 | 1 | 9223372036854775807 | 1 | no | 1 +Publications: + "regress_pub_forallsequences1" + +\dRp+ regress_pub_forallsequences1 + Publication regress_pub_forallsequences1 + Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root +--------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+---------- + regress_publication_user | f | t | t | t | t | t | none | f +(1 row) + +SET client_min_messages = 'ERROR'; +CREATE PUBLICATION regress_pub_forallsequences2 FOR ALL SEQUENCES; +RESET client_min_messages; +-- check that describe sequence lists both publications the sequence belongs to +\d+ pub_test.regress_pub_seq1 + Sequence "pub_test.regress_pub_seq1" + Type | Start | Minimum | Maximum | Increment | Cycles? | Cache +--------+-------+---------+---------------------+-----------+---------+------- + bigint | 1 | 1 | 9223372036854775807 | 1 | no | 1 +Publications: + "regress_pub_forallsequences1" + "regress_pub_forallsequences2" + +--- Specifying both ALL TABLES and ALL SEQUENCES +SET client_min_messages = 'ERROR'; +CREATE PUBLICATION regress_pub_for_allsequences_alltables FOR ALL SEQUENCES, ALL TABLES; +-- Specifying WITH clause in an ALL SEQUENCES publication will emit a NOTICE. +SET client_min_messages = 'NOTICE'; +CREATE PUBLICATION regress_pub_for_allsequences_alltables_withclause FOR ALL SEQUENCES, ALL TABLES WITH (publish = 'insert'); +NOTICE: publication parameters are not applicable to sequence synchronization and will be ignored for sequences +WARNING: "wal_level" is insufficient to publish logical changes +HINT: Set "wal_level" to "logical" before creating subscriptions. +CREATE PUBLICATION regress_pub_for_allsequences_withclause FOR ALL SEQUENCES WITH (publish_generated_columns = 'stored'); +NOTICE: publication parameters are not applicable to sequence synchronization and will be ignored for sequences +WARNING: "wal_level" is insufficient to publish logical changes +HINT: Set "wal_level" to "logical" before creating subscriptions. +RESET client_min_messages; +SELECT pubname, puballtables, puballsequences FROM pg_publication WHERE pubname = 'regress_pub_for_allsequences_alltables'; + pubname | puballtables | puballsequences +----------------------------------------+--------------+----------------- + regress_pub_for_allsequences_alltables | t | t +(1 row) + +\dRp+ regress_pub_for_allsequences_alltables + Publication regress_pub_for_allsequences_alltables + Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root +--------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+---------- + regress_publication_user | t | t | t | t | t | t | none | f +(1 row) + +DROP SEQUENCE regress_pub_seq0, pub_test.regress_pub_seq1; +DROP PUBLICATION regress_pub_forallsequences1; +DROP PUBLICATION regress_pub_forallsequences2; +DROP PUBLICATION regress_pub_for_allsequences_alltables; +DROP PUBLICATION regress_pub_for_allsequences_alltables_withclause; +DROP PUBLICATION regress_pub_for_allsequences_withclause; +-- fail - Specifying ALL TABLES more than once +CREATE PUBLICATION regress_pub_for_allsequences_alltables FOR ALL SEQUENCES, ALL TABLES, ALL TABLES; +ERROR: invalid publication object list +LINE 1: ...equences_alltables FOR ALL SEQUENCES, ALL TABLES, ALL TABLES... + ^ +DETAIL: ALL TABLES can be specified only once. +-- fail - Specifying ALL SEQUENCES more than once +CREATE PUBLICATION regress_pub_for_allsequences_alltables FOR ALL SEQUENCES, ALL TABLES, ALL SEQUENCES; +ERROR: invalid publication object list +LINE 1: ...equences_alltables FOR ALL SEQUENCES, ALL TABLES, ALL SEQUEN... + ^ +DETAIL: ALL SEQUENCES can be specified only once. -- Tests for partitioned tables SET client_min_messages = 'ERROR'; CREATE PUBLICATION testpub_forparted; @@ -255,10 +341,10 @@ UPDATE testpub_parted1 SET a = 1; -- only parent is listed as being in publication, not the partition ALTER PUBLICATION testpub_forparted ADD TABLE testpub_parted; \dRp+ testpub_forparted - Publication testpub_forparted - Owner | All tables | Inserts | Updates | Deletes | Truncates | Generated columns | Via root ---------------------------+------------+---------+---------+---------+-----------+-------------------+---------- - regress_publication_user | f | t | t | t | t | none | f + Publication testpub_forparted + Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root +--------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+---------- + regress_publication_user | f | f | t | t | t | t | none | f Tables: "public.testpub_parted" @@ -273,10 +359,10 @@ ALTER TABLE testpub_parted DETACH PARTITION testpub_parted1; UPDATE testpub_parted1 SET a = 1; ALTER PUBLICATION testpub_forparted SET (publish_via_partition_root = true); \dRp+ testpub_forparted - Publication testpub_forparted - Owner | All tables | Inserts | Updates | Deletes | Truncates | Generated columns | Via root ---------------------------+------------+---------+---------+---------+-----------+-------------------+---------- - regress_publication_user | f | t | t | t | t | none | t + Publication testpub_forparted + Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root +--------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+---------- + regress_publication_user | f | f | t | t | t | t | none | t Tables: "public.testpub_parted" @@ -305,10 +391,10 @@ SET client_min_messages = 'ERROR'; CREATE PUBLICATION testpub5 FOR TABLE testpub_rf_tbl1, testpub_rf_tbl2 WHERE (c <> 'test' AND d < 5) WITH (publish = 'insert'); RESET client_min_messages; \dRp+ testpub5 - Publication testpub5 - Owner | All tables | Inserts | Updates | Deletes | Truncates | Generated columns | Via root ---------------------------+------------+---------+---------+---------+-----------+-------------------+---------- - regress_publication_user | f | t | f | f | f | none | f + Publication testpub5 + Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root +--------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+---------- + regress_publication_user | f | f | t | f | f | f | none | f Tables: "public.testpub_rf_tbl1" "public.testpub_rf_tbl2" WHERE ((c <> 'test'::text) AND (d < 5)) @@ -321,10 +407,10 @@ Tables: ALTER PUBLICATION testpub5 ADD TABLE testpub_rf_tbl3 WHERE (e > 1000 AND e < 2000); \dRp+ testpub5 - Publication testpub5 - Owner | All tables | Inserts | Updates | Deletes | Truncates | Generated columns | Via root ---------------------------+------------+---------+---------+---------+-----------+-------------------+---------- - regress_publication_user | f | t | f | f | f | none | f + Publication testpub5 + Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root +--------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+---------- + regress_publication_user | f | f | t | f | f | f | none | f Tables: "public.testpub_rf_tbl1" "public.testpub_rf_tbl2" WHERE ((c <> 'test'::text) AND (d < 5)) @@ -340,10 +426,10 @@ Publications: ALTER PUBLICATION testpub5 DROP TABLE testpub_rf_tbl2; \dRp+ testpub5 - Publication testpub5 - Owner | All tables | Inserts | Updates | Deletes | Truncates | Generated columns | Via root ---------------------------+------------+---------+---------+---------+-----------+-------------------+---------- - regress_publication_user | f | t | f | f | f | none | f + Publication testpub5 + Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root +--------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+---------- + regress_publication_user | f | f | t | f | f | f | none | f Tables: "public.testpub_rf_tbl1" "public.testpub_rf_tbl3" WHERE ((e > 1000) AND (e < 2000)) @@ -351,10 +437,10 @@ Tables: -- remove testpub_rf_tbl1 and add testpub_rf_tbl3 again (another WHERE expression) ALTER PUBLICATION testpub5 SET TABLE testpub_rf_tbl3 WHERE (e > 300 AND e < 500); \dRp+ testpub5 - Publication testpub5 - Owner | All tables | Inserts | Updates | Deletes | Truncates | Generated columns | Via root ---------------------------+------------+---------+---------+---------+-----------+-------------------+---------- - regress_publication_user | f | t | f | f | f | none | f + Publication testpub5 + Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root +--------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+---------- + regress_publication_user | f | f | t | f | f | f | none | f Tables: "public.testpub_rf_tbl3" WHERE ((e > 300) AND (e < 500)) @@ -387,10 +473,10 @@ SET client_min_messages = 'ERROR'; CREATE PUBLICATION testpub_syntax1 FOR TABLE testpub_rf_tbl1, ONLY testpub_rf_tbl3 WHERE (e < 999) WITH (publish = 'insert'); RESET client_min_messages; \dRp+ testpub_syntax1 - Publication testpub_syntax1 - Owner | All tables | Inserts | Updates | Deletes | Truncates | Generated columns | Via root ---------------------------+------------+---------+---------+---------+-----------+-------------------+---------- - regress_publication_user | f | t | f | f | f | none | f + Publication testpub_syntax1 + Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root +--------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+---------- + regress_publication_user | f | f | t | f | f | f | none | f Tables: "public.testpub_rf_tbl1" "public.testpub_rf_tbl3" WHERE (e < 999) @@ -400,10 +486,10 @@ SET client_min_messages = 'ERROR'; CREATE PUBLICATION testpub_syntax2 FOR TABLE testpub_rf_tbl1, testpub_rf_schema1.testpub_rf_tbl5 WHERE (h < 999) WITH (publish = 'insert'); RESET client_min_messages; \dRp+ testpub_syntax2 - Publication testpub_syntax2 - Owner | All tables | Inserts | Updates | Deletes | Truncates | Generated columns | Via root ---------------------------+------------+---------+---------+---------+-----------+-------------------+---------- - regress_publication_user | f | t | f | f | f | none | f + Publication testpub_syntax2 + Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root +--------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+---------- + regress_publication_user | f | f | t | f | f | f | none | f Tables: "public.testpub_rf_tbl1" "testpub_rf_schema1.testpub_rf_tbl5" WHERE (h < 999) @@ -518,10 +604,10 @@ CREATE PUBLICATION testpub6 FOR TABLES IN SCHEMA testpub_rf_schema2; ALTER PUBLICATION testpub6 SET TABLES IN SCHEMA testpub_rf_schema2, TABLE testpub_rf_schema2.testpub_rf_tbl6 WHERE (i < 99); RESET client_min_messages; \dRp+ testpub6 - Publication testpub6 - Owner | All tables | Inserts | Updates | Deletes | Truncates | Generated columns | Via root ---------------------------+------------+---------+---------+---------+-----------+-------------------+---------- - regress_publication_user | f | t | t | t | t | none | f + Publication testpub6 + Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root +--------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+---------- + regress_publication_user | f | f | t | t | t | t | none | f Tables: "testpub_rf_schema2.testpub_rf_tbl6" WHERE (i < 99) Tables from schemas: @@ -813,10 +899,10 @@ CREATE PUBLICATION testpub_table_ins WITH (publish = 'insert, truncate'); RESET client_min_messages; ALTER PUBLICATION testpub_table_ins ADD TABLE testpub_tbl5 (a); -- ok \dRp+ testpub_table_ins - Publication testpub_table_ins - Owner | All tables | Inserts | Updates | Deletes | Truncates | Generated columns | Via root ---------------------------+------------+---------+---------+---------+-----------+-------------------+---------- - regress_publication_user | f | t | f | f | t | none | f + Publication testpub_table_ins + Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root +--------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+---------- + regress_publication_user | f | f | t | f | f | t | none | f Tables: "public.testpub_tbl5" (a) @@ -1006,10 +1092,10 @@ CREATE TABLE testpub_tbl_both_filters (a int, b int, c int, PRIMARY KEY (a,c)); ALTER TABLE testpub_tbl_both_filters REPLICA IDENTITY USING INDEX testpub_tbl_both_filters_pkey; ALTER PUBLICATION testpub_both_filters ADD TABLE testpub_tbl_both_filters (a,c) WHERE (c != 1); \dRp+ testpub_both_filters - Publication testpub_both_filters - Owner | All tables | Inserts | Updates | Deletes | Truncates | Generated columns | Via root ---------------------------+------------+---------+---------+---------+-----------+-------------------+---------- - regress_publication_user | f | t | t | t | t | none | f + Publication testpub_both_filters + Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root +--------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+---------- + regress_publication_user | f | f | t | t | t | t | none | f Tables: "public.testpub_tbl_both_filters" (a, c) WHERE (c <> 1) @@ -1217,10 +1303,10 @@ ERROR: relation "testpub_tbl1" is already member of publication "testpub_fortbl CREATE PUBLICATION testpub_fortbl FOR TABLE testpub_tbl1; ERROR: publication "testpub_fortbl" already exists \dRp+ testpub_fortbl - Publication testpub_fortbl - Owner | All tables | Inserts | Updates | Deletes | Truncates | Generated columns | Via root ---------------------------+------------+---------+---------+---------+-----------+-------------------+---------- - regress_publication_user | f | t | t | t | t | none | f + Publication testpub_fortbl + Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root +--------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+---------- + regress_publication_user | f | f | t | t | t | t | none | f Tables: "pub_test.testpub_nopk" "public.testpub_tbl1" @@ -1260,10 +1346,10 @@ Not-null constraints: "testpub_tbl1_id_not_null" NOT NULL "id" \dRp+ testpub_default - Publication testpub_default - Owner | All tables | Inserts | Updates | Deletes | Truncates | Generated columns | Via root ---------------------------+------------+---------+---------+---------+-----------+-------------------+---------- - regress_publication_user | f | t | t | t | f | none | f + Publication testpub_default + Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root +--------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+---------- + regress_publication_user | f | f | t | t | t | f | none | f Tables: "pub_test.testpub_nopk" "public.testpub_tbl1" @@ -1334,7 +1420,7 @@ SET ROLE regress_publication_user3; -- fail - new owner must be superuser ALTER PUBLICATION testpub4 owner to regress_publication_user2; -- fail ERROR: permission denied to change owner of publication "testpub4" -HINT: The owner of a FOR TABLES IN SCHEMA publication must be a superuser. +HINT: The owner of a FOR ALL TABLES or ALL SEQUENCES or TABLES IN SCHEMA publication must be a superuser. ALTER PUBLICATION testpub4 owner to regress_publication_user; -- ok SET ROLE regress_publication_user; DROP PUBLICATION testpub4; @@ -1343,10 +1429,10 @@ REVOKE CREATE ON DATABASE regression FROM regress_publication_user2; DROP TABLE testpub_parted; DROP TABLE testpub_tbl1; \dRp+ testpub_default - Publication testpub_default - Owner | All tables | Inserts | Updates | Deletes | Truncates | Generated columns | Via root ---------------------------+------------+---------+---------+---------+-----------+-------------------+---------- - regress_publication_user | f | t | t | t | f | none | f + Publication testpub_default + Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root +--------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+---------- + regress_publication_user | f | f | t | t | t | f | none | f (1 row) -- fail - must be owner of publication @@ -1356,20 +1442,20 @@ ERROR: must be owner of publication testpub_default RESET ROLE; ALTER PUBLICATION testpub_default RENAME TO testpub_foo; \dRp testpub_foo - List of publications - Name | Owner | All tables | Inserts | Updates | Deletes | Truncates | Generated columns | Via root --------------+--------------------------+------------+---------+---------+---------+-----------+-------------------+---------- - testpub_foo | regress_publication_user | f | t | t | t | f | none | f + List of publications + Name | Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root +-------------+--------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+---------- + testpub_foo | regress_publication_user | f | f | t | t | t | f | none | f (1 row) -- rename back to keep the rest simple ALTER PUBLICATION testpub_foo RENAME TO testpub_default; ALTER PUBLICATION testpub_default OWNER TO regress_publication_user2; \dRp testpub_default - List of publications - Name | Owner | All tables | Inserts | Updates | Deletes | Truncates | Generated columns | Via root ------------------+---------------------------+------------+---------+---------+---------+-----------+-------------------+---------- - testpub_default | regress_publication_user2 | f | t | t | t | f | none | f + List of publications + Name | Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root +-----------------+---------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+---------- + testpub_default | regress_publication_user2 | f | f | t | t | t | f | none | f (1 row) -- adding schemas and tables @@ -1385,19 +1471,19 @@ CREATE TABLE "CURRENT_SCHEMA"."CURRENT_SCHEMA"(id int); SET client_min_messages = 'ERROR'; CREATE PUBLICATION testpub1_forschema FOR TABLES IN SCHEMA pub_test1; \dRp+ testpub1_forschema - Publication testpub1_forschema - Owner | All tables | Inserts | Updates | Deletes | Truncates | Generated columns | Via root ---------------------------+------------+---------+---------+---------+-----------+-------------------+---------- - regress_publication_user | f | t | t | t | t | none | f + Publication testpub1_forschema + Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root +--------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+---------- + regress_publication_user | f | f | t | t | t | t | none | f Tables from schemas: "pub_test1" CREATE PUBLICATION testpub2_forschema FOR TABLES IN SCHEMA pub_test1, pub_test2, pub_test3; \dRp+ testpub2_forschema - Publication testpub2_forschema - Owner | All tables | Inserts | Updates | Deletes | Truncates | Generated columns | Via root ---------------------------+------------+---------+---------+---------+-----------+-------------------+---------- - regress_publication_user | f | t | t | t | t | none | f + Publication testpub2_forschema + Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root +--------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+---------- + regress_publication_user | f | f | t | t | t | t | none | f Tables from schemas: "pub_test1" "pub_test2" @@ -1411,44 +1497,44 @@ CREATE PUBLICATION testpub6_forschema FOR TABLES IN SCHEMA "CURRENT_SCHEMA", CUR CREATE PUBLICATION testpub_fortable FOR TABLE "CURRENT_SCHEMA"."CURRENT_SCHEMA"; RESET client_min_messages; \dRp+ testpub3_forschema - Publication testpub3_forschema - Owner | All tables | Inserts | Updates | Deletes | Truncates | Generated columns | Via root ---------------------------+------------+---------+---------+---------+-----------+-------------------+---------- - regress_publication_user | f | t | t | t | t | none | f + Publication testpub3_forschema + Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root +--------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+---------- + regress_publication_user | f | f | t | t | t | t | none | f Tables from schemas: "public" \dRp+ testpub4_forschema - Publication testpub4_forschema - Owner | All tables | Inserts | Updates | Deletes | Truncates | Generated columns | Via root ---------------------------+------------+---------+---------+---------+-----------+-------------------+---------- - regress_publication_user | f | t | t | t | t | none | f + Publication testpub4_forschema + Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root +--------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+---------- + regress_publication_user | f | f | t | t | t | t | none | f Tables from schemas: "CURRENT_SCHEMA" \dRp+ testpub5_forschema - Publication testpub5_forschema - Owner | All tables | Inserts | Updates | Deletes | Truncates | Generated columns | Via root ---------------------------+------------+---------+---------+---------+-----------+-------------------+---------- - regress_publication_user | f | t | t | t | t | none | f + Publication testpub5_forschema + Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root +--------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+---------- + regress_publication_user | f | f | t | t | t | t | none | f Tables from schemas: "CURRENT_SCHEMA" "public" \dRp+ testpub6_forschema - Publication testpub6_forschema - Owner | All tables | Inserts | Updates | Deletes | Truncates | Generated columns | Via root ---------------------------+------------+---------+---------+---------+-----------+-------------------+---------- - regress_publication_user | f | t | t | t | t | none | f + Publication testpub6_forschema + Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root +--------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+---------- + regress_publication_user | f | f | t | t | t | t | none | f Tables from schemas: "CURRENT_SCHEMA" "public" \dRp+ testpub_fortable - Publication testpub_fortable - Owner | All tables | Inserts | Updates | Deletes | Truncates | Generated columns | Via root ---------------------------+------------+---------+---------+---------+-----------+-------------------+---------- - regress_publication_user | f | t | t | t | t | none | f + Publication testpub_fortable + Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root +--------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+---------- + regress_publication_user | f | f | t | t | t | t | none | f Tables: "CURRENT_SCHEMA.CURRENT_SCHEMA" @@ -1482,10 +1568,10 @@ ERROR: schema "testpub_view" does not exist -- dropping the schema should reflect the change in publication DROP SCHEMA pub_test3; \dRp+ testpub2_forschema - Publication testpub2_forschema - Owner | All tables | Inserts | Updates | Deletes | Truncates | Generated columns | Via root ---------------------------+------------+---------+---------+---------+-----------+-------------------+---------- - regress_publication_user | f | t | t | t | t | none | f + Publication testpub2_forschema + Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root +--------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+---------- + regress_publication_user | f | f | t | t | t | t | none | f Tables from schemas: "pub_test1" "pub_test2" @@ -1493,20 +1579,20 @@ Tables from schemas: -- renaming the schema should reflect the change in publication ALTER SCHEMA pub_test1 RENAME to pub_test1_renamed; \dRp+ testpub2_forschema - Publication testpub2_forschema - Owner | All tables | Inserts | Updates | Deletes | Truncates | Generated columns | Via root ---------------------------+------------+---------+---------+---------+-----------+-------------------+---------- - regress_publication_user | f | t | t | t | t | none | f + Publication testpub2_forschema + Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root +--------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+---------- + regress_publication_user | f | f | t | t | t | t | none | f Tables from schemas: "pub_test1_renamed" "pub_test2" ALTER SCHEMA pub_test1_renamed RENAME to pub_test1; \dRp+ testpub2_forschema - Publication testpub2_forschema - Owner | All tables | Inserts | Updates | Deletes | Truncates | Generated columns | Via root ---------------------------+------------+---------+---------+---------+-----------+-------------------+---------- - regress_publication_user | f | t | t | t | t | none | f + Publication testpub2_forschema + Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root +--------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+---------- + regress_publication_user | f | f | t | t | t | t | none | f Tables from schemas: "pub_test1" "pub_test2" @@ -1514,10 +1600,10 @@ Tables from schemas: -- alter publication add schema ALTER PUBLICATION testpub1_forschema ADD TABLES IN SCHEMA pub_test2; \dRp+ testpub1_forschema - Publication testpub1_forschema - Owner | All tables | Inserts | Updates | Deletes | Truncates | Generated columns | Via root ---------------------------+------------+---------+---------+---------+-----------+-------------------+---------- - regress_publication_user | f | t | t | t | t | none | f + Publication testpub1_forschema + Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root +--------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+---------- + regress_publication_user | f | f | t | t | t | t | none | f Tables from schemas: "pub_test1" "pub_test2" @@ -1526,10 +1612,10 @@ Tables from schemas: ALTER PUBLICATION testpub1_forschema ADD TABLES IN SCHEMA non_existent_schema; ERROR: schema "non_existent_schema" does not exist \dRp+ testpub1_forschema - Publication testpub1_forschema - Owner | All tables | Inserts | Updates | Deletes | Truncates | Generated columns | Via root ---------------------------+------------+---------+---------+---------+-----------+-------------------+---------- - regress_publication_user | f | t | t | t | t | none | f + Publication testpub1_forschema + Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root +--------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+---------- + regress_publication_user | f | f | t | t | t | t | none | f Tables from schemas: "pub_test1" "pub_test2" @@ -1538,10 +1624,10 @@ Tables from schemas: ALTER PUBLICATION testpub1_forschema ADD TABLES IN SCHEMA pub_test1; ERROR: schema "pub_test1" is already member of publication "testpub1_forschema" \dRp+ testpub1_forschema - Publication testpub1_forschema - Owner | All tables | Inserts | Updates | Deletes | Truncates | Generated columns | Via root ---------------------------+------------+---------+---------+---------+-----------+-------------------+---------- - regress_publication_user | f | t | t | t | t | none | f + Publication testpub1_forschema + Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root +--------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+---------- + regress_publication_user | f | f | t | t | t | t | none | f Tables from schemas: "pub_test1" "pub_test2" @@ -1549,10 +1635,10 @@ Tables from schemas: -- alter publication drop schema ALTER PUBLICATION testpub1_forschema DROP TABLES IN SCHEMA pub_test2; \dRp+ testpub1_forschema - Publication testpub1_forschema - Owner | All tables | Inserts | Updates | Deletes | Truncates | Generated columns | Via root ---------------------------+------------+---------+---------+---------+-----------+-------------------+---------- - regress_publication_user | f | t | t | t | t | none | f + Publication testpub1_forschema + Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root +--------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+---------- + regress_publication_user | f | f | t | t | t | t | none | f Tables from schemas: "pub_test1" @@ -1560,10 +1646,10 @@ Tables from schemas: ALTER PUBLICATION testpub1_forschema DROP TABLES IN SCHEMA pub_test2; ERROR: tables from schema "pub_test2" are not part of the publication \dRp+ testpub1_forschema - Publication testpub1_forschema - Owner | All tables | Inserts | Updates | Deletes | Truncates | Generated columns | Via root ---------------------------+------------+---------+---------+---------+-----------+-------------------+---------- - regress_publication_user | f | t | t | t | t | none | f + Publication testpub1_forschema + Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root +--------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+---------- + regress_publication_user | f | f | t | t | t | t | none | f Tables from schemas: "pub_test1" @@ -1571,29 +1657,29 @@ Tables from schemas: ALTER PUBLICATION testpub1_forschema DROP TABLES IN SCHEMA non_existent_schema; ERROR: schema "non_existent_schema" does not exist \dRp+ testpub1_forschema - Publication testpub1_forschema - Owner | All tables | Inserts | Updates | Deletes | Truncates | Generated columns | Via root ---------------------------+------------+---------+---------+---------+-----------+-------------------+---------- - regress_publication_user | f | t | t | t | t | none | f + Publication testpub1_forschema + Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root +--------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+---------- + regress_publication_user | f | f | t | t | t | t | none | f Tables from schemas: "pub_test1" -- drop all schemas ALTER PUBLICATION testpub1_forschema DROP TABLES IN SCHEMA pub_test1; \dRp+ testpub1_forschema - Publication testpub1_forschema - Owner | All tables | Inserts | Updates | Deletes | Truncates | Generated columns | Via root ---------------------------+------------+---------+---------+---------+-----------+-------------------+---------- - regress_publication_user | f | t | t | t | t | none | f + Publication testpub1_forschema + Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root +--------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+---------- + regress_publication_user | f | f | t | t | t | t | none | f (1 row) -- alter publication set multiple schema ALTER PUBLICATION testpub1_forschema SET TABLES IN SCHEMA pub_test1, pub_test2; \dRp+ testpub1_forschema - Publication testpub1_forschema - Owner | All tables | Inserts | Updates | Deletes | Truncates | Generated columns | Via root ---------------------------+------------+---------+---------+---------+-----------+-------------------+---------- - regress_publication_user | f | t | t | t | t | none | f + Publication testpub1_forschema + Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root +--------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+---------- + regress_publication_user | f | f | t | t | t | t | none | f Tables from schemas: "pub_test1" "pub_test2" @@ -1602,10 +1688,10 @@ Tables from schemas: ALTER PUBLICATION testpub1_forschema SET TABLES IN SCHEMA non_existent_schema; ERROR: schema "non_existent_schema" does not exist \dRp+ testpub1_forschema - Publication testpub1_forschema - Owner | All tables | Inserts | Updates | Deletes | Truncates | Generated columns | Via root ---------------------------+------------+---------+---------+---------+-----------+-------------------+---------- - regress_publication_user | f | t | t | t | t | none | f + Publication testpub1_forschema + Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root +--------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+---------- + regress_publication_user | f | f | t | t | t | t | none | f Tables from schemas: "pub_test1" "pub_test2" @@ -1614,10 +1700,10 @@ Tables from schemas: -- removing the duplicate schemas ALTER PUBLICATION testpub1_forschema SET TABLES IN SCHEMA pub_test1, pub_test1; \dRp+ testpub1_forschema - Publication testpub1_forschema - Owner | All tables | Inserts | Updates | Deletes | Truncates | Generated columns | Via root ---------------------------+------------+---------+---------+---------+-----------+-------------------+---------- - regress_publication_user | f | t | t | t | t | none | f + Publication testpub1_forschema + Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root +--------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+---------- + regress_publication_user | f | f | t | t | t | t | none | f Tables from schemas: "pub_test1" @@ -1696,18 +1782,18 @@ SET client_min_messages = 'ERROR'; CREATE PUBLICATION testpub3_forschema; RESET client_min_messages; \dRp+ testpub3_forschema - Publication testpub3_forschema - Owner | All tables | Inserts | Updates | Deletes | Truncates | Generated columns | Via root ---------------------------+------------+---------+---------+---------+-----------+-------------------+---------- - regress_publication_user | f | t | t | t | t | none | f + Publication testpub3_forschema + Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root +--------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+---------- + regress_publication_user | f | f | t | t | t | t | none | f (1 row) ALTER PUBLICATION testpub3_forschema SET TABLES IN SCHEMA pub_test1; \dRp+ testpub3_forschema - Publication testpub3_forschema - Owner | All tables | Inserts | Updates | Deletes | Truncates | Generated columns | Via root ---------------------------+------------+---------+---------+---------+-----------+-------------------+---------- - regress_publication_user | f | t | t | t | t | none | f + Publication testpub3_forschema + Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root +--------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+---------- + regress_publication_user | f | f | t | t | t | t | none | f Tables from schemas: "pub_test1" @@ -1717,20 +1803,20 @@ CREATE PUBLICATION testpub_forschema_fortable FOR TABLES IN SCHEMA pub_test1, TA CREATE PUBLICATION testpub_fortable_forschema FOR TABLE pub_test2.tbl1, TABLES IN SCHEMA pub_test1; RESET client_min_messages; \dRp+ testpub_forschema_fortable - Publication testpub_forschema_fortable - Owner | All tables | Inserts | Updates | Deletes | Truncates | Generated columns | Via root ---------------------------+------------+---------+---------+---------+-----------+-------------------+---------- - regress_publication_user | f | t | t | t | t | none | f + Publication testpub_forschema_fortable + Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root +--------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+---------- + regress_publication_user | f | f | t | t | t | t | none | f Tables: "pub_test2.tbl1" Tables from schemas: "pub_test1" \dRp+ testpub_fortable_forschema - Publication testpub_fortable_forschema - Owner | All tables | Inserts | Updates | Deletes | Truncates | Generated columns | Via root ---------------------------+------------+---------+---------+---------+-----------+-------------------+---------- - regress_publication_user | f | t | t | t | t | none | f + Publication testpub_fortable_forschema + Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root +--------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+---------- + regress_publication_user | f | f | t | t | t | t | none | f Tables: "pub_test2.tbl1" Tables from schemas: @@ -1851,18 +1937,18 @@ DROP SCHEMA sch2 cascade; SET client_min_messages = 'ERROR'; CREATE PUBLICATION pub1 FOR ALL TABLES WITH (publish_generated_columns = stored); \dRp+ pub1 - Publication pub1 - Owner | All tables | Inserts | Updates | Deletes | Truncates | Generated columns | Via root ---------------------------+------------+---------+---------+---------+-----------+-------------------+---------- - regress_publication_user | t | t | t | t | t | stored | f + Publication pub1 + Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root +--------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+---------- + regress_publication_user | t | f | t | t | t | t | stored | f (1 row) CREATE PUBLICATION pub2 FOR ALL TABLES WITH (publish_generated_columns = none); \dRp+ pub2 - Publication pub2 - Owner | All tables | Inserts | Updates | Deletes | Truncates | Generated columns | Via root ---------------------------+------------+---------+---------+---------+-----------+-------------------+---------- - regress_publication_user | t | t | t | t | t | none | f + Publication pub2 + Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root +--------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+---------- + regress_publication_user | t | f | t | t | t | t | none | f (1 row) DROP PUBLICATION pub1; @@ -1873,50 +1959,50 @@ CREATE TABLE gencols (a int, gen1 int GENERATED ALWAYS AS (a * 2) STORED); -- Generated columns in column list, when 'publish_generated_columns'='none' CREATE PUBLICATION pub1 FOR table gencols(a, gen1) WITH (publish_generated_columns = none); \dRp+ pub1 - Publication pub1 - Owner | All tables | Inserts | Updates | Deletes | Truncates | Generated columns | Via root ---------------------------+------------+---------+---------+---------+-----------+-------------------+---------- - regress_publication_user | f | t | t | t | t | none | f + Publication pub1 + Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root +--------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+---------- + regress_publication_user | f | f | t | t | t | t | none | f Tables: "public.gencols" (a, gen1) -- Generated columns in column list, when 'publish_generated_columns'='stored' CREATE PUBLICATION pub2 FOR table gencols(a, gen1) WITH (publish_generated_columns = stored); \dRp+ pub2 - Publication pub2 - Owner | All tables | Inserts | Updates | Deletes | Truncates | Generated columns | Via root ---------------------------+------------+---------+---------+---------+-----------+-------------------+---------- - regress_publication_user | f | t | t | t | t | stored | f + Publication pub2 + Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root +--------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+---------- + regress_publication_user | f | f | t | t | t | t | stored | f Tables: "public.gencols" (a, gen1) -- Generated columns in column list, then set 'publish_generated_columns'='none' ALTER PUBLICATION pub2 SET (publish_generated_columns = none); \dRp+ pub2 - Publication pub2 - Owner | All tables | Inserts | Updates | Deletes | Truncates | Generated columns | Via root ---------------------------+------------+---------+---------+---------+-----------+-------------------+---------- - regress_publication_user | f | t | t | t | t | none | f + Publication pub2 + Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root +--------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+---------- + regress_publication_user | f | f | t | t | t | t | none | f Tables: "public.gencols" (a, gen1) -- Remove generated columns from column list, when 'publish_generated_columns'='none' ALTER PUBLICATION pub2 SET TABLE gencols(a); \dRp+ pub2 - Publication pub2 - Owner | All tables | Inserts | Updates | Deletes | Truncates | Generated columns | Via root ---------------------------+------------+---------+---------+---------+-----------+-------------------+---------- - regress_publication_user | f | t | t | t | t | none | f + Publication pub2 + Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root +--------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+---------- + regress_publication_user | f | f | t | t | t | t | none | f Tables: "public.gencols" (a) -- Add generated columns in column list, when 'publish_generated_columns'='none' ALTER PUBLICATION pub2 SET TABLE gencols(a, gen1); \dRp+ pub2 - Publication pub2 - Owner | All tables | Inserts | Updates | Deletes | Truncates | Generated columns | Via root ---------------------------+------------+---------+---------+---------+-----------+-------------------+---------- - regress_publication_user | f | t | t | t | t | none | f + Publication pub2 + Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root +--------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+---------- + regress_publication_user | f | f | t | t | t | t | none | f Tables: "public.gencols" (a, gen1) diff --git a/src/test/regress/expected/rules.out b/src/test/regress/expected/rules.out index 7f1cb3bb4af..16753b2e4c0 100644 --- a/src/test/regress/expected/rules.out +++ b/src/test/regress/expected/rules.out @@ -1462,6 +1462,14 @@ pg_prepared_xacts| SELECT p.transaction, FROM ((pg_prepared_xact() p(transaction, gid, prepared, ownerid, dbid) LEFT JOIN pg_authid u ON ((p.ownerid = u.oid))) LEFT JOIN pg_database d ON ((p.dbid = d.oid))); +pg_publication_sequences| SELECT p.pubname, + n.nspname AS schemaname, + c.relname AS sequencename + FROM pg_publication p, + LATERAL pg_get_publication_sequences((p.pubname)::text) gps(relid), + (pg_class c + JOIN pg_namespace n ON ((n.oid = c.relnamespace))) + WHERE (c.oid = gps.relid); pg_publication_tables| SELECT p.pubname, n.nspname AS schemaname, c.relname AS tablename, @@ -2140,11 +2148,12 @@ pg_stat_replication_slots| SELECT s.slot_name, s.stream_txns, s.stream_count, s.stream_bytes, + s.mem_exceeded_count, s.total_txns, s.total_bytes, s.stats_reset FROM pg_replication_slots r, - LATERAL pg_stat_get_replication_slot((r.slot_name)::text) s(slot_name, spill_txns, spill_count, spill_bytes, stream_txns, stream_count, stream_bytes, total_txns, total_bytes, stats_reset) + LATERAL pg_stat_get_replication_slot((r.slot_name)::text) s(slot_name, spill_txns, spill_count, spill_bytes, stream_txns, stream_count, stream_bytes, mem_exceeded_count, total_txns, total_bytes, stats_reset) WHERE (r.datoid IS NOT NULL); pg_stat_slru| SELECT name, blks_zeroed, @@ -2244,7 +2253,8 @@ pg_stat_user_functions| SELECT p.oid AS funcid, p.proname AS funcname, pg_stat_get_function_calls(p.oid) AS calls, pg_stat_get_function_total_time(p.oid) AS total_time, - pg_stat_get_function_self_time(p.oid) AS self_time + pg_stat_get_function_self_time(p.oid) AS self_time, + pg_stat_get_function_stat_reset_time(p.oid) AS stats_reset FROM (pg_proc p LEFT JOIN pg_namespace n ON ((n.oid = p.pronamespace))) WHERE ((p.prolang <> (12)::oid) AND (pg_stat_get_function_calls(p.oid) IS NOT NULL)); diff --git a/src/test/regress/expected/sysviews.out b/src/test/regress/expected/sysviews.out index 83228cfca29..3b37fafa65b 100644 --- a/src/test/regress/expected/sysviews.out +++ b/src/test/regress/expected/sysviews.out @@ -151,6 +151,7 @@ select name, setting from pg_settings where name like 'enable%'; enable_async_append | on enable_bitmapscan | on enable_distinct_reordering | on + enable_eager_aggregate | on enable_gathermerge | on enable_group_by_reordering | on enable_hashagg | on @@ -172,7 +173,7 @@ select name, setting from pg_settings where name like 'enable%'; enable_seqscan | on enable_sort | on enable_tidscan | on -(24 rows) +(25 rows) -- There are always wait event descriptions for various types. InjectionPoint -- may be present or absent, depending on history since last postmaster start. diff --git a/src/test/regress/expected/vacuum.out b/src/test/regress/expected/vacuum.out index 85c783e2e56..d4696bc3325 100644 --- a/src/test/regress/expected/vacuum.out +++ b/src/test/regress/expected/vacuum.out @@ -161,16 +161,14 @@ VACUUM (PARALLEL 2) pvactst; UPDATE pvactst SET i = i WHERE i < 1000; VACUUM (PARALLEL 0) pvactst; -- disable parallel vacuum VACUUM (PARALLEL -1) pvactst; -- error -ERROR: parallel workers for vacuum must be between 0 and 1024 +ERROR: PARALLEL option must be between 0 and 1024 LINE 1: VACUUM (PARALLEL -1) pvactst; ^ VACUUM (PARALLEL 2, INDEX_CLEANUP FALSE) pvactst; VACUUM (PARALLEL 2, FULL TRUE) pvactst; -- error, cannot use both PARALLEL and FULL ERROR: VACUUM FULL cannot be performed in parallel VACUUM (PARALLEL) pvactst; -- error, cannot use PARALLEL option without parallel degree -ERROR: parallel option requires a value between 0 and 1024 -LINE 1: VACUUM (PARALLEL) pvactst; - ^ +ERROR: parallel requires an integer value -- Test parallel vacuum using the minimum maintenance_work_mem with and without -- dead tuples. SET maintenance_work_mem TO 64; diff --git a/src/test/regress/expected/window.out b/src/test/regress/expected/window.out index a2867f477f0..9e2f53726f5 100644 --- a/src/test/regress/expected/window.out +++ b/src/test/regress/expected/window.out @@ -4537,6 +4537,22 @@ WHERE first_emp = 1 OR last_emp = 1; sales | 4 | 4800 | 08-08-2007 | 3 | 1 (6 rows) +CREATE INDEX empsalary_salary_empno_idx ON empsalary (salary, empno); +SET enable_seqscan = 0; +-- Ensure no sorting is done and that the IndexScan maintains all pathkeys +-- useful for the final sort order. +EXPLAIN (COSTS OFF) +SELECT salary, empno, row_number() OVER (ORDER BY salary) rn +FROM empsalary +ORDER BY salary, empno; + QUERY PLAN +--------------------------------------------------------------------- + WindowAgg + Window: w1 AS (ORDER BY salary ROWS UNBOUNDED PRECEDING) + -> Index Only Scan using empsalary_salary_empno_idx on empsalary +(3 rows) + +RESET enable_seqscan; -- cleanup DROP TABLE empsalary; -- test user-defined window function with named args and default args diff --git a/src/test/regress/parallel_schedule b/src/test/regress/parallel_schedule index fbffc67ae60..f9450cdc477 100644 --- a/src/test/regress/parallel_schedule +++ b/src/test/regress/parallel_schedule @@ -123,7 +123,7 @@ test: plancache limit plpgsql copy2 temp domain rangefuncs prepare conversion tr # The stats test resets stats, so nothing else needing stats access can be in # this group. # ---------- -test: partition_join partition_prune reloptions hash_part indexing partition_aggregate partition_info tuplesort explain compression compression_lz4 memoize stats predicate numa +test: partition_join partition_prune reloptions hash_part indexing partition_aggregate partition_info tuplesort explain compression compression_lz4 memoize stats predicate numa eager_aggregate # event_trigger depends on create_am and cannot run concurrently with # any test that runs DDL diff --git a/src/test/regress/sql/collate.icu.utf8.sql b/src/test/regress/sql/collate.icu.utf8.sql index dbc190227d0..6f5abac0dc0 100644 --- a/src/test/regress/sql/collate.icu.utf8.sql +++ b/src/test/regress/sql/collate.icu.utf8.sql @@ -997,6 +997,19 @@ INSERT INTO t5 (a, b) values (1, 'D1'), (2, 'D2'), (3, 'd1'); -- rewriting.) SELECT * FROM t5 ORDER BY c ASC, a ASC; +-- Check that DEFAULT expressions in SQL/JSON functions use the same collation +-- as the RETURNING type. Mismatched collations should raise an error. +CREATE DOMAIN d1 AS text COLLATE case_insensitive; +CREATE DOMAIN d2 AS text COLLATE "C"; +SELECT JSON_VALUE('{"a": "A"}', '$.a' RETURNING d1 DEFAULT ('C' COLLATE "C") COLLATE case_insensitive ON EMPTY) = 'a'; -- true +SELECT JSON_VALUE('{"a": "A"}', '$.a' RETURNING d1 DEFAULT 'C' ON EMPTY) = 'a'; -- true +SELECT JSON_VALUE('{"a": "A"}', '$.a' RETURNING d1 DEFAULT 'C'::d2 ON EMPTY) = 'a'; -- error +SELECT JSON_VALUE('{"a": "A"}', '$.a' RETURNING d1 DEFAULT 'C' COLLATE "C" ON EMPTY) = 'a'; -- error +SELECT JSON_VALUE('{"a": "A"}', '$.c' RETURNING d1 DEFAULT 'A' ON EMPTY) = 'a'; -- true +SELECT JSON_VALUE('{"a": "A"}', '$.c' RETURNING d1 DEFAULT 'A' COLLATE case_insensitive ON EMPTY) = 'a'; -- true +SELECT JSON_VALUE('{"a": "A"}', '$.c' RETURNING d1 DEFAULT 'A'::d2 ON EMPTY) = 'a'; -- error +SELECT JSON_VALUE('{"a": "A"}', '$.c' RETURNING d1 DEFAULT 'A' COLLATE "C" ON EMPTY) = 'a'; -- error +DROP DOMAIN d1, d2; -- cleanup RESET search_path; diff --git a/src/test/regress/sql/eager_aggregate.sql b/src/test/regress/sql/eager_aggregate.sql new file mode 100644 index 00000000000..abe6d6ae09f --- /dev/null +++ b/src/test/regress/sql/eager_aggregate.sql @@ -0,0 +1,377 @@ +-- +-- EAGER AGGREGATION +-- Test we can push aggregation down below join +-- + +CREATE TABLE eager_agg_t1 (a int, b int, c double precision); +CREATE TABLE eager_agg_t2 (a int, b int, c double precision); +CREATE TABLE eager_agg_t3 (a int, b int, c double precision); + +INSERT INTO eager_agg_t1 SELECT i, i, i FROM generate_series(1, 1000) i; +INSERT INTO eager_agg_t2 SELECT i, i%10, i FROM generate_series(1, 1000) i; +INSERT INTO eager_agg_t3 SELECT i%10, i%10, i FROM generate_series(1, 1000) i; + +ANALYZE eager_agg_t1; +ANALYZE eager_agg_t2; +ANALYZE eager_agg_t3; + + +-- +-- Test eager aggregation over base rel +-- + +-- Perform scan of a table, aggregate the result, join it to the other table +-- and finalize the aggregation. +EXPLAIN (VERBOSE, COSTS OFF) +SELECT t1.a, avg(t2.c) + FROM eager_agg_t1 t1 + JOIN eager_agg_t2 t2 ON t1.b = t2.b +GROUP BY t1.a ORDER BY t1.a; + +SELECT t1.a, avg(t2.c) + FROM eager_agg_t1 t1 + JOIN eager_agg_t2 t2 ON t1.b = t2.b +GROUP BY t1.a ORDER BY t1.a; + +-- Produce results with sorting aggregation +SET enable_hashagg TO off; + +EXPLAIN (VERBOSE, COSTS OFF) +SELECT t1.a, avg(t2.c) + FROM eager_agg_t1 t1 + JOIN eager_agg_t2 t2 ON t1.b = t2.b +GROUP BY t1.a ORDER BY t1.a; + +SELECT t1.a, avg(t2.c) + FROM eager_agg_t1 t1 + JOIN eager_agg_t2 t2 ON t1.b = t2.b +GROUP BY t1.a ORDER BY t1.a; + +RESET enable_hashagg; + + +-- +-- Test eager aggregation over join rel +-- + +-- Perform join of tables, aggregate the result, join it to the other table +-- and finalize the aggregation. +EXPLAIN (VERBOSE, COSTS OFF) +SELECT t1.a, avg(t2.c + t3.c) + FROM eager_agg_t1 t1 + JOIN eager_agg_t2 t2 ON t1.b = t2.b + JOIN eager_agg_t3 t3 ON t2.a = t3.a +GROUP BY t1.a ORDER BY t1.a; + +SELECT t1.a, avg(t2.c + t3.c) + FROM eager_agg_t1 t1 + JOIN eager_agg_t2 t2 ON t1.b = t2.b + JOIN eager_agg_t3 t3 ON t2.a = t3.a +GROUP BY t1.a ORDER BY t1.a; + +-- Produce results with sorting aggregation +SET enable_hashagg TO off; + +EXPLAIN (VERBOSE, COSTS OFF) +SELECT t1.a, avg(t2.c + t3.c) + FROM eager_agg_t1 t1 + JOIN eager_agg_t2 t2 ON t1.b = t2.b + JOIN eager_agg_t3 t3 ON t2.a = t3.a +GROUP BY t1.a ORDER BY t1.a; + +SELECT t1.a, avg(t2.c + t3.c) + FROM eager_agg_t1 t1 + JOIN eager_agg_t2 t2 ON t1.b = t2.b + JOIN eager_agg_t3 t3 ON t2.a = t3.a +GROUP BY t1.a ORDER BY t1.a; + +RESET enable_hashagg; + + +-- +-- Test that eager aggregation works for outer join +-- + +-- Ensure aggregation can be pushed down to the non-nullable side +EXPLAIN (VERBOSE, COSTS OFF) +SELECT t1.a, avg(t2.c) + FROM eager_agg_t1 t1 + RIGHT JOIN eager_agg_t2 t2 ON t1.b = t2.b +GROUP BY t1.a ORDER BY t1.a; + +SELECT t1.a, avg(t2.c) + FROM eager_agg_t1 t1 + RIGHT JOIN eager_agg_t2 t2 ON t1.b = t2.b +GROUP BY t1.a ORDER BY t1.a; + +-- Ensure aggregation cannot be pushed down to the nullable side +EXPLAIN (VERBOSE, COSTS OFF) +SELECT t2.b, avg(t2.c) + FROM eager_agg_t1 t1 + LEFT JOIN eager_agg_t2 t2 ON t1.b = t2.b +GROUP BY t2.b ORDER BY t2.b; + +SELECT t2.b, avg(t2.c) + FROM eager_agg_t1 t1 + LEFT JOIN eager_agg_t2 t2 ON t1.b = t2.b +GROUP BY t2.b ORDER BY t2.b; + + +-- +-- Test that eager aggregation works for parallel plans +-- + +SET parallel_setup_cost=0; +SET parallel_tuple_cost=0; +SET min_parallel_table_scan_size=0; +SET max_parallel_workers_per_gather=4; + +EXPLAIN (VERBOSE, COSTS OFF) +SELECT t1.a, avg(t2.c) + FROM eager_agg_t1 t1 + JOIN eager_agg_t2 t2 ON t1.b = t2.b +GROUP BY t1.a ORDER BY t1.a; + +SELECT t1.a, avg(t2.c) + FROM eager_agg_t1 t1 + JOIN eager_agg_t2 t2 ON t1.b = t2.b +GROUP BY t1.a ORDER BY t1.a; + +RESET parallel_setup_cost; +RESET parallel_tuple_cost; +RESET min_parallel_table_scan_size; +RESET max_parallel_workers_per_gather; + +-- +-- Test eager aggregation with GEQO +-- + +SET geqo = on; +SET geqo_threshold = 2; + +EXPLAIN (VERBOSE, COSTS OFF) +SELECT t1.a, avg(t2.c) + FROM eager_agg_t1 t1 + JOIN eager_agg_t2 t2 ON t1.b = t2.b +GROUP BY t1.a ORDER BY t1.a; + +SELECT t1.a, avg(t2.c) + FROM eager_agg_t1 t1 + JOIN eager_agg_t2 t2 ON t1.b = t2.b +GROUP BY t1.a ORDER BY t1.a; + +RESET geqo; +RESET geqo_threshold; + +DROP TABLE eager_agg_t1; +DROP TABLE eager_agg_t2; +DROP TABLE eager_agg_t3; + + +-- +-- Test eager aggregation for partitionwise join +-- + +-- Enable partitionwise aggregate, which by default is disabled. +SET enable_partitionwise_aggregate TO true; +-- Enable partitionwise join, which by default is disabled. +SET enable_partitionwise_join TO true; + +CREATE TABLE eager_agg_tab1(x int, y int) PARTITION BY RANGE(x); +CREATE TABLE eager_agg_tab1_p1 PARTITION OF eager_agg_tab1 FOR VALUES FROM (0) TO (5); +CREATE TABLE eager_agg_tab1_p2 PARTITION OF eager_agg_tab1 FOR VALUES FROM (5) TO (10); +CREATE TABLE eager_agg_tab1_p3 PARTITION OF eager_agg_tab1 FOR VALUES FROM (10) TO (15); +CREATE TABLE eager_agg_tab2(x int, y int) PARTITION BY RANGE(y); +CREATE TABLE eager_agg_tab2_p1 PARTITION OF eager_agg_tab2 FOR VALUES FROM (0) TO (5); +CREATE TABLE eager_agg_tab2_p2 PARTITION OF eager_agg_tab2 FOR VALUES FROM (5) TO (10); +CREATE TABLE eager_agg_tab2_p3 PARTITION OF eager_agg_tab2 FOR VALUES FROM (10) TO (15); +INSERT INTO eager_agg_tab1 SELECT i % 15, i % 10 FROM generate_series(1, 1000) i; +INSERT INTO eager_agg_tab2 SELECT i % 10, i % 15 FROM generate_series(1, 1000) i; + +ANALYZE eager_agg_tab1; +ANALYZE eager_agg_tab2; + +-- When GROUP BY clause matches; full aggregation is performed for each +-- partition. +EXPLAIN (VERBOSE, COSTS OFF) +SELECT t1.x, sum(t1.y), count(*) + FROM eager_agg_tab1 t1 + JOIN eager_agg_tab2 t2 ON t1.x = t2.y +GROUP BY t1.x ORDER BY t1.x; + +SELECT t1.x, sum(t1.y), count(*) + FROM eager_agg_tab1 t1 + JOIN eager_agg_tab2 t2 ON t1.x = t2.y +GROUP BY t1.x ORDER BY t1.x; + +-- GROUP BY having other matching key +EXPLAIN (VERBOSE, COSTS OFF) +SELECT t2.y, sum(t1.y), count(*) + FROM eager_agg_tab1 t1 + JOIN eager_agg_tab2 t2 ON t1.x = t2.y +GROUP BY t2.y ORDER BY t2.y; + +SELECT t2.y, sum(t1.y), count(*) + FROM eager_agg_tab1 t1 + JOIN eager_agg_tab2 t2 ON t1.x = t2.y +GROUP BY t2.y ORDER BY t2.y; + +-- When GROUP BY clause does not match; partial aggregation is performed for +-- each partition. +EXPLAIN (VERBOSE, COSTS OFF) +SELECT t2.x, sum(t1.x), count(*) + FROM eager_agg_tab1 t1 + JOIN eager_agg_tab2 t2 ON t1.x = t2.y +GROUP BY t2.x HAVING avg(t1.x) > 5 ORDER BY t2.x; + +SELECT t2.x, sum(t1.x), count(*) + FROM eager_agg_tab1 t1 + JOIN eager_agg_tab2 t2 ON t1.x = t2.y +GROUP BY t2.x HAVING avg(t1.x) > 5 ORDER BY t2.x; + +-- Check with eager aggregation over join rel +-- full aggregation +EXPLAIN (VERBOSE, COSTS OFF) +SELECT t1.x, sum(t2.y + t3.y) + FROM eager_agg_tab1 t1 + JOIN eager_agg_tab1 t2 ON t1.x = t2.x + JOIN eager_agg_tab1 t3 ON t2.x = t3.x +GROUP BY t1.x ORDER BY t1.x; + +SELECT t1.x, sum(t2.y + t3.y) + FROM eager_agg_tab1 t1 + JOIN eager_agg_tab1 t2 ON t1.x = t2.x + JOIN eager_agg_tab1 t3 ON t2.x = t3.x +GROUP BY t1.x ORDER BY t1.x; + +-- partial aggregation +SET enable_hashagg TO off; +SET max_parallel_workers_per_gather TO 0; + +EXPLAIN (VERBOSE, COSTS OFF) +SELECT t3.y, sum(t2.y + t3.y) + FROM eager_agg_tab1 t1 + JOIN eager_agg_tab1 t2 ON t1.x = t2.x + JOIN eager_agg_tab1 t3 ON t2.x = t3.x +GROUP BY t3.y ORDER BY t3.y; + +SELECT t3.y, sum(t2.y + t3.y) + FROM eager_agg_tab1 t1 + JOIN eager_agg_tab1 t2 ON t1.x = t2.x + JOIN eager_agg_tab1 t3 ON t2.x = t3.x +GROUP BY t3.y ORDER BY t3.y; + +RESET enable_hashagg; +RESET max_parallel_workers_per_gather; + +-- try that with GEQO too +SET geqo = on; +SET geqo_threshold = 2; + +EXPLAIN (VERBOSE, COSTS OFF) +SELECT t1.x, sum(t1.y), count(*) + FROM eager_agg_tab1 t1 + JOIN eager_agg_tab2 t2 ON t1.x = t2.y +GROUP BY t1.x ORDER BY t1.x; + +SELECT t1.x, sum(t1.y), count(*) + FROM eager_agg_tab1 t1 + JOIN eager_agg_tab2 t2 ON t1.x = t2.y +GROUP BY t1.x ORDER BY t1.x; + +RESET geqo; +RESET geqo_threshold; + +DROP TABLE eager_agg_tab1; +DROP TABLE eager_agg_tab2; + + +-- +-- Test with multi-level partitioning scheme +-- +CREATE TABLE eager_agg_tab_ml(x int, y int) PARTITION BY RANGE(x); +CREATE TABLE eager_agg_tab_ml_p1 PARTITION OF eager_agg_tab_ml FOR VALUES FROM (0) TO (10); +CREATE TABLE eager_agg_tab_ml_p2 PARTITION OF eager_agg_tab_ml FOR VALUES FROM (10) TO (20) PARTITION BY RANGE(x); +CREATE TABLE eager_agg_tab_ml_p2_s1 PARTITION OF eager_agg_tab_ml_p2 FOR VALUES FROM (10) TO (15); +CREATE TABLE eager_agg_tab_ml_p2_s2 PARTITION OF eager_agg_tab_ml_p2 FOR VALUES FROM (15) TO (20); +CREATE TABLE eager_agg_tab_ml_p3 PARTITION OF eager_agg_tab_ml FOR VALUES FROM (20) TO (30) PARTITION BY RANGE(x); +CREATE TABLE eager_agg_tab_ml_p3_s1 PARTITION OF eager_agg_tab_ml_p3 FOR VALUES FROM (20) TO (25); +CREATE TABLE eager_agg_tab_ml_p3_s2 PARTITION OF eager_agg_tab_ml_p3 FOR VALUES FROM (25) TO (30); +INSERT INTO eager_agg_tab_ml SELECT i % 30, i % 30 FROM generate_series(1, 1000) i; + +ANALYZE eager_agg_tab_ml; + +-- When GROUP BY clause matches; full aggregation is performed for each +-- partition. +EXPLAIN (VERBOSE, COSTS OFF) +SELECT t1.x, sum(t2.y), count(*) + FROM eager_agg_tab_ml t1 + JOIN eager_agg_tab_ml t2 ON t1.x = t2.x +GROUP BY t1.x ORDER BY t1.x; + +SELECT t1.x, sum(t2.y), count(*) + FROM eager_agg_tab_ml t1 + JOIN eager_agg_tab_ml t2 ON t1.x = t2.x +GROUP BY t1.x ORDER BY t1.x; + +-- When GROUP BY clause does not match; partial aggregation is performed for +-- each partition. +EXPLAIN (VERBOSE, COSTS OFF) +SELECT t1.y, sum(t2.y), count(*) + FROM eager_agg_tab_ml t1 + JOIN eager_agg_tab_ml t2 ON t1.x = t2.x +GROUP BY t1.y ORDER BY t1.y; + +SELECT t1.y, sum(t2.y), count(*) + FROM eager_agg_tab_ml t1 + JOIN eager_agg_tab_ml t2 ON t1.x = t2.x +GROUP BY t1.y ORDER BY t1.y; + +-- Check with eager aggregation over join rel +-- full aggregation +EXPLAIN (VERBOSE, COSTS OFF) +SELECT t1.x, sum(t2.y + t3.y), count(*) + FROM eager_agg_tab_ml t1 + JOIN eager_agg_tab_ml t2 ON t1.x = t2.x + JOIN eager_agg_tab_ml t3 ON t2.x = t3.x +GROUP BY t1.x ORDER BY t1.x; + +SELECT t1.x, sum(t2.y + t3.y), count(*) + FROM eager_agg_tab_ml t1 + JOIN eager_agg_tab_ml t2 ON t1.x = t2.x + JOIN eager_agg_tab_ml t3 ON t2.x = t3.x +GROUP BY t1.x ORDER BY t1.x; + +-- partial aggregation +EXPLAIN (VERBOSE, COSTS OFF) +SELECT t3.y, sum(t2.y + t3.y), count(*) + FROM eager_agg_tab_ml t1 + JOIN eager_agg_tab_ml t2 ON t1.x = t2.x + JOIN eager_agg_tab_ml t3 ON t2.x = t3.x +GROUP BY t3.y ORDER BY t3.y; + +SELECT t3.y, sum(t2.y + t3.y), count(*) + FROM eager_agg_tab_ml t1 + JOIN eager_agg_tab_ml t2 ON t1.x = t2.x + JOIN eager_agg_tab_ml t3 ON t2.x = t3.x +GROUP BY t3.y ORDER BY t3.y; + +-- try that with GEQO too +SET geqo = on; +SET geqo_threshold = 2; + +EXPLAIN (VERBOSE, COSTS OFF) +SELECT t1.x, sum(t2.y), count(*) + FROM eager_agg_tab_ml t1 + JOIN eager_agg_tab_ml t2 ON t1.x = t2.x +GROUP BY t1.x ORDER BY t1.x; + +SELECT t1.x, sum(t2.y), count(*) + FROM eager_agg_tab_ml t1 + JOIN eager_agg_tab_ml t2 ON t1.x = t2.x +GROUP BY t1.x ORDER BY t1.x; + +RESET geqo; +RESET geqo_threshold; + +DROP TABLE eager_agg_tab_ml; diff --git a/src/test/regress/sql/opr_sanity.sql b/src/test/regress/sql/opr_sanity.sql index 2fb3a852878..cd674d7dbca 100644 --- a/src/test/regress/sql/opr_sanity.sql +++ b/src/test/regress/sql/opr_sanity.sql @@ -847,7 +847,7 @@ WHERE aggfnoid = 0 OR aggtransfn = 0 OR (aggkind = 'n' AND aggnumdirectargs > 0) OR aggfinalmodify NOT IN ('r', 's', 'w') OR aggmfinalmodify NOT IN ('r', 's', 'w') OR - aggtranstype = 0 OR aggtransspace < 0 OR aggmtransspace < 0; + aggtranstype = 0 OR aggmtransspace < 0; -- Make sure the matching pg_proc entry is sensible, too. diff --git a/src/test/regress/sql/partition_aggregate.sql b/src/test/regress/sql/partition_aggregate.sql index ab070fee244..124cc260461 100644 --- a/src/test/regress/sql/partition_aggregate.sql +++ b/src/test/regress/sql/partition_aggregate.sql @@ -14,6 +14,8 @@ SET enable_partitionwise_join TO true; SET max_parallel_workers_per_gather TO 0; -- Disable incremental sort, which can influence selected plans due to fuzz factor. SET enable_incremental_sort TO off; +-- Disable eager aggregation, which can interfere with the generation of partitionwise aggregation. +SET enable_eager_aggregate TO off; -- -- Tests for list partitioned tables. diff --git a/src/test/regress/sql/publication.sql b/src/test/regress/sql/publication.sql index 3f423061395..00390aecd47 100644 --- a/src/test/regress/sql/publication.sql +++ b/src/test/regress/sql/publication.sql @@ -120,6 +120,52 @@ RESET client_min_messages; DROP TABLE testpub_tbl3, testpub_tbl3a; DROP PUBLICATION testpub3, testpub4; +--- Tests for publications with SEQUENCES +CREATE SEQUENCE regress_pub_seq0; +CREATE SEQUENCE pub_test.regress_pub_seq1; + +-- FOR ALL SEQUENCES +SET client_min_messages = 'ERROR'; +CREATE PUBLICATION regress_pub_forallsequences1 FOR ALL SEQUENCES; +RESET client_min_messages; + +SELECT pubname, puballtables, puballsequences FROM pg_publication WHERE pubname = 'regress_pub_forallsequences1'; +\d+ regress_pub_seq0 +\dRp+ regress_pub_forallsequences1 + +SET client_min_messages = 'ERROR'; +CREATE PUBLICATION regress_pub_forallsequences2 FOR ALL SEQUENCES; +RESET client_min_messages; + +-- check that describe sequence lists both publications the sequence belongs to +\d+ pub_test.regress_pub_seq1 + +--- Specifying both ALL TABLES and ALL SEQUENCES +SET client_min_messages = 'ERROR'; +CREATE PUBLICATION regress_pub_for_allsequences_alltables FOR ALL SEQUENCES, ALL TABLES; + +-- Specifying WITH clause in an ALL SEQUENCES publication will emit a NOTICE. +SET client_min_messages = 'NOTICE'; +CREATE PUBLICATION regress_pub_for_allsequences_alltables_withclause FOR ALL SEQUENCES, ALL TABLES WITH (publish = 'insert'); +CREATE PUBLICATION regress_pub_for_allsequences_withclause FOR ALL SEQUENCES WITH (publish_generated_columns = 'stored'); +RESET client_min_messages; + +SELECT pubname, puballtables, puballsequences FROM pg_publication WHERE pubname = 'regress_pub_for_allsequences_alltables'; +\dRp+ regress_pub_for_allsequences_alltables + +DROP SEQUENCE regress_pub_seq0, pub_test.regress_pub_seq1; +DROP PUBLICATION regress_pub_forallsequences1; +DROP PUBLICATION regress_pub_forallsequences2; +DROP PUBLICATION regress_pub_for_allsequences_alltables; +DROP PUBLICATION regress_pub_for_allsequences_alltables_withclause; +DROP PUBLICATION regress_pub_for_allsequences_withclause; + +-- fail - Specifying ALL TABLES more than once +CREATE PUBLICATION regress_pub_for_allsequences_alltables FOR ALL SEQUENCES, ALL TABLES, ALL TABLES; + +-- fail - Specifying ALL SEQUENCES more than once +CREATE PUBLICATION regress_pub_for_allsequences_alltables FOR ALL SEQUENCES, ALL TABLES, ALL SEQUENCES; + -- Tests for partitioned tables SET client_min_messages = 'ERROR'; CREATE PUBLICATION testpub_forparted; diff --git a/src/test/regress/sql/window.sql b/src/test/regress/sql/window.sql index 85fc621c8db..37d837a2f66 100644 --- a/src/test/regress/sql/window.sql +++ b/src/test/regress/sql/window.sql @@ -1522,6 +1522,19 @@ SELECT * FROM FROM empsalary) emp WHERE first_emp = 1 OR last_emp = 1; +CREATE INDEX empsalary_salary_empno_idx ON empsalary (salary, empno); + +SET enable_seqscan = 0; + +-- Ensure no sorting is done and that the IndexScan maintains all pathkeys +-- useful for the final sort order. +EXPLAIN (COSTS OFF) +SELECT salary, empno, row_number() OVER (ORDER BY salary) rn +FROM empsalary +ORDER BY salary, empno; + +RESET enable_seqscan; + -- cleanup DROP TABLE empsalary; |