本文探讨了PostgreSQL中client_encoding配置的实际效果，并通过源代码分析揭示了其背后的机制。当使用psql连接到数据库时，实际使用的字符集由数据库自身编码决定而非postgresql.conf文件中的设置。
磨砺技术珠矶，践行数据之道，追求卓越价值
回到上一级页面：PostgreSQL内部结构与源代码研究索引页 回到顶级页面：PostgreSQL索引页
[作者 高健@博客园 luckyjackgao@gmail.com]
我的PPAS下，edb数据库的Encoding是 UTF8：
edb=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access
privileges
-----------+--------------+----------+-------------+-------------+--------------
-----------------
edb | enterprisedb | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
postgres | enterprisedb | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
template0 | enterprisedb | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/enterprisedb +
| | | | | enterprisedb=CTc/enterprisedb
template1 | enterprisedb | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/enterprisedb +
| | | | | enterprisedb=CTc/enterprisedb
(4 rows)
edb=# 。
而我在postgresql.conf里面去设置 client_encoding，无论如何也无法生效。
client_encoding = sql_ascii # actually, defaults to database
# encoding
重新启动后也不行：
edb=# show client_encoding;
client_encoding
-----------------
UTF8
(1 row)
edb=#
也就是说，client_encoding的值，就算是设置了，也未必起作用。
查看社区版PostgreSQL的源代码作参考，看看是为何：
当我用psql连接到数据库时，CheckMyDatabase函数就会被执行。
我注意到其中的这一段：
/* If we have no other source of client_encoding, use server encoding */
SetConfigOption("client_encoding", GetDatabaseEncodingName(),
PGC_BACKEND, PGC_S_DYNAMIC_DEFAULT);
在准备client_encoding的时候，它用了 GetDatabaseEncodingName()函数，来返回数据库的Encoding名称。
可以认为，其实postgresql.conf里的 client_encoding的设定是没有用处的，因为内部运算的时候直接拿来了所连接的数据库的Encoding。也可以说，client_encoding是一个历史遗留问题，是PostgreSQL的开发者的失误造成的！
/*
* CheckMyDatabase -- fetch information from the pg_database entry for our DB
*/
static void
CheckMyDatabase(const char *name, bool am_superuser)
{
HeapTuple tup;
Form_pg_database dbform;
char *collate;
char *ctype;
/* Fetch our pg_database row normally, via syscache */
tup = SearchSysCache1(DATABASEOID, ObjectIdGetDatum(MyDatabaseId));
if (!HeapTupleIsValid(tup))
elog(ERROR, "cache lookup failed for database %u", MyDatabaseId);
dbform = (Form_pg_database) GETSTRUCT(tup);
/* This recheck is strictly paranoia */
if (strcmp(name, NameStr(dbform->datname)) != 0)
ereport(FATAL,
(errcode(ERRCODE_UNDEFINED_DATABASE),
errmsg("database \"%s\" has disappeared from pg_database",
name),
errdetail("Database OID %u now seems to belong to \"%s\".",
MyDatabaseId, NameStr(dbform->datname))));
/*
* Check permissions to connect to the database.
* These checks are not enforced when in standalone mode, so that there is
* a way to recover from disabling all access to all databases, for
* example "UPDATE pg_database SET datallowconn = false;".
*
* We do not enforce them for autovacuum worker processes either.
*/
if (IsUnderPostmaster && !IsAutoVacuumWorkerProcess())
{
/*
* Check that the database is currently allowing connections.
*/
if (!dbform->datallowconn)
ereport(FATAL,
(errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE),
errmsg("database \"%s\" is not currently accepting connections",
name)));
/*
* Check privilege to connect to the database. (The am_superuser test
* is redundant, but since we have the flag, might as well check it
* and save a few cycles.)
*/
if (!am_superuser &&
pg_database_aclcheck(MyDatabaseId, GetUserId(),
ACL_CONNECT) != ACLCHECK_OK)
ereport(FATAL,
(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
errmsg("permission denied for database \"%s\"", name),
errdetail("User does not have CONNECT privilege.")));
/*
* Check connection limit for this database.
*
* There is a race condition here --- we create our PGPROC before
* checking for other PGPROCs. If two backends did this at about the
* same time, they might both think they were over the limit, while
* ideally one should succeed and one fail. Getting that to work
* exactly seems more trouble than it is worth, however; instead we
* just document that the connection limit is approximate.
*/
if (dbform->datconnlimit >= 0 &&
!am_superuser &&
CountDBBackends(MyDatabaseId) > dbform->datconnlimit)
ereport(FATAL,
(errcode(ERRCODE_TOO_MANY_CONNECTIONS),
errmsg("too many connections for database \"%s\"",
name)));
}
/*
* OK, we're golden. Next to-do item is to save the encoding info out of
* the pg_database tuple.
*/
SetDatabaseEncoding(dbform->encoding);
/* Record it as a GUC internal option, too */
SetConfigOption("server_encoding", GetDatabaseEncodingName(),
PGC_INTERNAL, PGC_S_OVERRIDE);
/* If we have no other source of client_encoding, use server encoding */
SetConfigOption("client_encoding", GetDatabaseEncodingName(),
PGC_BACKEND, PGC_S_DYNAMIC_DEFAULT);
/* assign locale variables */
collate = NameStr(dbform->datcollate);
ctype = NameStr(dbform->datctype);
if (pg_perm_setlocale(LC_COLLATE, collate) == NULL)
ereport(FATAL,
(errmsg("database locale is incompatible with operating system"),
errdetail("The database was initialized with LC_COLLATE \"%s\", "
" which is not recognized by setlocale().", collate),
errhint("Recreate the database with another locale or install the missing locale.")));
if (pg_perm_setlocale(LC_CTYPE, ctype) == NULL)
ereport(FATAL,
(errmsg("database locale is incompatible with operating system"),
errdetail("The database was initialized with LC_CTYPE \"%s\", "
" which is not recognized by setlocale().", ctype),
errhint("Recreate the database with another locale or install the missing locale.")));
/* Make the locale settings visible as GUC variables, too */
SetConfigOption("lc_collate", collate, PGC_INTERNAL, PGC_S_OVERRIDE);
SetConfigOption("lc_ctype", ctype, PGC_INTERNAL, PGC_S_OVERRIDE);
/* Use the right encoding in translated messages */
#ifdef ENABLE_NLS
pg_bind_textdomain_codeset(textdomain(NULL));
#endif
ReleaseSysCache(tup);
}
[作者 高健@博客园 luckyjackgao@gmail.com]
回到上一级页面：PostgreSQL内部结构与源代码研究索引页 回到顶级页面：PostgreSQL索引页
磨砺技术珠矶，践行数据之道，追求卓越价值
扫一扫
4931
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
