Greetings,
* Tom Lane (tgl@sss.pgh.pa.us) wrote:
> Michael Paquier <michael@paquier.xyz> writes:
> > On Mon, Apr 30, 2018 at 02:22:32PM +0000, PG Bug reporting form wrote:
> >> Alter table add column if not exists with unique constraint will add extra
> >> duplicate of the unique constraint when the column exists.
> >> Example:
> >> ALTER TABLE api_values ADD COLUMN IF NOT EXISTS master_key bigint NULL
> >> UNIQUE;
>
> > ... I don't recall all the details in tablecmds.c, but I am
> > pretty sure that it would be quite messy to create the column before
> > creating the index itself.
>
> After thinking about it for awhile, I'm not exactly convinced that this
> is a bug at all. "UNIQUE" underspecifies the desired index, so that it's
> impossible to say that a given existing index does or does not match the
> command. The code errs in favor of deciding that it doesn't, but the
> opposite decision could also be "wrong" in some use-cases.
How is any of that relevant? The column either exists, or it doesn't.
If the column exists, then the entire add-column operation (and anything
associated with it) should become a no-op, not only half of it.
> I'll spare you my usual rant about how CREATE IF NOT EXISTS sucks because
> the subsequent state of the object isn't well-defined ... oops, too late.
> But this seems like just another case of that problem.
My understanding, and, it seems, that of the original poster and likely
anyone who works with PG, is that an IF NOT EXISTS means "only perform
this operation if the thing doesn't already exist." Even the NOTICE
reported from the above command says that we're skipping things:
=> create table t4 (c1 int);
CREATE TABLE
=*> alter table t4 add column if not exists c2 bigint null unique;
ALTER TABLE
=*> alter table t4 add column if not exists c2 bigint null unique;
NOTICE: column "c2" of relation "t4" already exists, skipping
ALTER TABLE
It doesn't say anything about "well, we didn't add the column, but we
did add the constraint that you asked for to some column that happened
to already exist with that name." That hardly seemed like we "skipped"
doing the operation.
This clearly looks like a bug to me, and one we should figure out how to
fix. We don't make CREATE TABLE IF NOT EXIST go monkey with the
existing table definition when the table already exists, and I certainly
don't see any argument here that it makes sense for the behavior of
ALTER TABLE ... ADD COLUMN IF NOT EXISTS to be different from that.
Thanks!
Stephen
