SET search_path TO upgrade_basic, public, pg_catalog; BEGIN; -- We have the tablename filter to avoid adding an alternative output for when the coordinator is in metadata vs when not SELECT * FROM pg_indexes WHERE schemaname = 'upgrade_basic' and tablename NOT LIKE 'r_%' ORDER BY tablename; schemaname | tablename | indexname | tablespace | indexdef --------------------------------------------------------------------- upgrade_basic | r | r_pkey | | CREATE UNIQUE INDEX r_pkey ON upgrade_basic.r USING btree (a) upgrade_basic | t | t_a_idx | | CREATE INDEX t_a_idx ON upgrade_basic.t USING hash (a) upgrade_basic | tp | tp_pkey | | CREATE UNIQUE INDEX tp_pkey ON upgrade_basic.tp USING btree (a) (3 rows) SELECT nextval('pg_dist_shardid_seq') > MAX(shardid) FROM pg_dist_shard; ?column? --------------------------------------------------------------------- t (1 row) SELECT nextval('pg_dist_placement_placementid_seq') > MAX(placementid) FROM pg_dist_placement; ?column? --------------------------------------------------------------------- t (1 row) SELECT nextval('pg_dist_groupid_seq') > MAX(groupid) FROM pg_dist_node; ?column? --------------------------------------------------------------------- t (1 row) SELECT nextval('pg_dist_node_nodeid_seq') > MAX(nodeid) FROM pg_dist_node; ?column? --------------------------------------------------------------------- t (1 row) SELECT nextval('pg_dist_colocationid_seq') > MAX(colocationid) FROM pg_dist_colocation; ?column? --------------------------------------------------------------------- t (1 row) -- while testing sequences on pg_dist_cleanup, they return null in pg upgrade schedule -- but return a valid value in citus upgrade schedule -- that's why we accept both NULL and MAX()+1 here SELECT CASE WHEN MAX(operation_id) IS NULL THEN true ELSE nextval('pg_dist_operationid_seq') > MAX(operation_id) END AS check_operationid FROM pg_dist_cleanup; check_operationid --------------------------------------------------------------------- t (1 row) SELECT CASE WHEN MAX(record_id) IS NULL THEN true ELSE nextval('pg_dist_cleanup_recordid_seq') > MAX(record_id) END AS check_recordid FROM pg_dist_cleanup; check_recordid --------------------------------------------------------------------- t (1 row) SELECT nextval('pg_dist_background_job_job_id_seq') > COALESCE(MAX(job_id), 0) FROM pg_dist_background_job; ?column? --------------------------------------------------------------------- t (1 row) SELECT nextval('pg_dist_background_task_task_id_seq') > COALESCE(MAX(task_id), 0) FROM pg_dist_background_task; ?column? --------------------------------------------------------------------- t (1 row) SELECT last_value > 0 FROM pg_dist_clock_logical_seq; ?column? --------------------------------------------------------------------- t (1 row) -- If this query gives output it means we've added a new sequence that should -- possibly be restored after upgrades. SELECT sequence_name FROM information_schema.sequences WHERE sequence_name LIKE 'pg_dist_%' AND sequence_name NOT IN ( -- these ones are restored above 'pg_dist_shardid_seq', 'pg_dist_placement_placementid_seq', 'pg_dist_groupid_seq', 'pg_dist_node_nodeid_seq', 'pg_dist_colocationid_seq', 'pg_dist_operationid_seq', 'pg_dist_cleanup_recordid_seq', 'pg_dist_background_job_job_id_seq', 'pg_dist_background_task_task_id_seq', 'pg_dist_clock_logical_seq' ); sequence_name --------------------------------------------------------------------- (0 rows) SELECT logicalrelid FROM pg_dist_partition JOIN pg_depend ON logicalrelid=objid JOIN pg_catalog.pg_class ON logicalrelid=oid WHERE refobjid=(select oid FROM pg_extension WHERE extname = 'citus') AND relnamespace='upgrade_basic'::regnamespace ORDER BY logicalrelid; logicalrelid --------------------------------------------------------------------- t tp t_ab r tr t_range (6 rows) SELECT tgrelid::regclass, tgfoid::regproc, tgisinternal, tgenabled, tgtype::int4::bit(8) FROM pg_dist_partition JOIN pg_trigger ON tgrelid=logicalrelid JOIN pg_class ON pg_class.oid=logicalrelid WHERE relnamespace='upgrade_basic'::regnamespace AND tgname LIKE 'truncate_trigger_%' ORDER BY tgrelid::regclass; tgrelid | tgfoid | tgisinternal | tgenabled | tgtype --------------------------------------------------------------------- t | citus_truncate_trigger | t | O | 00100000 tp | citus_truncate_trigger | t | O | 00100000 t_ab | citus_truncate_trigger | t | O | 00100000 r | citus_truncate_trigger | t | O | 00100000 tr | citus_truncate_trigger | t | O | 00100000 t_range | citus_truncate_trigger | t | O | 00100000 (6 rows) SELECT * FROM t ORDER BY a; a --------------------------------------------------------------------- 1 2 3 4 5 (5 rows) SELECT * FROM t WHERE a = 1; a --------------------------------------------------------------------- 1 (1 row) INSERT INTO t SELECT * FROM generate_series(10, 15); EXPLAIN (COSTS FALSE) SELECT * from t; QUERY PLAN --------------------------------------------------------------------- Custom Scan (Citus Adaptive) Task Count: 32 Tasks Shown: One of 32 -> Task Node: host=localhost port=xxxxx dbname=postgres -> Seq Scan on t_102008 t (6 rows) EXPLAIN (COSTS FALSE) SELECT * from t WHERE a = 1; QUERY PLAN --------------------------------------------------------------------- Custom Scan (Citus Adaptive) Task Count: 1 Tasks Shown: All -> Task Node: host=localhost port=xxxxx dbname=postgres -> Bitmap Heap Scan on t_102009 t Recheck Cond: (a = 1) -> Bitmap Index Scan on t_a_idx_102009 Index Cond: (a = 1) (9 rows) SELECT * FROM t WHERE a = 10; a --------------------------------------------------------------------- 10 (1 row) SELECT * FROM t WHERE a = 11; a --------------------------------------------------------------------- 11 (1 row) COPY t FROM PROGRAM 'echo 20 && echo 21 && echo 22 && echo 23 && echo 24' WITH CSV; ALTER TABLE t ADD COLUMN b int DEFAULT 10; SELECT * FROM t ORDER BY a; a | b --------------------------------------------------------------------- 1 | 10 2 | 10 3 | 10 4 | 10 5 | 10 10 | 10 11 | 10 12 | 10 13 | 10 14 | 10 15 | 10 20 | 10 21 | 10 22 | 10 23 | 10 24 | 10 (16 rows) TRUNCATE TABLE t; SELECT * FROM T; a | b --------------------------------------------------------------------- (0 rows) DROP TABLE t; \d t -- verify that the table whose column is dropped before a pg_upgrade still works as expected. SELECT * FROM t_ab ORDER BY b; b --------------------------------------------------------------------- 11 22 33 (3 rows) SELECT * FROM t_ab WHERE b = 11; b --------------------------------------------------------------------- 11 (1 row) SELECT * FROM t_ab WHERE b = 22; b --------------------------------------------------------------------- 22 (1 row) -- Check that we can create a distributed table out of a table that was created -- before the upgrade SELECT * FROM t2 ORDER BY a; a | b --------------------------------------------------------------------- 1 | 11 2 | 22 3 | 33 (3 rows) SELECT create_distributed_table('t2', 'a'); NOTICE: Copying data from local table... NOTICE: copying the data has completed DETAIL: The local data in the table is no longer visible, but is still on disk. HINT: To remove the local data, run: SELECT truncate_local_data_after_distributing_table($$upgrade_basic.t2$$) create_distributed_table --------------------------------------------------------------------- (1 row) SELECT * FROM t2 ORDER BY a; a | b --------------------------------------------------------------------- 1 | 11 2 | 22 3 | 33 (3 rows) ROLLBACK; BEGIN; SET LOCAL citus.multi_shard_modify_mode TO 'sequential'; SELECT * FROM r ORDER BY a; a --------------------------------------------------------------------- 1 2 3 4 5 (5 rows) SELECT * FROM tr ORDER BY pk; pk | a --------------------------------------------------------------------- 1 | 1 2 | 2 3 | 3 4 | 4 5 | 5 (5 rows) DELETE FROM r where a = 1; SELECT * FROM r ORDER BY a; a --------------------------------------------------------------------- 2 3 4 5 (4 rows) SELECT * FROM tr ORDER BY pk; pk | a --------------------------------------------------------------------- 2 | 2 3 | 3 4 | 4 5 | 5 (4 rows) UPDATE r SET a = 30 WHERE a = 3; SELECT * FROM r ORDER BY a; a --------------------------------------------------------------------- 2 4 5 30 (4 rows) SELECT * FROM tr ORDER BY pk; pk | a --------------------------------------------------------------------- 2 | 2 3 | 30 4 | 4 5 | 5 (4 rows) -- Check we can still create distributed tables after upgrade CREATE TABLE t3(a int, b int); SELECT create_distributed_table('t3', 'a'); create_distributed_table --------------------------------------------------------------------- (1 row) INSERT INTO t3 VALUES (1, 11); INSERT INTO t3 VALUES (2, 22); INSERT INTO t3 VALUES (3, 33); SELECT * FROM t3 ORDER BY a; a | b --------------------------------------------------------------------- 1 | 11 2 | 22 3 | 33 (3 rows) SELECT shardminvalue, shardmaxvalue FROM pg_dist_shard WHERE logicalrelid = 't_range'::regclass ORDER BY shardminvalue, shardmaxvalue; shardminvalue | shardmaxvalue --------------------------------------------------------------------- 1 | 3 5 | 7 (2 rows) SELECT * FROM t_range ORDER BY id; id | value_1 --------------------------------------------------------------------- 1 | 2 2 | 3 3 | 4 5 | 2 6 | 3 7 | 4 (6 rows) SELECT master_create_empty_shard('t_range') AS new_shard_id \gset UPDATE pg_dist_shard SET shardminvalue = '9', shardmaxvalue = '11' WHERE shardid = :new_shard_id; \copy t_range FROM STDIN with (DELIMITER ',') SELECT shardminvalue, shardmaxvalue FROM pg_dist_shard WHERE logicalrelid = 't_range'::regclass ORDER BY shardminvalue, shardmaxvalue; shardminvalue | shardmaxvalue --------------------------------------------------------------------- 1 | 3 5 | 7 9 | 11 (3 rows) SELECT * FROM t_range ORDER BY id; id | value_1 --------------------------------------------------------------------- 1 | 2 2 | 3 3 | 4 5 | 2 6 | 3 7 | 4 9 | 2 10 | 3 11 | 4 (9 rows) ROLLBACK; -- There is a difference in partkey Var representation between PG16 and older versions -- Sanity check here that we can properly do column_to_column_name SELECT column_to_column_name(logicalrelid, partkey) FROM pg_dist_partition WHERE partkey IS NOT NULL ORDER BY 1 LIMIT 1; column_to_column_name --------------------------------------------------------------------- a (1 row)