Subject: Extension disappearing act

From: Dominique Devienne <[email-masked]>
Date: Thu, 19 Jun 2025 15:09:03 +0200

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.
This also creates roles associated to those schemas.
One of the schema is owned by the DB owner (in case that matters).
Creates functions using pgcrypto, in some of those schemas.
Drop all schemas (and associated roles), thus pgcrypto-using functins are gone.
Of course, the DB owner role was not dropped. Can't in fact.
Somehow, the pgcrypto extension has disappeared, as side-effects of the drops.
We did a LIBPQ trace of the command to does all the drops,
and there's no explicit drop of the extension.

All of the above is done using a LOGIN role that has CREATEROLE and CREATEDB.

Extensions are not supposed to implicitly disappear, are they?
Any idea, what we're missing, that might explain pgcrypto's disappearance?
We're stumped for now.

TIA, --DD

---

From: Tomas Vondra <[email-masked]>
Date: Thu, 19 Jun 2025 16:13:48 +0200

> Extensions are not supposed to implicitly disappear, are they?

No, extensions should not disappear.

> Any idea, what we're missing, that might explain pgcrypto's disappearance?

The only idea I can come up with is that pgcrypto was in one of those
dropped schemas (but I know you're saying it was in public).

Are you able to reproduce this? If yes, can you share a reproducer?

regards

-- 
Tomas Vondra

---

From: Adrian Klaver <[email-masked]>
Date: Thu, 19 Jun 2025 07:18:39 -0700

> Drop all schemas (and associated roles), thus pgcrypto-using functins are gone.

Does 'all' include the public schema?

> Somehow, the pgcrypto extension has disappeared, as side-effects of the drops.

Was it in fact installed in the public schema?

-- 
Adrian Klaver
[email-masked]

---

From: Dominique Devienne <[email-masked]>
Date: Thu, 19 Jun 2025 16:54:03 +0200

> Does 'all' include the public schema?

No. We don't touch `public` at all, beside pgcrypto ending up inside it.

> Was it in fact installed in the public schema?

Sure was. It's my own command, but good enough. --DD

D:\>ppg -c my17 -d dd_v168a database_ --extensions
Connected OK (postgresql://ddevienne@localhost:5417/dd_v168a)
=======================================================
|   Name   | Version |       Owner       |   Schema   |
=======================================================
| pgcrypto | 1.3     | "Acme-DBA:000ik2" | public     |
| plpgsql  | 1.0     | postgres          | pg_catalog |
=======================================================
2 installed extensions (out of 61)

Which runs

SELECT extname, extversion,
       extowner::regrole::text as owner,
       extnamespace::regnamespace::text as "schema"
  FROM pg_extension
 ORDER BY 1

---

From: Laurenz Albe <[email-masked]>
Date: Thu, 19 Jun 2025 18:35:55 +0200

> Any idea, what we're missing, that might explain pgcrypto's disappearance?

So are we.  Why do you keep us guessing instead of posting a reproducer?

Yours,
Laurenz Albe

---

From: Dominique Devienne <[email-masked]>
Date: Fri, 20 Jun 2025 09:35:19 +0200

> So are we.  Why do you keep us guessing instead of posting a reproducer?

Hi. Simply because there's too much proprietary stuff, I'm afraid.
And it's likely some stupid mistakes on our part anyway. That I can't see...
Still, the fact I see nothing extension-related in the libpq trace is
intriguing, isn't it?

---

From: Tomas Vondra <[email-masked]>
Date: Fri, 20 Jun 2025 11:11:47 +0200

> Still, the fact I see nothing extension-related in the libpq trace is

PQtrace logs client-server communication. I would not expect it to say
anything about actions that happen on the server, like for example
automatically dropping objects in a schema, after the schema is dropped.

I think the best way to move this forward is sharing a reproducer. If
you have too much proprietary stuff, you'll have to remove those bits,
or rather replace them with something you can share.

In fact, a reproducer is meant to be "minimal" - the smallest example
causing the issue. So creating reproducers generally means simplifying
the example as much as possible anyway. And I wouldn't be surprised if
in the process of doing that you find the answer yourself.

regards
-- 
Tomas Vondra

---

From: Merlin Moncure <[email-masked]>
Date: Fri, 20 Jun 2025 09:07:59 -0500

> We did a LIBPQ trace of the command to does all the drops,

 Suggestion:
1. Turn on statement logging to 'all'.  Make sure times are logged
2. Install a trace. this could be as simple as:
select now(), count(*) FILTER (WHERE extname = 'pgcrypto') from
pg_extension ;
\watch
...in psql
3. that should nail the time of the drop. at that time, you can then find
the offending statement

merlin

---

From: Adrian Klaver <[email-masked]>
Date: Fri, 20 Jun 2025 08:37:32 -0700

> Connected OK (postgresql://ddevienne@localhost:5417/dd_v168a)

After you run the DROP commands the above query does not return 
pgcrypto, correct?

---

From: Álvaro Herrera <[email-masked]>
Date: Mon, 23 Jun 2025 12:59:04 +0200

> 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)