Feedback-ID: ie3de48e3:Fastmail
Date: Mon, 23 Jun 2025 12:59:04 +0200
From: =?utf-8?Q?=C3=81lvaro?= Herrera <alvherre@kurilemu.de>
To: Dominique Devienne <ddevienne@gmail.com>
Cc: pgsql-general@lists.postgresql.org
Subject: Re: Extension disappearing act
Message-ID: <202506231059.eflzcnqg67rc@alvherre.pgsql>
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Disposition: inline
Content-Transfer-Encoding: 8bit
In-Reply-To: <CAFCRh-_TQagsob_YH0nXktwhuBftNaiZ_VZ4REhZdSEkWXVKXg@mail.gmail.com>
Archived-At: <https://www.postgresql.org/message-id/202506231059.eflzcnqg67rc%40alvherre.pgsql>
Precedence: bulk

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)