Re: Why do we let CREATE DATABASE reassign encoding?

So it would still be possible to byass this check by cloning a
database into SQL_ASCII and then cloning it into the desired encoding?
Doesn't sound like it really accomplishes much.

I do seem to recall some discussion about this way back. I don't
recall the conclusion but I remember some talk about detecting an
empty template database and the ned to reindex.

--
Greg

On 23 Apr 2009, at 19:46, Bill Moran <wmoran(at)potentialtech(dot)com> wrote:

> 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/
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers