Skip to content

Commit 390533d

Browse files
vbwagnervbwagner
committed
Merge branch 'PGPRO9_6_TASK941' of gitlab.postgrespro.ru:pgpro-dev/postgrespro into PGPRO9_6_TASK941
2 parents f8ffcd4 + e799fea commit 390533d

28 files changed

+2471
-237
lines changed

‎contrib/pg_pathman/META.json

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -2,7 +2,7 @@
22
"name": "pg_pathman",
33
"abstract": "Partitioning tool",
44
"description": "The `pg_pathman` module provides optimized partitioning mechanism and functions to manage partitions.",
5-
"version": "1.4.2",
5+
"version": "1.4.3",
66
"maintainer": [
77
"Ildar Musin <i.musin@postgrespro.ru>",
88
"Dmitry Ivanov <d.ivanov@postgrespro.ru>",
@@ -24,7 +24,7 @@
2424
"pg_pathman": {
2525
"file": "pg_pathman--1.4.sql",
2626
"docfile": "README.md",
27-
"version": "1.4.2",
27+
"version": "1.4.3",
2828
"abstract": "Partitioning tool"
2929
}
3030
},

‎contrib/pg_pathman/Makefile

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -47,6 +47,7 @@ REGRESS = pathman_array_qual \
4747
pathman_rowmarks \
4848
pathman_runtime_nodes \
4949
pathman_update_trigger \
50+
pathman_upd_del \
5051
pathman_utility_stmt
5152

5253
EXTRA_REGRESS_OPTS=--temp-config=$(top_srcdir)/$(subdir)/conf.add

‎contrib/pg_pathman/README.md

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -263,7 +263,7 @@ Update RANGE partitioned table interval. Note that interval must not be negative
263263
```plpgsql
264264
set_enable_parent(relation REGCLASS, value BOOLEAN)
265265
```
266-
Include/exclude parent table into/from query plan. In original PostgreSQL planner parent table is always included into query plan even if it's empty which can lead to additional overhead. You can use `disable_parent()` if you are never going to use parent table as a storage. Default value depends on the `partition_data` parameter that was specified during initial partitioning in `create_range_partitions()` or `create_partitions_from_range()` functions. If the `partition_data` parameter was `true` then all data have already been migrated to partitions and parent table disabled. Otherwise it is enabled.
266+
Include/exclude parent table into/from query plan. In original PostgreSQL planner parent table is always included into query plan even if it's empty which can lead to additional overhead. You can use `disable_parent()` if you are never going to use parent table as a storage. Default value depends on the `partition_data` parameter that was specified during initial partitioning in `create_range_partitions()` function. If the `partition_data` parameter was `true` then all data have already been migrated to partitions and parent table disabled. Otherwise it is enabled.
267267

268268
```plpgsql
269269
set_auto(relation REGCLASS, value BOOLEAN)

‎contrib/pg_pathman/expected/pathman_basic.out

Lines changed: 3 additions & 90 deletions
Original file line numberDiff line numberDiff line change
@@ -1653,100 +1653,13 @@ EXPLAIN (COSTS OFF) SELECT * FROM test.range_rel WHERE dt > '2010-12-15';
16531653
-> Seq Scan on range_rel_14
16541654
(4 rows)
16551655

1656-
/* Temporary table for JOINs */
1657-
CREATE TABLE test.tmp (id INTEGER NOT NULL, value INTEGER NOT NULL);
1658-
INSERT INTO test.tmp VALUES (1, 1), (2, 2);
1659-
/* Test UPDATE and DELETE */
1660-
EXPLAIN (COSTS OFF) UPDATE test.range_rel SET value = 111 WHERE dt = '2010-06-15'; /* have partitions for this 'dt' */
1661-
QUERY PLAN
1662-
--------------------------------------------------------------------------------
1663-
Update on range_rel_6
1664-
-> Seq Scan on range_rel_6
1665-
Filter: (dt = 'Tue Jun 15 00:00:00 2010'::timestamp without time zone)
1666-
(3 rows)
1667-
1668-
UPDATE test.range_rel SET value = 111 WHERE dt = '2010-06-15';
1669-
SELECT * FROM test.range_rel WHERE dt = '2010-06-15';
1670-
id | dt | value
1671-
-----+--------------------------+-------
1672-
166 | Tue Jun 15 00:00:00 2010 | 111
1673-
(1 row)
1674-
1675-
EXPLAIN (COSTS OFF) DELETE FROM test.range_rel WHERE dt = '2010-06-15'; /* have partitions for this 'dt' */
1676-
QUERY PLAN
1677-
--------------------------------------------------------------------------------
1678-
Delete on range_rel_6
1679-
-> Seq Scan on range_rel_6
1680-
Filter: (dt = 'Tue Jun 15 00:00:00 2010'::timestamp without time zone)
1681-
(3 rows)
1682-
1683-
DELETE FROM test.range_rel WHERE dt = '2010-06-15';
1684-
SELECT * FROM test.range_rel WHERE dt = '2010-06-15';
1685-
id | dt | value
1686-
----+----+-------
1687-
(0 rows)
1688-
1689-
EXPLAIN (COSTS OFF) UPDATE test.range_rel SET value = 222 WHERE dt = '1990-01-01'; /* no partitions for this 'dt' */
1690-
QUERY PLAN
1691-
--------------------------------------------------------------------------------
1692-
Update on range_rel
1693-
-> Seq Scan on range_rel
1694-
Filter: (dt = 'Mon Jan 01 00:00:00 1990'::timestamp without time zone)
1695-
(3 rows)
1696-
1697-
UPDATE test.range_rel SET value = 111 WHERE dt = '1990-01-01';
1698-
SELECT * FROM test.range_rel WHERE dt = '1990-01-01';
1699-
id | dt | value
1700-
----+----+-------
1701-
(0 rows)
1702-
1703-
EXPLAIN (COSTS OFF) DELETE FROM test.range_rel WHERE dt < '1990-01-01'; /* no partitions for this 'dt' */
1704-
QUERY PLAN
1705-
--------------------------------------------------------------------------------
1706-
Delete on range_rel
1707-
-> Seq Scan on range_rel
1708-
Filter: (dt < 'Mon Jan 01 00:00:00 1990'::timestamp without time zone)
1709-
(3 rows)
1710-
1711-
DELETE FROM test.range_rel WHERE dt < '1990-01-01';
1712-
SELECT * FROM test.range_rel WHERE dt < '1990-01-01';
1713-
id | dt | value
1714-
----+----+-------
1715-
(0 rows)
1716-
1717-
EXPLAIN (COSTS OFF) UPDATE test.range_rel r SET value = t.value FROM test.tmp t WHERE r.dt = '2010-01-01' AND r.id = t.id;
1718-
QUERY PLAN
1719-
--------------------------------------------------------------------------------------------
1720-
Update on range_rel_1 r
1721-
-> Hash Join
1722-
Hash Cond: (t.id = r.id)
1723-
-> Seq Scan on tmp t
1724-
-> Hash
1725-
-> Index Scan using range_rel_1_pkey on range_rel_1 r
1726-
Filter: (dt = 'Fri Jan 01 00:00:00 2010'::timestamp without time zone)
1727-
(7 rows)
1728-
1729-
UPDATE test.range_rel r SET value = t.value FROM test.tmp t WHERE r.dt = '2010-01-01' AND r.id = t.id;
1730-
EXPLAIN (COSTS OFF) DELETE FROM test.range_rel r USING test.tmp t WHERE r.dt = '2010-01-02' AND r.id = t.id;
1731-
QUERY PLAN
1732-
--------------------------------------------------------------------------------------------
1733-
Delete on range_rel_1 r
1734-
-> Hash Join
1735-
Hash Cond: (t.id = r.id)
1736-
-> Seq Scan on tmp t
1737-
-> Hash
1738-
-> Index Scan using range_rel_1_pkey on range_rel_1 r
1739-
Filter: (dt = 'Sat Jan 02 00:00:00 2010'::timestamp without time zone)
1740-
(7 rows)
1741-
1742-
DELETE FROM test.range_rel r USING test.tmp t WHERE r.dt = '2010-01-02' AND r.id = t.id;
17431656
/* Create range partitions from whole range */
17441657
SELECT drop_partitions('test.range_rel');
1745-
NOTICE: 44 rows copied from test.range_rel_1
1658+
NOTICE: 45 rows copied from test.range_rel_1
17461659
NOTICE: 31 rows copied from test.range_rel_3
17471660
NOTICE: 30 rows copied from test.range_rel_4
17481661
NOTICE: 31 rows copied from test.range_rel_5
1749-
NOTICE: 29 rows copied from test.range_rel_6
1662+
NOTICE: 30 rows copied from test.range_rel_6
17501663
NOTICE: 31 rows copied from test.range_rel_7
17511664
NOTICE: 31 rows copied from test.range_rel_8
17521665
NOTICE: 30 rows copied from test.range_rel_9
@@ -1939,6 +1852,6 @@ ORDER BY partition;
19391852
DROP TABLE test.provided_part_names CASCADE;
19401853
NOTICE: drop cascades to 2 other objects
19411854
DROP SCHEMA test CASCADE;
1942-
NOTICE: drop cascades to 29 other objects
1855+
NOTICE: drop cascades to 28 other objects
19431856
DROP EXTENSION pg_pathman CASCADE;
19441857
DROP SCHEMA pathman CASCADE;

‎contrib/pg_pathman/expected/pathman_calamity.out

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -12,7 +12,7 @@ SELECT debug_capture();
1212
SELECT get_pathman_lib_version();
1313
get_pathman_lib_version
1414
-------------------------
15-
10402
15+
10403
1616
(1 row)
1717

1818
set client_min_messages = NOTICE;

‎contrib/pg_pathman/expected/pathman_inserts.out

Lines changed: 165 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -853,6 +853,171 @@ NOTICE: AFTER INSERTION TRIGGER ON TABLE storage_14 HAS EXPIRED. INSERTED ROW:
853853
256 | 128 | test_inserts.storage_14
854854
(27 rows)
855855

856+
/* test EXPLAIN (VERBOSE) - for PartitionFilter's targetlists */
857+
EXPLAIN (VERBOSE, COSTS OFF)
858+
INSERT INTO test_inserts.storage (b, d, e) SELECT i, i, i
859+
FROM generate_series(1, 10) i
860+
RETURNING e * 2, b, tableoid::regclass;
861+
QUERY PLAN
862+
-------------------------------------------------------------------------------
863+
Insert on test_inserts.storage
864+
Output: (storage.e * 2), storage.b, (storage.tableoid)::regclass
865+
-> Custom Scan (PartitionFilter)
866+
Output: NULL::integer, storage.b, NULL::integer, storage.d, storage.e
867+
-> Function Scan on pg_catalog.generate_series i
868+
Output: NULL::integer, i.i, NULL::integer, i.i, i.i
869+
Function Call: generate_series(1, 10)
870+
(7 rows)
871+
872+
EXPLAIN (VERBOSE, COSTS OFF)
873+
INSERT INTO test_inserts.storage (d, e) SELECT i, i
874+
FROM generate_series(1, 10) i;
875+
QUERY PLAN
876+
-------------------------------------------------------------------------------
877+
Insert on test_inserts.storage
878+
-> Custom Scan (PartitionFilter)
879+
Output: NULL::integer, storage.b, NULL::integer, storage.d, storage.e
880+
-> Function Scan on pg_catalog.generate_series i
881+
Output: NULL::integer, NULL::integer, NULL::integer, i.i, i.i
882+
Function Call: generate_series(1, 10)
883+
(6 rows)
884+
885+
EXPLAIN (VERBOSE, COSTS OFF)
886+
INSERT INTO test_inserts.storage (b) SELECT i
887+
FROM generate_series(1, 10) i;
888+
QUERY PLAN
889+
-----------------------------------------------------------------------------------
890+
Insert on test_inserts.storage
891+
-> Custom Scan (PartitionFilter)
892+
Output: NULL::integer, storage.b, NULL::integer, storage.d, storage.e
893+
-> Function Scan on pg_catalog.generate_series i
894+
Output: NULL::integer, i.i, NULL::integer, NULL::text, NULL::bigint
895+
Function Call: generate_series(1, 10)
896+
(6 rows)
897+
898+
EXPLAIN (VERBOSE, COSTS OFF)
899+
INSERT INTO test_inserts.storage (b, d, e) SELECT b, d, e
900+
FROM test_inserts.storage;
901+
QUERY PLAN
902+
----------------------------------------------------------------------------------------------
903+
Insert on test_inserts.storage
904+
-> Custom Scan (PartitionFilter)
905+
Output: NULL::integer, storage.b, NULL::integer, storage.d, storage.e
906+
-> Result
907+
Output: NULL::integer, storage_11.b, NULL::integer, storage_11.d, storage_11.e
908+
-> Append
909+
-> Seq Scan on test_inserts.storage_11
910+
Output: storage_11.b, storage_11.d, storage_11.e
911+
-> Seq Scan on test_inserts.storage_1
912+
Output: storage_1.b, storage_1.d, storage_1.e
913+
-> Seq Scan on test_inserts.storage_2
914+
Output: storage_2.b, storage_2.d, storage_2.e
915+
-> Seq Scan on test_inserts.storage_3
916+
Output: storage_3.b, storage_3.d, storage_3.e
917+
-> Seq Scan on test_inserts.storage_4
918+
Output: storage_4.b, storage_4.d, storage_4.e
919+
-> Seq Scan on test_inserts.storage_5
920+
Output: storage_5.b, storage_5.d, storage_5.e
921+
-> Seq Scan on test_inserts.storage_6
922+
Output: storage_6.b, storage_6.d, storage_6.e
923+
-> Seq Scan on test_inserts.storage_7
924+
Output: storage_7.b, storage_7.d, storage_7.e
925+
-> Seq Scan on test_inserts.storage_8
926+
Output: storage_8.b, storage_8.d, storage_8.e
927+
-> Seq Scan on test_inserts.storage_9
928+
Output: storage_9.b, storage_9.d, storage_9.e
929+
-> Seq Scan on test_inserts.storage_10
930+
Output: storage_10.b, storage_10.d, storage_10.e
931+
-> Seq Scan on test_inserts.storage_12
932+
Output: storage_12.b, storage_12.d, storage_12.e
933+
-> Seq Scan on test_inserts.storage_13
934+
Output: storage_13.b, storage_13.d, storage_13.e
935+
-> Seq Scan on test_inserts.storage_14
936+
Output: storage_14.b, storage_14.d, storage_14.e
937+
(34 rows)
938+
939+
EXPLAIN (VERBOSE, COSTS OFF)
940+
INSERT INTO test_inserts.storage (b, d) SELECT b, d
941+
FROM test_inserts.storage;
942+
QUERY PLAN
943+
----------------------------------------------------------------------------------------------
944+
Insert on test_inserts.storage
945+
-> Custom Scan (PartitionFilter)
946+
Output: NULL::integer, storage.b, NULL::integer, storage.d, storage.e
947+
-> Result
948+
Output: NULL::integer, storage_11.b, NULL::integer, storage_11.d, NULL::bigint
949+
-> Append
950+
-> Seq Scan on test_inserts.storage_11
951+
Output: storage_11.b, storage_11.d
952+
-> Seq Scan on test_inserts.storage_1
953+
Output: storage_1.b, storage_1.d
954+
-> Seq Scan on test_inserts.storage_2
955+
Output: storage_2.b, storage_2.d
956+
-> Seq Scan on test_inserts.storage_3
957+
Output: storage_3.b, storage_3.d
958+
-> Seq Scan on test_inserts.storage_4
959+
Output: storage_4.b, storage_4.d
960+
-> Seq Scan on test_inserts.storage_5
961+
Output: storage_5.b, storage_5.d
962+
-> Seq Scan on test_inserts.storage_6
963+
Output: storage_6.b, storage_6.d
964+
-> Seq Scan on test_inserts.storage_7
965+
Output: storage_7.b, storage_7.d
966+
-> Seq Scan on test_inserts.storage_8
967+
Output: storage_8.b, storage_8.d
968+
-> Seq Scan on test_inserts.storage_9
969+
Output: storage_9.b, storage_9.d
970+
-> Seq Scan on test_inserts.storage_10
971+
Output: storage_10.b, storage_10.d
972+
-> Seq Scan on test_inserts.storage_12
973+
Output: storage_12.b, storage_12.d
974+
-> Seq Scan on test_inserts.storage_13
975+
Output: storage_13.b, storage_13.d
976+
-> Seq Scan on test_inserts.storage_14
977+
Output: storage_14.b, storage_14.d
978+
(34 rows)
979+
980+
EXPLAIN (VERBOSE, COSTS OFF)
981+
INSERT INTO test_inserts.storage (b) SELECT b
982+
FROM test_inserts.storage;
983+
QUERY PLAN
984+
--------------------------------------------------------------------------------------------
985+
Insert on test_inserts.storage
986+
-> Custom Scan (PartitionFilter)
987+
Output: NULL::integer, storage.b, NULL::integer, storage.d, storage.e
988+
-> Result
989+
Output: NULL::integer, storage_11.b, NULL::integer, NULL::text, NULL::bigint
990+
-> Append
991+
-> Seq Scan on test_inserts.storage_11
992+
Output: storage_11.b
993+
-> Seq Scan on test_inserts.storage_1
994+
Output: storage_1.b
995+
-> Seq Scan on test_inserts.storage_2
996+
Output: storage_2.b
997+
-> Seq Scan on test_inserts.storage_3
998+
Output: storage_3.b
999+
-> Seq Scan on test_inserts.storage_4
1000+
Output: storage_4.b
1001+
-> Seq Scan on test_inserts.storage_5
1002+
Output: storage_5.b
1003+
-> Seq Scan on test_inserts.storage_6
1004+
Output: storage_6.b
1005+
-> Seq Scan on test_inserts.storage_7
1006+
Output: storage_7.b
1007+
-> Seq Scan on test_inserts.storage_8
1008+
Output: storage_8.b
1009+
-> Seq Scan on test_inserts.storage_9
1010+
Output: storage_9.b
1011+
-> Seq Scan on test_inserts.storage_10
1012+
Output: storage_10.b
1013+
-> Seq Scan on test_inserts.storage_12
1014+
Output: storage_12.b
1015+
-> Seq Scan on test_inserts.storage_13
1016+
Output: storage_13.b
1017+
-> Seq Scan on test_inserts.storage_14
1018+
Output: storage_14.b
1019+
(34 rows)
1020+
8561021
/* test gap case (missing partition in between) */
8571022
CREATE TABLE test_inserts.test_gap(val INT NOT NULL);
8581023
INSERT INTO test_inserts.test_gap SELECT generate_series(1, 30);

0 commit comments

Comments
 (0)