Re: Catastrophic changes to PostgreSQL 8.4

On 3/12/2009 11:03 AM, Tom Lane wrote:
> Craig Ringer<craig(at)postnewspapers(dot)com(dot)au> writes:
>> It's a pity that attempting to specify an encoding other than the safe
>> one when using a non-template0 database doesn't cause the CREATE
>> DATABASE command to fail with an error.
>
> Huh?
>
> regression=# create database foo lc_ctype = 'en_US.utf8' encoding = 'latin1';
> ERROR: encoding LATIN1 does not match locale en_US.utf8
> DETAIL: The chosen LC_CTYPE setting requires encoding UTF8.
>
> As best I can tell, the OP is complaining exactly because it did fail,
> in contrast to pre-8.4 versions that let him create unsafe combinations.

Teach me not to check it myself before saying anything. I read the OP's
post as saying that they were having issues with creation _succeeding_
but disregarding the encoding specification:

> CREATE DATABASE bacula ENCODING 'SQL_ASCII';
>
> However, with PostgreSQL 8.4, the above command is ignored because the default
> table copied is not template0. This means that some Bacula users who have
> created PostgreSQL databases with version 8.4, typically find them created
> with SQL_UTF8 format, which results in serious errors when doing backups for
> certain machines.

... which would indeed be nasty. That's what I was commenting on.
Failing to create an unsafe database, on the other hand, is just what I
want to happen, and it's good to see that's what happens on my 8.4
systems. I'm confused by the above, though.

Kern: Is the issue that the CREATE DATABASE is failing, or that it's
succeeding incorrectly? If it's succeeding incorrectly, it'd really help
to know more about the problem systems.

The creation script shipped in 3.0.2 was:

#
# KES: Note: the CREATE DATABASE, probably should be
# CREATE DATABASE ${db_name} $ENCODING TEMPLATE template0
#
if $bindir/psql -f - -d template1 $* <<END-OF-DATA
CREATE DATABASE ${db_name} $ENCODING;
ALTER DATABASE ${db_name} SET datestyle TO 'ISO, YMD';
END-OF-DATA
then
echo "Creation of ${db_name} database succeeded."
else
echo "Creation of ${db_name} database failed."
fi
exit 0

... which doesn't look like it'd do anything unexpected to template1 if
the CREATE DATABASE failed. You might want to set ON_ERROR_STOP in psql
anyway, though.

--
Craig Ringer