BUG #17598: EXTENSION can no longer create it's own schema! (Create Schema IF NOT EXISTS XXX)

From: PG Bug reporting form <noreply(at)postgresql(dot)org>
To: pgsql-bugs(at)lists(dot)postgresql(dot)org
Cc: wolakk(at)gmail(dot)com
Subject: BUG #17598: EXTENSION can no longer create it's own schema! (Create Schema IF NOT EXISTS XXX)
Date: 2022-08-28 21:30:01
Message-ID: 17598-90d4e2f6057cbfec@postgresql.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

The following bug has been logged on the website:

Bug reference: 17598
Logged by: Kirk Wolak
Email address: wolakk(at)gmail(dot)com
PostgreSQL version: 14.5
Operating system: Ubuntu 14.5-0ubuntu0.22.04.1
Description:

I ran into this with Login_hook. I've simplified it with my own example.
This worked in 14.3... (the script runs fine in 14.3), we jumped straight to
14.5

Effectively if FEELS like the check for an schema/extension
"owning/belonging to" a schema/extension is failing.
In this case, the schema DOES NOT EXIST. And the create if not exists
FAILS! (Which feels like an edge case.
You cannot tell who owns the object or who it belongs to, if it does not
exist).

// WORK AROUND: Create the schema manually, and COMMENT out the offending
code (Ouch) [in the extension!]

/* Actual Session: This is what I get when I try to install this simple
extension */
ncblah=# create extension simple_extension;

ERROR: schema simple_extension is not a member of extension
"simple_extension"
DETAIL: An extension may only use CREATE ... IF NOT EXISTS to skip object
creation if the conflicting object is one
that it already owns.

ncblah=# create extension simple_extension schema simple_extension;

ERROR: schema "simple_extension" does not exist

-- ATTEMPT TO Create the Schema First (which SHOULD PROBABLY TRIGGER this
message)...
ncblah=# create schema simple_extension;
CREATE SCHEMA
ncblah=# create extension simple_extension schema simple_extension;
ERROR: schema simple_extension is not a member of extension
"simple_extension"
DETAIL: An extension may only use CREATE ... IF NOT EXISTS to skip object
creation if the conflicting object is one that it already owns.
ncblah=#

Thanks in Advance...

-- Simple enough code to reproduce:

/* simple_extension.control */
# simple_extension to show problem creating schema!
comment = 'simple_extension - creates a schema (which fails) and tries to
create a version function'
encoding = 'UTF-8'
default_version = '1.0'
superuser = true
relocatable = false
schema = 'simple_extension'

/* simple_extension--1.0.sql */
create schema if not exists simple_extension;
comment on schema simple_extension is 'Belongs to the simple_extension
extension';
grant usage on schema simple_extension to public;

create or replace function simple_extension.get_simple_extension_version()
returns text
language sql return '1.0 -- But hardly Provable!';

grant execute on function simple_extension.get_simple_extension_version() to
public;

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message PG Bug reporting form 2022-08-29 00:46:50 BUG #17599: After creating more than 10,000 roles, select becomes slow after role switching
Previous Message Tom Lane 2022-08-26 23:59:06 Re: BUG #17596: "invalid attribute number 11" when updating partitioned table with a MULTIEXPR_SUBLINK