Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

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

 Sign up
Appearance settings

Commit4d6603f

Browse files
committed
Simplify psql \d's rule for ordering the indexes of a table.
The previous rule was "primary key (if any) first, then other uniqueindexes in name order, then all other indexes in name order".But the preference for unique indexes seems a bit obsolete since theintroduction of exclusion constraints. It's no longer the casethat unique indexes are the only ones that constrain what data canbe in the table, and it's hard to see what other rationale there isfor separating out unique indexes. Other new features like thepossibility for some indexes to be INVALID (hence, not constraininganything) make this even shakier.Hence, simplify the sort order to be "primary key (if any) first,then all other indexes in name order".No documentation change, since this was never documented anyway.A couple of existing regression test cases change output, though.Discussion:https://postgr.es/m/14422.1561474929@sss.pgh.pa.us
1 parent66c5bd3 commit4d6603f

File tree

4 files changed

+16
-16
lines changed

4 files changed

+16
-16
lines changed

‎src/bin/psql/describe.c

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -2312,7 +2312,7 @@ describeOneTableDetails(const char *schemaname,
23122312
" LEFT JOIN pg_catalog.pg_constraint con ON (conrelid = i.indrelid AND conindid = i.indexrelid AND contype IN ('p','u','x'))\n");
23132313
appendPQExpBuffer(&buf,
23142314
"WHERE c.oid = '%s' AND c.oid = i.indrelid AND i.indexrelid = c2.oid\n"
2315-
"ORDER BY i.indisprimary DESC,i.indisunique DESC,c2.relname;",
2315+
"ORDER BY i.indisprimary DESC, c2.relname;",
23162316
oid);
23172317
result=PSQLexec(buf.data);
23182318
if (!result)

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

Lines changed: 4 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -1925,12 +1925,12 @@ create unique index on anothertab(f4);
19251925
Indexes:
19261926
"anothertab_pkey" PRIMARY KEY, btree (f1)
19271927
"anothertab_f1_f4_key" UNIQUE CONSTRAINT, btree (f1, f4)
1928-
"anothertab_f2_key" UNIQUE CONSTRAINT, btree (f2)
1929-
"anothertab_f4_idx" UNIQUE, btree (f4)
19301928
"anothertab_f2_f3_idx" btree (f2, f3)
1929+
"anothertab_f2_key" UNIQUE CONSTRAINT, btree (f2)
19311930
"anothertab_f3_excl" EXCLUDE USING btree (f3 WITH =)
19321931
"anothertab_f4_excl" EXCLUDE USING btree (f4 WITH =) WHERE (f4 IS NOT NULL)
19331932
"anothertab_f4_excl1" EXCLUDE USING btree (f4 WITH =) WHERE (f5 > 0)
1933+
"anothertab_f4_idx" UNIQUE, btree (f4)
19341934

19351935
alter table anothertab alter column f1 type bigint;
19361936
alter table anothertab
@@ -1950,12 +1950,12 @@ alter table anothertab alter column f5 type bigint;
19501950
Indexes:
19511951
"anothertab_pkey" PRIMARY KEY, btree (f1)
19521952
"anothertab_f1_f4_key" UNIQUE CONSTRAINT, btree (f1, f4)
1953-
"anothertab_f2_key" UNIQUE CONSTRAINT, btree (f2)
1954-
"anothertab_f4_idx" UNIQUE, btree (f4)
19551953
"anothertab_f2_f3_idx" btree (f2, f3)
1954+
"anothertab_f2_key" UNIQUE CONSTRAINT, btree (f2)
19561955
"anothertab_f3_excl" EXCLUDE USING btree (f3 WITH =)
19571956
"anothertab_f4_excl" EXCLUDE USING btree (f4 WITH =) WHERE (f4 IS NOT NULL)
19581957
"anothertab_f4_excl1" EXCLUDE USING btree (f4 WITH =) WHERE (f5 > 0)
1958+
"anothertab_f4_idx" UNIQUE, btree (f4)
19591959

19601960
drop table anothertab;
19611961
create table another (f1 int, f2 text);

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

Lines changed: 5 additions & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -1374,10 +1374,10 @@ VACUUM FULL concur_heap;
13741374
f1 | text | | |
13751375
f2 | text | | |
13761376
Indexes:
1377-
"concur_index2" UNIQUE, btree (f1)
1378-
"concur_index3" UNIQUE, btree (f2) INVALID
13791377
"concur_heap_expr_idx" btree ((f2 || f1))
13801378
"concur_index1" btree (f2, f1)
1379+
"concur_index2" UNIQUE, btree (f1)
1380+
"concur_index3" UNIQUE, btree (f2) INVALID
13811381
"concur_index4" btree (f2) WHERE f1 = 'a'::text
13821382
"concur_index5" btree (f2) WHERE f1 = 'x'::text
13831383
"std_index" btree (f2)
@@ -1390,10 +1390,10 @@ REINDEX TABLE concur_heap;
13901390
f1 | text | | |
13911391
f2 | text | | |
13921392
Indexes:
1393-
"concur_index2" UNIQUE, btree (f1)
1394-
"concur_index3" UNIQUE, btree (f2)
13951393
"concur_heap_expr_idx" btree ((f2 || f1))
13961394
"concur_index1" btree (f2, f1)
1395+
"concur_index2" UNIQUE, btree (f1)
1396+
"concur_index3" UNIQUE, btree (f2)
13971397
"concur_index4" btree (f2) WHERE f1 = 'a'::text
13981398
"concur_index5" btree (f2) WHERE f1 = 'x'::text
13991399
"std_index" btree (f2)
@@ -2115,8 +2115,8 @@ WARNING: cannot reindex system catalogs concurrently, skipping all
21152115
c2 | text | | |
21162116
Indexes:
21172117
"concur_reindex_ind1" PRIMARY KEY, btree (c1)
2118-
"concur_reindex_ind3" UNIQUE, btree (abs(c1))
21192118
"concur_reindex_ind2" btree (c2)
2119+
"concur_reindex_ind3" UNIQUE, btree (abs(c1))
21202120
"concur_reindex_ind4" btree (c1, c1, c2)
21212121
Referenced by:
21222122
TABLE "concur_reindex_tab2" CONSTRAINT "concur_reindex_tab2_c1_fkey" FOREIGN KEY (c1) REFERENCES concur_reindex_tab(c1)

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

Lines changed: 6 additions & 6 deletions
Original file line numberDiff line numberDiff line change
@@ -85,13 +85,13 @@ SELECT relreplident FROM pg_class WHERE oid = 'test_replica_identity'::regclass;
8585
Indexes:
8686
"test_replica_identity_pkey" PRIMARY KEY, btree (id) REPLICA IDENTITY
8787
"test_replica_identity_expr" UNIQUE, btree (keya, keyb, (3))
88+
"test_replica_identity_hash" hash (nonkey)
89+
"test_replica_identity_keyab" btree (keya, keyb)
8890
"test_replica_identity_keyab_key" UNIQUE, btree (keya, keyb)
8991
"test_replica_identity_nonkey" UNIQUE, btree (keya, nonkey)
9092
"test_replica_identity_partial" UNIQUE, btree (keya, keyb) WHERE keyb <> '3'::text
9193
"test_replica_identity_unique_defer" UNIQUE CONSTRAINT, btree (keya, keyb) DEFERRABLE
9294
"test_replica_identity_unique_nondefer" UNIQUE CONSTRAINT, btree (keya, keyb)
93-
"test_replica_identity_hash" hash (nonkey)
94-
"test_replica_identity_keyab" btree (keya, keyb)
9595

9696
-- succeed, nondeferrable unique constraint over nonnullable cols
9797
ALTER TABLE test_replica_identity REPLICA IDENTITY USING INDEX test_replica_identity_unique_nondefer;
@@ -115,13 +115,13 @@ SELECT relreplident FROM pg_class WHERE oid = 'test_replica_identity'::regclass;
115115
Indexes:
116116
"test_replica_identity_pkey" PRIMARY KEY, btree (id)
117117
"test_replica_identity_expr" UNIQUE, btree (keya, keyb, (3))
118+
"test_replica_identity_hash" hash (nonkey)
119+
"test_replica_identity_keyab" btree (keya, keyb)
118120
"test_replica_identity_keyab_key" UNIQUE, btree (keya, keyb) REPLICA IDENTITY
119121
"test_replica_identity_nonkey" UNIQUE, btree (keya, nonkey)
120122
"test_replica_identity_partial" UNIQUE, btree (keya, keyb) WHERE keyb <> '3'::text
121123
"test_replica_identity_unique_defer" UNIQUE CONSTRAINT, btree (keya, keyb) DEFERRABLE
122124
"test_replica_identity_unique_nondefer" UNIQUE CONSTRAINT, btree (keya, keyb)
123-
"test_replica_identity_hash" hash (nonkey)
124-
"test_replica_identity_keyab" btree (keya, keyb)
125125

126126
SELECT count(*) FROM pg_index WHERE indrelid = 'test_replica_identity'::regclass AND indisreplident;
127127
count
@@ -163,13 +163,13 @@ SELECT relreplident FROM pg_class WHERE oid = 'test_replica_identity'::regclass;
163163
Indexes:
164164
"test_replica_identity_pkey" PRIMARY KEY, btree (id)
165165
"test_replica_identity_expr" UNIQUE, btree (keya, keyb, (3))
166+
"test_replica_identity_hash" hash (nonkey)
167+
"test_replica_identity_keyab" btree (keya, keyb)
166168
"test_replica_identity_keyab_key" UNIQUE, btree (keya, keyb)
167169
"test_replica_identity_nonkey" UNIQUE, btree (keya, nonkey)
168170
"test_replica_identity_partial" UNIQUE, btree (keya, keyb) WHERE keyb <> '3'::text
169171
"test_replica_identity_unique_defer" UNIQUE CONSTRAINT, btree (keya, keyb) DEFERRABLE
170172
"test_replica_identity_unique_nondefer" UNIQUE CONSTRAINT, btree (keya, keyb)
171-
"test_replica_identity_hash" hash (nonkey)
172-
"test_replica_identity_keyab" btree (keya, keyb)
173173
Replica Identity: FULL
174174

175175
ALTER TABLE test_replica_identity REPLICA IDENTITY NOTHING;

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp