Re: BUG #14827: "ALTER TABLE... IF NOT EXISTS...ADD.. BIGSERIAL" leaves extra sequences

=?UTF-8?Q?Fabr=C3=ADzio_de_Royes_Mello?= <fabriziomello(at)gmail(dot)com> writes:
> I didn't came with better solution, but for now what I did is inside
> transformaAlterTableStmt when calling transformColumnDefinition now we pass
> down "AlterTableStmt->missing_ok" to check and skip CREATE SEQUENCE
> statements when use SERIAL pseudo-types.

> It's not an elegant solution because during ATExecAddColumn we check it
> again by calling check_for_column_name_collision... Ideas are very welcome?

I do not think this is a solution at all. It doesn't address the
fundamental problem that we decide whether to make a serial sequence
before determining whether we're going to make a column default
depending on it. What it does do is introduce a different set of failure
conditions, basically race conditions around the discrepancy between
parse-time and execution-time state.

I don't feel like this is exactly a "must fix" problem, and it certainly
isn't one that we should fix by introducing different oddities of
behavior.

We could maybe fix things by arranging to create the sequence only after
we've made the column successfully, but that would be messy. I'm also
not clear on how to document it. The documentation right now is quite
clear, and accurate, about what SERIAL does:
https://www.postgresql.org/docs/devel/static/datatype-numeric.html#datatype-serial
This patch falsifies that, and so would any other conditional creation
of the sequence.

regards, tom lane