Thread: Enums again
I've been going through the thread that Andrew Dunstan started with his enumkit. 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? 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.
> 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 :)