Binary vs. SQL compatibility during upgrades

[marcoslot]

One of the underappreciated problems in extension building is the SQL vs. binary compatibility. Once you install a new version of an extension that has both SQL files and a binary, two things can happen:

1. The user runs ALTER EXTENSION myext UPDATE, now the SQL is ahead of the binary and PostgreSQL could crash if the extension code is invoked before restart.
2. The user restarts PostgreSQL, now the binary is ahead of the SQL and PostgreSQL could crash if the extension code is invoked before ALTER EXTENSION myext UPDATE.

Unfortunately, there is no third option that does not involve PostgreSQL possibly crashing or maybe even getting corrupted. The binary & SQL never update at the same time.

The issue could be as simple as adding an argument to a function. In scenario 2 (the more common upgrade scenario), invoking the function before ALTER EXTENSION would cause one of the parameters to have an undefined value within the implementation. Consider that the user might be upgrading over several versions, so the binary will have to take into account many different SQL schemas. It gets even hairier if the extension is in shared_preload_libraries, because PostgreSQL might crash before the user gets to run ALTER EXTENSION.

Many extensions ignore the issue, but for more advanced and mission-critical extensions it's not really an option.

In Citus, we addressed it by blocking scenario 1 and checking the SQL version in every entry-point of the implementation, including every UDF (!). That means every function throws an error until the user runs ALTER EXTENSION to prevent crashes.

In pg_cron, I try to write all the C code with the expectation that the SQL may be behind. For instance, functions that have added new arguments always check the actual number of arguments of the invocation before assuming they are defined.

Curious how others have dealt with this, and what can be done to improve the situation.

It would be nice if PostgreSQL auto-updated extensions on start-up, though updates can fail, and sometimes fail deliberately (e.g. if a deprecated feature is in use and updating would cause data loss), and that should not prevent the server from starting.

[yrashk]

Marco, thank you for sharing!

I 100% agree that this is, overall, a very serious issue. Just about anything database is kind of mission-critical.

Few questions:

1. I wonder if, in your cases, you use the same name for the .so file across versions leading to the scenarios you described?

2. You also mentioned restarts. Are you referring to anything else beyond preloaded libraries and their interaction with their extension/SQL counterparts?

In my experience, I ran into a related issue with differently named .so files across versions, where a simple upgrade without changing functions leaves pg_proc entries pointing to the old files (which is extremely unsafe). omni extensions that I developed addresses this in the extension update utility hook ensuring no dangling references on upgrade.

I think being defensive and checking all conditions upon every UDF is excessive and not conducive to performance. Furthermore, this would imply that background workers need to run the same defensive checks for every transaction.

One of the ideas I proposed at PGConf.dev 2024 was to embed the source of truth of native exports (such as functions) into the .so file instead of SQL. It's certainly a departure from what we have today. It requires either patching Postgres or adding new functionality to behaviour-altering extensions like omni (which I am all for, as its purpose is to be an iterative, production-capable experimentation area before we extract something to the core). What do you think?

[marcoslot]

Renaming binaries is interesting, but if the goal is to let multiple versions coexist across backends, then that does not seem very safe either (what about shared memory, locking, invalidations, conflicting symbols) and also not universally applicable (shared_preload_libraries, decoders, archive modules, etc.).

I think in most production environments / managed service architectures, binaries are immutable for the lifetime of the postmaster process (upgrade = new container instance) or the lifetime of the server (upgrade = switchover). Scenario 1 happens only in development and monkey patching scenarios, so scenario 2 (surviving from server start with new binaries and old SQL until ALTER EXTENSION) seems like the main problem to solve.

I think being defensive and checking all conditions upon every UDF is excessive and not conducive to performance.

In Citus it's only checked once per process and then cached in a global boolean variable (except in the error case). It's mostly very tedious to add the checks and easy to forget.

Furthermore, this would imply that background workers need to run the same defensive checks for every transaction.

That's definitely an issue. Citus uses per-database worker and doesn't start them until the SQL in that database is up-to-date, so that helps a bit. The pg_cron background worker primarily interacts with its catalogs via SPI, so if the schema does not match the query it'll get a parse error and the background worker gets into a restart loop. Not pretty, but better than crashing.

embed the source of truth of native exports (such as functions) into the .so file instead of SQL.

I think it would make sense for functions. I've seen this model in DuckDB extensions which register all the UDF when the module gets loaded. A challenge is that PostgreSQL does not have any kind of catalog or parse-time hooks.

[yrashk]

Renaming binaries is interesting, but if the goal is to let multiple versions coexist across backends, then that does not seem very safe either (what about shared memory, locking, invalidations, conflicting symbols) and also not universally applicable (shared_preload_libraries, decoders, archive modules, etc.).

I didn't mean renaming binaries on the fly during the postmaster's lifetime. That could indeed lead to (at the very least) some undefined scenarios or worse. What I meant was rather following a versioning scheme for native modules (like omni_httpd--0.1.so, omni_httpd--0.2.so, and so on) so that they are indeed immutable, and we always know what we refer to.

This does lead to the issue of "stale" records in pg_proc as outlined before – but it is solvable: from manual "refresh" to the hook that omni installs, or native library exports. Another idea I shared last week at PGConf.dev was to consider pg_proc have a different way to refer to extension's function. Instead of pointing to the file, point to the extension. This does have some limitations, but they are not unsolvable either: 1) the approach based on the [current] assumption of one and only one version of extension at a time per database; 2) assumes only one native module per extension, which is enforced by the control file but not by anything else – SQL scripts are free to involve however many files.

That's definitely an issue. Citus uses per-database worker and doesn't start them until the SQL in that database is up-to-date, so that helps a bit. The pg_cron background worker primarily interacts with its catalogs via SPI, so if the schema does not match the query it'll get a parse error and the background worker gets into a restart loop. Not pretty, but better than crashing.

Having background workers operating cleanly requires precise timing of communication to ensure they do get the right schema. I've addressed this before with some "clever" tricks that allow me to signal background workers after the changes are visible to all backends. Still not perfect, either.

I think it would make sense for functions. I've seen this model in DuckDB extensions which register all the UDF when the module gets loaded. A challenge is that PostgreSQL does not have any kind of catalog or parse-time hooks.

I suppose this would mean that changes of these magnitude would likely require an upstream patch. I'd love to find a workaround to try playing with this before going that way.

At the very least I can experiment with this approach in omni where it already defines its own "layout" of a native module and can handle things at a load time and has hooks to handle load and unload; therefore, it would be able to extract the export definitions, provision them in the pg_proc catalog and drop them when they are retired.

[pramsey]

Versioning the binaries just results in a different set of problems, we found, mostly relating to the fact that packagers choose to only build out one version of the extension per PgSQL version. So while you can theoretically have multiple co-existing versions, in practice the packager only every builds one.

[pgguru]

In Citus it's only checked once per process and then cached in a global boolean variable (except in the error case). It's mostly very tedious to add the checks and easy to forget.

Musing, I wonder if we could redefine some of the function declaration macros to automate the checks for us, via some sort of static validation/launchpad or similar; since we already need to declare the functions that way it could at least do some additional logic for us. (I can also envision scenarios where it was able to dispatch between different binaries, doing appropriate symbol mangling to support multiple C function version in the same extension's source, though that would start to get hard to reason about, so not sure it's a good idea.)

[jcflack]

PL/Java seems to have avoided misfortune here by naming the .so file with a version number.

In the session where you run ALTER EXTENSION ... UPDATE, the new catalog entries refer to the new library and you can test things. When your transaction commits, everybody's catalog entries refer to the new library. If you roll back, they never do.

Of course, PL/Java has only a small handful of C functions (its handlers), which are always unconditionally CREATE OR REPLACEd, so the issue Yurii describes of leftover pg_proc entries pointing to the old .so hasn't cropped up.

The approach does give rise to the question of what will remove the old .so from the filesystem, and when.