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

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