Hello,
Thanks for your response.
On Thursday 03 December 2009 16:46:54 Tom Lane wrote:
> Sam Mason <sam@samason.me.uk> writes:
> > As others have said; BYTEA is probably the best datatype for you to
> > use. The encoding of BYTEA literals is a bit of a fiddle and may need
> > some changes, but it's going to be much more faithful to your needs of
> > treating the filename as an opaque blob of data.
>
> bytea might be theoretically the best choice, but the fact remains that
> 99% of the entries will be text that's readable in the user's encoding
> (whatever that is). bytea will just be a serious PITA because of its
> escaping issues. Also, the fact that 8.5 may change to hex display by
> default will make bytea even more of a PITA for mostly-text data.
> So I think Bacula's choice to use sql_ascii with text columns is
> entirely defensible.
Thank you for confirming this. I do appreciate the SQL_ASCII feature that
PosgreSQL has. It is far preferable for our users than the MySQL BLOB
solution, because as you say, most of the filenames do go in as valid UTF-8,
and those that don't are backed up and restored correctly, but may have a few
problems during display of their names, which is rather rare anyway.
>
> What concerns me is the claim that PG made a database with some
> arbitrary parameters after having rejected a now-considered-invalid
> command. I frankly do not believe that, but if it did happen it's
> a *serious* bug that requires investigation.
Concerning the problem with specifying ENCODING 'SQL_ASCII' but not TEMPLATE
template0: it appears that Postgres *did* reject the command and did not
create a database with SQL_UTF8 as I had supposed, so as you say, it is not a
PosgreSQL bug. The best I can tell is that the command failed, so the user
manually submitted it, probably without the ENCODING and so it ended up with
the wrong type.
I just fixed our Bacula create_postgresql_database script so that it clearly
detects any problem creating the database (ON_ERROR_STOP), which is was not
previously the case.
Concerning the future: I am still a bit concerning about the mention in the
document of possible future changes concerning SQL_ASCII and LC_CTYPE not C
or POSIX ("Using this combination of settings is deprecated and may someday
be forbidden altogether. "), so if you have any suggestions for CREATE
DATABASE that we can use now that will ensure that for future versions of
Bacula + PostgreSQL that we will end up with our bacula database created with
SQL_ASCII, I would much appreciate it.
Best regards,
Kern
