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

On Tue, Sep 26, 2017 at 10:04 AM, Fabrízio de Royes Mello <
fabriziomello(at)gmail(dot)com> wrote:
>
> On Mon, Sep 25, 2017 at 5:45 AM, <hvisage(at)gmail(dot)com> wrote:
> >
> > The following bug has been logged on the website:
> >
> > Bug reference: 14827
> > Logged by: Hendrik Visage
> > Email address: hvisage(at)gmail(dot)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(at)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(at)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(at)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?

Att,

--
Fabrízio de Royes Mello
Consultoria/Coaching PostgreSQL
>> Timbira: http://www.timbira.com.br
>> Blog: http://fabriziomello.github.io
>> Linkedin: http://br.linkedin.com/in/fabriziomello
>> Twitter: http://twitter.com/fabriziomello
>> Github: http://github.com/fabriziomello