Re: [BUGS] BUG #14827: "ALTER TABLE... IF NOT EXISTS...ADD..BIGSERIAL" leaves extra sequences - Mailing list pgsql-bugs

From Fabrízio de Royes Mello
Subject Re: [BUGS] BUG #14827: "ALTER TABLE... IF NOT EXISTS...ADD..BIGSERIAL" leaves extra sequences
Date
Msg-id CAFcNs+o6j69NkY4uYNaxNJVQ9Oe7Sz-3Gg5FDz_WApeZG5rOjQ@mail.gmail.com
Whole thread Raw
In response to Re: [BUGS] BUG #14827: "ALTER TABLE... IF NOT EXISTS...ADD..BIGSERIAL" leaves extra sequences  (Fabrízio de Royes Mello <fabriziomello@gmail.com>)
Responses Re: [BUGS] BUG #14827: "ALTER TABLE... IF NOT EXISTS...ADD.. BIGSERIAL" leaves extra sequences
List pgsql-bugs
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?

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
Attachment

pgsql-bugs by date:

Previous
From: Tom Lane
Date:
Subject: Re: [BUGS] BUG #14825: enum type: unsafe use?
Next
From: Andrew Dunstan
Date:
Subject: Re: [BUGS] BUG #14825: enum type: unsafe use?