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

On Thursday 03 December 2009 16:42:58 Adrian Klaver wrote:
> 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;

I had forgotten about that, but it does show that I do go and read the
documentation from time to time :-)

>
> http://bacula.git.sourceforge.net/git/gitweb.cgi?p=bacula/bacula;a=blob;f=b
>acula/src/cats/create_postgresql_database.in;hb=6e024d0fe47ea0d9e6d3fbec52c4
>165caa44967f
>
> > 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.

Have you ever tried to push a fix for postgres downstream?  It usually is not
easy and takes a long time.

Anyway, now that we understand the problem, we will be able to warn users,
which is what happened when we copied the bacula-users email list, and those
who miss we can help rather quickly.

>
> > 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.'
>

Duh, yes, I misunderstood it. Another comment by Tom Lane clarified the
situation for me. I've now ensured that for future Bacula versions the
LC_COLLATE and LC_CYTPE are set to 'C' when we set SQL_ASCII.

Thanks for the help and polite comments from everyone :-)

Kern