Skip to content

Navigation Menu

Sign in

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly
Appearance settings

Commit d53a428

Browse filesBrowse files
akorotkovakorotkov
committed
Provide deterministic order for catalog queries in partition_split.sql
System catalog tables are subject to modification by parallel tests. This is the source of instability when querying them without explicit ORDER BY. This commit adds explicit ORDER BY to system catalog queries in partition_split.sql to stabilize the result. Reported-by: Tom Lane Discussion: https://postgr.es/m/695264.1716578979%40sss.pgh.pa.us
1 parent 87331c6 commit d53a428
Copy full SHA for d53a428

File tree

2 files changed

+34
-34
lines changed
Filter options

2 files changed

+34
-34
lines changed

‎src/test/regress/expected/partition_split.out

Copy file name to clipboardExpand all lines: src/test/regress/expected/partition_split.out
+17-17Lines changed: 17 additions & 17 deletions
Original file line numberDiff line numberDiff line change
@@ -469,7 +469,7 @@ SELECT * FROM sales_others;
469469
14 | Smith | 510 | 05-04-2022
470470
(11 rows)
471471

472-
SELECT * FROM pg_indexes WHERE tablename = 'sales_others' and schemaname = 'partition_split_schema';
472+
SELECT * FROM pg_indexes WHERE tablename = 'sales_others' and schemaname = 'partition_split_schema' ORDER BY indexname;
473473
schemaname | tablename | indexname | tablespace | indexdef
474474
------------------------+--------------+-----------------------------+------------+----------------------------------------------------------------------------------------------------------
475475
partition_split_schema | sales_others | sales_others_sales_date_idx | | CREATE INDEX sales_others_sales_date_idx ON partition_split_schema.sales_others USING btree (sales_date)
@@ -516,25 +516,25 @@ SELECT * FROM sales_others where sales_date > '2022-01-01';
516516

517517
SET enable_indexscan = ON;
518518
SET enable_seqscan = ON;
519-
SELECT * FROM pg_indexes WHERE tablename = 'sales_feb2022' and schemaname = 'partition_split_schema';
519+
SELECT * FROM pg_indexes WHERE tablename = 'sales_feb2022' and schemaname = 'partition_split_schema' ORDER BY indexname;
520520
schemaname | tablename | indexname | tablespace | indexdef
521521
------------------------+---------------+------------------------------+------------+------------------------------------------------------------------------------------------------------------
522522
partition_split_schema | sales_feb2022 | sales_feb2022_sales_date_idx | | CREATE INDEX sales_feb2022_sales_date_idx ON partition_split_schema.sales_feb2022 USING btree (sales_date)
523523
(1 row)
524524

525-
SELECT * FROM pg_indexes WHERE tablename = 'sales_mar2022' and schemaname = 'partition_split_schema';
525+
SELECT * FROM pg_indexes WHERE tablename = 'sales_mar2022' and schemaname = 'partition_split_schema' ORDER BY indexname;
526526
schemaname | tablename | indexname | tablespace | indexdef
527527
------------------------+---------------+------------------------------+------------+------------------------------------------------------------------------------------------------------------
528528
partition_split_schema | sales_mar2022 | sales_mar2022_sales_date_idx | | CREATE INDEX sales_mar2022_sales_date_idx ON partition_split_schema.sales_mar2022 USING btree (sales_date)
529529
(1 row)
530530

531-
SELECT * FROM pg_indexes WHERE tablename = 'sales_apr2022' and schemaname = 'partition_split_schema';
531+
SELECT * FROM pg_indexes WHERE tablename = 'sales_apr2022' and schemaname = 'partition_split_schema' ORDER BY indexname;
532532
schemaname | tablename | indexname | tablespace | indexdef
533533
------------------------+---------------+------------------------------+------------+------------------------------------------------------------------------------------------------------------
534534
partition_split_schema | sales_apr2022 | sales_apr2022_sales_date_idx | | CREATE INDEX sales_apr2022_sales_date_idx ON partition_split_schema.sales_apr2022 USING btree (sales_date)
535535
(1 row)
536536

537-
SELECT * FROM pg_indexes WHERE tablename = 'sales_others' and schemaname = 'partition_split_schema';
537+
SELECT * FROM pg_indexes WHERE tablename = 'sales_others' and schemaname = 'partition_split_schema' ORDER BY indexname;
538538
schemaname | tablename | indexname | tablespace | indexdef
539539
------------------------+--------------+------------------------------+------------+-----------------------------------------------------------------------------------------------------------
540540
partition_split_schema | sales_others | sales_others_sales_date_idx1 | | CREATE INDEX sales_others_sales_date_idx1 ON partition_split_schema.sales_others USING btree (sales_date)
@@ -622,7 +622,7 @@ sales_date DATE) PARTITION BY RANGE (sales_date);
622622
CREATE TABLE sales_jan2022 PARTITION OF sales_range FOR VALUES FROM ('2022-01-01') TO ('2022-02-01');
623623
CREATE TABLE sales_feb_mar_apr2022 PARTITION OF sales_range FOR VALUES FROM ('2022-02-01') TO ('2022-05-01');
624624
CREATE TABLE sales_others PARTITION OF sales_range DEFAULT;
625-
SELECT pg_get_constraintdef(oid), conname, conkey FROM pg_constraint WHERE conrelid = 'sales_feb_mar_apr2022'::regclass::oid;
625+
SELECT pg_get_constraintdef(oid), conname, conkey FROM pg_constraint WHERE conrelid = 'sales_feb_mar_apr2022'::regclass::oid ORDER BY conname;
626626
pg_get_constraintdef | conname | conkey
627627
---------------------------------------------------------------------+---------------------------------+--------
628628
CHECK ((sales_amount > 1)) | sales_range_sales_amount_check | {2}
@@ -634,21 +634,21 @@ ALTER TABLE sales_range SPLIT PARTITION sales_feb_mar_apr2022 INTO
634634
PARTITION sales_mar2022 FOR VALUES FROM ('2022-03-01') TO ('2022-04-01'),
635635
PARTITION sales_apr2022 FOR VALUES FROM ('2022-04-01') TO ('2022-05-01'));
636636
-- We should see the same CONSTRAINTs as on sales_feb_mar_apr2022 partition
637-
SELECT pg_get_constraintdef(oid), conname, conkey FROM pg_constraint WHERE conrelid = 'sales_feb2022'::regclass::oid;
637+
SELECT pg_get_constraintdef(oid), conname, conkey FROM pg_constraint WHERE conrelid = 'sales_feb2022'::regclass::oid ORDER BY conname;;
638638
pg_get_constraintdef | conname | conkey
639639
---------------------------------------------------------------------+---------------------------------+--------
640640
CHECK ((sales_amount > 1)) | sales_range_sales_amount_check | {2}
641641
FOREIGN KEY (salesperson_id) REFERENCES salespeople(salesperson_id) | sales_range_salesperson_id_fkey | {1}
642642
(2 rows)
643643

644-
SELECT pg_get_constraintdef(oid), conname, conkey FROM pg_constraint WHERE conrelid = 'sales_mar2022'::regclass::oid;
644+
SELECT pg_get_constraintdef(oid), conname, conkey FROM pg_constraint WHERE conrelid = 'sales_mar2022'::regclass::oid ORDER BY conname;;
645645
pg_get_constraintdef | conname | conkey
646646
---------------------------------------------------------------------+---------------------------------+--------
647647
CHECK ((sales_amount > 1)) | sales_range_sales_amount_check | {2}
648648
FOREIGN KEY (salesperson_id) REFERENCES salespeople(salesperson_id) | sales_range_salesperson_id_fkey | {1}
649649
(2 rows)
650650

651-
SELECT pg_get_constraintdef(oid), conname, conkey FROM pg_constraint WHERE conrelid = 'sales_apr2022'::regclass::oid;
651+
SELECT pg_get_constraintdef(oid), conname, conkey FROM pg_constraint WHERE conrelid = 'sales_apr2022'::regclass::oid ORDER BY conname;;
652652
pg_get_constraintdef | conname | conkey
653653
---------------------------------------------------------------------+---------------------------------+--------
654654
CHECK ((sales_amount > 1)) | sales_range_sales_amount_check | {2}
@@ -818,22 +818,22 @@ CREATE TABLE salespeople2_5(salesperson_id INT NOT NULL, salesperson_name VARCHA
818818
ALTER TABLE salespeople ATTACH PARTITION salespeople2_5 FOR VALUES FROM (2) TO (5);
819819
INSERT INTO salespeople (salesperson_name) VALUES ('Poirot');
820820
INSERT INTO salespeople (salesperson_name) VALUES ('Ivanov');
821-
SELECT attname, attidentity, attgenerated FROM pg_attribute WHERE attnum > 0 AND attrelid = 'salespeople'::regclass::oid;
821+
SELECT attname, attidentity, attgenerated FROM pg_attribute WHERE attnum > 0 AND attrelid = 'salespeople'::regclass::oid ORDER BY attnum;
822822
attname | attidentity | attgenerated
823823
------------------+-------------+--------------
824824
salesperson_id | a |
825825
salesperson_name | |
826826
(2 rows)
827827

828-
SELECT attname, attidentity, attgenerated FROM pg_attribute WHERE attnum > 0 AND attrelid = 'salespeople1_2'::regclass::oid;
828+
SELECT attname, attidentity, attgenerated FROM pg_attribute WHERE attnum > 0 AND attrelid = 'salespeople1_2'::regclass::oid ORDER BY attnum;
829829
attname | attidentity | attgenerated
830830
------------------+-------------+--------------
831831
salesperson_id | a |
832832
salesperson_name | |
833833
(2 rows)
834834

835835
-- Split partition has identity column:
836-
SELECT attname, attidentity, attgenerated FROM pg_attribute WHERE attnum > 0 AND attrelid = 'salespeople2_5'::regclass::oid;
836+
SELECT attname, attidentity, attgenerated FROM pg_attribute WHERE attnum > 0 AND attrelid = 'salespeople2_5'::regclass::oid ORDER BY attnum;
837837
attname | attidentity | attgenerated
838838
------------------+-------------+--------------
839839
salesperson_id | a |
@@ -870,36 +870,36 @@ SELECT * FROM salespeople4_5;
870870
4 | Ford
871871
(1 row)
872872

873-
SELECT attname, attidentity, attgenerated FROM pg_attribute WHERE attnum > 0 AND attrelid = 'salespeople'::regclass::oid;
873+
SELECT attname, attidentity, attgenerated FROM pg_attribute WHERE attnum > 0 AND attrelid = 'salespeople'::regclass::oid ORDER BY attnum;
874874
attname | attidentity | attgenerated
875875
------------------+-------------+--------------
876876
salesperson_id | a |
877877
salesperson_name | |
878878
(2 rows)
879879

880-
SELECT attname, attidentity, attgenerated FROM pg_attribute WHERE attnum > 0 AND attrelid = 'salespeople1_2'::regclass::oid;
880+
SELECT attname, attidentity, attgenerated FROM pg_attribute WHERE attnum > 0 AND attrelid = 'salespeople1_2'::regclass::oid ORDER BY attnum;
881881
attname | attidentity | attgenerated
882882
------------------+-------------+--------------
883883
salesperson_id | a |
884884
salesperson_name | |
885885
(2 rows)
886886

887887
-- New partitions have identity-columns:
888-
SELECT attname, attidentity, attgenerated FROM pg_attribute WHERE attnum > 0 AND attrelid = 'salespeople2_3'::regclass::oid;
888+
SELECT attname, attidentity, attgenerated FROM pg_attribute WHERE attnum > 0 AND attrelid = 'salespeople2_3'::regclass::oid ORDER BY attnum;
889889
attname | attidentity | attgenerated
890890
------------------+-------------+--------------
891891
salesperson_id | a |
892892
salesperson_name | |
893893
(2 rows)
894894

895-
SELECT attname, attidentity, attgenerated FROM pg_attribute WHERE attnum > 0 AND attrelid = 'salespeople3_4'::regclass::oid;
895+
SELECT attname, attidentity, attgenerated FROM pg_attribute WHERE attnum > 0 AND attrelid = 'salespeople3_4'::regclass::oid ORDER BY attnum;
896896
attname | attidentity | attgenerated
897897
------------------+-------------+--------------
898898
salesperson_id | a |
899899
salesperson_name | |
900900
(2 rows)
901901

902-
SELECT attname, attidentity, attgenerated FROM pg_attribute WHERE attnum > 0 AND attrelid = 'salespeople4_5'::regclass::oid;
902+
SELECT attname, attidentity, attgenerated FROM pg_attribute WHERE attnum > 0 AND attrelid = 'salespeople4_5'::regclass::oid ORDER BY attnum;
903903
attname | attidentity | attgenerated
904904
------------------+-------------+--------------
905905
salesperson_id | a |

‎src/test/regress/sql/partition_split.sql

Copy file name to clipboardExpand all lines: src/test/regress/sql/partition_split.sql
+17-17Lines changed: 17 additions & 17 deletions
Original file line numberDiff line numberDiff line change
@@ -255,7 +255,7 @@ INSERT INTO sales_range VALUES (13, 'Gandi', 377, '2022-01-09');
255255
INSERT INTO sales_range VALUES (14, 'Smith', 510, '2022-05-04');
256256

257257
SELECT * FROM sales_others;
258-
SELECT * FROM pg_indexes WHERE tablename = 'sales_others' and schemaname = 'partition_split_schema';
258+
SELECT * FROM pg_indexes WHERE tablename = 'sales_others' and schemaname = 'partition_split_schema' ORDER BY indexname;
259259

260260
ALTER TABLE sales_range SPLIT PARTITION sales_others INTO
261261
(PARTITION sales_feb2022 FOR VALUES FROM ('2022-02-01') TO ('2022-03-01'),
@@ -275,10 +275,10 @@ SELECT * FROM sales_others where sales_date > '2022-01-01';
275275
SET enable_indexscan = ON;
276276
SET enable_seqscan = ON;
277277

278-
SELECT * FROM pg_indexes WHERE tablename = 'sales_feb2022' and schemaname = 'partition_split_schema';
279-
SELECT * FROM pg_indexes WHERE tablename = 'sales_mar2022' and schemaname = 'partition_split_schema';
280-
SELECT * FROM pg_indexes WHERE tablename = 'sales_apr2022' and schemaname = 'partition_split_schema';
281-
SELECT * FROM pg_indexes WHERE tablename = 'sales_others' and schemaname = 'partition_split_schema';
278+
SELECT * FROM pg_indexes WHERE tablename = 'sales_feb2022' and schemaname = 'partition_split_schema' ORDER BY indexname;
279+
SELECT * FROM pg_indexes WHERE tablename = 'sales_mar2022' and schemaname = 'partition_split_schema' ORDER BY indexname;
280+
SELECT * FROM pg_indexes WHERE tablename = 'sales_apr2022' and schemaname = 'partition_split_schema' ORDER BY indexname;
281+
SELECT * FROM pg_indexes WHERE tablename = 'sales_others' and schemaname = 'partition_split_schema' ORDER BY indexname;
282282

283283
DROP TABLE sales_range CASCADE;
284284

@@ -362,17 +362,17 @@ CREATE TABLE sales_jan2022 PARTITION OF sales_range FOR VALUES FROM ('2022-01-01
362362
CREATE TABLE sales_feb_mar_apr2022 PARTITION OF sales_range FOR VALUES FROM ('2022-02-01') TO ('2022-05-01');
363363
CREATE TABLE sales_others PARTITION OF sales_range DEFAULT;
364364

365-
SELECT pg_get_constraintdef(oid), conname, conkey FROM pg_constraint WHERE conrelid = 'sales_feb_mar_apr2022'::regclass::oid;
365+
SELECT pg_get_constraintdef(oid), conname, conkey FROM pg_constraint WHERE conrelid = 'sales_feb_mar_apr2022'::regclass::oid ORDER BY conname;
366366

367367
ALTER TABLE sales_range SPLIT PARTITION sales_feb_mar_apr2022 INTO
368368
(PARTITION sales_feb2022 FOR VALUES FROM ('2022-02-01') TO ('2022-03-01'),
369369
PARTITION sales_mar2022 FOR VALUES FROM ('2022-03-01') TO ('2022-04-01'),
370370
PARTITION sales_apr2022 FOR VALUES FROM ('2022-04-01') TO ('2022-05-01'));
371371

372372
-- We should see the same CONSTRAINTs as on sales_feb_mar_apr2022 partition
373-
SELECT pg_get_constraintdef(oid), conname, conkey FROM pg_constraint WHERE conrelid = 'sales_feb2022'::regclass::oid;
374-
SELECT pg_get_constraintdef(oid), conname, conkey FROM pg_constraint WHERE conrelid = 'sales_mar2022'::regclass::oid;
375-
SELECT pg_get_constraintdef(oid), conname, conkey FROM pg_constraint WHERE conrelid = 'sales_apr2022'::regclass::oid;
373+
SELECT pg_get_constraintdef(oid), conname, conkey FROM pg_constraint WHERE conrelid = 'sales_feb2022'::regclass::oid ORDER BY conname;;
374+
SELECT pg_get_constraintdef(oid), conname, conkey FROM pg_constraint WHERE conrelid = 'sales_mar2022'::regclass::oid ORDER BY conname;;
375+
SELECT pg_get_constraintdef(oid), conname, conkey FROM pg_constraint WHERE conrelid = 'sales_apr2022'::regclass::oid ORDER BY conname;;
376376

377377
-- ERROR: new row for relation "sales_mar2022" violates check constraint "sales_range_sales_amount_check"
378378
INSERT INTO sales_range VALUES (1, 0, '2022-03-11');
@@ -495,10 +495,10 @@ ALTER TABLE salespeople ATTACH PARTITION salespeople2_5 FOR VALUES FROM (2) TO (
495495
INSERT INTO salespeople (salesperson_name) VALUES ('Poirot');
496496
INSERT INTO salespeople (salesperson_name) VALUES ('Ivanov');
497497

498-
SELECT attname, attidentity, attgenerated FROM pg_attribute WHERE attnum > 0 AND attrelid = 'salespeople'::regclass::oid;
499-
SELECT attname, attidentity, attgenerated FROM pg_attribute WHERE attnum > 0 AND attrelid = 'salespeople1_2'::regclass::oid;
498+
SELECT attname, attidentity, attgenerated FROM pg_attribute WHERE attnum > 0 AND attrelid = 'salespeople'::regclass::oid ORDER BY attnum;
499+
SELECT attname, attidentity, attgenerated FROM pg_attribute WHERE attnum > 0 AND attrelid = 'salespeople1_2'::regclass::oid ORDER BY attnum;
500500
-- Split partition has identity column:
501-
SELECT attname, attidentity, attgenerated FROM pg_attribute WHERE attnum > 0 AND attrelid = 'salespeople2_5'::regclass::oid;
501+
SELECT attname, attidentity, attgenerated FROM pg_attribute WHERE attnum > 0 AND attrelid = 'salespeople2_5'::regclass::oid ORDER BY attnum;
502502

503503
ALTER TABLE salespeople SPLIT PARTITION salespeople2_5 INTO
504504
(PARTITION salespeople2_3 FOR VALUES FROM (2) TO (3),
@@ -513,12 +513,12 @@ SELECT * FROM salespeople2_3;
513513
SELECT * FROM salespeople3_4;
514514
SELECT * FROM salespeople4_5;
515515

516-
SELECT attname, attidentity, attgenerated FROM pg_attribute WHERE attnum > 0 AND attrelid = 'salespeople'::regclass::oid;
517-
SELECT attname, attidentity, attgenerated FROM pg_attribute WHERE attnum > 0 AND attrelid = 'salespeople1_2'::regclass::oid;
516+
SELECT attname, attidentity, attgenerated FROM pg_attribute WHERE attnum > 0 AND attrelid = 'salespeople'::regclass::oid ORDER BY attnum;
517+
SELECT attname, attidentity, attgenerated FROM pg_attribute WHERE attnum > 0 AND attrelid = 'salespeople1_2'::regclass::oid ORDER BY attnum;
518518
-- New partitions have identity-columns:
519-
SELECT attname, attidentity, attgenerated FROM pg_attribute WHERE attnum > 0 AND attrelid = 'salespeople2_3'::regclass::oid;
520-
SELECT attname, attidentity, attgenerated FROM pg_attribute WHERE attnum > 0 AND attrelid = 'salespeople3_4'::regclass::oid;
521-
SELECT attname, attidentity, attgenerated FROM pg_attribute WHERE attnum > 0 AND attrelid = 'salespeople4_5'::regclass::oid;
519+
SELECT attname, attidentity, attgenerated FROM pg_attribute WHERE attnum > 0 AND attrelid = 'salespeople2_3'::regclass::oid ORDER BY attnum;
520+
SELECT attname, attidentity, attgenerated FROM pg_attribute WHERE attnum > 0 AND attrelid = 'salespeople3_4'::regclass::oid ORDER BY attnum;
521+
SELECT attname, attidentity, attgenerated FROM pg_attribute WHERE attnum > 0 AND attrelid = 'salespeople4_5'::regclass::oid ORDER BY attnum;
522522

523523
DROP TABLE salespeople CASCADE;
524524

0 commit comments

Comments
0 (0)
Morty Proxy This is a proxified and sanitized view of the page, visit original site.