From: Tom Lane <tgl@sss.pgh.pa.us>
To: pgsql-hackers@lists.postgresql.org
Subject: Improving collation-dependent indexes in system catalogs
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <19345.1544895308.1@sss.pgh.pa.us>
Date: Sat, 15 Dec 2018 12:35:09 -0500
Message-ID: <19346.1544895309@sss.pgh.pa.us>
Precedence: bulk

Awhile back we noticed that a couple of system catalogs had
acquired indexes on "text" columns, which were unsafe because
their sort order was collation-dependent, so that cloning
template0 with a different database collation could yield
broken indexes.  We fixed this in commit 0b28ea79 with a
bit of a hack: we said it's okay to have such indexes as long
as they use the text_pattern_ops opclass, making them
collation insensitive.

While fooling with the idea of making type "name" collation
aware, it occurred to me that there's a better, more general
answer, which is to insist that collation-aware system catalog
columns must be marked with C collation.  This rule would apply
without modification to both "text" and "name" columns.  In the
wake of commit 5e0928005, it also means that pg_statistic data
for such a column would port safely across a database collation
change, which up to now it does not.  And I think we could have
the bootstrap code apply the rule automatically, making for one
less way to screw up when changing catalog definitions.

Thoughts, objections?

			regards, tom lane