1

I have two PostgreSQL (14.5, Ubuntu 22.04) extensions A and B that each creates some functions in a common schema schm.

Now I am having trouble re-creating the two extensions as there seems to be conflicts on the ownership of the common scheme.

If I run CREATE EXTENSION A; first, it automatically creates the common schema schm; and when I run CREATE EXTENSION B;, postgres complains that

ERROR: schema schm is not a member of extension "b"

An extension may only use CREATE ... IF NOT EXISTS to skip object creation if the conflicting object is one that it already owns.

I also tried creating the shared schema in advance explicitly, which results in similar errors. The shared schema has worked before without a problem prior to PostgreSQL 14.4. But it stopped working after upgrading to 14.5, and the same error happens as of PostgreSQL 15 beta3.

My questions are:

What does it mean when postgres says a schema is a member of an extension or not? Is it not the database user/role that owns an extension? (e.g. among several extensions that create functions in public., which extension owns public.?)

How to check/display the ownership between schemas and extensions?

How to solve the above "membership" or ownership conflict between the two extensions sharing a schema.

Improve this question

2 Answers 2

Reset to default
1

This seems to be an issue with the PostgreSQL 14.5 version (from the pdpg repo). I went back to an old image with PostgreSQL 14.4, and repeated the process. There is no error with the old version.

For now, I've used apt-mark hold postgresql-14 to pin the postgresql-14 version since pdpg does not allow downgrading to minor versions such as 14.4.

Improve this answer
1
  • Interesting. Then this may be connected to the first entry in the change log. You certainly should resolve that, since not applying bug fixes is a bad solution.
    – Laurenz Albe
    Commented Aug 29, 2022 at 6:07
0

The mistake is that you issue the CREATE SCHEMA statement as part of the extension SQL script, so that the schema belongs to the extension.

It is best to omit the schema creation from the script, at least in all but one of the extensions.

Improve this answer
2
  • "The mistake is that you issue the CREATE SCHEMA statement as part of the extension SQL script". I did not, unless the PostgreSQL create schema statement does that by default.
    – tinlyx
    Commented Aug 29, 2022 at 4:17
  • I don't think it does, but could you check with \dx+ b in psql? What other statement than CREATE SCHEMA could cause the error message? A good idea would be to eliminate statements from the extension script until the error message goes away. Then you know the cause.
    – Laurenz Albe
    Commented Aug 29, 2022 at 6:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.