\set VERBOSITY terse SET search_path = 'public'; CREATE EXTENSION pg_pathman; CREATE SCHEMA test_update_node; SET pg_pathman.enable_partitionrouter = ON; /* Partition table by RANGE (NUMERIC) */ CREATE TABLE test_update_node.test_range(val NUMERIC NOT NULL, comment TEXT); CREATE INDEX val_idx ON test_update_node.test_range (val); INSERT INTO test_update_node.test_range SELECT i, i FROM generate_series(1, 100) i; SELECT create_range_partitions('test_update_node.test_range', 'val', 1, 10); create_range_partitions ------------------------- 10 (1 row) /* Moving from 2st to 1st partition */ EXPLAIN (COSTS OFF) UPDATE test_update_node.test_range SET val = 5 WHERE val = 15; QUERY PLAN ------------------------------------------------------------------------- Custom Scan (PartitionOverseer) -> Update on test_range_2 -> Custom Scan (PartitionFilter) -> Custom Scan (PartitionRouter) -> Bitmap Heap Scan on test_range_2 Recheck Cond: (val = '15'::numeric) -> Bitmap Index Scan on test_range_2_val_idx Index Cond: (val = '15'::numeric) (8 rows) /* Keep same partition */ EXPLAIN (COSTS OFF) UPDATE test_update_node.test_range SET val = 14 WHERE val = 15; QUERY PLAN ------------------------------------------------------------------------- Custom Scan (PartitionOverseer) -> Update on test_range_2 -> Custom Scan (PartitionFilter) -> Custom Scan (PartitionRouter) -> Bitmap Heap Scan on test_range_2 Recheck Cond: (val = '15'::numeric) -> Bitmap Index Scan on test_range_2_val_idx Index Cond: (val = '15'::numeric) (8 rows) /* Update values in 1st partition (rows remain there) */ UPDATE test_update_node.test_range SET val = 5 WHERE val <= 10; /* Check values #1 */ SELECT tableoid::REGCLASS, * FROM test_update_node.test_range WHERE val < 10 ORDER BY comment; tableoid | val | comment -------------------------------+-----+--------- test_update_node.test_range_1 | 5 | 1 test_update_node.test_range_1 | 5 | 10 test_update_node.test_range_1 | 5 | 2 test_update_node.test_range_1 | 5 | 3 test_update_node.test_range_1 | 5 | 4 test_update_node.test_range_1 | 5 | 5 test_update_node.test_range_1 | 5 | 6 test_update_node.test_range_1 | 5 | 7 test_update_node.test_range_1 | 5 | 8 test_update_node.test_range_1 | 5 | 9 (10 rows) SELECT count(*) FROM test_update_node.test_range; count ------- 100 (1 row) /* Update values in 2nd partition (rows move to 3rd partition) */ UPDATE test_update_node.test_range SET val = val + 10 WHERE val > 10 AND val <= 20; /* Check values #2 */ SELECT tableoid::REGCLASS, * FROM test_update_node.test_range WHERE val > 20 AND val <= 30 ORDER BY comment; tableoid | val | comment -------------------------------+-----+--------- test_update_node.test_range_3 | 21 | 11 test_update_node.test_range_3 | 22 | 12 test_update_node.test_range_3 | 23 | 13 test_update_node.test_range_3 | 24 | 14 test_update_node.test_range_3 | 25 | 15 test_update_node.test_range_3 | 26 | 16 test_update_node.test_range_3 | 27 | 17 test_update_node.test_range_3 | 28 | 18 test_update_node.test_range_3 | 29 | 19 test_update_node.test_range_3 | 30 | 20 test_update_node.test_range_3 | 21 | 21 test_update_node.test_range_3 | 22 | 22 test_update_node.test_range_3 | 23 | 23 test_update_node.test_range_3 | 24 | 24 test_update_node.test_range_3 | 25 | 25 test_update_node.test_range_3 | 26 | 26 test_update_node.test_range_3 | 27 | 27 test_update_node.test_range_3 | 28 | 28 test_update_node.test_range_3 | 29 | 29 test_update_node.test_range_3 | 30 | 30 (20 rows) SELECT count(*) FROM test_update_node.test_range; count ------- 100 (1 row) /* Move single row */ UPDATE test_update_node.test_range SET val = 90 WHERE val = 80; /* Check values #3 */ SELECT tableoid::REGCLASS, * FROM test_update_node.test_range WHERE val = 90 ORDER BY comment; tableoid | val | comment -------------------------------+-----+--------- test_update_node.test_range_9 | 90 | 80 test_update_node.test_range_9 | 90 | 90 (2 rows) SELECT count(*) FROM test_update_node.test_range; count ------- 100 (1 row) /* Move single row (create new partition) */ UPDATE test_update_node.test_range SET val = -1 WHERE val = 50; /* Check values #4 */ SELECT tableoid::REGCLASS, * FROM test_update_node.test_range WHERE val = -1 ORDER BY comment; tableoid | val | comment --------------------------------+-----+--------- test_update_node.test_range_11 | -1 | 50 (1 row) SELECT count(*) FROM test_update_node.test_range; count ------- 100 (1 row) /* Update non-key column */ UPDATE test_update_node.test_range SET comment = 'test!' WHERE val = 100; /* Check values #5 */ SELECT tableoid::REGCLASS, * FROM test_update_node.test_range WHERE val = 100 ORDER BY comment; tableoid | val | comment --------------------------------+-----+--------- test_update_node.test_range_10 | 100 | test! (1 row) SELECT count(*) FROM test_update_node.test_range; count ------- 100 (1 row) /* Try moving row into a gap (ERROR) */ DROP TABLE test_update_node.test_range_4; UPDATE test_update_node.test_range SET val = 35 WHERE val = 70; ERROR: cannot spawn a partition /* Check values #6 */ SELECT tableoid::REGCLASS, * FROM test_update_node.test_range WHERE val = 70 ORDER BY comment; tableoid | val | comment -------------------------------+-----+--------- test_update_node.test_range_7 | 70 | 70 (1 row) SELECT count(*) FROM test_update_node.test_range; count ------- 90 (1 row) /* Test trivial move (same key) */ UPDATE test_update_node.test_range SET val = 65 WHERE val = 65; /* Check values #7 */ SELECT tableoid::REGCLASS, * FROM test_update_node.test_range WHERE val = 65 ORDER BY comment; tableoid | val | comment -------------------------------+-----+--------- test_update_node.test_range_7 | 65 | 65 (1 row) SELECT count(*) FROM test_update_node.test_range; count ------- 90 (1 row) /* Test tuple conversion (attached partition) */ CREATE TABLE test_update_node.test_range_inv(comment TEXT, val NUMERIC NOT NULL); SELECT attach_range_partition('test_update_node.test_range', 'test_update_node.test_range_inv', 101::NUMERIC, 111::NUMERIC); attach_range_partition --------------------------------- test_update_node.test_range_inv (1 row) UPDATE test_update_node.test_range SET val = 105 WHERE val = 60; UPDATE test_update_node.test_range SET val = 105 WHERE val = 105; /* Check values #8 */ SELECT tableoid::REGCLASS, * FROM test_update_node.test_range WHERE val = 105 ORDER BY comment; tableoid | val | comment ---------------------------------+-----+--------- test_update_node.test_range_inv | 105 | 60 (1 row) UPDATE test_update_node.test_range SET val = 60 WHERE val = 105; SELECT count(*) FROM test_update_node.test_range; count ------- 90 (1 row) /* Test RETURNING */ UPDATE test_update_node.test_range SET val = 71 WHERE val = 41 RETURNING val, comment; val | comment -----+--------- 71 | 41 (1 row) UPDATE test_update_node.test_range SET val = 71 WHERE val = 71 RETURNING val, comment; val | comment -----+--------- 71 | 71 71 | 41 (2 rows) UPDATE test_update_node.test_range SET val = 106 WHERE val = 61 RETURNING val, comment; val | comment -----+--------- 106 | 61 (1 row) UPDATE test_update_node.test_range SET val = 106 WHERE val = 106 RETURNING val, comment; val | comment -----+--------- 106 | 61 (1 row) UPDATE test_update_node.test_range SET val = 61 WHERE val = 106 RETURNING val, comment; val | comment -----+--------- 61 | 61 (1 row) /* Just in case, check we don't duplicate anything */ SELECT count(*) FROM test_update_node.test_range; count ------- 90 (1 row) /* Test tuple conversion (dropped column) */ ALTER TABLE test_update_node.test_range DROP COLUMN comment CASCADE; SELECT append_range_partition('test_update_node.test_range'); append_range_partition -------------------------------- test_update_node.test_range_12 (1 row) UPDATE test_update_node.test_range SET val = 115 WHERE val = 55; UPDATE test_update_node.test_range SET val = 115 WHERE val = 115; /* Check values #9 */ SELECT tableoid::REGCLASS, * FROM test_update_node.test_range WHERE val = 115; tableoid | val --------------------------------+----- test_update_node.test_range_12 | 115 (1 row) UPDATE test_update_node.test_range SET val = 55 WHERE val = 115; SELECT count(*) FROM test_update_node.test_range; count ------- 90 (1 row) DROP TABLE test_update_node.test_range CASCADE; NOTICE: drop cascades to 13 other objects /* recreate table and mass move */ CREATE TABLE test_update_node.test_range(val NUMERIC NOT NULL, comment TEXT); INSERT INTO test_update_node.test_range SELECT i, i FROM generate_series(1, 100) i; SELECT create_range_partitions('test_update_node.test_range', 'val', 1, 10); create_range_partitions ------------------------- 10 (1 row) SELECT tableoid::regclass, MIN(val) FROM test_update_node.test_range GROUP BY tableoid::regclass ORDER BY tableoid::regclass; tableoid | min --------------------------------+----- test_update_node.test_range_1 | 1 test_update_node.test_range_2 | 11 test_update_node.test_range_3 | 21 test_update_node.test_range_4 | 31 test_update_node.test_range_5 | 41 test_update_node.test_range_6 | 51 test_update_node.test_range_7 | 61 test_update_node.test_range_8 | 71 test_update_node.test_range_9 | 81 test_update_node.test_range_10 | 91 (10 rows) SELECT count(*) FROM test_update_node.test_range; count ------- 100 (1 row) /* move everything to next partition */ UPDATE test_update_node.test_range SET val = val + 10; SELECT tableoid::regclass, MIN(val) FROM test_update_node.test_range GROUP BY tableoid::regclass ORDER BY tableoid::regclass; tableoid | min --------------------------------+----- test_update_node.test_range_2 | 11 test_update_node.test_range_3 | 21 test_update_node.test_range_4 | 31 test_update_node.test_range_5 | 41 test_update_node.test_range_6 | 51 test_update_node.test_range_7 | 61 test_update_node.test_range_8 | 71 test_update_node.test_range_9 | 81 test_update_node.test_range_10 | 91 test_update_node.test_range_11 | 101 (10 rows) /* move everything to previous partition */ UPDATE test_update_node.test_range SET val = val - 10; SELECT tableoid::regclass, MIN(val) FROM test_update_node.test_range GROUP BY tableoid::regclass ORDER BY tableoid::regclass; tableoid | min --------------------------------+----- test_update_node.test_range_1 | 1 test_update_node.test_range_2 | 11 test_update_node.test_range_3 | 21 test_update_node.test_range_4 | 31 test_update_node.test_range_5 | 41 test_update_node.test_range_6 | 51 test_update_node.test_range_7 | 61 test_update_node.test_range_8 | 71 test_update_node.test_range_9 | 81 test_update_node.test_range_10 | 91 (10 rows) SELECT count(*) FROM test_update_node.test_range; count ------- 100 (1 row) /* Partition table by HASH (INT4) */ CREATE TABLE test_update_node.test_hash(val INT4 NOT NULL, comment TEXT); INSERT INTO test_update_node.test_hash SELECT i, i FROM generate_series(1, 10) i; SELECT create_hash_partitions('test_update_node.test_hash', 'val', 3); create_hash_partitions ------------------------ 3 (1 row) /* Shuffle rows a few times */ UPDATE test_update_node.test_hash SET val = val + 1; UPDATE test_update_node.test_hash SET val = val + 1; UPDATE test_update_node.test_hash SET val = val + 1; UPDATE test_update_node.test_hash SET val = val + 1; UPDATE test_update_node.test_hash SET val = val + 1; UPDATE test_update_node.test_hash SET val = val + 1; UPDATE test_update_node.test_hash SET val = val + 1; UPDATE test_update_node.test_hash SET val = val + 1; UPDATE test_update_node.test_hash SET val = val + 1; /* Check values #0 */ SELECT tableoid::regclass, * FROM test_update_node.test_hash ORDER BY val; tableoid | val | comment ------------------------------+-----+--------- test_update_node.test_hash_2 | 10 | 1 test_update_node.test_hash_1 | 11 | 2 test_update_node.test_hash_1 | 12 | 3 test_update_node.test_hash_2 | 13 | 4 test_update_node.test_hash_1 | 14 | 5 test_update_node.test_hash_1 | 15 | 6 test_update_node.test_hash_2 | 16 | 7 test_update_node.test_hash_0 | 17 | 8 test_update_node.test_hash_1 | 18 | 9 test_update_node.test_hash_0 | 19 | 10 (10 rows) /* Move all rows into single partition */ UPDATE test_update_node.test_hash SET val = 1; /* Check values #1 */ SELECT tableoid::REGCLASS, * FROM test_update_node.test_hash WHERE val = 1 ORDER BY comment; tableoid | val | comment ------------------------------+-----+--------- test_update_node.test_hash_2 | 1 | 1 test_update_node.test_hash_2 | 1 | 10 test_update_node.test_hash_2 | 1 | 2 test_update_node.test_hash_2 | 1 | 3 test_update_node.test_hash_2 | 1 | 4 test_update_node.test_hash_2 | 1 | 5 test_update_node.test_hash_2 | 1 | 6 test_update_node.test_hash_2 | 1 | 7 test_update_node.test_hash_2 | 1 | 8 test_update_node.test_hash_2 | 1 | 9 (10 rows) SELECT count(*) FROM test_update_node.test_hash; count ------- 10 (1 row) /* Don't move any rows */ UPDATE test_update_node.test_hash SET val = 3 WHERE val = 2; /* Check values #2 */ SELECT tableoid::REGCLASS, * FROM test_update_node.test_hash WHERE val = 3 ORDER BY comment; tableoid | val | comment ----------+-----+--------- (0 rows) SELECT count(*) FROM test_update_node.test_hash; count ------- 10 (1 row) DROP SCHEMA test_update_node CASCADE; NOTICE: drop cascades to 17 other objects DROP EXTENSION pg_pathman;