Mentat

I am actually confused.

Migration 3fb6b209a5cd for mentat_events is also using plpgsql. I wonder why this issue wasn't discovered with that migration.

Locally, when I create my virtual machine from scratch using vagrant up, I have plpgsql in the list of extensions (\dx). And when I create a test function

CREATE OR REPLACE FUNCTION Sum(a int, b int) 
RETURNS int AS $$
BEGIN
  /*
   *  My first trivial PL/pgSQL function.
   */
  RETURN a + b;
END;
$$ LANGUAGE plpgsql IMMUTABLE STRICT;

there is no error when executing SELECT Sum(14,13);. I don't have to 'enable' the extension using createlong or anything. But I haven't seen anything regarding plpgsql anywhere in the codebase.

This is maybe question to Radko? Aren't some extensions on by default? (Or possibly only on some platforms?)

Radko Krkoš wrote in #note-5:

As far as I know, no extensions are enabled by default. Some extensions are built-in, such as plpgsql (and many more), so they need no installation, just enabling for target database(s) through CREATE EXTENSION. On the other end, another extension we use heavily, ip4r, must be installed explicitly (there is a .deb package) and also activated for select database(s) by CREATE EXTENSION.
As for plpgsql, we have it explicitly enabled on production, because we use it sometimes. For example in the two migrations mentioned, but also for some testing analytics (which are not part of production Mentat). Therefore this issue does not affect our systems, but would most probably fail for other users.
Simply adding CREATE and DROP to a migration would disable the extension for users which have it enabled without explicit dependencies, such as permanently defined functions. So, also us. The correct approach would be detecting the availability of extension in the beginning and in case of it missing, creating it before functions and their usage, and dropping it at the end. Otherwise, no modification to the extension state should be done. There is also the question of extension versioning (what to do if the installed version is different from then-current/tested for migration?). Solving this right is complex, the impact is low, so this issue was created as a reminder and a riddle for less interesting times.
Declaring plpgsql a requirement and managing its upgrades just like in case of ip4r is also a viable path. It would also allow us to use plpgsql functions as part of Mentat which might make some operations easier or cleaner (data integrity checks for arrays, timeline queries, ...).

I am afraid that that is all I can recall. Is it of any help?

Yes, I understand the part about detecting the extension and enabling/disabling it if not found. What confuses me is that when I created a virtual machine using vagrant, the extension was already enabled and I don't know why.

Rajmund Hruška wrote in #note-6:

Yes, I understand the part about detecting the extension and enabling/disabling it if not found. What confuses me is that when I created a virtual machine using vagrant, the extension was already enabled and I don't know why.

I see.
According to documentation, section "43.1. Overview" [1]:
"In PostgreSQL 9.0 and later, PL/pgSQL is installed by default. However it is still a loadable module, so especially security-conscious administrators could choose to remove it."
So, plpgsql seems to be enabled by default. My bad, sorry for the confusion.

[1] https://www.postgresql.org/docs/15/plpgsql-overview.html

Radko Krkoš wrote in #note-7:

Rajmund Hruška wrote in #note-6:

Yes, I understand the part about detecting the extension and enabling/disabling it if not found. What confuses me is that when I created a virtual machine using vagrant, the extension was already enabled and I don't know why.

I see.
According to documentation, section "43.1. Overview" [1]:
"In PostgreSQL 9.0 and later, PL/pgSQL is installed by default. However it is still a loadable module, so especially security-conscious administrators could choose to remove it."
So, plpgsql seems to be enabled by default. My bad, sorry for the confusion.

[1] https://www.postgresql.org/docs/15/plpgsql-overview.html

Also, according to documentation, section "42.1. Installing Procedural Languages" [2]:
"In a default PostgreSQL installation, the handler for the PL/pgSQL language is built and installed into the “library” directory; furthermore, the PL/pgSQL language itself is installed in all databases." What is quite explicit.
In line with [1], the change came in version 9.0, see difference between [3] (9.0) and [4] (8.4 - last 8.x version), where the cited sentence reads: "In a default PostgreSQL installation, the handler for the PL/pgSQL language is built and installed into the "library" directory."
PostgreSQL version 9.0 was released on September 20th 2010 [5], so the change is by no means recent and predates this issue substantially.

[2] https://www.postgresql.org/docs/15/xplang-install.html
[3] https://www.postgresql.org/docs/9.0/xplang-install.html
[4] https://www.postgresql.org/docs/8.4/xplang-install.html
[5] https://www.postgresql.org/support/versioning/