On Fri, Jun 18, 2010 at 6:17 PM, Andrew Dunstan <and...@dunslane.net> wrote: > > > Tom Lane wrote: >> >> Insert a sort order column into pg_enum, and rearrange the values in >> that whenever the user wants to add a new value in a particular place.
+1 I was going to say exactly the same thing. >> You give up cheap comparisons in exchange for flexibility. I think lots >> of people would accept that tradeoff, especially if they could make it >> per-datatype. > Hmm. Yes, that could work. The assumption in my proposal was that existing > values would not be reordered anyway. > > But I'm not happy about giving up cheap comparison. And how would it be per > data-type? That part isn't clear to me. Would we mark a given enum type as > having its oids in order? It would also be sensible to quantify how much > more expensive comparisons would become. If the sort order data were kept in > the syscache the extra cost might get very small. I think you would need a syscache or something like it. My first instinct was to load the whole enum value->sort order mapping into a hash table the first time you're asked to compare two values in a given type. Then your comparison operator amounts to "look up/initialize hash table for this enum type, look up both sort orders in hash table, return comparison". You might need something like a syscache for the hash tables so that you don't keep the hash tables around forever. Using a syscache for the individual sort values would be slower to initially load if you're sorting a list since you would be doing a lot of retail lookups of individual values. But then perhaps it's a cheap way to protect against very large enums which using a hash table per enum type would be fragile against. -- greg -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
