Skip site navigation (1) Skip section navigation (2)

Re: Re: [COMMITTERS] pgsql: Update: < * Allow adding enumerated values to an existing

From: "Tom Dunstan" <pgsql(at)tomd(dot)cc>
To: "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: "Brendan Jurd" <direvus(at)gmail(dot)com>, "Alvaro Herrera" <alvherre(at)commandprompt(dot)com>, "Andrew Dunstan" <andrew(at)dunslane(dot)net>, "Bruce Momjian" <momjian(at)postgresql(dot)org>, pgsql-hackers(at)postgresql(dot)org
Subject: Re: Re: [COMMITTERS] pgsql: Update: < * Allow adding enumerated values to an existing
Date: 2008-04-25 22:01:37
Message-ID: ca33c0a30804251501s7f751c7erd8cb775d3e8d8247@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-committerspgsql-hackers
On Sat, Apr 26, 2008 at 2:51 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>  I'm not ... it strikes me that it will add implementation complexity and
>  runtime overhead for a feature that two days ago we didn't think we
>  needed at all, and IMHO one we still shouldn't be thinking to expend a
>  lot of work on.

Well, I *did* think it was necessary, I just hadn't spent the effort
in coming up with a solution. And on the effort side, I'm not going to
be hacking the optimizer any day soon. :)

>  I like #1 with no rewrite support.  That strikes me as covering 99%
>  of the requirements with 10% of the work.

>  Further, as already noted, if you do have to rewrite then a series of
>  manual ALTER COLUMN TYPE operations would probably be a *better* answer
>  than a monolithic implementation, because of the locking problems
>  involved in doing it in one transaction.

I don't understand this if it's calling option 2 the monolithic
implementation. I was intending that the values be permanent tokens if
you like, so that ZERO rewriting would be required for any types of
modification. So I don't see where locking comes in. I don't want
rewriting either.

Hmm, maybe I haven't explained it properly. Here's an example:

CREATE TYPE thing AS ENUM('vegetable', 'animal');

Hypothetical new pg_enum:
 enum_id | value | order | label
---------+-------+-------+-----------
       0 |     0 |     0 | vegetable
       0 |     1 |     1 | animal


ALTER TYPE thing AS ENUM('animal', 'mineral', 'vegetable');

pg_enum:
 enum_id | value | order | label
---------+-------+-------+-----------
       0 |     0 |     2 | vegetable
       0 |     1 |     0 | animal
       0 |     2 |     1 | mineral

So we store the 'value' column on disk, and it never changes. The
downside is that we now have to look up the order when we call a
non-equality operator, but I reckon we can pretty efficiently cache
that, so the lookup is just a couple of array index operations. The
upside is that we can reorder, and we'll never run out of values
"in-between" existing ones.

Anyway, sorry if all of the above *was* clear and I just misunderstood
the comment. If there's consensus to go with option 1 I'll pursue that
path. It's much less of a change to go from option 1 to option 2 than
the current code to either of them anyway, so doing some benchmarking
of both options shouldn't be hard if I want to.

Cheers

Tom

In response to

Responses

pgsql-hackers by date

Next:From: Tom LaneDate: 2008-04-25 22:28:57
Subject: Re: Re: [COMMITTERS] pgsql: Update: < * Allow adding enumerated values to an existing
Previous:From: Andrew DunstanDate: 2008-04-25 21:37:07
Subject: Re: Re: [COMMITTERS] pgsql: Update: < * Allow adding enumerated values to an existing

pgsql-committers by date

Next:From: Tom LaneDate: 2008-04-25 22:28:57
Subject: Re: Re: [COMMITTERS] pgsql: Update: < * Allow adding enumerated values to an existing
Previous:From: Bruce MomjianDate: 2008-04-25 21:38:46
Subject: pgsql: Add URL for: * Allow adding/renaming/removing enumerated values

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group