From: PG Bug reporting form <noreply@postgresql.org>
To: pgsql-bugs@lists.postgresql.org
Cc: wolakk@gmail.com
Subject: BUG #17598: EXTENSION can no longer create it's own schema! (Create Schema IF NOT EXISTS XXX)
Date: Sun, 28 Aug 2022 21:30:01 +0000
Message-ID: <17598-90d4e2f6057cbfec@postgresql.org> (raw)
The following bug has been logged on the website:
Bug reference: 17598
Logged by: Kirk Wolak
Email address: wolakk@gmail.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;
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: noreply@postgresql.org, pgsql-bugs@lists.postgresql.org, wolakk@gmail.com
Subject: Re: BUG #17598: EXTENSION can no longer create it's own schema! (Create Schema IF NOT EXISTS XXX)
In-Reply-To: <17598-90d4e2f6057cbfec@postgresql.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox