Skip site navigation (1) Skip section navigation (2)

Re: Enums patch v2

From: Tom Dunstan <pgsql(at)tomd(dot)cc>
To: Heikki Linnakangas <heikki(at)enterprisedb(dot)com>
Cc: pgsql-patches(at)postgresql(dot)org
Subject: Re: Enums patch v2
Date: 2006-12-20 01:23:02
Message-ID: 45889076.5020507@tomd.cc (view raw or flat)
Thread:
Lists: pgsql-hackerspgsql-patches
Heikki Linnakangas wrote:
> I'm sorry I missed the original discussions, but I have to ask: Why do 
> we want enums in core? The only potential advantage I can see over using 
> a look-up table and FK references is performance.

Well, there are a few things. Sometimes its tidiness, sometimes 
integrity... I've seen more than one system with hundreds of these 
things, and they've either gone down the table-per-enum solution, with 
LOTS of extra tables whose values never change, or the EAV solution, 
with one or two globally referenced tables to which everything in your 
system has as a FK, and an integrity check in a trigger if you're very 
lucky. Yuck on both accounts. Enums hit a sweet spot in the middle and 
provide data integrity and performance for non-changing values.

> 1. What's the point of having comparison operators for enums? For most 
> use cases, there's no natural ordering of enum values.

Well, there are a number of cases where ordering IS important, and 
indeed, enums provide a way to do it easily where many of the 
alternative solutions do not. It's one of the key benefits.

> 2. The comparison routine compares oids, right? If the oids wrap around 
> when the enum values are created, the ordering isn't what the user expects.

As has been pointed out by others quicker on the draw than me, I do sort 
the OIDs at enum creation time, for exactly this reason.

> 3. 4 bytes per value is wasteful if you're storing simple status codes 
> etc. Especially if you're doing this for performance, let's do no harm 
> by wasting space. One byte seems enough for the typical use cases. I'd 
> even argue that having a high upper limit on the number of enum values 
> encourages misuse of the feature.

I'd really love to have these fit into a 1 or 2 byte value on disk, but 
AFAIK there's simply no way to do it currently in postgresql. If we ever 
move to a solution where on-disk representation is noticeably different 
from in-memory representation, then it might be doable. If that does 
happen, we might benefit from other improvements such as being able to 
order columns in a tuple on disk so as to minimize alignment padding, 
not having to store a composite type's oid, etc. Until that happens, 
though, if it ever does, this is probably the tightest on-disk 
representation we're likely to get, unless we're happy to impose some 
pretty severe restrictions, like 8 bits per enum, and only 256 enums in 
total (for a 2 byte total). I was already shot down trying to make 
similar restrictions when I first brought it up. :) The OID solution 
seems to offend the least. :)

We did discuss this somewhat earlier, and I'm happy to take alternative 
suggestions, but AFAIK this is about as good as it's going to get right now.

Cheers

Tom

In response to

pgsql-hackers by date

Next:From: Alvaro HerreraDate: 2006-12-20 01:25:50
Subject: Re: Autovacuum Improvements
Previous:From: Hiroshi SaitoDate: 2006-12-20 01:20:25
Subject: Re: pg_restore fails with a custom backup file

pgsql-patches by date

Next:From: Tom DunstanDate: 2006-12-20 01:39:58
Subject: Re: [HACKERS] Enums patch v2
Previous:From: Bruce MomjianDate: 2006-12-19 17:00:05
Subject: Re: Load distributed checkpoint patch

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group