Thread: Vacuuming unicode database

My Postgres databases used to have default (SQL_ASCII) encoding. I could
store any 8-bit character in it regardless of actual charset, because
all clients also used default encoding and no charset conversion was
done.

Now we started a new project with Qt3 and it's Postgres driver defaults
to UNICODE client encoding. This time charset conversion comes to play
and messes up all 8-bit characters. This forces me to create all
databases with correct charsets. So I recreated my database with UNICODE
encoding and imported all data in it, paying attention to client
encoding. I didn't re-initdb whole cluster, only recreated problematic
database. Everything seems to work fine, only I can't vacuum the
database:

epos=# vacuum verbose analyze;
INFO:  --Relation pg_catalog.pg_description--
INFO:  Pages 18: Changed 0, Empty 0; Tup 1895: Vac 0, Keep 0, UnUsed 5.
        Total CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  --Relation pg_toast.pg_toast_16416--
INFO:  Pages 0: Changed 0, Empty 0; Tup 0: Vac 0, Keep 0, UnUsed 0.
        Total CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  Analyzing pg_catalog.pg_description
INFO:  --Relation pg_catalog.pg_group--
INFO:  Pages 1: Changed 0, Empty 0; Tup 31: Vac 0, Keep 0, UnUsed 31.
        Total CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  --Relation pg_toast.pg_toast_1261--
INFO:  Pages 0: Changed 0, Empty 0; Tup 0: Vac 0, Keep 0, UnUsed 0.
        Total CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  Analyzing pg_catalog.pg_group
ERROR:  Invalid UNICODE character sequence found (0xdc6b)

Table pg_group is giving errors, because I have group name with 8-bit
characters in it. As I understand, groups are common for all databases
and pg_group is created during initdb, so it should be considered having
SQL_ASCII charset, not UNICODE. Seems like a bug to me?

What would you suggest in this case:
1. Re-initdb with UNICODE encoding and recreate all databases. Basically
all databases should have the same encoding.
2. Use some single-byte encoding for database, instead of UNICODE.
Vacuuming wouldn't complain any more, but I have some doubts that CREATE
GROUP "Name with 8-bit characters" behaves differently depending on
encoding of the active database.

  Tambet

"Tambet Matiisen" <t.matiisen@aprote.ee> writes:
> Table pg_group is giving errors, because I have group name with 8-bit
> characters in it. As I understand, groups are common for all databases
> and pg_group is created during initdb, so it should be considered having
> SQL_ASCII charset, not UNICODE. Seems like a bug to me?

Unfortunately, we don't have any way of dealing with different character
sets or locales in different tables.  (AFAICT this is not practical
without implementing our own locale library, which would be an enormous
task; someday we will solve this problem, but don't hold your breath.)
So pg_shadow, pg_group, and pg_database are all risk spots.

I think the best advice is to limit your user/group/database names to
7-bit-ASCII if you are going to use different encodings in different
databases.  That way, they'll look valid in all databases.

            regards, tom lane

On Wed, Aug 13, 2003 at 12:50:03PM -0400, Tom Lane wrote:
> Unfortunately, we don't have any way of dealing with different character
> sets or locales in different tables.  (AFAICT this is not practical
> without implementing our own locale library, which would be an enormous
> task; someday we will solve this problem, but don't hold your breath.)
> So pg_shadow, pg_group, and pg_database are all risk spots.

All this stuffing around with multiple locales and collating stuff on the
fly. Surely we're not the only project to have to deal with this problem?
Someone must have come up witha set of routines to deal with this. If it
can be abstracted into a library to share around it could be made much
easier.

--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> "All that is needed for the forces of evil to triumph is for enough good
> men to do nothing." - Edmond Burke
> "The penalty good people pay for not being interested in politics is to be
> governed by people worse than themselves." - Plato