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

Re: enums

From: Rod Taylor <pg(at)rbt(dot)ca>
To: "Jim C(dot) Nasby" <jnasby(at)pervasive(dot)com>
Cc: Andrew Dunstan <andrew(at)dunslane(dot)net>,PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org>,Michael Fuhr <mike(at)fuhr(dot)org>
Subject: Re: enums
Date: 2005-10-28 22:10:26
Message-ID: 1130537426.846.146.camel@home (view raw or flat)
Thread:
Lists: pgsql-hackers
On Fri, 2005-10-28 at 16:28 -0500, Jim C. Nasby wrote:
> On Fri, Oct 28, 2005 at 04:36:26PM -0400, Rod Taylor wrote:
> > On Fri, 2005-10-28 at 15:21 -0500, Jim C. Nasby wrote:
> > > On Fri, Oct 28, 2005 at 02:57:03PM -0400, Rod Taylor wrote:
> > > > The basic idea is that most of us break out schemas by creating fake
> > > > primary keys for the purpose of obtaining performance because using the
> > > > proper primary key (single or multiple columns) is often very slow.
> > > > 
> > > > The automatic and transparent creation of a surrogate key by PostgreSQL
> > > > would allow us to dramatically clean up the presentation of our schema
> > > > to the users using the database without the performance hit we currently
> > > > get.
> > > > 
> > > > 
> > > > It puts surrogate keys (fake primary keys) back to the level of table
> > > > spaces, indexes and other performance enhancements where they belong.
> > > 
> > > Ahh. Yes, that would definately be great to have. Although it would
> > > probably take me months if not years to get used to not seeing a bunch
> > > of _id fields laying all over the place...
> > > 
> > > Is SURROGATE part of any of the ANSI specs?
> > 
> > No, but neither is an index, rollback segment, or table space.  The ANSI
> > spec doesn't usually deal with performance tweaks that are the
> > responsibility of the DBA.
> 
> True, but none of those other things you mention affect external
> representation of data. But I was more wondering if we were inventing
> syntax on the fly here or not...

It isn't supposed to impact the external representation of the data and
generally neither is an ENUM outside of the potential sorting ability. I
was just getting the impression that the big push for enums was to be
able to use a 'real word' but without a performance hit.

A regular old table, foreign key to a varchar gives you the 'real word'
and the surrogate key allows you to do so without a performance hit.

-- 


In response to

  • Re: enums at 2005-10-28 21:28:37 from Jim C. Nasby

Responses

  • Re: enums at 2005-10-28 22:21:54 from Jim C. Nasby

pgsql-hackers by date

Next:From: Jim C. NasbyDate: 2005-10-28 22:21:54
Subject: Re: enums
Previous:From: Jim NasbyDate: 2005-10-28 22:10:19
Subject: Re: TRAP: FailedAssertion("!((itemid)->lp_flags & 0x01)",

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