Re: Custom Data Type Question
От | Tom Dunstan |
---|---|
Тема | Re: Custom Data Type Question |
Дата | |
Msg-id | 45626055.5020802@tomd.cc обсуждение исходный текст |
Ответ на | Re: Custom Data Type Question (Andrew Dunstan <andrew@dunslane.net>) |
Список | 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
В списке pgsql-hackers по дате отправления: