Support creating the TEST PostgreSQL database from a custom template

[Chris Jerdonek]

Using PostgreSQL 9.3.13, when I set the TEST database setting CHARSET to UTF8 and run tests with a personal project, I get the following error:

Got an error creating the test database: new encoding (UTF8) is incompatible with the encoding of the template database (SQL_ASCII)
HINT: Use the same encoding as in the template database, or use template0 as template.

I believe the way to do this in postgres is something like:

CREATE DATABASE <name> WITH TEMPLATE template0 ENCODING 'UTF8'

(See ​here for postgres documentation.)

You can see ​here in Django's sql_table_creation_suffix() that it doesn't allow passing a template name:

def sql_table_creation_suffix(self):
    test_settings = self.connection.settings_dict['TEST']
    assert test_settings['COLLATION'] is None, (
        "PostgreSQL does not support collation setting at database creation time."
    )
    if test_settings['CHARSET']:
        return "WITH ENCODING '%s'" % test_settings['CHARSET']
    return ''

Perhaps adding a postgres-specific TEMPLATE setting to the TEST database settings would be a general way to solve this?

[Claude Paroz]

This was already handled in #17207 and #18710 as documentation/wont' fix resolutions. I still think you should fix your main template encoding.
I'm letting the ticket open for another opinion.

[Chris Jerdonek]

I still think you should fix your main template encoding.

There's no need for me to change the PostgreSQL installation or change the template encoding because I'm able to run the following CREATE statement in my environment setup:

CREATE DATABASE <name> WITH TEMPLATE template0 ENCODING 'UTF8'

But Django doesn't support this option.

If Django could clone a test database from an existing database, that would be another option. It already has syntax like the following in postgresql's _clone_test_db() to clone a test database from another test database (from ​here):

cursor.execute("CREATE DATABASE %s WITH TEMPLATE %s" % (
    qn(target_database_name), qn(source_database_name)))

So it's almost there. It's just the bootstrapping part that's missing, which we would get for free if the Django code were more DRY.

[Chris Jerdonek]

For future reference, ​here is a link to the official PostgreSQL docs, where it explains how to use other encodings without changing any installation templates. For example--

The encoding and locale settings must match those of the template database, except when template0 is used as template. ... CREATE DATABASE music ENCODING 'LATIN1' TEMPLATE template0; In this example, the TEMPLATE template0 clause would only be required if template1's encoding is not ISO-8859-1.

[Claude Paroz]

Hopefully you don't mind the summary change!

[Chris Jerdonek]

Cool, thanks! And not at all! :)

[Chris Jerdonek]

I posted a pull request for this ​here.

[Tim Graham <timograham@…>]

In a3db4803:

Fixed #27061 -- Added a TESTTEMPLATE setting for PostgreSQL.