Re: Why do we let CREATE DATABASE reassign encoding?

In response to Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>:

> If I have locale set to C, I can do this:
>
> regression=# create database u8 encoding 'utf8';
> CREATE DATABASE
> regression=# create database l1 encoding 'latin1' template u8;
> CREATE DATABASE
>
> Had I had any actual utf8 data in u8, l1 would now contain
> encoding-corrupt information. Given that we've tried to
> clamp down on encoding violations in recent releases, I wonder
> why this case is still allowed.
>
> (In non-C locales, this will typically fail because the two
> different encodings can't both match the locale. But I don't
> believe it's our policy to enforce encoding validity only for
> non-C locales.)
>
> We should presumably let the encoding be changed when cloning
> from template0, and probably it's reasonable to trust the user
> if either source or destination DB encoding is SQL_ASCII.
> In other cases I'm thinking it should fail.

On a pedantic level, doesn't this remove the ability to have
databases on a single cluster that are different encodings? I mean,
if template1 is utf8, and I can't change that using CREATE
DATABASE, then I'm stuck with utf8 for all databases on that
cluster ... unless I'm missing something.

Granted, there's the potential for special cases with databases used
only for templates, but as I see it, this should be allowed, it should
just fail if any data in the template can't be converted to the
desired encoding. I mean, I can always alter template1 by inserting
non-utf8 data, and then try to use it to create a utf8 encoded
database ...

--
Bill Moran
http://www.potentialtech.com
http://people.collaborativefusion.com/~wmoran/