Обсуждение: [BUGS] BUG #14827: "ALTER TABLE... IF NOT EXISTS...ADD.. BIGSERIAL" leavesextra sequences

The following bug has been logged on the website:

Bug reference:      14827
Logged by:          Hendrik Visage
Email address:      hvisage@gmail.com
PostgreSQL version: 9.6.5
Operating system:   Debian 9
Description:

Goodday,With an alter table to add an UID field if it doesn't exist, the process
creates a sequence, but when the column does exist, the created sequence,
that isn't used, is just left there, and subsequent runs keeps adding extra
sequences.

postgres@tracsdbhvt01:~$ cat test-serial.sql
create database test;
\c test
create table test_serial ( teststring varchar(5));
alter table test_serial add column if not exists uid BIGSERIAL;
alter table test_serial add column if not exists uid BIGSERIAL;
\d
\d test_serial
postgres@tracsdbhvt01:~$ psql -p 5433 < test-serial.sql
CREATE DATABASE
You are now connected to database "test" as user "postgres".
CREATE TABLE
ALTER TABLE
NOTICE:  column "uid" of relation "test_serial" already exists, skipping
ALTER TABLE                 List of relationsSchema |         Name         |   Type   |  Owner
--------+----------------------+----------+----------public | test_serial          | table    | postgrespublic |
test_serial_uid_seq | sequence | postgrespublic | test_serial_uid_seq1 | sequence | postgres
 
(3 rows)
                                 Table "public.test_serial"  Column   |         Type         |
Modifiers
------------+----------------------+-----------------------------------------------------------teststring | character
varying(5)|uid        | bigint               | not null default 
nextval('test_serial_uid_seq'::regclass)

postgres@tracsdbhvt01:~$



--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

Fabrízio de Royes Mello <fabriziomello@gmail.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


--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

On Wed, Sep 27, 2017 at 6:23 AM, Fabrízio de Royes Mello
<fabriziomello@gmail.com> wrote:
> Seems a good plan... but I don't agree with RESET SEQUENCE... maybe just
> CREATE SEQUENCE IF NOT EXISTS when provide IF NOT EXISTS on ALTER TABLE ADD
> COLUMN is enough...

Anything like that is not completely hole-proof either. Let's not
forget that the sequence used with a default expression is not tracked
with its name, so if the sequence created after the serial definition
is renamed, and an INE is used on the given column, then you would
still create a sequence. Even worse, we need to be careful about not
linking the newly-created sequence instead of the one currently used.
In my opinion, the current behavior is more predictible. I think that
we should just document that IFE can leave behind sequences, and live
with that.
--
Michael


--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs