Re: Custom Data Type Question - Mailing list pgsql-hackers

Andrew Dunstan wrote:
> Simon Riggs wrote:
>> 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.

Right. Enums (as implemented) require no explicit FK check. When you try 
to enter an enum, the input function does a syscache lookup using the 
typoid and the string value entered and returns the enum value OID. 
There's probably a point at which the syscache lookup becomes faster 
than a CHECK constraint, as I assume that the CHECK will get slower 
linearly as the number of allowed values increases. That number might be 
quite small.

>> - 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.

Well, if there really is demand, there are some things that we could do 
here to make life a bit easier. Firstly, just adding a new value to the 
end of the enum list should be trivial (just add a new row to pg_enum) 
except in the case of OID wraparound. When that happens, or if they want 
to add a value in the middle or start of the enum, we could possibly do 
the create new type, alter referenced tables, drop old type, rename new 
type automagically. Dropping a value from an enum would be a matter of 
checking that no referencing tables had the to-be-dropped value stored, 
and I suppose locking them while the delete from pg_enum is performed. 
Maybe that would be easy, maybe hard, but these things aren't 
impossible, just more work than it seemed it was worth at the time. If 
other people have use cases that require changing these more than we 
anticipated, however, maybe they'll leap forward with contributions. :)

What I *would* say, though, is that if people want these to be 
sufficiently dynamic that they can ever foresee using code rather than a 
schema script to change them, then they're using the wrong solution, and 
should go back to using a table.

Cheers

Tom