"CREATE EXTENSION" statement broke some providers

[kalaomer]

trafficstars

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 :thinking:

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 ?

[ewjoachim]

It might be possible to do it all in the postgresql migration: https://stackoverflow.com/questions/59037549/try-catch-equivalent-in-postgres

[aryehklein]

Noting that I hit this issue as well in a CosmosDB which does not support the create extension command. Using Aurora DB worked without an issue.

[wahajahmedkhan]

Hi there,

We are encountering the same issue while using PostgreSQL in Azure. After some investigation, we found a workaround:

We replaced the migration script with a copy of the script, stripping out the line: CREATE EXTENSION IF NOT EXISTS plpgsql WITH SCHEMA pg_catalog;

This resolved the issue for us, but it seems more like a temporary fix rather than a proper solution.

It would be nice if someone on the procrastinate team could provide any updates on this issue, any plans to fix this in the future? or suggest a better workaround,

Thanks!

[medihack]

@wahajahmedkhan Unfortunately, I have not yet had the time to look into it. It is on my TODO, but it will take several weeks as it is not a high priority for me. I am not using Azure, so it is hard to reproduce. If there is an easy fix (maybe with the mentioned try-catch PostgreSQL equivalent), would you mind fixing it and creating a PR?