Re: Changes to pg_dump/psql following collation "C" in the catalog

"Daniel Verite" <daniel(at)manitou-mail(dot)org> writes:
> One consequence of using the "C" collation in the catalog versus
> the db collation is that pg_dump -t with a regexp may not find
> the same tables as before. It happens when these conditions are
> all met:
> - the collation of the database is not "C"
> - the regexp has locale-dependant parts
> - the names to match include characters that are sensitive to
> locale-dependant matching

Hm, interesting.

> It seems that to fix that, we could qualify the references to columns such
> as "relname" and "schema_name" with COLLATE "default" clauses in the
> queries that use pattern-matching in client-side tools, AFAICS
> pg_dump and psql.

Seems reasonable. I was initially worried that this might interfere with
query optimization, but some experimentation says that the planner
successfully derives prefix index clauses anyway (which is correct,
because matching a fixed regex prefix doesn't depend on locale).

It might be better to attach the COLLATE clause to the pattern constant
instead of the column name; that'd be less likely to break if sent to
an older server.

> Before going any further with this idea, is there agreement that it's an
> issue to address and does this look like the best way to do that?

That is a question worth asking. We're going to be forcing people to get
used to this when working directly in SQL, so I don't know if masking it
in a subset of tools is really a win or not.

regards, tom lane