Re: Privilege required for IF EXISTS event if the object already exists

On Thu, Dec 16, 2021 at 3:38 AM Shay Rojansky <roji(at)roji(dot)org> wrote:

> >> Now, before creating tables, the ORM generates CREATE SCHEMA IF NOT
> EXISTS, to ensure that the schema exists before CREATE TABLE; that's
> reasonable general-purpose behavior.
> >
> > If the user hasn’t specified they want the schema created it’s arguable
> that executing create schema anyway is reasonable. The orm user should
> know whether they are expected/able to create the schema as part of their
> responsibilities and instruct the orm accordingly and expect it to only
> create what it has been explicitly directed to create.
>
> I think the point being missed here, is that the user isn't interacting
> directly with PostgreSQL - they're doing so via an ORM which isn't
> necessarily aware of everything. Yes, a switch could be added to the ORM
> where the user instructs it to not ensure that the schema exists, but
> that's placing unnecessary burden on the user - having the "ensure"
> operation silently no-op (instead of throwing) if the schema already exists
> just makes everything smoother.
>

I get that point, and even have sympathy for it. But I'm also fond of the
position that "ensuring a schema exists" is not something the ORM should be
doing. But, if you want to do it anyway you can, with a minimal amount of
pl/pgsql code.

> Is there any specific reason you think this shouldn't be done?
>

As I said before, your position seems reasonable. I've also got a couple
of reasonable complaints about IF EXISTS out there. But there is little
interest in changing the status quo with regards to the promises that IF
EXISTS makes. And even with my less constrained views I find that doing
anything but returning an error to a user that issues CREATE SCHEMA on a
database for which they lack CREATE privileges is of limited benefit.
Would I support a well-written patch that made this the new rule?
Probably. Would I write one or spend time trying to convince others to
write one? No.

David J.