Provide option to specify the charset or collation of text columns in the registry tables

[datafoo]

PostgreSQL indicates:

The drawback of using locales other than C or POSIX in PostgreSQL is its performance impact. It slows character handling and prevents ordinary indexes from being used by LIKE. For this reason use locales only if you actually need them.

Could Sqitch create its tables using the C collation rather than the default collation?

[datafoo]

That would remove the need from re-indexing due to collation version mismatch (see https://www.postgresql.org/docs/current/sql-altercollation.html#SQL-ALTERCOLLATION-NOTES) since the C collation does not change.

[theory]

Yeah, probably a good idea, although even the C and POSIX collations can change when you upgrade glibc. There was a ton of discussion of the issue at PGCon this year, including this session. Maybe C locale would be the least bad, tho, considering that Sqitch mainly relies on indexes for uniqueness rather than sorting (it mainly sorts by dates).

[datafoo]

even the C and POSIX collations can change when you upgrade glibc

I was under the impression that C collation doesn't ever change after reading a comment from @laurenz on a blog article he wrote at https://www.cybertec-postgresql.com/en/icu-collations-against-postgresql-data-corruption/#comment-6208123551

[the C collation] doesn't ever change. it is just the order defined by memcmp() of the strings, which only depends on the encoding.

Look here:

The C and POSIX collations both specify “traditional C” behavior, in which only the ASCII letters “A” through “Z” are treated as letters, and sorting is done strictly by character code byte values.

[theory]

Oh, perhaps you're right, I forgot about that. The examples from the presentation use en-US.UTF-8.

[theory]

I'm gonna drop this issue for now. There just hasn't been the demand, and as my notes in #765 and #857 show, it's a non-trivial problem. If, however, one would like to enforce C/POSIX collation, here's a script to do it:

BEGIN;

ALTER TABLE :"registry".releases
      ALTER COLUMN installer_name  SET DATA TYPE TEXT COLLATE :"collation",
      ALTER COLUMN installer_email SET DATA TYPE TEXT COLLATE :"collation"
;

ALTER TABLE :"registry".projects
      ALTER COLUMN project       SET DATA TYPE TEXT COLLATE :"collation",
      ALTER COLUMN uri           SET DATA TYPE TEXT COLLATE :"collation",
      ALTER COLUMN creator_name  SET DATA TYPE TEXT COLLATE :"collation",
      ALTER COLUMN creator_email SET DATA TYPE TEXT COLLATE :"collation"
;

ALTER TABLE :"registry".changes
      ALTER COLUMN change_id       SET DATA TYPE TEXT COLLATE :"collation",
      ALTER COLUMN script_hash     SET DATA TYPE TEXT COLLATE :"collation",
      ALTER COLUMN project         SET DATA TYPE TEXT COLLATE :"collation",
      ALTER COLUMN note            SET DATA TYPE TEXT COLLATE :"collation",
      ALTER COLUMN committer_name  SET DATA TYPE TEXT COLLATE :"collation",
      ALTER COLUMN committer_email SET DATA TYPE TEXT COLLATE :"collation",
      ALTER COLUMN planner_name    SET DATA TYPE TEXT COLLATE :"collation",
      ALTER COLUMN planner_email   SET DATA TYPE TEXT COLLATE :"collation"
;

ALTER TABLE :"registry".tags
      ALTER COLUMN tag_id          SET DATA TYPE TEXT COLLATE :"collation",
      ALTER COLUMN tag             SET DATA TYPE TEXT COLLATE :"collation",
      ALTER COLUMN project         SET DATA TYPE TEXT COLLATE :"collation",
      ALTER COLUMN change_id       SET DATA TYPE TEXT COLLATE :"collation",
      ALTER COLUMN note            SET DATA TYPE TEXT COLLATE :"collation",
      ALTER COLUMN committer_name  SET DATA TYPE TEXT COLLATE :"collation",
      ALTER COLUMN committer_email SET DATA TYPE TEXT COLLATE :"collation",
      ALTER COLUMN planner_name    SET DATA TYPE TEXT COLLATE :"collation",
      ALTER COLUMN planner_email   SET DATA TYPE TEXT COLLATE :"collation"
;

ALTER TABLE :"registry".dependencies
      ALTER COLUMN change_id     SET DATA TYPE TEXT COLLATE :"collation",
      ALTER COLUMN type          SET DATA TYPE TEXT COLLATE :"collation",
      ALTER COLUMN dependency    SET DATA TYPE TEXT COLLATE :"collation",
      ALTER COLUMN dependency_id SET DATA TYPE TEXT COLLATE :"collation"
;

ALTER TABLE :"registry".events
      ALTER COLUMN change_id       SET DATA TYPE TEXT   COLLATE :"collation",
      ALTER COLUMN change          SET DATA TYPE TEXT   COLLATE :"collation",
      ALTER COLUMN project         SET DATA TYPE TEXT   COLLATE :"collation",
      ALTER COLUMN note            SET DATA TYPE TEXT   COLLATE :"collation",
      ALTER COLUMN requires        SET DATA TYPE TEXT[] COLLATE :"collation",
      ALTER COLUMN conflicts       SET DATA TYPE TEXT[] COLLATE :"collation",
      ALTER COLUMN tags            SET DATA TYPE TEXT[] COLLATE :"collation",
      ALTER COLUMN committer_name  SET DATA TYPE TEXT   COLLATE :"collation",
      ALTER COLUMN committer_email SET DATA TYPE TEXT   COLLATE :"collation",
      ALTER COLUMN planner_name    SET DATA TYPE TEXT   COLLATE :"collation",
      ALTER COLUMN planner_email   SET DATA TYPE TEXT   COLLATE :"collation"
;

COMMIT;

Use psql -set registry=sqitch --set collation=POSIX to convert all text columns to POSIX collation in the sqitch schema.