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

Commit0b28ea7

Browse files
committed
Avoid collation dependence in indexes of system catalogs.
No index in template0 should have collation-dependent ordering, especiallynot indexes on shared catalogs. For most textual columns we avoid thisissue by using type "name" (which sorts per strcmp()). However there are afew indexed columns that we'd prefer to use "text" for, and for that, thedefault opclass text_ops is unsafe. Fortunately, text_pattern_ops is safe(it sorts per memcmp()), and it has no real functional disadvantage for ourpurposes. So change the indexes on pg_seclabel.provider andpg_shseclabel.provider to use text_pattern_ops.In passing, also mark pg_replication_origin.roname as usingtext_pattern_ops --- for some reason it was labeled varchar_pattern_opswhich is just wrong, even though it accidentally worked.Add regression test queries to catch future errors of these kinds.We still can't do anything about the misdeclared pg_seclabel andpg_shseclabel indexes in back branches :-(
1 parentafee043 commit0b28ea7

File tree

4 files changed

+154
-10
lines changed

4 files changed

+154
-10
lines changed

‎src/include/catalog/catversion.h

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -53,6 +53,6 @@
5353
*/
5454

5555
/*yyyymmddN */
56-
#defineCATALOG_VERSION_NO201505153
56+
#defineCATALOG_VERSION_NO201505191
5757

5858
#endif

‎src/include/catalog/indexing.h

Lines changed: 3 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -290,10 +290,10 @@ DECLARE_UNIQUE_INDEX(pg_default_acl_oid_index, 828, on pg_default_acl using btre
290290
DECLARE_UNIQUE_INDEX(pg_db_role_setting_databaseid_rol_index,2965,onpg_db_role_settingusingbtree(setdatabaseoid_ops,setroleoid_ops));
291291
#defineDbRoleSettingDatidRolidIndexId2965
292292

293-
DECLARE_UNIQUE_INDEX(pg_seclabel_object_index,3597,onpg_seclabelusingbtree(objoidoid_ops,classoidoid_ops,objsubidint4_ops,providertext_ops));
293+
DECLARE_UNIQUE_INDEX(pg_seclabel_object_index,3597,onpg_seclabelusingbtree(objoidoid_ops,classoidoid_ops,objsubidint4_ops,providertext_pattern_ops));
294294
#defineSecLabelObjectIndexId3597
295295

296-
DECLARE_UNIQUE_INDEX(pg_shseclabel_object_index,3593,onpg_shseclabelusingbtree(objoidoid_ops,classoidoid_ops,providertext_ops));
296+
DECLARE_UNIQUE_INDEX(pg_shseclabel_object_index,3593,onpg_shseclabelusingbtree(objoidoid_ops,classoidoid_ops,providertext_pattern_ops));
297297
#defineSharedSecLabelObjectIndexId3593
298298

299299
DECLARE_UNIQUE_INDEX(pg_extension_oid_index,3080,onpg_extensionusingbtree(oidoid_ops));
@@ -313,7 +313,7 @@ DECLARE_UNIQUE_INDEX(pg_policy_polrelid_polname_index, 3258, on pg_policy using
313313
DECLARE_UNIQUE_INDEX(pg_replication_origin_roiident_index,6001,onpg_replication_originusingbtree(roidentoid_ops));
314314
#defineReplicationOriginIdentIndex 6001
315315

316-
DECLARE_UNIQUE_INDEX(pg_replication_origin_roname_index,6002,onpg_replication_originusingbtree(ronamevarchar_pattern_ops));
316+
DECLARE_UNIQUE_INDEX(pg_replication_origin_roname_index,6002,onpg_replication_originusingbtree(ronametext_pattern_ops));
317317
#defineReplicationOriginNameIndex 6002
318318

319319
DECLARE_UNIQUE_INDEX(pg_tablesample_method_name_index,3331,onpg_tablesample_methodusingbtree(tsmnamename_ops));

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

Lines changed: 80 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -2,7 +2,7 @@
22
-- OPR_SANITY
33
-- Sanity checks for common errors in making operator/procedure system tables:
44
-- pg_operator, pg_proc, pg_cast, pg_aggregate, pg_am,
5-
-- pg_amop, pg_amproc, pg_opclass, pg_opfamily.
5+
-- pg_amop, pg_amproc, pg_opclass, pg_opfamily, pg_index.
66
--
77
-- Every test failure in this file should be closely inspected.
88
-- The description of the failing test should be read carefully before
@@ -27,7 +27,9 @@ SELECT ($1 = $2) OR
2727
($2 = 'pg_catalog.any'::pg_catalog.regtype) OR
2828
($2 = 'pg_catalog.anyarray'::pg_catalog.regtype AND
2929
EXISTS(select 1 from pg_catalog.pg_type where
30-
oid = $1 and typelem != 0 and typlen = -1))
30+
oid = $1 and typelem != 0 and typlen = -1)) OR
31+
($2 = 'pg_catalog.anyrange'::pg_catalog.regtype AND
32+
(select typtype from pg_catalog.pg_type where oid = $1) = 'r')
3133
$$ language sql strict stable;
3234
-- This one ignores castcontext, so it considers only physical equivalence
3335
-- and not whether the coercion can be invoked implicitly.
@@ -39,7 +41,9 @@ SELECT ($1 = $2) OR
3941
($2 = 'pg_catalog.any'::pg_catalog.regtype) OR
4042
($2 = 'pg_catalog.anyarray'::pg_catalog.regtype AND
4143
EXISTS(select 1 from pg_catalog.pg_type where
42-
oid = $1 and typelem != 0 and typlen = -1))
44+
oid = $1 and typelem != 0 and typlen = -1)) OR
45+
($2 = 'pg_catalog.anyrange'::pg_catalog.regtype AND
46+
(select typtype from pg_catalog.pg_type where oid = $1) = 'r')
4347
$$ language sql strict stable;
4448
-- **************** pg_proc ****************
4549
-- Look for illegal values in pg_proc fields.
@@ -2014,3 +2018,76 @@ WHERE p1.amproc = p2.oid AND
20142018
--------------+--------+--------
20152019
(0 rows)
20162020

2021+
-- **************** pg_index ****************
2022+
-- Look for illegal values in pg_index fields.
2023+
SELECT p1.indexrelid, p1.indrelid
2024+
FROM pg_index as p1
2025+
WHERE p1.indexrelid = 0 OR p1.indrelid = 0 OR
2026+
p1.indnatts <= 0 OR p1.indnatts > 32;
2027+
indexrelid | indrelid
2028+
------------+----------
2029+
(0 rows)
2030+
2031+
-- oidvector and int2vector fields should be of length indnatts.
2032+
SELECT p1.indexrelid, p1.indrelid
2033+
FROM pg_index as p1
2034+
WHERE array_lower(indkey, 1) != 0 OR array_upper(indkey, 1) != indnatts-1 OR
2035+
array_lower(indclass, 1) != 0 OR array_upper(indclass, 1) != indnatts-1 OR
2036+
array_lower(indcollation, 1) != 0 OR array_upper(indcollation, 1) != indnatts-1 OR
2037+
array_lower(indoption, 1) != 0 OR array_upper(indoption, 1) != indnatts-1;
2038+
indexrelid | indrelid
2039+
------------+----------
2040+
(0 rows)
2041+
2042+
-- Check that opclasses and collations match the underlying columns.
2043+
-- (As written, this test ignores expression indexes.)
2044+
SELECT indexrelid::regclass, indrelid::regclass, attname, atttypid::regtype, opcname
2045+
FROM (SELECT indexrelid, indrelid, unnest(indkey) as ikey,
2046+
unnest(indclass) as iclass, unnest(indcollation) as icoll
2047+
FROM pg_index) ss,
2048+
pg_attribute a,
2049+
pg_opclass opc
2050+
WHERE a.attrelid = indrelid AND a.attnum = ikey AND opc.oid = iclass AND
2051+
(NOT binary_coercible(atttypid, opcintype) OR icoll != attcollation);
2052+
indexrelid | indrelid | attname | atttypid | opcname
2053+
------------+----------+---------+----------+---------
2054+
(0 rows)
2055+
2056+
-- For system catalogs, be even tighter: nearly all indexes should be
2057+
-- exact type matches not binary-coercible matches. At this writing
2058+
-- the only exception is an OID index on a regproc column.
2059+
SELECT indexrelid::regclass, indrelid::regclass, attname, atttypid::regtype, opcname
2060+
FROM (SELECT indexrelid, indrelid, unnest(indkey) as ikey,
2061+
unnest(indclass) as iclass, unnest(indcollation) as icoll
2062+
FROM pg_index
2063+
WHERE indrelid < 16384) ss,
2064+
pg_attribute a,
2065+
pg_opclass opc
2066+
WHERE a.attrelid = indrelid AND a.attnum = ikey AND opc.oid = iclass AND
2067+
(opcintype != atttypid OR icoll != attcollation)
2068+
ORDER BY 1;
2069+
indexrelid | indrelid | attname | atttypid | opcname
2070+
--------------------------+--------------+----------+----------+---------
2071+
pg_aggregate_fnoid_index | pg_aggregate | aggfnoid | regproc | oid_ops
2072+
(1 row)
2073+
2074+
-- Check for system catalogs with collation-sensitive ordering. This is not
2075+
-- a representational error in pg_index, but simply wrong catalog design.
2076+
-- It's bad because we expect to be able to clone template0 and assign the
2077+
-- copy a different database collation. It would especially not work for
2078+
-- shared catalogs. Note that although text columns will show a collation
2079+
-- in indcollation, they're still okay to index with text_pattern_ops,
2080+
-- so allow that case.
2081+
SELECT indexrelid::regclass, indrelid::regclass, iclass, icoll
2082+
FROM (SELECT indexrelid, indrelid,
2083+
unnest(indclass) as iclass, unnest(indcollation) as icoll
2084+
FROM pg_index
2085+
WHERE indrelid < 16384) ss
2086+
WHERE icoll != 0 AND iclass !=
2087+
(SELECT oid FROM pg_opclass
2088+
WHERE opcname = 'text_pattern_ops' AND opcmethod =
2089+
(SELECT oid FROM pg_am WHERE amname = 'btree'));
2090+
indexrelid | indrelid | iclass | icoll
2091+
------------+----------+--------+-------
2092+
(0 rows)
2093+

‎src/test/regress/sql/opr_sanity.sql

Lines changed: 70 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -2,7 +2,7 @@
22
-- OPR_SANITY
33
-- Sanity checks for common errors in making operator/procedure system tables:
44
-- pg_operator, pg_proc, pg_cast, pg_aggregate, pg_am,
5-
-- pg_amop, pg_amproc, pg_opclass, pg_opfamily.
5+
-- pg_amop, pg_amproc, pg_opclass, pg_opfamily, pg_index.
66
--
77
-- Every test failure in this file should be closely inspected.
88
-- The description of the failing test should be read carefully before
@@ -30,7 +30,9 @@ SELECT ($1 = $2) OR
3030
($2='pg_catalog.any'::pg_catalog.regtype)OR
3131
($2='pg_catalog.anyarray'::pg_catalog.regtypeAND
3232
EXISTS(select1frompg_catalog.pg_typewhere
33-
oid= $1and typelem!=0and typlen=-1))
33+
oid= $1and typelem!=0and typlen=-1))OR
34+
($2='pg_catalog.anyrange'::pg_catalog.regtypeAND
35+
(select typtypefrompg_catalog.pg_typewhereoid= $1)='r')
3436
$$ language sql strict stable;
3537

3638
-- This one ignores castcontext, so it considers only physical equivalence
@@ -43,7 +45,9 @@ SELECT ($1 = $2) OR
4345
($2='pg_catalog.any'::pg_catalog.regtype)OR
4446
($2='pg_catalog.anyarray'::pg_catalog.regtypeAND
4547
EXISTS(select1frompg_catalog.pg_typewhere
46-
oid= $1and typelem!=0and typlen=-1))
48+
oid= $1and typelem!=0and typlen=-1))OR
49+
($2='pg_catalog.anyrange'::pg_catalog.regtypeAND
50+
(select typtypefrompg_catalog.pg_typewhereoid= $1)='r')
4751
$$ language sql strict stable;
4852

4953
-- **************** pg_proc ****************
@@ -1316,3 +1320,66 @@ FROM pg_amproc AS p1, pg_proc AS p2
13161320
WHEREp1.amproc=p2.oidAND
13171321
p1.amproclefttype!=p1.amprocrighttypeAND
13181322
p2.provolatile='v';
1323+
1324+
-- **************** pg_index ****************
1325+
1326+
-- Look for illegal values in pg_index fields.
1327+
1328+
SELECTp1.indexrelid,p1.indrelid
1329+
FROM pg_indexas p1
1330+
WHEREp1.indexrelid=0ORp1.indrelid=0OR
1331+
p1.indnatts<=0ORp1.indnatts>32;
1332+
1333+
-- oidvector and int2vector fields should be of length indnatts.
1334+
1335+
SELECTp1.indexrelid,p1.indrelid
1336+
FROM pg_indexas p1
1337+
WHERE array_lower(indkey,1)!=0OR array_upper(indkey,1)!= indnatts-1OR
1338+
array_lower(indclass,1)!=0OR array_upper(indclass,1)!= indnatts-1OR
1339+
array_lower(indcollation,1)!=0OR array_upper(indcollation,1)!= indnatts-1OR
1340+
array_lower(indoption,1)!=0OR array_upper(indoption,1)!= indnatts-1;
1341+
1342+
-- Check that opclasses and collations match the underlying columns.
1343+
-- (As written, this test ignores expression indexes.)
1344+
1345+
SELECT indexrelid::regclass, indrelid::regclass, attname, atttypid::regtype, opcname
1346+
FROM (SELECT indexrelid, indrelid, unnest(indkey)as ikey,
1347+
unnest(indclass)as iclass, unnest(indcollation)as icoll
1348+
FROM pg_index) ss,
1349+
pg_attribute a,
1350+
pg_opclass opc
1351+
WHEREa.attrelid= indrelidANDa.attnum= ikeyANDopc.oid= iclassAND
1352+
(NOT binary_coercible(atttypid, opcintype)OR icoll!= attcollation);
1353+
1354+
-- For system catalogs, be even tighter: nearly all indexes should be
1355+
-- exact type matches not binary-coercible matches. At this writing
1356+
-- the only exception is an OID index on a regproc column.
1357+
1358+
SELECT indexrelid::regclass, indrelid::regclass, attname, atttypid::regtype, opcname
1359+
FROM (SELECT indexrelid, indrelid, unnest(indkey)as ikey,
1360+
unnest(indclass)as iclass, unnest(indcollation)as icoll
1361+
FROM pg_index
1362+
WHERE indrelid<16384) ss,
1363+
pg_attribute a,
1364+
pg_opclass opc
1365+
WHEREa.attrelid= indrelidANDa.attnum= ikeyANDopc.oid= iclassAND
1366+
(opcintype!= atttypidOR icoll!= attcollation)
1367+
ORDER BY1;
1368+
1369+
-- Check for system catalogs with collation-sensitive ordering. This is not
1370+
-- a representational error in pg_index, but simply wrong catalog design.
1371+
-- It's bad because we expect to be able to clone template0 and assign the
1372+
-- copy a different database collation. It would especially not work for
1373+
-- shared catalogs. Note that although text columns will show a collation
1374+
-- in indcollation, they're still okay to index with text_pattern_ops,
1375+
-- so allow that case.
1376+
1377+
SELECT indexrelid::regclass, indrelid::regclass, iclass, icoll
1378+
FROM (SELECT indexrelid, indrelid,
1379+
unnest(indclass)as iclass, unnest(indcollation)as icoll
1380+
FROM pg_index
1381+
WHERE indrelid<16384) ss
1382+
WHERE icoll!=0AND iclass!=
1383+
(SELECToidFROM pg_opclass
1384+
WHERE opcname='text_pattern_ops'AND opcmethod=
1385+
(SELECToidFROM pg_amWHERE amname='btree'));

0 commit comments

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