Enums again

Maybe I missed it, but I didn't see any conclusion. If I want to design
an Open Source system now that may be in beta in three to six months and
I'd like to use enums, is this a good place to look?

There's no way you're going to be using enums.

I guess I'm wondering about the kit going into PgFoundry, being accepted
in the main branch or being dropped. And the timeframe for all that.

Time frame is more like 18 months. The kit is also very rough - not
like a finished feature would be like at all. Plus, there's no
guarantee the feature would ever make it into postgres.

Just don't use enums - they're awful.

Chris

Just don't use enums - they're awful.

In general?

So, instead of using enums for order states or originating system, I'll user
numbers or text? Or implement lookup tables ?

So, instead of using enums for order states or originating system, I'll
user numbers or text? Or implement lookup tables ?

Use a text field and a CHECK constraint if you have just a couple of
states, and a lookup table if you have many.

Always use a lookup table if you plan on adding new states regularly.

Chris

Christopher Kings-Lynne wrote:

Maybe I missed it, but I didn't see any conclusion. If I want to
design an Open Source system now that may be in beta in three to six
months and I'd like to use enums, is this a good place to look?

There's no way you're going to be using enums.

I guess I'm wondering about the kit going into PgFoundry, being
accepted in the main branch or being dropped. And the timeframe for
all that.

Time frame is more like 18 months. The kit is also very rough - not
like a finished feature would be like at all. Plus, there's no
guarantee the feature would ever make it into postgres.

Just don't use enums - they're awful.

Well, perhaps I should speak ;-)

First, enumkit is not intended for pgfoundry or contrib. It's a
byproduct of some research I was doing. It occurred to me that making
what I had done generic would be very simple - the only variables in
fact are the type name and the list of enumeration values. So I spent 30
minutes making it generic and enumkit was born.

So of course it's rough - it is a research workproduct, not finished code.

Now, my intention is to use that work product as part of allowing first
class enumeration types. Currently the proposal is till in my head, but
basically it would involve a fairly small set of changes. There would
(probably) need to be a new unreserved keyword plus a new rule set in
the grammar to allow for type creation, In the catalog, pg_type would
get a new column of type text[] that would hold the list of values, and
typtype would have a new possible value of 'e' for enumeration. There
might be other consequential changes too, but I think that would be most
of it. The only functions that actually need to have any knowledge of
the enumeration strings are the input/output functions and the to/from
text casts. These would get the relevant info from fcinfo.flinfo ... and
then looking up the type cache - not sure yet if an extra cache
operation is needed. I haven't yet worked out how to build the qsorted
table that enumkit uses for bsearch lookup on input, or even whether it
is worth doing. And I haven't done a line of code. Like I say it's all
in my head right now.

As for the timeframe - if this direction is acceptable I want to get it
into 8.2. I really hope that 8.2 is not 18 months away. I think we
should aim for a release cycle no longer than a year. This last cycle
worked pretty well, and I think we should try to repeat it. But within 6
months ... no.

So ... since Kaare asks, would a feature along the lines I outlined
above be acceptable? And does anyone have an alternative proposal that
they are prepared to work on?

If the answer to both of these is "no", then I will probably produce a
patch at some stage that would be hosted on pgfoundry. But that would be
far from ideal.

BTW, does the standard have a way of doing this? ISTR hearing something
about distinct types. If so, what should the grammar look like, and can
we use it (or something close)? I'd rather not just invent syntax freely.

Also, Christopher - I was somewhat motivated to work on this by your
recent comment about enums being the number one demand of migrating
MySQL users, so I am mildly amused by your last sentence ;-)

cheers

andrew

Andrew Dunstan wrote:

In the catalog, pg_type would
get a new column of type text[] that would hold the list of values, and
typtype would have a new possible value of 'e' for enumeration. There
might be other consequential changes too, but I think that would be most
of it.

Huh, why not have the actual values in a separate catalog like
pg_enumvalues or some such?

The only functions that actually need to have any knowledge of
the enumeration strings are the input/output functions and the to/from
text casts. These would get the relevant info from fcinfo.flinfo ... and
then looking up the type cache - not sure yet if an extra cache
operation is needed.

It'd be interesting to measure the difference of having the cache vs.
not having it.

Thinking on how to pg_dump the whole thing is important too.

--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

Alvaro Herrera wrote:

Andrew Dunstan wrote:

In the catalog, pg_type would
get a new column of type text[] that would hold the list of values, and
typtype would have a new possible value of 'e' for enumeration. There
might be other consequential changes too, but I think that would be most
of it.

Huh, why not have the actual values in a separate catalog like
pg_enumvalues or some such?

Sure, could do that. I don't have strong feelings either way.

The only functions that actually need to have any knowledge of
the enumeration strings are the input/output functions and the to/from
text casts. These would get the relevant info from fcinfo.flinfo ... and
then looking up the type cache - not sure yet if an extra cache
operation is needed.

It'd be interesting to measure the difference of having the cache vs.
not having it.

Possibly. I would expect it to make a noticeable difference.

Thinking on how to pg_dump the whole thing is important too.

Yes, that would certainly be part of the work. I should have mentioned
that. It's not a showstopper, though - I see no reason in principal for
it to be a difficulty.

cheers

andrew

Andrew Dunstan <andrew@dunslane.net> writes:

Alvaro Herrera wrote:

Huh, why not have the actual values in a separate catalog like
pg_enumvalues or some such?

Sure, could do that. I don't have strong feelings either way.

I'd vote for the separate catalog instead of bloating pg_type.

regards, tom lane

Tom Lane wrote:

Andrew Dunstan <andrew@dunslane.net> writes:

Alvaro Herrera wrote:

Huh, why not have the actual values in a separate catalog like
pg_enumvalues or some such?

Sure, could do that. I don't have strong feelings either way.

I'd vote for the separate catalog instead of bloating pg_type.

Ok, consider that a done deal. Any other tweaks?

cheers

andrew

Also, Christopher - I was somewhat motivated to work on this by your
recent comment about enums being the number one demand of migrating
MySQL users, so I am mildly amused by your last sentence ;-)

They're not mutually exclusive statements :)