Tuesday, July 19, 2011

Undefined symbol

Here is a small DBA tale from a few months ago. I wanted to upgrade a few servers to the latest minor release of PostgreSQL. It was the upgrade from 8.4.4 to 8.4.5. To minimize downtime, I had the following obvious idea: I'd install all the packages first, and once everything was in place and an opportune time for a service disruption had arrived, I'd just restart the services, and everything would be backup in less than a minute. (If you just let the package manager do its thing, it's always a bit unpredictable when the services are stopped and restarted, which might result several minutes of downtime.)

This operation had dramatic consequences. As soon as I had the new packages installed, I got massive amounts of these errors:

ERROR: could not load library "/usr/lib/postgresql/8.4/lib/plpgsql.so": /usr/lib/postgresql/8.4/lib/plpgsql.so: undefined symbol: PinPortal

Since a lot of things use PL/pgSQL, this meant that the database system was pretty much dead at this point.

What had happened?

Well, version 8.4.5 introduced some new functions in the server that PL/pgSQL made use of. As soon as you install the new plpgsql module, the next session that starts will load the new libary, but unless you restarted the server, the running server process won't have that symbol.

It was easy to fix this by restarting the server, but that's not how things were planned.

Consequence: Unless shown otherwise and a lot of care is taken, you must upgrade the server and all loadable modules to exactly matching versions and at the same time, and you must restart the server at the same time.

It might be useful as a preventive measure to compare symbol tables before upgrading. It could also be tracked more carefully at the source level, by explicitly mentioning the addition of meant-to-be-called-externally functions to the server in the release notes. (I suppose we don't usually remove functions, but who knows.)


  1. I assume using shared_preload_libraries to load plpgsql.so would have avoided this specific problem, right? I do agree that installing the binaries/libraries while the server is down is the safest.

  2. It often seems like overkill, but for OmniTI's managed hosting business, we've taken the stand that even minor versions deserve their own install directories. You don't have to get bitten too many times by issues like the one above to start to feel it's worth it.

  3. @Bruce: Yes, I think shared_preload_libraries would help.