WIP: extensible enums

Excerpts from Andrew Dunstan's message of lun ago 23 05:35:09 -0400 2010:

To add a label at the end of the list, do:

ALTER TYPE myenum ADD 'newlabel';

To add a label somewhere else, do:

ALTER TYPE myenum ADD 'newlabel' BEFORE 'existinglabel';

or

ALTER TYPE myenum ADD 'newlabel' AFTER 'existinglabel';

What do you need AFTER for? Seems to me that BEFORE should be enough.
(You already have the unadorned syntax for adding an item after the last
one, which is the corner case that BEFORE alone doesn't cover).

--
Álvaro Herrera <alvherre@commandprompt.com>
The PostgreSQL Company - Command Prompt, Inc.
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

On Aug 23, 2010, at 2:35 AM, Andrew Dunstan wrote:

I'm not wedded to the syntax. Let the bikeshedding begin.

Seems pretty good to me as-is.

David

On Mon, August 23, 2010 11:49 am, Alvaro Herrera wrote:

Excerpts from Andrew Dunstan's message of lun ago 23 05:35:09 -0400 2010:

To add a label at the end of the list, do:

ALTER TYPE myenum ADD 'newlabel';

To add a label somewhere else, do:

ALTER TYPE myenum ADD 'newlabel' BEFORE 'existinglabel';

or

ALTER TYPE myenum ADD 'newlabel' AFTER 'existinglabel';

What do you need AFTER for? Seems to me that BEFORE should be enough.
(You already have the unadorned syntax for adding an item after the last
one, which is the corner case that BEFORE alone doesn't cover).

You're right. Strictly speaking we don't need it. But it doesn't hurt much
to provide it for a degree of symmetry.

cheers

andrew

On Mon, August 23, 2010 11:49 am, Alvaro Herrera wrote:

Excerpts from Andrew Dunstan's message of lun ago 23 05:35:09 -0400 2010:

To add a label at the end of the list, do:

ALTER TYPE myenum ADD 'newlabel';

To add a label somewhere else, do:

ALTER TYPE myenum ADD 'newlabel' BEFORE 'existinglabel';

or

ALTER TYPE myenum ADD 'newlabel' AFTER 'existinglabel';

What do you need AFTER for? Seems to me that BEFORE should be enough.
(You already have the unadorned syntax for adding an item after the last
one, which is the corner case that BEFORE alone doesn't cover).

You're right. Strictly speaking we don't need it. But it doesn't hurt much
to provide it for a degree of symmetry.

cheers

andrew

"Andrew Dunstan" <andrew@dunslane.net> writes:

On Mon, August 23, 2010 11:49 am, Alvaro Herrera wrote:

What do you need AFTER for? Seems to me that BEFORE should be enough.
(You already have the unadorned syntax for adding an item after the last
one, which is the corner case that BEFORE alone doesn't cover).

You're right. Strictly speaking we don't need it. But it doesn't hurt much
to provide it for a degree of symmetry.

I'm with Alvaro: drop the AFTER variant. It provides more than one way
to do the same thing, which isn't that exciting, and it's also going to
make it harder to document the performance issues. Without that, you
can just say "ADD BEFORE will make the enum slower, but plain ADD won't"
(ignoring the issue of OID wraparound, which'll confuse matters in any
case).

regards, tom lane

You're right. Strictly speaking we don't need it. But it doesn't hurt much
to provide it for a degree of symmetry.

Swami Josh predicts that if we don't add AFTER now, we'll be adding it
in 2 years when enough people complain about it.

--
-- Josh Berkus
PostgreSQL Experts Inc.
http://www.pgexperts.com

On 23 August 2010 10:35, Andrew Dunstan <andrew@dunslane.net> wrote:

Attached is a WIP patch that allows enums to be extended with additional
labels arbitrarily. As previously discussed, it works by adding an explicit
sort order column to pg_enum. It keeps track of whether the labels are
correctly sorted by oid value, and if so uses that for comparison, so the
possible performance impact on existing uses, and on almost all cases where
a label is added at the end of the list, should be negligible.

Open items include

  * some additional error checking required
  * missing documentation
  * pg_upgrade considerations

To add a label at the end of the list, do:

 ALTER TYPE myenum ADD 'newlabel';

To add a label somewhere else, do:

 ALTER TYPE myenum ADD 'newlabel' BEFORE 'existinglabel';

or

 ALTER TYPE myenum ADD 'newlabel' AFTER 'existinglabel';

I'm not wedded to the syntax. Let the bikeshedding begin.

cheers

andrew

When you write the supporting doc changes, you might want to add a
note in to mention that you cannot remove a label once it has been
added.

Will the modified enums remain intact after a dump/restore?

--
Thom Brown
Registered Linux user: #516935

On Mon, Aug 23, 2010 at 11:49:41AM -0400, Alvaro Herrera wrote:

Excerpts from Andrew Dunstan's message of lun ago 23 05:35:09 -0400 2010:

To add a label at the end of the list, do:

ALTER TYPE myenum ADD 'newlabel';

To add a label somewhere else, do:

ALTER TYPE myenum ADD 'newlabel' BEFORE 'existinglabel';

or

ALTER TYPE myenum ADD 'newlabel' AFTER 'existinglabel';

What do you need AFTER for? Seems to me that BEFORE should be enough.
(You already have the unadorned syntax for adding an item after the last
one, which is the corner case that BEFORE alone doesn't cover).

Making things easier for the users is a good thing all by itself :)

+1 for including both BEFORE and AFTER. Would it be worth it to allow
something like FIRST and LAST?

ALTER TYPE myenum ADD 'newlabel' FIRST;

ALTER TYPE myenum ADD 'newlabel' LAST;

Cheers,
David.
--
David Fetter <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david.fetter@gmail.com
iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

On Mon, Aug 23, 2010 at 01:54:40PM -0400, Tom Lane wrote:

"Andrew Dunstan" <andrew@dunslane.net> writes:

On Mon, August 23, 2010 11:49 am, Alvaro Herrera wrote:

What do you need AFTER for? Seems to me that BEFORE should be
enough. (You already have the unadorned syntax for adding an
item after the last one, which is the corner case that BEFORE
alone doesn't cover).

You're right. Strictly speaking we don't need it. But it doesn't
hurt much to provide it for a degree of symmetry.

I'm with Alvaro: drop the AFTER variant. It provides more than one
way to do the same thing, which isn't that exciting,

Not to you, maybe, but to users, it's really handy to have intuitive,
rather than strictly orthogonal, ways to do things.

Cheers,
David.
--
David Fetter <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david.fetter@gmail.com
iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

Josh Berkus <josh@agliodbs.com> writes:

Swami Josh predicts that if we don't add AFTER now, we'll be adding it
in 2 years when enough people complain about it.

If it's not there, no one will ever miss it. You might as well argue
that there should be a way of creating a foreign key reference by
ALTER'ing the referenced table instead of the referencing table.
Sure, if the SQL committee was into symmetry, they might have provided
such a thing. But they didn't and no one misses it.

regards, tom lane

On Mon, Aug 23, 2010 at 1:54 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

"Andrew Dunstan" <andrew@dunslane.net> writes:

On Mon, August 23, 2010 11:49 am, Alvaro Herrera wrote:

What do you need AFTER for?  Seems to me that BEFORE should be enough.
(You already have the unadorned syntax for adding an item after the last
one, which is the corner case that BEFORE alone doesn't cover).

You're right. Strictly speaking we don't need it. But it doesn't hurt much
to provide it for a degree of symmetry.

I'm with Alvaro: drop the AFTER variant.  It provides more than one way
to do the same thing, which isn't that exciting, and it's also going to
make it harder to document the performance issues.  Without that, you
can just say "ADD BEFORE will make the enum slower, but plain ADD won't"
(ignoring the issue of OID wraparound, which'll confuse matters in any
case).

But what if you want to insert an OID at the end? You can't do it if
all you've got is BEFORE:

CREATE TYPE colors AS ENUM ('red', 'green', 'blue');

If I want it to become ('red', 'green', 'blue', 'orange'), what am I to do?

On 23 August 2010 19:25, Joseph Adams <joeyadams3.14159@gmail.com> wrote:

On Mon, Aug 23, 2010 at 1:54 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

"Andrew Dunstan" <andrew@dunslane.net> writes:

On Mon, August 23, 2010 11:49 am, Alvaro Herrera wrote:

What do you need AFTER for?  Seems to me that BEFORE should be enough.
(You already have the unadorned syntax for adding an item after the last
one, which is the corner case that BEFORE alone doesn't cover).

You're right. Strictly speaking we don't need it. But it doesn't hurt much
to provide it for a degree of symmetry.

I'm with Alvaro: drop the AFTER variant.  It provides more than one way
to do the same thing, which isn't that exciting, and it's also going to
make it harder to document the performance issues.  Without that, you
can just say "ADD BEFORE will make the enum slower, but plain ADD won't"
(ignoring the issue of OID wraparound, which'll confuse matters in any
case).

But what if you want to insert an OID at the end?  You can't do it if
all you've got is BEFORE:

CREATE TYPE colors AS ENUM ('red', 'green', 'blue');

If I want it to become ('red', 'green', 'blue', 'orange'), what am I to do?

ALTER TYPE colors ADD 'orange';

--
Thom Brown
Registered Linux user: #516935

Thom Brown <thom@linux.com> writes:

On 23 August 2010 19:25, Joseph Adams <joeyadams3.14159@gmail.com> wrote:

But what if you want to insert an OID at the end?

ALTER TYPE colors ADD 'orange';

Alternatively, if people are dead set on symmetry, what we should do
to simplify is drop *this* syntax, and just have the BEFORE and AFTER
variants.

regards, tom lane

If it's not there, no one will ever miss it. You might as well argue
that there should be a way of creating a foreign key reference by
ALTER'ing the referenced table instead of the referencing table.
Sure, if the SQL committee was into symmetry, they might have provided
such a thing. But they didn't and no one misses it.

That's a very different situation, since the relationship is not
symmetrical, and it would take far more than a single keyword. Analogy
fail.

And one of the reasons people don't miss it is because far too many
users don't use FKs in the first place. ;-( The only reason why users
wouldn't notice the absence of AFTER (or, more likely, try it and then
ask on IRC for error message diagnosis) is because they're not using the
feature. (In which case it doesn't matter how it operates)

Docs which say "Add new enums BEFORE the enum you want to add them to,
and if you need to add an enum at the end, then add it without the
BEFORE keyword" is unnecessarily confusing to users. Saying "Add new
enum values using the BEFORE or AFTER keyword before or after the
appropriate value" is vastly easier to understand.

I really don't see the value in making a command substantially less
intuitive in order to avoid a single keyword, unless it affects areas of
Postgres outside of this particular command.

--
-- Josh Berkus
PostgreSQL Experts Inc.
http://www.pgexperts.com

On 23/08/10 22:06, Tom Lane wrote:

Thom Brown<thom@linux.com> writes:

On 23 August 2010 19:25, Joseph Adams<joeyadams3.14159@gmail.com> wrote:

But what if you want to insert an OID at the end?

ALTER TYPE colors ADD 'orange';

Alternatively, if people are dead set on symmetry, what we should do
to simplify is drop *this* syntax, and just have the BEFORE and AFTER
variants.

Then you need to know the last existing value to add a new one to the
end. Seems awkward.

--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com

Josh Berkus <josh@agliodbs.com> writes:

I really don't see the value in making a command substantially less
intuitive in order to avoid a single keyword, unless it affects areas of
Postgres outside of this particular command.

It's the three variants to do two things that I find unintuitive.

As I mentioned a minute ago, dropping the "abbreviated" syntax and
just having BEFORE and AFTER would be a good way of achieving
symmetry if you find that important.

regards, tom lane

On Mon, Aug 23, 2010 at 3:06 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

Thom Brown <thom@linux.com> writes:

On 23 August 2010 19:25, Joseph Adams <joeyadams3.14159@gmail.com> wrote:

But what if you want to insert an OID at the end?

ALTER TYPE colors ADD 'orange';

Alternatively, if people are dead set on symmetry, what we should do
to simplify is drop *this* syntax, and just have the BEFORE and AFTER
variants.

FWIW, I think Andrew's originally proposed syntax is fine and useful,
and we should just go with it.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise Postgres Company

On Mon, August 23, 2010 3:20 pm, Heikki Linnakangas wrote:

On 23/08/10 22:06, Tom Lane wrote:

Thom Brown<thom@linux.com> writes:

On 23 August 2010 19:25, Joseph Adams<joeyadams3.14159@gmail.com>
wrote:

But what if you want to insert an OID at the end?

ALTER TYPE colors ADD 'orange';

Alternatively, if people are dead set on symmetry, what we should do
to simplify is drop *this* syntax, and just have the BEFORE and AFTER
variants.

Then you need to know the last existing value to add a new one to the
end. Seems awkward.

I agree. This is a non-starter, I think. The most common case in my
experience is where the user doesn't care at all about the order, and just
wants to add a new label. We should make that as easy as possible,
especially since it's the most efficient.

cheers

andrew

On Aug 23, 2010, at 12:20 PM, Tom Lane wrote:

Josh Berkus <josh@agliodbs.com> writes:

I really don't see the value in making a command substantially less
intuitive in order to avoid a single keyword, unless it affects areas of
Postgres outside of this particular command.

It's the three variants to do two things that I find unintuitive.

I strongly suspect that you are in the minority on this one.

Best,

David