Re: Enum proposal / design

We forgot to mention that we'll need to implement domains over enums and
arrays of enums too.

cheers

andrew

Tom Dunstan wrote:
> Hi guys
>
> Andrew and I got together and worked out a more detailed idea of how we
> want to add enums to the postgresql core. This follows on from his
> original enumkit prototype last year [1]. Here's a more formal proposal
> / design with what we came up with. Comments / criticism hereby solicited.
>
>
> How they will work (once created) is more or less the same as last time
> with the enumkit, with the exception of how they're created.
>
> Enum types will be created with a specialised version of the CREATE TYPE
> command thusly:
>
> CREATE TYPE rgb AS ENUM ('red', 'green', 'blue');
>
> They can then be used as column types, being input in quoted string form
> as with other user types:
>
> CREATE TABLE enumtest (col rgb);
> INSERT INTO enumtest VALUES ('red');
>
> Input is to be case sensitive, and ordering is to be in the definition
> order, not the collation order of the text values (ie 'red' < 'green' in
> the example above). See the original thread for more discussion and
> usage examples.
>
>
> The implementation will work as below. I've included something of a list
> of stuff to do as well.
>
> On disk, enums will occupy 4 bytes: the high 22 bits will be an enum
> identifier, with the bottom 10 bits being the enum value. This allows
> 1024 values for a given enum, and 2^22 different enum types, both of
> which should be heaps. The exact distribution of bits doesn't matter all
> that much, we just picked some that we were comfortable with.
>
> The identifier is required as output functions are not fed information
> about which exact type they are being asked to format (see below).
>
> The creation of a new pg_enum catalog is required. This will hold:
> - the type OID for the enum, from pg_type
> - the enum identifier for on disk storage
> - the enum values in definition order, as an array of text values
>
> The CREATE TYPE command will create a row in pg_type and a row in
> pg_enum. We will get a new enum id by scanning pg_enum and looking for
> the first unused value, rather than using a sequence, to make reuse of
> enum ids more predictable.
>
> Two new syscaches on pg_enum will be created to simplify lookup in the
> i/o functions: one indexed by type oid for the input function, and one
> indexed by enum id for the output function.
>
> All functions will be builtins; there will be no duplicate entries of
> them in pg_proc as was required for the enumkit.
>
> The i/o functions will both cache enum info in the same way that the
> domain and composite type i/o functions do, by attaching the data to the
> fcinfo->flinfo->fn_extra pointer. The input function will look up the
> enum data in the syscache using the type oid that it will be passed, and
> cache it in a hashtable or binary tree for easy repeated lookup. The
> output function will look up the enum data in the syscache using the
> enum id stripped from the high 22 bits of the on-disk value and cache
> the data as a straight array for easy access, with the enum value being
> used as a index into the array.
>
> The other functions will all work pretty much like they did in the
> enumkit, with comparison operators more or less treating the enum as its
> integer representation.
>
> The grammar will have to be extended to support the new CREATE TYPE
> syntax. This should not require making ENUM a reserved word. Likewise
> psql will be extended to learn the new grammar. There's probably a bit
> of work to do in DROP TYPE to make sure it deletes rows from pg_enum
> when appropriate.
>
> pg_dump must be taught how to dump enums properly.
>
> We'll need some regression tests, maybe including one in one of the PL
> testsuites to ensure that the io functions work happily when called from
> a non-standard direction.
>
> Documentation etc.
>
>
> General discussion:
>
> While we would really like to have had a 2 byte representation on disk
> (or even 1 for most cases), with the stored value being *just* the enum
> ordinal and not containing any type info about the enum type itself,
> this is difficult. Since the output function cleanup [2] [3], postgresql
> doesn't pass through the expected output type to output functions. This
> makes it difficult to tell the difference between e.g. the first value
> of the various enums, which would all have an integer representation of
> 0. We could have gone down the path of having the output function look
> up its expected type from the fcinfo->flinfo struct, as Martijn's tagged
> types do [4], but that would have required extra entries in pg_proc for
> every single enum. Alternatively we could have stored the full enum type
> oid on disk, but that would have blown out the on-disk representation to
> 5 or 6 bytes. The given approach of having a smaller enum id and the
> enum ordinal value stored in the 4 bytes seems a reasonable tradeoff
> given the current constraints.
>
> To preempt some questions (particularly some which came up in the
> enumkit discussion), here's a list of stuff which will *not* be
> implemented in the initial patch (and quite possibly never):
>
> - Support for ALTER TYPE to allow adding / modifying values etc. For
> the time being you'll just have to create a new type, do a bunch of
> ALTER TABLE commands, DROP the old type and rename the new one if you
> want the old name back.
>
> - Inline column enum declarations a la MySQL. While this feature might
> allow easier migration from MySQL, and we could theoretically do it by
> creating an anonymous type when creating the table, the cleanup when the
> column/table are dropped is a real problem, and pg_dump has to get a lot
> smarter. Given the ugliness of suppporting something similar with SERIAL
> columns [5], this is definitely not on the cards anytime soon.
>
> - Ordering by text value rather than the declaration order. If you
> want this, you really want a varchar domain instead. Or alternately you
> can order by e.g. colname::text if that does what you want. Doing
> something like that sounds suspiciously like ordering something for
> human consumption, though, which sounds like a really fast way to make
> your application difficult to localize. Anyway, if that's the only
> ordering you'll ever want, just define the values in alphabetical order.
> :)
>
> - Access to the internal integer representation. If you need to modify
> the values used or want to know what the integer is, use a lookup table
> instead. Enums are the wrong abstraction for you.
>
>
> Comments? Particularly on implementation strategy; the functionality was
> thrashed out pretty well last time around.
>
> Cheers
>
> Tom "unholy chimera" Dunstan
>
>
> [1] http://archives.postgresql.org/pgsql-hackers/2005-10/msg01243.php
> [2] http://archives.postgresql.org/pgsql-hackers/2005-04/msg00998.php
> [3] http://archives.postgresql.org/pgsql-hackers/2005-12/msg00454.php
> [4] http://svana.org/kleptog/pgsql/taggedtypes.html
> [5] http://archives.postgresql.org/pgsql-hackers/2006-04/msg01118.php
>
>