Hmm... the database itself predates me, so I can't say for sure what
encoding it was created with, but when I did a "pg_dumpall -s" it
showed every database in the cluster uses "SET client_encoding =
'UTF8';"
On Thu, May 9, 2013 at 7:25 PM, Bruce Momjian <bruce@momjian.us> wrote:
> On Thu, May 9, 2013 at 05:41:39PM -0400, Evan D. Hoffman wrote:
>> I believe the history of this cluster is that it started on 9.0 and
>> was upgraded to 9.1 via pg_upgrade. The instance I'm working on was
>> created as a streaming replica, then I broke the replication to make
>> it a standalone master specifically for testing pg_upgrade to 9.2.
>
> OK, I did some research on this, using a stripped-down copy of his table
> schema:
>
> CREATE TABLE test(
> x1 bigint,
> x2 bigint,
> x3 CHARACTER varying(40),
> x4 CHARACTER varying(40),
> x5 CHARACTER varying(40),
> x6 CHARACTER varying(40),
> x7 CHARACTER varying(40),
> x8 CHARACTER varying(40),
> x9 boolean,
> x10 CHARACTER varying(40),
> x11 boolean,
> x12 CHARACTER varying(100),
> x13 CHARACTER varying(100),
> x14 DATE,
> x15 DATE,
> x16 CHARACTER varying(10),
> x17 CHARACTER varying(10)
> );
>
> Using my default UTF8 encoding, I see a TOAST table created for all
> versions of Postgres, 9.0 through 9.3. However, if I create a database
> with C locale/Latin1 encoding:
>
> CREATE DATABASE test3 ENCODING 'LATIN1' LC_COLLATE 'C'
> LC_CTYPE 'C' TEMPLATE template0;
>
> I then get a table with no TOAST table. Could the encoding of this
> database have changed? I know pg_upgrade has always tests to see
> old/new clusters had matching "LC_COLLATE" and "LC_CTYPE" match. It is
> possible that the encodings were changed, and pg_upgrade didn't test
> that? I don't think so because pg_dumpall is the one who creates the
> new databases with matching encodings.
>
> Could someone have manually changed the encoding of the 9.1 database in
> the system tables? If so, that would cause this problem.
>
> --
> Bruce Momjian <bruce@momjian.us> http://momjian.us
> EnterpriseDB http://enterprisedb.com
>
> + It's impossible for everything to be true. +
