Simon Riggs wrote:
> On Sat, 2006-11-18 at 10:54 -0500, Andrew Dunstan wrote:
>
>
>>> Your enum sounds good, apart from the hardcoded/compilation thing. That
>>> is a data management nightmare AFAICS and so restricts the usefulness of
>>> the solution.
>>>
>
>
>> Simon, Tom Dunstan has submitted a patch for first class enum types that
>> do not have the compilation requirement - it's in the queue for 8.3. You
>> might want to review that.
>>
>
> Well, the link to previous discussion ends: How about being more
> specific about what you are trying to accomplish? My thoughts:
>
> IMHO we need to optimise FOREIGN KEY checks. One way of doing that is by
> having enums that build the allowable values into the datatypes, I can
> think of others. An overall evaluation of the various approaches should
> be made before we settle on a specific one.
>
Using the submitted patch, FK checks should be VERY fast - the stored
values are just oids.
> My requirements list would be to allow FOREIGN KEYS to
> - be specified in SQL standard syntax
> - work as fast as CHECK (col IN (1,2,3))
>
If I understand it, you are really not talking about doing an FK check
on an enum value, but rather using an FK check as a means of validating
an enum. That is not what we are talking about. But the validity checks
will be at least as fast as any check constraint.
> - use less memory and completely avoid any spill-to-disk nightmare(*)
>
See above.
> - have the list of allowable values to be dynamically updateable,
> automatically as INSERTs/DELETEs occur on the referenced table
>
Why? People seem so hung up on this. If you want dynamically updatable
set, then use a reference table. The whole point of this exercise was to
provide first class enum types that work *just*like*other *types*. If
you want to change a column's type, you do 'alter table foo alter column
bar type newtype'. And so you would if you need to change to a different
enum type. What if you deleted a value in the allowed set? Boom goes
data integrity.
> - optimize without needing to change/reload database, just by adding
> minimum number of statements (zero being the best)
>
> (*) doesn't exist yet, thats why its a nightmare
>
I should add that it would have been nice if this discussion had
happened back in August when the work was being done.
cheers
andrew