Re: BUG #15180: Alter table add column if not exists with unique constraint will add extra duplicate

Stephen Frost <sfrost(at)snowman(dot)net> writes:
> * Tom Lane (tgl(at)sss(dot)pgh(dot)pa(dot)us) wrote:
>> Not clear. If the column exists but there's no unique index on it,
>> should this command cause the index to spring into existence?

> No, it shouldn't, and I view that as quite clear.

I'm not exactly convinced; but if we wanted to deal with this case
as well as the SERIAL-column case that was complained of earlier,
I think we'd have to proceed more or less like this:

* Extend the execution state of ALTER TABLE to include an array of
flags. (In the general case, we need a separate flag for each IF
NOT EXISTS clause in the command.)

* Invent new AT sub-command(s) that test the existence of columns,
or any other sub-objects we have IF NOT EXISTS for, and set a
specified flag based on the result.

* Extend AlterTableCmd with a field that marks a particular subcommand
as to be executed or not conditionally on the state of flag N. Remove
the existing special-case conditional behavior in ATExecAddColumn.

* Extend transformAlterTableStmt so that, whenever there is an IF NOT
EXISTS flag on a given subcommand, it assigns a flag number for that
condition, prepends a suitable test subcommand to the list of
AlterTableCmds it's building, and marks all the AlterTableCmds generated
from that subcommand as conditional on that flag.

This is kind of complicated, but in order to handle the SERIAL-column
case, we have to do the sequence creation conditionally before the
column is created, so I don't think we can make it any simpler.

A possible objection to this design is that right now, you can do

alter table foo
add column if not exists f2 text,
add column if not exists f2 int;

and it will skip the second ADD subcommand because by that point the
column exists. With this design, both test subcommands would find that
f2 doesn't exist so we'd try to do both ADD subcommands, and the second
one would fail. That doesn't particularly bother me, because this
command is silly. Conceivably, transformAlterTableStmt could be taught
to check for identical test subcommands and turn the later ones into
constant-false results (or throw away the later subcommands entirely);
but I'm doubtful that it's worth the trouble.

regards, tom lane