public inbox for [email protected]
help / color / mirror / Atom feedExtension disappearing act
10+ messages / 6 participants
[nested] [flat]
* Extension disappearing act
@ 2025-06-19 13:09 Dominique Devienne <[email protected]>
2025-06-19 14:13 ` Re: Extension disappearing act Tomas Vondra <[email protected]>
2025-06-19 14:18 ` Re: Extension disappearing act Adrian Klaver <[email protected]>
2025-06-19 16:35 ` Re: Extension disappearing act Laurenz Albe <[email protected]>
2025-06-20 14:07 ` Re: Extension disappearing act Merlin Moncure <[email protected]>
2025-06-23 10:59 ` Re: Extension disappearing act Álvaro Herrera <[email protected]>
0 siblings, 5 replies; 10+ messages in thread
From: Dominique Devienne @ 2025-06-19 13:09 UTC (permalink / raw)
To: [email protected]
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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Extension disappearing act
2025-06-19 13:09 Extension disappearing act Dominique Devienne <[email protected]>
@ 2025-06-19 14:13 ` Tomas Vondra <[email protected]>
4 siblings, 0 replies; 10+ messages in thread
From: Tomas Vondra @ 2025-06-19 14:13 UTC (permalink / raw)
To: Dominique Devienne <[email protected]>; [email protected]
On 6/19/25 15:09, 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.
> 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?
No, extensions should not disappear.
> Any idea, what we're missing, that might explain pgcrypto's disappearance?
> We're stumped for now.
>
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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Extension disappearing act
2025-06-19 13:09 Extension disappearing act Dominique Devienne <[email protected]>
@ 2025-06-19 14:18 ` Adrian Klaver <[email protected]>
2025-06-19 14:54 ` Re: Extension disappearing act Dominique Devienne <[email protected]>
4 siblings, 1 reply; 10+ messages in thread
From: Adrian Klaver @ 2025-06-19 14:18 UTC (permalink / raw)
To: Dominique Devienne <[email protected]>; [email protected]
On 6/19/25 06:09, 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.
> 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.
Does 'all' include the public schema?
> 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.
Was it in fact installed in the public schema?
> TIA, --DD
>
>
--
Adrian Klaver
[email protected]
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Extension disappearing act
2025-06-19 13:09 Extension disappearing act Dominique Devienne <[email protected]>
2025-06-19 14:18 ` Re: Extension disappearing act Adrian Klaver <[email protected]>
@ 2025-06-19 14:54 ` Dominique Devienne <[email protected]>
2025-06-20 15:37 ` Re: Extension disappearing act Adrian Klaver <[email protected]>
0 siblings, 1 reply; 10+ messages in thread
From: Dominique Devienne @ 2025-06-19 14:54 UTC (permalink / raw)
To: Adrian Klaver <[email protected]>; +Cc: [email protected]
On Thu, Jun 19, 2025 at 4:18 PM Adrian Klaver <[email protected]> wrote:
> On 6/19/25 06:09, Dominique Devienne wrote:
> > Hi. Little mystery we don't understand. v17.
> Does 'all' include the public schema?
No. We don't touch `public` at all, beside pgcrypto ending up inside it.
> > 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.
>
> 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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Extension disappearing act
2025-06-19 13:09 Extension disappearing act Dominique Devienne <[email protected]>
2025-06-19 14:18 ` Re: Extension disappearing act Adrian Klaver <[email protected]>
2025-06-19 14:54 ` Re: Extension disappearing act Dominique Devienne <[email protected]>
@ 2025-06-20 15:37 ` Adrian Klaver <[email protected]>
0 siblings, 0 replies; 10+ messages in thread
From: Adrian Klaver @ 2025-06-20 15:37 UTC (permalink / raw)
To: Dominique Devienne <[email protected]>; +Cc: [email protected]
On 6/19/25 07:54, Dominique Devienne wrote:
> On Thu, Jun 19, 2025 at 4:18 PM Adrian Klaver <[email protected]> wrote:
>> On 6/19/25 06:09, Dominique Devienne wrote:
>>> Hi. Little mystery we don't understand. v17.
>> Does 'all' include the public schema?
>
> No. We don't touch `public` at all, beside pgcrypto ending up inside it.
>
>>> 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.
>>
>> 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
After you run the DROP commands the above query does not return
pgcrypto, correct?
--
Adrian Klaver
[email protected]
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Extension disappearing act
2025-06-19 13:09 Extension disappearing act Dominique Devienne <[email protected]>
@ 2025-06-19 16:35 ` Laurenz Albe <[email protected]>
2025-06-20 07:35 ` Re: Extension disappearing act Dominique Devienne <[email protected]>
4 siblings, 1 reply; 10+ messages in thread
From: Laurenz Albe @ 2025-06-19 16:35 UTC (permalink / raw)
To: Dominique Devienne <[email protected]>; [email protected]
On Thu, 2025-06-19 at 15:09 +0200, 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.
> 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.
So are we. Why do you keep us guessing instead of posting a reproducer?
Yours,
Laurenz Albe
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Extension disappearing act
2025-06-19 13:09 Extension disappearing act Dominique Devienne <[email protected]>
2025-06-19 16:35 ` Re: Extension disappearing act Laurenz Albe <[email protected]>
@ 2025-06-20 07:35 ` Dominique Devienne <[email protected]>
2025-06-20 09:11 ` Re: Extension disappearing act Tomas Vondra <[email protected]>
0 siblings, 1 reply; 10+ messages in thread
From: Dominique Devienne @ 2025-06-20 07:35 UTC (permalink / raw)
To: Laurenz Albe <[email protected]>; +Cc: [email protected]
On Thu, Jun 19, 2025 at 6:35 PM Laurenz Albe <[email protected]> wrote:
>
> On Thu, 2025-06-19 at 15:09 +0200, Dominique Devienne wrote:
> > Hi. Little mystery we don't understand. v17.
> > We're stumped for now.
>
> 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?
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Extension disappearing act
2025-06-19 13:09 Extension disappearing act Dominique Devienne <[email protected]>
2025-06-19 16:35 ` Re: Extension disappearing act Laurenz Albe <[email protected]>
2025-06-20 07:35 ` Re: Extension disappearing act Dominique Devienne <[email protected]>
@ 2025-06-20 09:11 ` Tomas Vondra <[email protected]>
0 siblings, 0 replies; 10+ messages in thread
From: Tomas Vondra @ 2025-06-20 09:11 UTC (permalink / raw)
To: Dominique Devienne <[email protected]>; Laurenz Albe <[email protected]>; +Cc: [email protected]
On 6/20/25 09:35, Dominique Devienne wrote:
> On Thu, Jun 19, 2025 at 6:35 PM Laurenz Albe <[email protected]> wrote:
>>
>> On Thu, 2025-06-19 at 15:09 +0200, Dominique Devienne wrote:
>>> Hi. Little mystery we don't understand. v17.
>>> We're stumped for now.
>>
>> 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?
>
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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Extension disappearing act
2025-06-19 13:09 Extension disappearing act Dominique Devienne <[email protected]>
@ 2025-06-20 14:07 ` Merlin Moncure <[email protected]>
4 siblings, 0 replies; 10+ messages in thread
From: Merlin Moncure @ 2025-06-20 14:07 UTC (permalink / raw)
To: Dominique Devienne <[email protected]>; +Cc: [email protected]
On Thu, Jun 19, 2025 at 8:09 AM Dominique Devienne <[email protected]>
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.
> 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,
>
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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Extension disappearing act
2025-06-19 13:09 Extension disappearing act Dominique Devienne <[email protected]>
@ 2025-06-23 10:59 ` Álvaro Herrera <[email protected]>
4 siblings, 0 replies; 10+ messages in thread
From: Álvaro Herrera @ 2025-06-23 10:59 UTC (permalink / raw)
To: Dominique Devienne <[email protected]>; +Cc: [email protected]
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)
^ permalink raw reply [nested|flat] 10+ messages in thread
end of thread, other threads:[~2025-06-23 10:59 UTC | newest]
Thread overview: 10+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2025-06-19 13:09 Extension disappearing act Dominique Devienne <[email protected]>
2025-06-19 14:13 ` Tomas Vondra <[email protected]>
2025-06-19 14:18 ` Adrian Klaver <[email protected]>
2025-06-19 14:54 ` Dominique Devienne <[email protected]>
2025-06-20 15:37 ` Adrian Klaver <[email protected]>
2025-06-19 16:35 ` Laurenz Albe <[email protected]>
2025-06-20 07:35 ` Dominique Devienne <[email protected]>
2025-06-20 09:11 ` Tomas Vondra <[email protected]>
2025-06-20 14:07 ` Merlin Moncure <[email protected]>
2025-06-23 10:59 ` Álvaro Herrera <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox