Re: BUG #17170: Invalid collation created with provider icu and initdb' locale C

PG Bug reporting form <noreply@postgresql.org> writes:
> When using postgres built "--with-icu" I get an invalid collation with the
> following script:
> initdb -D db --locale=C
> createdb testdb
> psql testdb -c "CREATE COLLATION test (provider=icu,
> locale='en_US.utf8');"
> psql testdb -c "SELECT 'a' < 'b' COLLATE test;"
> ERROR:  collation "test" for encoding "SQL_ASCII" does not exist
> LINE 1: SELECT 'a' < 'b' COLLATE test;
>                          ^

That error doesn't seem inappropriate; ICU collations don't work
in SQL_ASCII encoding.

> psql testdb -c "DROP COLLATION test;"
> ERROR:  collation "test" for encoding "SQL_ASCII" does not exist

That, however, is a bit annoying.  If you can create the collation
you should be able to drop it.

The immediate issue is that DROP COLLATION uses the same lookup
rule as other code paths, so it fails to see inapplicable
pg_collation entries at all.  So one approach to a fix is to
relax its lookup rule.  But I wonder if we should have prevented
the CREATE in the first place, instead.

Unfortunately, either approach is likely to still have some
edge-case odd behaviors :-(.  This whole business of having the
database encoding be implicitly part of the lookup rule is just
prone to that.

            regards, tom lane