External functions/languages and transactions

Philip Warner wrote:

Dear All,

I've been working (a little) on external functions and laguages recently, and was wondering how PG handles updates to libraries and/or stored procedures in the context of transactions. This is probably most relevant to Perl, but really applies to any external function.

If:

1. user A starts a TX, and calls a user defined function in userlib.so,
2. user B changes, recompiles, and reloads the shared library
3. user A calls the same function within the same TX.

Does PG prevent the 'load' command by user B (via locking)?
Does user A get a different result?
Does user A's backend not load the new module until after a commit?

In the extreme case this is obvoiusly totally outside the control of PostgreSQL, but in the case of Perl, which includes all sorts of external files, should the implementor of PL/Perl be careful to NOT update already loaded module? Or to update them ASAP?

This is probably too unlikely to worry about, but I am (academically) interested...

It's mostly OS dependant. PostgreSQL itself load's a shared
object only the first time it is required PER BACKEND. It
remembers which objects have been loaded by path. So for the
entire lifetime of a connection, PostgreSQL will never reload
a once dynamically linked library. So the new functionality
will only show up if you reconnect.

If the OS supports writing into shared objects that are in
use, and what would happen if it is done - dunno! Some might
allow it but the backend potentially dumps core - some might
deny write access - some might whatever.

Under every UNIX operating system, there's a clean way you
can protect the backend from any danger. Install the shared
objects in a common place (like PGLIB) and inside the install
target, first remove an eventually existing old version. That
would cause that the real deletion of the old version will be
delayed by the OS until the last backend using it has
terminated, but the directory is ready to receive the new
version.

Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#========================================= wieck@debis.com (Jan Wieck) #