extension: pl_pgsql

[kamilogorek]

postgres-wasm has a full support for plpgsql language extension, however for some reason it's not correctly loaded when instantiating PGlite.

Code:

import { PGlite } from '@electric-sql/pglite'
const db = new PGlite()
await db.query('CREATE TRUSTED LANGUAGE plpgsql')

Output:

error: could not open extension control file "/usr/local/pgsql/share/extension/plpgsql.control": No such file or directory

[kamilogorek]

After a quick investigation, this issue is caused by make sharedir only handling src/backend install which doesn't include pl extensions. Those live inside a separate src/pl directory.

Note that in order to start working on pl compilation on osx, you need this patch applied locally https://github.com/postgres/postgres/commit/3aa021b29b0ecdafa518d225fa08b98d7bf5c84e

[samwillis]

Hey @kamilogorek,

As you've spotted we currently don't build and bundle any of the standard extensions. But plpgsql is one we definitely want to support (python/perl/tlc are much lower priority and probably much more complex to make work).

We've not quite started work on adding support for extensions, it maybe that this is the best one to start with before moving onto pgvector and postgis.

There are some complexities around emscriptens handling of runtime loadable dylibs we need to test out. Essentially there are two routes:

1. Use emscriptens runtime loadable dylib support, but this requires turning off its dead code removal feature (to some extent) to ensure code required by loaded extensions isn't removed. This will make the main bundle larger.
2. Build the extensions into the main WASM, we can then continue to do the dead code removal as it will be across everything in the bundle. But this again increases the size of the bundle for all users.

Once we do a little more testing we will know more.

My current priority is parameterized queries, and a little benchmarking. Then it's onto extensions.

[kamilogorek]

Build the extensions into the main WASM, we can then continue to do the dead code removal as it will be across everything in the bundle. But this again increases the size of the bundle for all users.

I think for something like plpgsql that should be fine, as it's quite widely used, but for additional languages or other extensions, I agree that they should be opt-in. Not sure if it's worth having 2 separate ways of including things into the core though, so maybe in the end, it's better to treat plpgsql as a pluggable extensions as well.

[samwillis]

I have pl/pgsql working in PR #48, there is a dev build linked from it's description. After trying to use emscriptens dynamic liking machinery and hitting a wall I swapped to statically linking the extension. Seems to work well.

We are planning to do a release early next week after a few final checks.

I expect we will find statically linking extensions (with the additional code changes needed) is the way to make this work for others too.

[kamilogorek]

Can confirm that plpgsql works as expected in the lastest build. Thanks!