On Tue, Sep 26, 2017 at 10:04 AM, Fabrízio de Royes Mello <
fabriziomello@gmail.com> wrote:
>
> On Mon, Sep 25, 2017 at 5:45 AM, <
hvisage@gmail.com> wrote:
> >
> > 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 relations
> > Schema | Name | Type | Owner
> > --------+----------------------+----------+----------
> > public | test_serial | table | postgres
> > public | test_serial_uid_seq | sequence | postgres
> > public | 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:~$
> >
> >
>
> That's awful... I'll take a look into it.
>
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?