Re: [BUGS] BUG #14827: "ALTER TABLE... IF NOT EXISTS...ADD..BIGSERIAL" leaves extra sequences
| От | Fabrízio de Royes Mello |
|---|---|
| Тема | Re: [BUGS] BUG #14827: "ALTER TABLE... IF NOT EXISTS...ADD..BIGSERIAL" leaves extra sequences |
| Дата | |
| Msg-id | CAFcNs+psydoPkxqT2u8LT2xmfCDNAwCHCtai1xjC5DDqR2-E3Q@mail.gmail.com обсуждение исходный текст |
| Ответ на | Re: [BUGS] BUG #14827: "ALTER TABLE... IF NOT EXISTS...ADD.. BIGSERIAL" leaves extra sequences (Tom Lane <tgl@sss.pgh.pa.us>) |
| Ответы |
Re: [BUGS] BUG #14827: "ALTER TABLE... IF NOT EXISTS...ADD..BIGSERIAL" leaves extra sequences
|
| Список | pgsql-bugs |
On Tue, Sep 26, 2017 at 4:57 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> Fabrízio de Royes Mello <fabriziomello@gmail.com> writes:
> > 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?
>
> I do not think this is a solution at all. It doesn't address the
> fundamental problem that we decide whether to make a serial sequence
> before determining whether we're going to make a column default
> depending on it.
>
> Fabrízio de Royes Mello <fabriziomello@gmail.com> writes:
> > 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?
>
> I do not think this is a solution at all. It doesn't address the
> fundamental problem that we decide whether to make a serial sequence
> before determining whether we're going to make a column default
> depending on it.
I tried to address only the ALTER TABLE ... ADD COLUMN IF NOT EXISTS statement, and do not touch CREATE TABLE statements...
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;
And what I tried to implement is skip step 1 and 3... and fo step 2 skip the DEFAULT constraint
> What it does do is introduce a different set of failure
> conditions, basically race conditions around the discrepancy between
> parse-time and execution-time state.
>
I didn't understand what you mean here...
--
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
> conditions, basically race conditions around the discrepancy between
> parse-time and execution-time state.
>
I didn't understand what you mean here...
> 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.
>
> isn't one that we should fix by introducing different oddities of
> behavior.
>
When I see the code I felt the same... :-(
> We could maybe fix things by arranging to create the sequence only after
> we've made the column successfully, but that would be messy.
> we've made the column successfully, but that would be messy.
If we do that we should add more steps to execution queue...
> 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...
Regards,
> 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...
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 по дате отправления: