Open Stack

On Oct 7, 2014, at 8:29 AM, Ihar Hrachyshka <ihrachys at redhat.com> wrote:

> 
> That said, I wonder how we're going to manage cases when those
> *global* settings for the whole server should be really limited to
> specific databases. Isn't it better to enforce utf8 on service side,
> since we already know that we always run against utf8 database?

I think whenever we do a “CREATE DATABASE”, we should make sure the desired encodings are set up at that level.  I’ve seen lots of migration scripts that are listing through tables and setting each table individually to utf-8, and that’s less than ideal, though I suppose that’s more of a retroactive fix.

> 
> Please let me clarify... Do you say that setting client encoding on
> oslo.db side is actually ok? I haven't suggested to enforce utf8 per
> column/table, though I guess we're already there.

The way we are setting client encoding now should be fine, if you could clarify what about that isn’t working for PG 8.4 that would be helpful.    IMHO especially on Postgresql we should be able to get away with not having it.   MySQLdb is not as nicely implemented as far as encoding (including the use_unicode speed issues) so it may be more necessary there.

But yes what I really *dont* want is the encoding set up on every column definition, e.g. “VARCHAR(20) CHARACTER SET ‘utf-8’”, that’s been suggested before and that would be terrible.   I don’t think Postgresql has quite the same thing available (only collation per column).

> 
> Forgoing, again, means some users running with utf8 client encoding,
> others without it.
> I strive towards consistency here, that's why I try
> to find a way to set the setting for all clients we support (and the
> question is *which* of those clients we really support).
> 
> The thing that is not supported in psql < 9.1 is a connection option
> added to libpq as of:
> http://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=02e14562a806a96f38120c96421d39dfa7394192

OK but that is just the connection parameter, when you pass client_encoding to SQLAlchemy’s psycopg2 dialect, the encoding is set using psycopg2’s set_client_encoding() method: http://initd.org/psycopg/docs/connection.html#connection.set_client_encoding.  This ultimately emits “SET client_encoding TO ‘utf8’” on the connection:

conn_set_client_encoding -> https://github.com/psycopg/psycopg2/blob/master/psycopg/connection_int.c#L1188

pq_set_guc_locked -> https://github.com/psycopg/psycopg2/blob/master/psycopg/pqpath.c#L709

“SET client_encoding TO <value>” is supported in all Postgresql versions, here’s 8.0: http://www.postgresql.org/docs/8.0/static/multibyte.html

So there’s no issue with Postgresql 8.2 here as far as client encoding, the libpq feature isn’t used for that.   Also, it defaults to the encoding that is set for the database (e.g. server side), so if we make sure CREATE DATABASE is emitted with the encoding, then we wouldn’t even need to set it (and perhaps we shouldn’t if the database is set to a different encoding).