Could not load library "/usr/lib/pgsql/plpgsql.so" & undefined symbol: PinPortal

Question

I have been running Davical on a CentOS 5 box for a while now with no problems.

Yesterday however, I installed Trac bug-tracker which eventually forced me to run a full update via Yum which updated a whole heap of packages.

I cant seem to work out exactly what the issue is and time spent googling didn't seem to bring about much in the way of ideas.

Has anyone had the same problem or could anyone indicate a way to better identify whats going on?

Many Thanks!

Full Error readout :

[Wed May 11 17:52:53 2011] [error] davical: LOG: always: Query: QF: SQL error "58P01" - ERROR: could not load library "/usr/lib/pgsql/plpgsql.so": /usr/lib/pgsql/plpgsql.so: undefined symbol: PinPortal"

Checking to see if file exists

[@shogun ~]# tree -a /usr/lib/pgsql/ | grep "plpgsql"

|-- plpgsql.so

Version of pg installed

[@shogun ~]# pg_config | grep "VERSION"

VERSION = PostgreSQL 8.1.23

[@shogun postgresql-8.3.8]# yum list installed | grep 'post'

postgresql.i386 8.1.23-1.el5_6.1 installed

postgresql-devel.i386 8.1.23-1.el5_6.1 installed

postgresql-libs.i386 8.1.23-1.el5_6.1 installed

postgresql-python.i386 8.1.23-1.el5_6.1 installed

postgresql-server.i386 8.1.23-1.el5_6.1 installed

Precast

It's version 8.1.23 - and to be quite honest, there were so many packages updated I didn't even notice. I don't think my Yum has a history manager either, is there a way to find out do you know? – Precast

Hmm. I've only ever installed it through Yum and checking through there there only seems to be one version installed : – Precast

Answer 1

I have had this problem before, although with 8.4 instead of 8.1, but the issue is the same, I believe.

A recent minor upgrade of all supported maintenance branches of PostgreSQL introduced the function PinPortal in the server, and made PL/pgSQL use it. So if you use a plpgsql.so from the newer version with a server from the older version, you will get this error. In your case, the change happened between 8.1.21 and 8.1.22. And even if all your installed packages show the newer version, you need to restart the server to make sure you actually use the newer version.

The problem is, as soon as you install the newer PL/pgSQL, it will get used by the next session that is started, but the newer server binary won't get used until you restart the server. So if your upgrade process doesn't restart the server immediately, you will invariably get these errors as soon as something tries to use PL/pgSQL. If this actually turns out to be the problem, you might want to review why your server wasn't restarted.

Weswesa

Same happened to me after a recent MacPorts upgrade. New MacPorts postgres packages provide select and load functionality, execute both and maybe kill your running server, launchd will restart a new perfectly fine one. – Gutter