From: | "David G(dot) Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com> |
---|---|
To: | "fabriziomello(at)gmail(dot)com" <fabriziomello(at)gmail(dot)com> |
Cc: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, Hendrik Visage <hvisage(at)gmail(dot)com>, "pgsql-bugs(at)postgresql(dot)org" <pgsql-bugs(at)postgresql(dot)org> |
Subject: | Re: BUG #14827: "ALTER TABLE... IF NOT EXISTS...ADD.. BIGSERIAL" leaves extra sequences |
Date: | 2017-09-26 21:07:03 |
Message-ID: | CAKFQuwbo5dgf8pecshOwacC4a9zzUCR2qpP6yEbuYfGoPMrQbw@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
On Tue, Sep 26, 2017 at 1:42 PM, Fabrízio de Royes Mello <
fabriziomello(at)gmail(dot)com> wrote:
> On Tue, Sep 26, 2017 at 4:57 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)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.
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.
> > 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.
David J.
From | Date | Subject | |
---|---|---|---|
Next Message | Bruce Momjian | 2017-09-26 21:14:19 | Re: [BUGS] BUG #14825: enum type: unsafe use? |
Previous Message | Fabrízio de Royes Mello | 2017-09-26 20:42:06 | Re: BUG #14827: "ALTER TABLE... IF NOT EXISTS...ADD.. BIGSERIAL" leaves extra sequences |