This has already been posted to pgsql-patches, but perhaps it belongs
There is a problem in PL/TCL that can cause the postgres backend to
become multithreaded. Postgres is not designed to be multithreaded, so
this causes downstream errors in signal handling. We have seen this
cause a number of "unexpected state" errors associated with notification
handling; however, unpredictable signal handling would be likely to
cause other kinds of errors as well.
Some sample scripts are attached which will reproduce this problem when
running against a multithreaded version of TCL, but will work without
error with single-threaded TCL library. The scripts are a combination
of Unix shell, perl DBI, and SQL commands. The postgres process can be
seen to have multiple threads using the Linux command "ps -Lwfu
postgres". In this command the NLWP columns will be 2 for multithreaded
backend processes. The threaded/non-threaded state of the TCL library
can be ascertained on Linux using ldd to determine if libpthread.so is
linked to the TCL library (e.g. "ldd /usr/lib/libtcl8.4.so").
The multithreaded behavior occurs the first time PL/TCL is used in a
postgres backend, but only when postgres is linked against a
multithread-enabled version of libtcl. Thus, this problem can be
side-stepped by linking against the proper TCL library. However
multithreaded TCL libraries are becoming the norm in Linux distributions
and seems ubiquitous in the Windows world. Therefore a fix to the
PL/TCL code is warrented.
We determined that postgres became multithreaded during the creation of
the TCL interpreter in a function called tcl_InitNotifier. This
function is part of TCL's Notifier subsystem, which is used to monitor
for events asynchronously from the TCL event loop. Although initialized
when an interpreter is created, the Notifier subsystem is not used until
a process enters the TCL event loop. This never happens within a
postgres process, because postgres implements its own event loop.
Therefore the initialization of the Notifier subsystem is not necessary
within the context of PL/TCL.
Our solution was to disable the Notifier subsystem by overriding the
functions associated with it using the Tcl_SetNotifier function. This
allows 8 functions related to the Notifier to overriden. Even though we
found only two of the functions were ever called within postgres, we
overrode all 8 functions with no-op versions, just for completeness. A
patch file containing the changes to pltcl.c from its 8.2.4 version is
We tested this patch with PostgreSQL 8.2.4 on both RedHat Enterprise 4.0
usingTCL 8.4 (single threaded) and RHE 5.0 using TCL 8.4.13
(multithreaded). We expect this solution to work with Windows as well,
although we have not tested it. There may be some problems using this
solution with old versions of TCL that pre-date the Tcl_SetNotifier
function. However this function has been around for quite a while; it
was added in in the TCL 8.2 release, circa 2000.
We hope this patch will be considered for a future PostgreSQL release.
pgsql-bugs by date
|Next:||From: valgog||Date: 2007-09-12 14:41:31|
|Subject: Cannot select from 'UPDATE RETURNING'|
|Previous:||From: Albert László-Róbert||Date: 2007-09-11 11:11:02|
|Subject: Re: hu_HU.UTF8 case insensitive search fail to return values|