2

In a pre-production environment running django 1.6 on PostgreSQL 9.1 we had a problem with database records arriving in a different order from what the application was expecting. So we ran this command as the postgres user:

update pg_database set datcollate='C' where datname='appname';

About a week later it was suddenly impossible to log into the application as a user which did exist in the database and had been unchanged for years. Attempts at looking up the user by username fails but looking up the same user by primary key worked, and the returned username was in fact correct.

That problem was fixed by reindexing the database like this:

REINDEX DATABASE appname;

Is it possible that the update command has broken the indexes? If that update command is indeed unsafe, then what is the safe way to change collation on a PostgreSQL database?

In case it is relevant, these are the queries that django generates for looking up the user by username or primary key:

SELECT "auth_user"."id", "auth_user"."password", "auth_user"."last_login", "auth_user"."is_superuser", "auth_user"."username", "auth_user"."first_name", "auth_user"."last_name", "auth_user"."email", "auth_user"."is_staff", "auth_user"."is_active", "auth_user"."date_joined" FROM "auth_user" WHERE "auth_user"."username" = username 
SELECT "auth_user"."id", "auth_user"."password", "auth_user"."last_login", "auth_user"."is_superuser", "auth_user"."username", "auth_user"."first_name", "auth_user"."last_name", "auth_user"."email", "auth_user"."is_staff", "auth_user"."is_active", "auth_user"."date_joined" FROM "auth_user" WHERE "auth_user"."id" = 1 
  • 9
    Do not just go UPDATEing the catalogs. Really. – Craig Ringer Apr 29 '15 at 17:23
4

The datcollate column of pg_database stores

LC_COLLATE for this database

An other page of the documentation about collations says:

The collation feature allows specifying the sort order and character classification behavior of data per-column, or even per-operation. This alleviates the restriction that the LC_COLLATE and LC_CTYPE settings of a database cannot be changed after its creation.

That means that by updating that column you managed to cross some river that should not be crossed under any circumstances.

On the other hand, as collations affect the ordering of text, varchar and varchar typed columns (and possibly some others, check the documentation page for details). This also means that the existing indexes on such columns will be ordered differently from what the LC_COLLATE dictates. This will result of some weird behaviour, and as you've seen, at least a REINDEX will be needed. I would expect that doing a

SET enable_indexscan TO off;

would help you finding the user by username, because it would do a sequential scan instead of an index scan, so not trying to use the unusable index.

I cannot tell if this is enough to fix your DB, but I would do the following instead:

  1. set up a new cluster with the desired collation
  2. take a dump from your DB
  3. restore it on the new cluster, watching for errors
  4. test it thoroughly and then start using it instead of the old one

For the future, you cannot change the DB level collation, but you can define collation on the column level.

One can even change the collation on a column like

ALTER TABLE t ALTER COLUMN col1 TYPE text COLLATE "en_US";

where you keep the type as it is, just set a different collation.

Just a final notice: changing the system catalogs directly should always be a last resort. Before doing that (probably except adding new enum values in pre-9.1 versions), one should carefully examine the possible outcomes from the documentation, blog or mailing list posts and testing.

| improve this answer | |
  • Unfortunately from what I can tell Django doesn't support column level collation. This question which I found through Google seems to agree: stackoverflow.com/questions/29144468/… – kasperd Apr 29 '15 at 12:55
  • Can you directly connect to the database with some sufficiently powerful user? If yes, you still can fix the collation - I'll edit the answer with it. – dezso Apr 29 '15 at 13:04
  • I have root access on the server, so I can access the psql command line as any user I need. But as updates to the app are deployed there will be new tables and columns added by auto-generated migration scripts. Any approach that would not ensure those new tables and columns automatically have correct collation would be considered a non-solution. – kasperd Apr 29 '15 at 13:09
  • In this case, you'll have to create a new cluster and so on (unless you are sure that the current DB is not corrupted somehow). – dezso Apr 29 '15 at 13:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.