RE: BUG #15361: Add column if not exists create duplicate constraint

Hi Tom,

Thanks for your point of view. I'm far from being a skilled postgres user so
I could not discuss it much. From a "lambda" point of you, I found
interesting to use those IF EXISTS because it saved me a few lines of code
to check the information_table.columns and add the test accordingly. I
appreciate it was doing that for me, code contains already so much lines
just to check if everything is ok/not ok and rather few for the algorithm by
itself).

I expected that if column was not added, it was obvious that references
should not, sort of transaction rollback. Thinking about backward
compatibility, I can't figure out who could have used ADD COLUMN IF NOT
EXISTS ...REFERENCES ... , expecting that the REFERENCES will be created
anyway, but I do not have your experience.

If this behaviour is not changed, perhaps would it be enough to modify docs
which states "IF NOT EXISTS is specified and a column already exists with
this name, no error is thrown" adding something like "but others statements
like REFERENCES will be executed anyway."

BR

Olivier
-----Message d'origine-----
De : Tom Lane [mailto:tgl(at)sss(dot)pgh(dot)pa(dot)us]
Envoyé : samedi 1 septembre 2018 20:25
À : postgresql(at)noetika(dot)com
Cc : pgsql-bugs(at)lists(dot)postgresql(dot)org
Objet : Re: BUG #15361: Add column if not exists create duplicate constraint

=?utf-8?q?PG_Bug_reporting_form?= <noreply(at)postgresql(dot)org> writes:
> I have a patching script that is supposed to add column if not existing :

> ALTER TABLE myschem.table1
> ADD COLUMN IF NOT EXISTS col1 VARCHAR(254) REFERENCES
> myschem.table2(col2)

> When col1 already exists, I expected that nothing would happen.
> [ but actually, it creates an FK constraint anyway ]

As I've said many times before, I hate CREATE IF NOT EXISTS with a passion,
because its semantics are so squishy. This is a perfect example of that:
it's impossible to make a principled decision whether this is a bug or not,
or what the correct behavior is if you think it's a bug.
Should the command do nothing at all if col1 exists, regardless of whether
there's an FK constraint or not? Should it avoid creating a duplicate
constraint, and if so how picky are we to be about what "duplicate" means?
What happens if myschem.table2(col2) doesn't exist?

Not to mention whether we should change the behavior for other secondary
objects that might be shown in the command, such as UNIQUE or CHECK
constraints. Right now all of those get added, possibly redundantly, just
like FK constraints.

I believe this exact issue was debated when ADD COLUMN IF NOT EXISTS was
added, and the camp that wanted it thought this behavior was fine.
Even if we were now to conclude that this is a bug and agree on what'd be
better semantics, there would be a pretty strong backwards-compatibility
argument against changing it; some people's scripts might expect the
constraint(s) to get added.

The short answer is that IF NOT EXISTS gives you no guarantees whatsoever
about the subsequent properties of the object, only that something by that
name will exist. If you don't like that, don't use IF NOT EXISTS.

regards, tom lane