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)