Re: Catastrophic changes to PostgreSQL 8.4 - Mailing list pgsql-general

On Wednesday 02 December 2009 11:33:38 pm Kern Sibbald wrote:

> > ( BTW, one way to handle incorrectly encoded filenames and paths might
> > be to have a `bytea' field that's generally null to store such mangled
> > file names. Personally though I'd favour just rejecting them. )
> >
> > > We set SQL_ASCII by default when creating the database via the command
> > > recommended in recent versions of PostgreSQL (e.g. 8.1), with:
> > >
> > > CREATE DATABASE bacula ENCODING 'SQL_ASCII';
> > >
> > > However, with PostgreSQL 8.4, the above command is ignored because the
> > > default table copied is not template0.
> >
> > 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.
>
> I didn't actually run it myself, so it is possible that it produced an
> error message, but it did apparently create the database but with UTF-8
> encoding. Most of these things are done in script files, so certain
> non-fatal errors may be overlooked.
>
> As far as I can tell, it took the above encoding command, and perhaps
> printed an error message but went ahead and created the database with an
> encoding that was not correct.  If that is indeed the case, then it is in
> my opinion, a bad design policy.  I would much prefer that either Postgres
> accept the command or that it not create the database.  This way, either
> the database would work as the user expects or there would be no database,
> and the problem would be resolved before it creates databases that cannot
> be read.

It does not CREATE the database. If the users are seeing that happen, then as
others have suggested it is a bug. The other option is that they are
un-commenting the #ENCODING="ENCODING 'UTF8'" line in the
create_postgresql_database.in script to get it to run. The interesting part in
that script is the Note:

# KES: Note: the CREATE DATABASE, probably should be
#   CREATE DATABASE ${db_name} $ENCODING TEMPLATE template0

According to the git repository this showed up in July of this year;


http://bacula.git.sourceforge.net/git/gitweb.cgi?p=bacula/bacula;a=blob;f=bacula/src/cats/create_postgresql_database.in;hb=6e024d0fe47ea0d9e6d3fbec52c4165caa44967f

>
> In any case we have corrected the command to include the TEMPLATE, but this
> won't help people with older Bacula's.

Could they not just get the corrected version of create_postgresql_database.in.
It would run on the old versions as well.

>
> The other point I wanted to emphasize is that the documentation implied
> that future versions of Postgres may eliminate the feature of having
> SQL_ASCII (i.e. the ability to input arbritrary binary strings).  As I
> said, that would be a pity -- I suppose we could switch to using LOs or
> whatever they are called in Postgres, but that would be rather
> inconvenient.

Per Tom's previous post:
"You misread it.  We are not talking about eliminating SQL_ASCII --- as
you say, that's useful.  What is deprecated is trying to use SQL_ASCII
with a non-C locale, which is dangerous, and always has been.  If you've
been putting non-UTF8 data into a database that could be running under a
UTF8-dependent locale, I'm surprised you haven't noticed problems already.'

>
> Thanks for all the responses,
>
> Best regards,
>
> Kern



--
Adrian Klaver
aklaver@comcast.net