0

Is there a way in PostgreSQL to check whether the current context is during CREATE EXTENSION or ALTER EXTENSION … UPDATE?

I'm switching over some of my schemas that I managed with my home-brewed PostgreSQL migration framework to be based on extensions instead. In my migration framework, I had various way to see if I was running within a migration, and I would like to see something similar during extension installation/upgrade.

Maybe I must throw and catch an exception and inspect the stack? I hope not…

Context [added Nov. 21, '22]: I would like to create an event trigger that ignores events that are part of extension installation and upgrade. To be honest, I don't really need this. (I realized that, within my use case, extension installation is handled from within migrations anyway.) But, as a theoretical exercise, I still would like to know if this is possible.

2 Answers 2

2

Extension scripts have a magic token, @extschema@, that gets replaced with the target schema name. You can check if the replacement occurs. I'm not sure it can be in a string literal and still get replaced; its designed to be used in an identifier. Details in the PostgreSQL manual.

As extension scripts have to live in a special place and only get called with CREATE EXTENSION / ALTER EXTENSION UPDATE and can have protection against being included via psql you can be fairly certain about how your code got called. You could put a wrapper on your code that passes a source identifier to an inner function through an argument.

2
  • I like your idea, which I hadn't thought of myself. However, it doesn't solve my precise problem: I want to create an event trigger that ignores events that take place during CREATE EXTENSION and ALTER EXTENSION … UPDATE. I will update my question with that bit of context.
    – BigSmoke
    Commented Nov 21, 2022 at 9:33
  • I have edited my answer to contain a better, more universal solution to my problem.
    – BigSmoke
    Commented Apr 12, 2023 at 13:55
0

1. Finding out if and which extension is being installed

All one has to do to find out if an extension is currently being installed is to create any type of object that would normally become tracked as part of the extension (i.e., any object type that can be appropriated or disowned via ALTER EXTENSION.), and then check (via the pg_depend catalog) if that object now depends on an extension (and which extension that is).

The object being tested for belonging to an extension may even be a temporary object:

create temporary table test_object (col int)
    on commit drop;

select
    pg_extension.extname
from
    pg_catalog.pg_depend
inner join
    pg_catalog.pg_extension
    on pg_extension.oid = pg_depend.refobjid
where
    pg_depend.classid = 'pg_catalog.pg_class'::regclass
    and pg_depend.objid = 'test_object'::regclass
    and pg_depend.refclassid = 'pg_catalog.pg_extension'::regclass
;

The above SELECT will return NULL if this code is run outside of a CREATE EXTENSION/ALTER EXTENSION context. Otherwise, it will return the name of the extension that is in the process of being installed. (If an extension is being installed due to a dependent extension being installed WITH CASCADE, the extension name returned will be the name of the dependency, not the dependent.)

Of course, normally there would scarcely be a reason to create a dummy object, since the point of many extension set up and upgrade scripts is to create objects anyway.

2. Finding out if the current event trigger fires in the context of an extension script

I could have just turned to the event trigger documentation for the answer: https://www.postgresql.org/docs/current/functions-event-triggers.html

One of the columns in the record set that pg_event_trigger_ddl_commands() returns is: in_extension, which is “[t]rue if the command is part of an extension script”.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.