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+oS2+gf7avcsLN5pXv+6hkw3f4KTxxDDrvha=2wMDfSnw@mail.gmail.com
Whole thread Raw
In response to Re: [BUGS] BUG #14827: "ALTER TABLE... IF NOT EXISTS...ADD..BIGSERIAL" leaves extra sequences  ("David G. Johnston" <david.g.johnston@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 6:07 PM, David G. Johnston <david.g.johnston@gmail.com> wrote:
>
> On Tue, Sep 26, 2017 at 1:42 PM, Fabrízio de Royes Mello <fabriziomello@gmail.com> wrote:
>>
>> On Tue, Sep 26, 2017 at 4:57 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>>
>> I tried to address only the ALTER TABLE ... ADD COLUMN IF NOT EXISTS statement, and do not touch CREATE TABLE statements...
>
> And since our docs don't explain the equivalence in terms of ALTER TABLE we are not falsifying anything.
>

+1


>> For example when we add a new SERIAL column to a relation:
>>
>> ALTER TABLE foo ADD COLUMN bar SERIAL;
>>
>> What I understood is actually PostgreSQL will convert it to:
>>
>> 1. CREATE SEQUENCE foo_bar_seq;
>> 2. ALTER TABLE foo ADD COLUMN bar INTEGER DEFAULT nextval('foo_bar_seq');
>> 3. ALTER SEQUENCE foo_bar_seq OWNER BY foo.bar;
>>
> [...]
>  
>>
>> > 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.
>> >
>>
>> When I see the code I felt the same... :-(
>
> Agreed, but it seems worthwhile to consider making it work as the OP expected.
>  

Sure... but the way things happen in code is not easy how to figure out a good elegant solution.


>> >  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.
>> >
>>
>> This patch doesn't falsifies that, because will act just when IF NOT EXISTS is used...
>>
>
> And we already deviate for ALTER TABLE by not strictly adhering to the specified format: tablename_colname_seq; instead we allow for appending "N" to the end of the name if necessary to make the sequence name unique.
>
> It seems like we'd want to invoke:
>
> CREATE SEQUENCE IF NOT EXISTS tablename_colname_seq
>
> If the corresponding add column is likewise IF NOT EXISTS.
>
> If we detect the column was newly created maybe then also issue a RESET SEQUENCE just in case we picked up a left-over?  This feels both cleaner and more dangerous than just inspecting everything first and deciding how to proceed on both fronts.
>

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...


Regards,

--
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

pgsql-bugs by date:

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