Skip to content

Avoiding Postgres Extensions Limitations¶

Postgres extensions are great. They enable fantastic use cases and bring new capabilities to one of the most loved open source databases. But there are edges in some of its features and this can be heard in conversations: limitations of the upgrade system, lack of parameterized initialization, search path/OID resolution issues, hard-wired dependency on .control files, schema droppage 1, etc.

However, the beauty of it is that what we ultimately want from extensions does not need to use CREATE EXTENSION's framework.

I've seen it a few times where newcomers to the extension world of Postgres learn to understand the relationship between .control, .sql and .so files. The questions I keep hearing are something like this:

  • are extensions required to have an .so file?
  • are SQL files required?
  • where do we make users store metadata so that it doesn't get dropped accidentally?

It's convenient to think about Postgres extensions feature as a whole, from CREATE EXTENSION down to your functions. However, I think it hides the fact that technically speaking, extension are a relatively thin mechanism on top of some of the more fundamental capabilities that Postgres provides.

  • It allows one to execute SQL (duh!)
  • It allows to define functions that are contained in an .so file2.

Postgres' vanilla extension framework reads the .control file, executes SQL scripts with a bit of quick-and-dirty string value replacements (like MODULE_PATHNAME or @extschema@) and these scripts deal with provisioning all that extension requires.

You can see most of this code in src/backend/commands/extension.c. You can see that that code doesn't really deal with .so files. That code is actually in src/backend/utils/fmgr/dfmgr.c, like load_external_function.

What this means is that you don't really need to follow the path charted by the framework to add that extended functionality to your database. You can roll your own upgrades, you can can have multiple .so files (instead of depending on MODULE_PATHNAME), you can call whatever initialization callbacks you want during installation, you don't need .control files. You can think of a lot of your own cases.

What do you have to give up for this? You have to give up CREATE EXTENSION. Instead, you'd need to do something like this:

psql=# select myextmgr.install('extension_name');

Which is probably not a big ask (implementing the install function itself is a bigger one!). In fact, it may be even more interesting because one can use this function over an entire dataset. For example, this will allow one to install multiple extensions not known ahead of time.

But...

How would we call create function and provide the path to .so files if it is not known ahead of time?

Great question. There are two ways I can see, depending on how deep you want to go:

  1. You can format() your create function query to supply the correct path with %L. I personally don't love it, but it's done a lot in PL/pgSQL.
  2. If you're writing this in C, there's ProcedureCreate. It has an ungodly number of parameters, but once you're through, it works really well!

A bigger thing you're potentially giving up here is that if you're developing an extension that you want others to use and it can't be fit into the mold provided by vanilla Postgres extensions framework, well, you're presented with a new challenge. You will need to find a way to convince your users to use your installation method. Whether it is worth it entirely depends on whether the limitations you're concerned about are worth overcoming.

  1. Unless explicitly depended on afterwards ↩

  2. Filename extension that means shared object. ↩

Comments