"CREATE EXTENSION" statement broke some providers

[kalaomer]

Hello there;

I tried to use Xata with procrastinate. But Xata disabled CREATE EXTENSION usage for their security. So "CREATE EXTENSION IF NOT EXISTS plpgsql WITH SCHEMA pg_catalog;" statement which is the first line of first migration is not passed well. Xata already enabled plpgsql extension, so this statement does not actually change anything for Xata. Is there a way to check extension first and then try to apply activate? I believe this solution is not just for Xata, maybe there are too many other providers already disabled create extension statement.

Thanks for help :)

[kalaomer]

Ok looks like Xata is not allow also "create function" statement, so my request is not cover the solution :/

[ewjoachim]

Is there a way to check extension first and then try to apply activate?

Yes but... That's called CREATE EXTENSION IF NOT EXISTS :D
Maybe it's on Xata's side to not block CREATE EXTENSION IF NOT EXISTS when the extension already exists 🤔

I think the best way forward from there would be to get the schema code (procrastinate schema --read), remove the offending line and execute the rest ?

Ok looks like Xata is not allow also "create function" statement

Ah. Maybe it's fundamentally incompatible with procrastinate then :/ We do create a few functions & triggers. If they don't let you do that, then... I'm not sure what to suggest.

[kalaomer]

Only I can suggest for now, as a Django developer, django checks first extension's availability. So if the extension is already activated then django does not execute "Create Extension" command. This is a good behavior i guess.

Here is the django way: https://github.com/django/django/blob/aa5293068782dfa2d2173c75c8477f58a9989942/django/contrib/postgres/operations.py#L27

Anyway thanks for your response @ewjoachim :)

[ewjoachim]

Here's the associated ticket on the Django tracker https://code.djangoproject.com/ticket/31615

It seems more compelling an argument that CREATE EXTENSION (often) requires superuser, though I can't get a definitive answer whether plpgsql actually does or not.

In contrast, CREATE FUNCTION only requires USAGE.

[ewjoachim]

Would you be interested in making a PR ?