Re: Extension disappearing act

On 2025-Jun-19, Dominique Devienne wrote:

> Hi. Little mystery we don't understand. v17.
>
> Create new DB, owned by dedicated new ROLE.
> Create extension (pgcrypto) in our case. Installed in public, owned by
> DB owner role.
> Create schemas and populate them inside the DB.

I would investigate this using an event trigger attached to the sql_drop
event. That should allow you to identify exactly when the extension is
dropped. Something like

CREATE OR REPLACE FUNCTION report_dropped()
RETURNS event_trigger
AS $$
DECLARE r record;
BEGIN
FOR r IN SELECT * from pg_event_trigger_dropped_objects()
LOOP
RAISE NOTICE
'orig=% normal=% istemp=% type=% identity=% name=% args=%',
r.original, r.normal, r.is_temporary, r.object_type,
r.object_identity, r.address_names, r.address_args;
END LOOP;
END;
$$ LANGUAGE plpgsql;

CREATE EVENT TRIGGER svar_regress_event_trigger_report_dropped ON sql_drop
EXECUTE PROCEDURE report_dropped();

--
Álvaro Herrera Breisgau, Deutschland — https://www.EnterpriseDB.com/
"En las profundidades de nuestro inconsciente hay una obsesiva necesidad
de un universo lógico y coherente. Pero el universo real se halla siempre
un paso más allá de la lógica" (Irulan)