cargo pgx schema does CREATE OR REPLACE FUNCTION and it probably shouldn't

[eeeebbbbrrrr]

After some thought, I think our practice of doing CREATE OR REPLACE FUNCTION is incorrect and potentially destructive to a database that might already have an existing foo() function.

We should simply do CREATE FUNCTION, but perhaps we can have an attribute value #[pg_extern(or_replace)] for when the developer thinks they know what they're doing.

[svenklemm]

In addition to the potentially destructive behaviour using CREATE OR REPLACE FUNCTION in an installation script is also a security vulnerability.

See https://www.postgresql.org/docs/14/extend-extensions.html#EXTEND-EXTENSIONS-SECURITY-SCRIPTS

[JamesGuthrie]

Note that the same security vulnerabilities arise with CREATE ... IF NOT EXISTS.

[workingjubilee]

Considering the rather sternly worded recommendation to not use CREATE OR REPLACE FUNCTION for functions, and instead use CREATE FUNCTION, what use cases are actually foreseen for the CREATE OR REPLACE example?

[JamesGuthrie]

In general (for SQL functions, not C functions) it's used to update the body of an existing function. If you know that you CREATEd the function, and are the owner of it, then using CREATE OR REPLACE on the same function is safe.

We (the Promscale team at Timescale) have built mechanisms around the way that pgx generates functions in order for it to still be safe, so we would like to continue being able to use CREATE OR REPLACE. We are combining a lot of hand-written SQL with pgx's generated SQL.

For additional context, what we do is the following:

• mark extension as non-relocatable, and trusted, tell it to be installed in public schema
• put all functions into a custom namespace (_prom_ext)
• create the _prom_ext schema (in a bootstrap SQL script which runs only once)
• use pgx's CREATE OR REPLACE without worry, because we know they're being created into a schema that we created and own

I hope that helps somehow.

[eeeebbbbrrrr]

In general (for SQL functions, not C functions) it's used to update the body of an existing function. If you know that you CREATEd the function, and are the owner of it, then using CREATE OR REPLACE on the same function is safe.

I would say for extension upgrade scripts it's fine to use CREATE OR REPLACE, sure. However, pgx doesn't (yet) know how to generate those.

Since we only know how to generate an "initial creation" script, we should stick to CREATE only. Down the road when pgx learns how to make upgrade scripts we can introduce CREATE OR REPLACE where it makes sense as we can be mostly assured that the extension would own that function during ALTER EXTENSION UPDATE.