Re: [GENERAL] How to modify ENUM datatypes? (The solution)
Done. Now it's BSD licensed. :-) On Wed, Aug 13, 2008 at 4:57 PM, Merlin Moncure <[EMAIL PROTECTED]> wrote: > On Wed, Aug 13, 2008 at 5:20 AM, Dmitry Koterov <[EMAIL PROTECTED]> wrote: > > About LGPL - I don't know. > > But the license is not a problem, this code is totally freeware (because > too > > simple). > > LGPL is just my favorite license type for years. :-) > > > > I'll change this if you prefer another license and explain, why (why BSD? > > BSD is the PostgreSQL license?) > > yup. > > merlin > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general >
Re: [GENERAL] How to modify ENUM datatypes? (The solution)
On Wed, Aug 13, 2008 at 5:20 AM, Dmitry Koterov <[EMAIL PROTECTED]> wrote: > About LGPL - I don't know. > But the license is not a problem, this code is totally freeware (because too > simple). > LGPL is just my favorite license type for years. :-) > > I'll change this if you prefer another license and explain, why (why BSD? > BSD is the PostgreSQL license?) yup. merlin -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How to modify ENUM datatypes? (The solution)
About LGPL - I don't know.
But the license is not a problem, this code is totally freeware (because too
simple).
LGPL is just my favorite license type for years. :-)
I'll change this if you prefer another license and explain, why (why BSD?
BSD is the PostgreSQL license?)
On Wed, Aug 13, 2008 at 4:25 AM, Merlin Moncure <[EMAIL PROTECTED]> wrote:
> On Tue, Aug 12, 2008 at 5:40 PM, Dmitry Koterov <[EMAIL PROTECTED]> wrote:
> > Here is the solution about "on the fly" ALTER ENUM:
> > http://en.dklab.ru/lib/dklab_postgresql_enum/
> >
> > Usage:
> >
> > -- Add a new element to the ENUM "on the fly".
> >
> > SELECT enum.enum_add('my_enum', 'third');
> >
> > -- Remove an element from the ENUM "on the fly".
> > SELECT enum.enum_del('my_enum', 'first');
> >
> > Possibly future versions of PostgreSQL will include built-in ALTER TYPE
> for
> > ENUM, all the more its implementation is not impossible, as you see
> above.
> > Hope this will be helpful.
>
> Decent user space solution...it's easy enough. IMO 'real' solution is
> through alter type as you suggest. It's worth noting there there is
> no handling for the unlikely but still possible event of oid
> wraparound. Also, there is no 'enum_insert', which is not so pleasant
> with how enums are implemented.
>
> Also, is lgpl compatible with bsd licnese? Not that it matters, but I'm
> curious.
>
> merlin
>
Re: [GENERAL] How to modify ENUM datatypes? (The solution)
On Tue, Aug 12, 2008 at 5:40 PM, Dmitry Koterov <[EMAIL PROTECTED]> wrote:
> Here is the solution about "on the fly" ALTER ENUM:
> http://en.dklab.ru/lib/dklab_postgresql_enum/
>
> Usage:
>
> -- Add a new element to the ENUM "on the fly".
>
> SELECT enum.enum_add('my_enum', 'third');
>
> -- Remove an element from the ENUM "on the fly".
> SELECT enum.enum_del('my_enum', 'first');
>
> Possibly future versions of PostgreSQL will include built-in ALTER TYPE for
> ENUM, all the more its implementation is not impossible, as you see above.
> Hope this will be helpful.
Decent user space solution...it's easy enough. IMO 'real' solution is
through alter type as you suggest. It's worth noting there there is
no handling for the unlikely but still possible event of oid
wraparound. Also, there is no 'enum_insert', which is not so pleasant
with how enums are implemented.
Also, is lgpl compatible with bsd licnese? Not that it matters, but I'm curious.
merlin
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How to modify ENUM datatypes? (The solution)
Here is the solution about "on the fly" ALTER ENUM:
http://en.dklab.ru/lib/dklab_postgresql_enum/
Usage:
*-- Add a new element to the ENUM "on the fly".
SELECT enum.enum_add('my_enum', 'third');*
*-- Remove an element from the ENUM "on the fly".
SELECT enum.enum_del('my_enum', 'first');*
Possibly future versions of PostgreSQL will include built-in ALTER TYPE for
ENUM, all the more its implementation is not impossible, as you see above.
Hope this will be helpful.
On Wed, Apr 23, 2008 at 4:25 AM, Merlin Moncure <[EMAIL PROTECTED]> wrote:
> On Tue, Apr 22, 2008 at 6:11 PM, Jeff Davis <[EMAIL PROTECTED]> wrote:
> > If you store an integer reference instead, joins are not necessarily
> > expensive. If the number of distinct values is small (which is the
> > normal use case for ENUM), I would expect the joins to be quite cheap.
> > Beware of running into bad plans however, or making the optimizer work
> > too hard (if you have a lot of other joins, too).
>
> Necessarily being the operative word here. Think about an enum as
> part of a composite key for example. It's a lot nicer to rely on enum
> for natural ordering than doing something like a functional index.
>
> Anyways, it's pretty easy to extend an enum...you can manually insert
> an entry into pg_enum (see the relevent docs). Just watch out for oid
> overlap. One thing currently that is very difficult currently to do
> is to alter the order of the enum elements. The current state of
> things is pretty workable though.
>
> Scott's color/mystuff example is generally preferred for a lot of
> cases. I _really_ prefer this to surrogate style enums where you have
> color_id...this approach makes your database unreadable IMO. A decent
> hybrid approach which I have been using lately is "char" (not char)
> where the choices set is reasonably small, well represented by a
> single character, and the intrinsic ordering property is not too
> important (where an enum might be better). In many cases though, the
> pure natural approach is simply the best. The enum though with is
> intrinsic ordering and more efficient indexing has an important niche
> however.
>
> merlin
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>
Re: [GENERAL] How to modify ENUM datatypes?
On Fri, May 02, 2008 at 11:06:15PM +0200, Andreas 'ads' Scherbaum wrote: > > (I confess I am a little astonished that the most recent stable > > release ended up with 7.4. I thought that at _least_ 8.0 was out > > prior to freeze. What happened?) > > It was, i think ... but after feature freeze for Debian 3.1 it took > several month for the final release. Maybe the just released 8.0 was > "too new" for the feature freeze. Finally they ended up with 7.4 for > a very long time (before Debian 4.0 was released). The most recent stable release does include 8.1, but you have to explicitly ask for it (Debian supports parallel installation of multiple versions). That release was the first stable release to support that feature. I beleive the issue was that people who installed "postgresql" from the old release shouldn't be automatically be upgraded (and hence make their database unusable). After the last release "postgresql" became a virtual package which will always install the latest version available. But the long debian release cycle makes it a little odder than you might expect. Have a nice day, -- Martijn van Oosterhout <[EMAIL PROTECTED]> http://svana.org/kleptog/ > Please line up in a tree and maintain the heap invariant while > boarding. Thank you for flying nlogn airlines. signature.asc Description: Digital signature
Re: [GENERAL] How to modify ENUM datatypes?
On Fri, 2 May 2008 15:48:13 -0400 Andrew Sullivan wrote: > On Fri, May 02, 2008 at 09:18:39AM -0700, Rob Wultsch wrote: > > Not really Postgres's problem, but for whatever its worth if I do the > > following on Debian stable: > > $apt-get install postgresql > > > > I get 7.4 . When I install Debian I generally expect the software to > > be supported for a long time. Perhaps it might make sense to declare > > it dead except for security issues? > > I suspect this really is the Debian package maintainer's problem. > Debian itself has a very high bar for changes after feature freeze. > This is great from the point of view of stability, but I hope they (or > their users) aren't expecting the coommunities producing the software > to do their maintenance of old releases for them. Postgres is way > better in that respect than some of the included software. That's true. I heard some stories about updating Firefox (or was it still Mozilla?) and changing version numbers in Debian. That was because the Mozilla guys seem to stop maintaining older versions the time a new minor version is released. With an upstream like that, you have a really hard time to support a package for the distribution lifetime - and things get worse if newer releases contain bugfixes which cannot ported back to the version used in the distribution. > (I confess I am a little astonished that the most recent stable > release ended up with 7.4. I thought that at _least_ 8.0 was out > prior to freeze. What happened?) It was, i think ... but after feature freeze for Debian 3.1 it took several month for the final release. Maybe the just released 8.0 was "too new" for the feature freeze. Finally they ended up with 7.4 for a very long time (before Debian 4.0 was released). Bye -- Andreas 'ads' Scherbaum German PostgreSQL User Group -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How to modify ENUM datatypes?
On Fri, May 02, 2008 at 09:57:58PM +0200, Tino Wildenhain wrote: > apt-get install postgresql-8.1 gives you 8.1 in etch (stable) > in addition to 7.4. Huh. Debian gets more inscrutable every time I fail to look. A -- Andrew Sullivan [EMAIL PROTECTED] +1 503 667 4564 x104 http://www.commandprompt.com/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How to modify ENUM datatypes?
Andrew Sullivan wrote: On Fri, May 02, 2008 at 09:18:39AM -0700, Rob Wultsch wrote: Not really Postgres's problem, but for whatever its worth if I do the following on Debian stable: $apt-get install postgresql I get 7.4 . When I install Debian I generally expect the software to be supported for a long time. Perhaps it might make sense to declare it dead except for security issues? ... (I confess I am a little astonished that the most recent stable release ended up with 7.4. I thought that at _least_ 8.0 was out prior to freeze. What happened?) apt-get install postgresql-8.1 gives you 8.1 in etch (stable) in addition to 7.4. Cheers Tino smime.p7s Description: S/MIME Cryptographic Signature
Re: [GENERAL] How to modify ENUM datatypes?
On Fri, May 02, 2008 at 09:18:39AM -0700, Rob Wultsch wrote: > Not really Postgres's problem, but for whatever its worth if I do the > following on Debian stable: > $apt-get install postgresql > > I get 7.4 . When I install Debian I generally expect the software to > be supported for a long time. Perhaps it might make sense to declare > it dead except for security issues? I suspect this really is the Debian package maintainer's problem. Debian itself has a very high bar for changes after feature freeze. This is great from the point of view of stability, but I hope they (or their users) aren't expecting the coommunities producing the software to do their maintenance of old releases for them. Postgres is way better in that respect than some of the included software. (I confess I am a little astonished that the most recent stable release ended up with 7.4. I thought that at _least_ 8.0 was out prior to freeze. What happened?) A -- Andrew Sullivan [EMAIL PROTECTED] +1 503 667 4564 x104 http://www.commandprompt.com/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How to modify ENUM datatypes?
On Thu, May 1, 2008 at 10:27 PM, Tom Lane <[EMAIL PROTECTED]> wrote: > >> Maybe I'm some crazy, radical DBA, but I've never had a version of > >> pgsql get EOLed out from underneath me. > > Just for fun, I did a bit of digging in the release notes > http://developer.postgresql.org/pgdocs/postgres/release.html > and came up with this table about PG major releases and their > follow-on bug fix/minor releases: > > Version Release date# updates Days till final update Days till > next major > > 6.0 1997-01-29 0 0 130 > 6.1 1997-06-08 1 44 116 > 6.2 1997-10-02 1 15 150 > 6.3 1998-03-01 2 37 243 > 6.4 1998-10-30 2 51 222 > 6.5 1999-06-09 3 126 334 > 7.0 2000-05-08 3 187 340 > 7.1 2001-04-13 3 124 297 > 7.2 2002-02-04 8 1190296 > 7.3 2002-11-27 21 1867355 > 7.4 2003-11-17 19+ ? 429 > 8.0 2005-01-19 15+ ? 293 > 8.1 2005-11-08 11+ ? 392 > 8.2 2006-12-05 7+ ? 426 > 8.3 2008-02-04 1+ ? ? > > It's pretty clear that there was a sea-change around 7.2/7.3 --- > before that, nobody thought that PG releases were anything that > might be long-lived. And there's nothing in this table that > suggests we've really settled on a new lifespan ... other than that > we're still putting out new majors at a constant rate, and the community > hasn't got the resources or interest to maintain an ever-increasing > number of back branches. > > regards, tom lane Not really Postgres's problem, but for whatever its worth if I do the following on Debian stable: $apt-get install postgresql I get 7.4 . When I install Debian I generally expect the software to be supported for a long time. Perhaps it might make sense to declare it dead except for security issues? -- Rob Wultsch [EMAIL PROTECTED] wultsch (aim) -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How to modify ENUM datatypes?
>> Maybe I'm some crazy, radical DBA, but I've never had a version of >> pgsql get EOLed out from underneath me. Just for fun, I did a bit of digging in the release notes http://developer.postgresql.org/pgdocs/postgres/release.html and came up with this table about PG major releases and their follow-on bug fix/minor releases: Version Release date# updates Days till final update Days till next major 6.0 1997-01-29 0 0 130 6.1 1997-06-08 1 44 116 6.2 1997-10-02 1 15 150 6.3 1998-03-01 2 37 243 6.4 1998-10-30 2 51 222 6.5 1999-06-09 3 126 334 7.0 2000-05-08 3 187 340 7.1 2001-04-13 3 124 297 7.2 2002-02-04 8 1190296 7.3 2002-11-27 21 1867355 7.4 2003-11-17 19+ ? 429 8.0 2005-01-19 15+ ? 293 8.1 2005-11-08 11+ ? 392 8.2 2006-12-05 7+ ? 426 8.3 2008-02-04 1+ ? ? It's pretty clear that there was a sea-change around 7.2/7.3 --- before that, nobody thought that PG releases were anything that might be long-lived. And there's nothing in this table that suggests we've really settled on a new lifespan ... other than that we're still putting out new majors at a constant rate, and the community hasn't got the resources or interest to maintain an ever-increasing number of back branches. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How to modify ENUM datatypes?
> Maybe I'm some crazy, radical DBA, but I've never had a version of > pgsql get EOLed out from underneath me. I migrated from 7.4 to 8.1 > right around the time 8.2 came out then upgraded to 8.2 around 6 > months later. > > Where I work now we are looking at migrating from 8.1 to 8.2 or 8.3 > (depending on whether or not we have the man power to fix a few issues > with type coercion, our app, and 8.3) These aren't "the DBA got a > wild hair and just had to upgrade" upgrades. Each time I've migrated > it's been because there were performance or maintenance issues that > were solved by upgrading. Perhaps I'm in a unique situation as well, but as the DBA of a data-tank style DB, I haven't had a problem at all finding opportunities to upgrade to later versions of postgresql. My schema isn't all that complicated; it's just a very large amount of data and some very complex queries on that data- but the queries have been kept to extremely standard SQL specifically for migration and cross-platform reasons. It's definitely been annoying on occasion to find that I need to do a dump and restore to move to a new version, but at the same time cheap, large storage is extremely inexpensive when compared to the sort of storage acceptable for day-to-day use, so size isn't generally a problem- just dump to a big, cheap disk and then restore. I'm probably lucky in that I manage a shop that can tolerate a day's downtime for such a situation, but at the same time, we also demand the most from database performance for complex queries, so a day's downtime here could easily save many days' worth of query time down the line. 8.3, FWIW, was particularly attractive in this regard. I couldn't quite justify upgrading to the release candidates, but the performance improvements were pretty tempting. -- - David T. Wilson [EMAIL PROTECTED] -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How to modify ENUM datatypes?
On Thu, May 1, 2008 at 3:57 PM, Chris Browne <[EMAIL PROTECTED]> wrote: > [EMAIL PROTECTED] (Robert Treat) writes: > > On Thursday 01 May 2008 13:40, Tom Lane wrote: > > >> 7.4 was released 2003-11-17, so I think that it will very likely get > >> obsoleted at the end of 2008. > >> > > If that's the case, it'd be nice to get an official statement of that now. > :-) > > People have been making noises suggesting the idea already; I expect > that the flurry of counterarguments will start diminishing at that > point. Maybe I'm some crazy, radical DBA, but I've never had a version of pgsql get EOLed out from underneath me. I migrated from 7.4 to 8.1 right around the time 8.2 came out then upgraded to 8.2 around 6 months later. Where I work now we are looking at migrating from 8.1 to 8.2 or 8.3 (depending on whether or not we have the man power to fix a few issues with type coercion, our app, and 8.3) These aren't "the DBA got a wild hair and just had to upgrade" upgrades. Each time I've migrated it's been because there were performance or maintenance issues that were solved by upgrading. OTOH, a db I set up YEARS ago on 7.2 was still running last year I believe. they dump, initdb and reload it every year or two and it still works for what they designed the app on top of it to do. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How to modify ENUM datatypes?
[EMAIL PROTECTED] (Robert Treat) writes: > On Thursday 01 May 2008 13:40, Tom Lane wrote: >> 7.4 was released 2003-11-17, so I think that it will very likely get >> obsoleted at the end of 2008. >> > If that's the case, it'd be nice to get an official statement of that now. :-) People have been making noises suggesting the idea already; I expect that the flurry of counterarguments will start diminishing at that point. -- let name="cbbrowne" and tld="linuxdatabases.info" in String.concat "@" [name;tld];; http://www3.sympatico.ca/cbbrowne/sap.html "My dog appears to require more PM than my car, although he also seems to be cheaper to service." -- GSB -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How to modify ENUM datatypes?
Robert Treat <[EMAIL PROTECTED]> writes: > On Thursday 01 May 2008 13:40, Tom Lane wrote: >> I'm not sure how you're doing the math, but my copy of the release notes >> dates 7.3 as 2002-11-27 and 7.3.21 as 2008-01-07, which makes it five >> years plus that we provided bug-fix releases for 7.3. > The whole thing started with "If I were to have installed postgres 5 years > ago", which would be 2003-05-01, then I would not have gotten 5 years of > support from that system. Essentially that statement is true of any install > up to the 7.4 release. I have never heard of anyone measuring product support lifespans from any point other than the original release date. If you want to define it in some random other fashion, that's your privilege, but it doesn't change how I'm going to think about it. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How to modify ENUM datatypes?
On Thursday 01 May 2008 13:40, Tom Lane wrote:
> Robert Treat <[EMAIL PROTECTED]> writes:
> > And again, if you do the math, any install before 2008-11-17 would have
> > been on 7.3, which is less than 5 years.
>
> I'm not sure how you're doing the math, but my copy of the release notes
> dates 7.3 as 2002-11-27 and 7.3.21 as 2008-01-07, which makes it five
> years plus that we provided bug-fix releases for 7.3.
>
The whole thing started with "If I were to have installed postgres 5 years
ago", which would be 2003-05-01, then I would not have gotten 5 years of
support from that system. Essentially that statement is true of any install
up to the 7.4 release.
> > Or, looking forward, I'm not expecting
> > 7.4 will be supported beyond 2010 (there have already been calls to stop
> > supporting it for some time) which is what would be required if we really
> > have an expectation of support for more than 5 years.
>
> 7.4 was released 2003-11-17, so I think that it will very likely get
> obsoleted at the end of 2008.
>
If that's the case, it'd be nice to get an official statement of that now. :-)
--
Robert Treat
Build A Brighter LAMP :: Linux Apache {middleware} PostgreSQL
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How to modify ENUM datatypes?
Robert Treat <[EMAIL PROTECTED]> writes: > And again, if you do the math, any install before 2008-11-17 would have been > on 7.3, which is less than 5 years. I'm not sure how you're doing the math, but my copy of the release notes dates 7.3 as 2002-11-27 and 7.3.21 as 2008-01-07, which makes it five years plus that we provided bug-fix releases for 7.3. > Or, looking forward, I'm not expecting > 7.4 will be supported beyond 2010 (there have already been calls to stop > supporting it for some time) which is what would be required if we really > have an expectation of support for more than 5 years. 7.4 was released 2003-11-17, so I think that it will very likely get obsoleted at the end of 2008. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How to modify ENUM datatypes?
Robert Treat <[EMAIL PROTECTED]> writes: > This all sounds nice, but I don't see any movement from the project to > increase community commitment to 5 years for any release, so I think it's all > moot. "Movement"? We did in fact support 7.3 for five years. Other than the special case of deciding to obsolete pre-8.2 Windows ports, I don't see anything on the horizon that would cause us to obsolete the current releases earlier. If anything, I foresee pressure to support the latest releases longer than that, since as Greg said, they are more credible long-term prospects than 7.x ever was. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How to modify ENUM datatypes?
On Wednesday 30 April 2008 11:00, Craig Ringer wrote:
> Robert Treat wrote:
> > If one were to have built something on postgresql 5 years ago, they would
> > have had to do it on 7.3. Whenever anyone posts a problem on 7.3, the
> > first thing people do now days is jump up and down waving thier arms
> > about while exclaiming how quickly they should upgrade.
>
> [snip]
>
> > I'd have to
> > say that the core developers for this project do not release software
> > with the expectation that you will use if for more than 5 years.
>
> That says nothing about the people out there still using 7.3 and similar
> without problems, running well within its capabilities and happy with
> what it's doing. I doubt many people would advise them to upgrade - at
> least not in a hurry and not with any jumping and hand-waving.
>
> My impression from using PostgreSQL is that people using old versions
> are taken seriously. Data corruption, crash and security bug fixes get
> applied to very old versions. For example, 7.3.21 was released on Jan
> 2008, and includes several fixes:
>
> http://www.postgresql.org/docs/current/static/release-7-3-21.html
>
from those very release notes "This is expected to be the last PostgreSQL
release in the 7.3.X series. Users are encouraged to update to a newer
release branch soon."
If you are on any version of 7.3, the official response is "you need to
upgrade to a newer major version" regardless of your problems. You're
overlooking data-loss level bugs that can bite people even if they aren't
currently suffering from any issues.
And again, if you do the math, any install before 2008-11-17 would have been
on 7.3, which is less than 5 years. Or, looking forward, I'm not expecting
7.4 will be supported beyond 2010 (there have already been calls to stop
supporting it for some time) which is what would be required if we really
have an expectation of support for more than 5 years.
--
Robert Treat
Build A Brighter LAMP :: Linux Apache {middleware} PostgreSQL
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How to modify ENUM datatypes?
On Thursday 01 May 2008 01:30, Greg Smith wrote:
> On Wed, 30 Apr 2008, Robert Treat wrote:
> > Whenever anyone posts a problem on 7.3, the first thing people do now
> > days is jump up and down waving thier arms about while exclaiming how
> > quickly they should upgrade. While I am certain there are even older
> > versions of postgres still running in production out there, I'd have to
> > say that the core developers for this project do not release software
> > with the expectation that you will use if for more than 5 years.
>
> You could easily make a case that 7.3 wasn't quite mature enough overall
> to be useful for 5 years. There's little reason to keep pumping support
> effort into something with unfixable flaws. I know when I was using 7.4
> heavily, I never felt like that was something I could keep going for that
> long; the VACUUM issues in particular really stuck out as something I
> wouldn't be likely to handle on future hardware having larger databases.
>
> 8.1, on the other hand, is the first release I thought you could base a
> long-term effort on, and 8.2 and 8.3 have moved further in that direction.
> 8.1 has been out for 2.5 years now, and it seems like it's got plenty of
> useful left in it still (except on Windows). The improvements in 8.2 and
> 8.3 are significant but not hugely important unless you're suffering
> performance issues.
>
> Compare with 7.3, which came out at the end of 2002. By 2.5 years after
> that, the project was well into 8.0, which was clearly a huge leap.
> PITR, tablespaces, whole new buffer strategy, these are really fundamental
> and compelling rather than the more incremental improvements coming out
> nowadays.
>
This all sounds nice, but I don't see any movement from the project to
increase community commitment to 5 years for any release, so I think it's all
moot.
> (Obligatory Oracle comparison: for customers with standard support
> levels, Oracle 8.1 was EOL'd after slightly more than 4 years. It wasn't
> until V9 that they pushed that to 5 years)
>
And even that isn't full support. IIRC Oracle certified applications can only
be done within the first 3 years of the product. I think there are other
scenarios under 5 years as well.
--
Robert Treat
Build A Brighter LAMP :: Linux Apache {middleware} PostgreSQL
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How to modify ENUM datatypes?
On Wed, 30 Apr 2008, Robert Treat wrote: Whenever anyone posts a problem on 7.3, the first thing people do now days is jump up and down waving thier arms about while exclaiming how quickly they should upgrade. While I am certain there are even older versions of postgres still running in production out there, I'd have to say that the core developers for this project do not release software with the expectation that you will use if for more than 5 years. You could easily make a case that 7.3 wasn't quite mature enough overall to be useful for 5 years. There's little reason to keep pumping support effort into something with unfixable flaws. I know when I was using 7.4 heavily, I never felt like that was something I could keep going for that long; the VACUUM issues in particular really stuck out as something I wouldn't be likely to handle on future hardware having larger databases. 8.1, on the other hand, is the first release I thought you could base a long-term effort on, and 8.2 and 8.3 have moved further in that direction. 8.1 has been out for 2.5 years now, and it seems like it's got plenty of useful left in it still (except on Windows). The improvements in 8.2 and 8.3 are significant but not hugely important unless you're suffering performance issues. Compare with 7.3, which came out at the end of 2002. By 2.5 years after that, the project was well into 8.0, which was clearly a huge leap. PITR, tablespaces, whole new buffer strategy, these are really fundamental and compelling rather than the more incremental improvements coming out nowadays. (Obligatory Oracle comparison: for customers with standard support levels, Oracle 8.1 was EOL'd after slightly more than 4 years. It wasn't until V9 that they pushed that to 5 years) -- * Greg Smith [EMAIL PROTECTED] http://www.gregsmith.com Baltimore, MD -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How to modify ENUM datatypes?
On Wed, Apr 30, 2008 at 10:19:25AM -0400, Robert Treat wrote: > exclaiming how quickly they should upgrade. While I am certain there are even > older versions of postgres still running in production out there, I'd have to > say that the core developers for this project do not release software with > the expectation that you will use if for more than 5 years. You're equivocating here. The PostgreSQL project is under active development, and there are supported and unsupported versions. Moreover, it's not really just an application; it's more like application infrastructure. The database engine one uses for an application is more like a shared library, from the application's point of view, than it is like another application. Most application software doesn't remain under active development once delivered. You write it to the point where it works, and then when it is doing the job expected, it starts to be used. There is no maintenance, and there are no opportunities for new nifty features. This is _especially_ true of bespoke software, which is why most of it is so awful when you look at it: there's no incentive at all to make the code maintainable, because it will never get any maintenance anyway. Think of the difference between making durable goods like washing machines or automobiles, as compared to more or less disposable goods like kitchen gadgets or, these days, toasters and blenders. The former either come with or have available for them spare parts, schematic documents, and long warranties. The latter come with, if you're lucky, a warranty of a year and warnings that there are no servicable parts inside. Yet people expect to use their toasters and kitchen gadgets for years, and are annoyed if they fail after just a year. A -- Andrew Sullivan [EMAIL PROTECTED] +1 503 667 4564 x104 http://www.commandprompt.com/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How to modify ENUM datatypes?
Robert Treat wrote: If one were to have built something on postgresql 5 years ago, they would have had to do it on 7.3. Whenever anyone posts a problem on 7.3, the first thing people do now days is jump up and down waving thier arms about while exclaiming how quickly they should upgrade. [snip] > I'd have to > say that the core developers for this project do not release software > with the expectation that you will use if for more than 5 years. From what I've seen on the list so far upgrades are advised precisely because somebody has had to ask for help with a problem - one that's usually resolved in newer versions. Doubly so because the issues raised are usually performance related or are caused by limitations in 7.3 (or whatever they're running). If upgrading solves a problem that somebody is asking for help with, then advising the user to upgrade makes sense. Consider operating systems. An OS vendor might expect a given version to be used for five years or more, but if you ask them for help because you're encountering awful performance with 2TB disks or high CPU load on 10GigE networks they're quite likely to advise you to upgrade the OS to a version with enhancements that solve your problems. That says nothing about the people out there still using 7.3 and similar without problems, running well within its capabilities and happy with what it's doing. I doubt many people would advise them to upgrade - at least not in a hurry and not with any jumping and hand-waving. I often see responses along the lines of "if you're using 8.3 then just do otherwise you'll need to" - so there doesn't appear to be any assumption that the first step must be to upgrade to the latest version. My impression from using PostgreSQL is that people using old versions are taken seriously. Data corruption, crash and security bug fixes get applied to very old versions. For example, 7.3.21 was released on Jan 2008, and includes several fixes: http://www.postgresql.org/docs/current/static/release-7-3-21.html Given that 7.3 was released in late 2002: http://www.postgresql.org/docs/current/static/release-7-3.html I think that's pretty good myself. Given that PostgreSQL upgrades aren't trivial, it's important to have these ongoing releases for older versions. It's great to see that need so well met. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How to modify ENUM datatypes?
On Monday 28 April 2008 10:28, Andrew Sullivan wrote:
> On Sat, Apr 26, 2008 at 08:33:28PM -0400, Robert Treat wrote:
> > enum types custom ordering. It also showcases the idea of data
> > definitions that "should never change", but that do changes every half
> > dozen years or so. Now you can argue that since it is expected that the
> > ratings might change in some way every few years that an enum type is not
> > a good choice for this, but I feel like some type of counter-argument is
> > that this is probably longer than one would expect thier database
> > software to last. :-)
>
> I think that if you are building software on the premise that it's
> only going to last five years, you oughta have a look around on the
> Internet again. Or think about why banks spent the money they did a
> few years back poring over ancient code making sure that two-digit
> year representations weren't in use.
>
If one were to have built something on postgresql 5 years ago, they would have
had to do it on 7.3. Whenever anyone posts a problem on 7.3, the first thing
people do now days is jump up and down waving thier arms about while
exclaiming how quickly they should upgrade. While I am certain there are even
older versions of postgres still running in production out there, I'd have to
say that the core developers for this project do not release software with
the expectation that you will use if for more than 5 years.
--
Robert Treat
Build A Brighter LAMP :: Linux Apache {middleware} PostgreSQL
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How to modify ENUM datatypes?
On Monday 28 April 2008 17:35, Jeff Davis wrote:
> On Sat, 2008-04-26 at 20:33 -0400, Robert Treat wrote:
> > I think one of the best examples of this is the movie rating system
> > (which I blogged about at
> > http://people.planetpostgresql.org/xzilla/index.php?/archives/320-Postgre
> >SQL-8.3-Features-Enum-Datatype.html )
> >
> > It's a good example of setting pre-defined values that really can
> > leverage the enum types custom ordering. It also showcases the idea of
> > data definitions that "should never change", but that do changes every
> > half dozen years or so. Now you can argue that since it is expected that
> > the ratings might change in some way every few years that an enum type is
> > not a good choice for this, but I feel like some type of counter-argument
> > is that this is probably longer than one would expect thier database
> > software to last. :-)
>
> Let's say you have ratings A, B, and D for 5 years, and then you add
> rating C between B and D.
>
> If you have a constant stream of movies that must be reviewed, then the
> addition of a new rating will necessarily take some fraction of the
> movies away from at least one of the old ratings. In that case, is an
> old B really equal to a new B?
>
> Similar concerns apply to other changes in ENUMs, and for that matter,
> they apply to the FK design, as well.
>
> I would say the *actual* rating is the combination of the rating name,
> and the version of the standards under which it was rated.
>
*You* would say that, but typically movie ratings are not adjusted when a new
rating comes out. For good examples of this, go back and look at 70's era
movies (cowboy movies, war movies, etc...) that are G rated, but have a lot
of people being shot/killed on-screen, something which would give you an
automatic PG rating today. (There are similar issues with PG/R movies in the
80's, typically focused on violence and drug use, before the PG-13 rating
came out).
--
Robert Treat
Build A Brighter LAMP :: Linux Apache {middleware} PostgreSQL
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How to modify ENUM datatypes?
On Sat, 2008-04-26 at 20:33 -0400, Robert Treat wrote: > I think one of the best examples of this is the movie rating system (which I > blogged about at > http://people.planetpostgresql.org/xzilla/index.php?/archives/320-PostgreSQL-8.3-Features-Enum-Datatype.html > ) > > It's a good example of setting pre-defined values that really can leverage > the > enum types custom ordering. It also showcases the idea of data definitions > that "should never change", but that do changes every half dozen years or so. > Now you can argue that since it is expected that the ratings might change in > some way every few years that an enum type is not a good choice for this, but > I feel like some type of counter-argument is that this is probably longer > than one would expect thier database software to last. :-) > Let's say you have ratings A, B, and D for 5 years, and then you add rating C between B and D. If you have a constant stream of movies that must be reviewed, then the addition of a new rating will necessarily take some fraction of the movies away from at least one of the old ratings. In that case, is an old B really equal to a new B? Similar concerns apply to other changes in ENUMs, and for that matter, they apply to the FK design, as well. I would say the *actual* rating is the combination of the rating name, and the version of the standards under which it was rated. Regards, Jeff Davis -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How to modify ENUM datatypes?
Hi, Merlin Moncure wrote: On Thu, Apr 24, 2008 at 3:01 PM, Tino Wildenhain <[EMAIL PROTECTED]> wrote: Merlin Moncure wrote: I think you're being a little too hard on enums here. I was actually in the anti-enum camp until it was demonstrated to me (and in my own testing) that using enum for natural ordering vs. fielding the ordering of the type out to a join is can be a huge win in such cases where it is important. Relational theory is all well and good, but in practical terms things like record size, index size, and query performance are important. Uhm. Sorry what? Can you demonstrate this particular use? When I first saw discussion about enumns I kinda hoped they will be implemented as kind of macro to really map to a table. But here you go. I'm still looking for a good example to demonstrate the usefullness of enums (same for arrays for that matter) You must not be aware that enums are naturally ordered to make that statement. Suppose your application needs to order a large table by a,b,c where b is the an 'enum' type of data. With an enum, the order is inlined into the key order, otherwise it's out of line, meaning your you key is larger (enum is 4 bytes, varchar is guaranteed to be larger), and you need to join out to get the ordering position, use a functional index, or cache it in the main table. I see, but couldn't you just use int in this case? And map only when you need the values for display (usually you want it localized anyway) I agree with disagree with you on arrays. I think they are generally a bad idea in terms of using them as a column type. However they are useful passing data to/from functions and back/forth from the client. Yes of course, I thought of that (wondering why we can't use value expressions everywhere) Tino -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How to modify ENUM datatypes?
Chris Browne wrote: [EMAIL PROTECTED] (Robert Treat) writes: I feel like some type of counter-argument is that this is probably longer than one would expect thier database software to last. :-) That has the counterargument that if the database software works, it's likely to get used for longer than one would expect. I don't think I have ever seen a case where DB-based software got replaced *earlier* than planned. I have - but only where it only worked if you adopt a very limited definition of "worked". When management's paid for something and they want to deploy it despite warnings that it's just not going to do the job these things can happen, and a rush project to replace the system can arise when it becomes clear (to management) just how broken the system really is. I've been lucky enough not to have to directly experience such problems, but I know a couple of people who've been saddled with implementing, then rapidly replacing, monster-from-the-deep database systems. "VB6 and IBM UniVerse? Why not? ..." I've hacked together small web apps in a couple of days that've gone on to see six years (and ongoing) of heavy use at my work - because they do the job, and because I haven't had the time or cause to replace them with something cleaner. Every couple of years I have to go back and fix something when a mistake in the code bites me, but all in all they've worked amazingly well. Given that I'd have to agree that it's a good idea to assume your database software will live on well beyond your expectations, and it's worth considering how you'll feel when someone calls you in 5 years with an issue with some code you haven't touched since you wrote it as a throwaway tool. The call will, of course, be urgent, and involve either breakage that must be fixed in 10 minutes for some critical business process (that you didn't know they even used your tool for) to continue, or will be to inform you that you have one day to make major changes they could've told you about six weeks ago if they felt like it. So ... it's well worth considering the long term now. Unfortunately I speak from recent experience - and my beginner perl+MySQL code was NOT designed for long term flexibility and robustness. *shudder*. -- Craig Ringer -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How to modify ENUM datatypes?
[EMAIL PROTECTED] (Robert Treat) writes:
> I feel like some type of counter-argument is that this is probably longer
> than one would expect thier database software to last. :-)
That has the counterargument that if the database software works, it's
likely to get used for longer than one would expect.
I don't think I have ever seen a case where DB-based software got
replaced *earlier* than planned.
--
output = reverse("ofni.sesabatadxunil" "@" "enworbbc")
http://cbbrowne.com/info/emacs.html
Rules of the Evil Overlord #172. "I will allow guards to operate under
a flexible work schedule. That way if one is feeling sleepy, he can
call for a replacement, punch out, take a nap, and come back refreshed
and alert to finish out his shift.
Re: [GENERAL] How to modify ENUM datatypes?
On Sat, Apr 26, 2008 at 08:33:28PM -0400, Robert Treat wrote: > enum types custom ordering. It also showcases the idea of data definitions > that "should never change", but that do changes every half dozen years or so. > Now you can argue that since it is expected that the ratings might change in > some way every few years that an enum type is not a good choice for this, but > I feel like some type of counter-argument is that this is probably longer > than one would expect thier database software to last. :-) I think that if you are building software on the premise that it's only going to last five years, you oughta have a look around on the Internet again. Or think about why banks spent the money they did a few years back poring over ancient code making sure that two-digit year representations weren't in use. You can _of course_ make this sort of trade-off: the cost of the upgrade might be worth the natural ordering and boost in performance. But that was part of my point when noting that enums oughta come with a warning (and why I compared them to char()). I'm not arguing that they're completely useless; just that, like any oddly specialised tool, they require careful use. A -- Andrew Sullivan [EMAIL PROTECTED] +1 503 667 4564 x104 http://www.commandprompt.com/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How to modify ENUM datatypes?
On Friday 25 April 2008 14:56, Merlin Moncure wrote:
> On Thu, Apr 24, 2008 at 3:01 PM, Tino Wildenhain <[EMAIL PROTECTED]> wrote:
> > Merlin Moncure wrote:
> > > I think you're being a little too hard on enums here. I was actually
> > > in the anti-enum camp until it was demonstrated to me (and in my own
> > > testing) that using enum for natural ordering vs. fielding the
> > > ordering of the type out to a join is can be a huge win in such cases
> > > where it is important. Relational theory is all well and good, but in
> > > practical terms things like record size, index size, and query
> > > performance are important.
> >
> > Uhm. Sorry what? Can you demonstrate this particular use?
> > When I first saw discussion about enumns I kinda hoped they
> > will be implemented as kind of macro to really map to a table.
> > But here you go. I'm still looking for a good example to
> > demonstrate the usefullness of enums (same for arrays for that
> > matter)
>
> You must not be aware that enums are naturally ordered to make that
> statement. Suppose your application needs to order a large table by
> a,b,c where b is the an 'enum' type of data. With an enum, the order
> is inlined into the key order, otherwise it's out of line, meaning
> your you key is larger (enum is 4 bytes, varchar is guaranteed to be
> larger), and you need to join out to get the ordering position, use a
> functional index, or cache it in the main table.
>
I think one of the best examples of this is the movie rating system (which I
blogged about at
http://people.planetpostgresql.org/xzilla/index.php?/archives/320-PostgreSQL-8.3-Features-Enum-Datatype.html
)
It's a good example of setting pre-defined values that really can leverage the
enum types custom ordering. It also showcases the idea of data definitions
that "should never change", but that do changes every half dozen years or so.
Now you can argue that since it is expected that the ratings might change in
some way every few years that an enum type is not a good choice for this, but
I feel like some type of counter-argument is that this is probably longer
than one would expect thier database software to last. :-)
--
Robert Treat
Build A Brighter LAMP :: Linux Apache {middleware} PostgreSQL
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How to modify ENUM datatypes?
On Thu, Apr 24, 2008 at 3:01 PM, Tino Wildenhain <[EMAIL PROTECTED]> wrote: > Merlin Moncure wrote: > > I think you're being a little too hard on enums here. I was actually > > in the anti-enum camp until it was demonstrated to me (and in my own > > testing) that using enum for natural ordering vs. fielding the > > ordering of the type out to a join is can be a huge win in such cases > > where it is important. Relational theory is all well and good, but in > > practical terms things like record size, index size, and query > > performance are important. > > > > Uhm. Sorry what? Can you demonstrate this particular use? > When I first saw discussion about enumns I kinda hoped they > will be implemented as kind of macro to really map to a table. > But here you go. I'm still looking for a good example to > demonstrate the usefullness of enums (same for arrays for that > matter) You must not be aware that enums are naturally ordered to make that statement. Suppose your application needs to order a large table by a,b,c where b is the an 'enum' type of data. With an enum, the order is inlined into the key order, otherwise it's out of line, meaning your you key is larger (enum is 4 bytes, varchar is guaranteed to be larger), and you need to join out to get the ordering position, use a functional index, or cache it in the main table. I agree with disagree with you on arrays. I think they are generally a bad idea in terms of using them as a column type. However they are useful passing data to/from functions and back/forth from the client. merlin -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Sync some database tables, but not others ... WAS Re: [GENERAL] How to modify ENUM datatypes?
Tino Wildenhain wrote: D. Dante Lorenso wrote: Alvaro Herrera wrote: D. Dante Lorenso wrote: Or, here's another way to look at it ... make it easier to modify ENUM datatypes because we all know that you will eventually need that feature whether you males, females, and unknowns think so or not. Agreed. Let's keep in mind that the current ENUM implementation is completely new. Here's a use case that I doubt too many are aware of ... what's unique about ENUM is that the data for the ENUM becomes part of the schema of a database. This is actually something I like very much. I have systems where the DEV and PROD databases are separate and must be synchronized when pushing out new features. I currently use non-free tools to synchronize just the schemas for both databases. Often if a new row is added to a lookup table, that row doesn't make it to the QA or PROD databases because it's part of the data of a table and is not part of the schema. For data (like ENUM data) that should be consistent across databases, it helps if it gets deployed with the schema so that lookups will succeed properly. Well since its configuration and not payload its nothing wrong with just having the data in your repository as well and load it every time when you roll out a new release. I have a convenient 3rd party tool that will "sync this database schema with that database schema". I just run the tool, accept the discovered changes and voila, I've deployed the database changes to the next environment. I haven't written any custom scripts to import content into specific tables. As I see it, that would be a little complex also since you'd have to find the difference between dev and prod and only push the changes across (additions or deletes). For potentially hundreds of small ENUM-like lookup tables, this seems quite tedious ... like the kind of thing a DB sync tool should handle for you ;-) Perhaps there is a 3rd party tool that would not only sync the DB schema, but could add a list of tables which must also have their data synced? Something that could remember that these 50 tables are "constant/deployable" and must be mirrored as-is to the other database while these other tables store environment-specific data and should not be synced. Anyone know of such a tool? -- Dante Cheers Tino -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How to modify ENUM datatypes?
On Apr 25, 2008, at 8:19 AM, Ben Chobot wrote: On Apr 25, 2008, at 4:49 AM, Giorgio Valoti wrote: And reorder them, too. Why would you want to reorder an enum? It seems to me the point of them is to hold a small list of valid values. The order the list is described in surely should be irrelevant? One of the main charms of using an enum for some things is that it has a natural ordering. Critical > Urgent > Normal > Low > Marginal It's not the only reason to use an enum, but for me it'd be the most compelling. Cheers, Steve -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How to modify ENUM datatypes?
On Apr 25, 2008, at 4:49 AM, Giorgio Valoti wrote: And reorder them, too. Why would you want to reorder an enum? It seems to me the point of them is to hold a small list of valid values. The order the list is described in surely should be irrelevant? -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How to modify ENUM datatypes?
D. Dante Lorenso wrote: Alvaro Herrera wrote: D. Dante Lorenso wrote: Or, here's another way to look at it ... make it easier to modify ENUM datatypes because we all know that you will eventually need that feature whether you males, females, and unknowns think so or not. Agreed. Let's keep in mind that the current ENUM implementation is completely new. Here's a use case that I doubt too many are aware of ... what's unique about ENUM is that the data for the ENUM becomes part of the schema of a database. This is actually something I like very much. I have systems where the DEV and PROD databases are separate and must be synchronized when pushing out new features. I currently use non-free tools to synchronize just the schemas for both databases. Often if a new row is added to a lookup table, that row doesn't make it to the QA or PROD databases because it's part of the data of a table and is not part of the schema. For data (like ENUM data) that should be consistent across databases, it helps if it gets deployed with the schema so that lookups will succeed properly. Well since its configuration and not payload its nothing wrong with just having the data in your repository as well and load it every time when you roll out a new release. Cheers Tino -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How to modify ENUM datatypes?
On 24/apr/08, at 22:15, Matthew T. O'Connor wrote: Bruce Momjian wrote: Matthew T. O'Connor wrote: D. Dante Lorenso wrote: Or, here's another way to look at it ... make it easier to modify ENUM datatypes because we all know that you will eventually need that feature whether you males, females, and unknowns think so or not. +1 Added to TODO: * Allow adding enumerated values to an existing enumerated data type And removing values if possible (fail if values are in use?). And reorder them, too. -- Giorgio Valoti -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How to modify ENUM datatypes?
On Thu, Apr 24, 2008 at 10:25:44AM +0200, Andreas 'ads' Scherbaum wrote: > If you define a medical database, you have to extend the ENUM values a > bit, but even then you know the possible values in advance Considering scary genetic experiments I wouldn't even be sure about that. > It all depends on the use case, just like ENUM itself. Absolutely. Maybe another outcome of this discussion might be that PostgreSQL documentation - as thorough as the whole project is set up - should never use gender as an example for ENUM (it doesn't now in 8.3 docs btw). Karsten -- GPG key ID E4071346 @ wwwkeys.pgp.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How to modify ENUM datatypes?
On Wed, Apr 23, 2008 at 07:46:07PM -0400, brian wrote: >> I would put it that gender is not so easily defined, which makes it a >> poor choice for enum. > > Absolutely true. Which is odd, because this example is trotted out > whenever there's a thread about ENUMs. So it's good we got it in the archives now :-) Karsten -- GPG key ID E4071346 @ wwwkeys.pgp.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How to modify ENUM datatypes?
On Wed, Apr 23, 2008 at 05:18:12PM -0600, Scott Marlowe wrote: > > hermaphrodite > > transgender with female phenotype > > transgender with male phenotype > the most common and easiest is intersex. The political correctness of any one term changes over time. The above list should close the circle if phenotype and genotype are taken into account: male (g: m, p: m) female (g: f, p: m) hermaphrodite or intersex (g: f+m, p: f+m) (note this also includes mosaic -- extremely rare) transgender f pheno (g: f+m, p: f) transgender m pheno (g: f+m, p: f) > I would put it that gender is not so easily defined, which makes it a > poor choice for enum. That surely is the last word on it also from my point of view. Karsten -- GPG key ID E4071346 @ wwwkeys.pgp.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How to modify ENUM datatypes?
On Wed, Apr 23, 2008 at 05:51:48PM -0400, Robert Treat wrote: > > Add > > > > hermaphrodite > > transgender with female phenotype > > transgender with male phenotype > > > > and you should be set from current medical science's point > > of view ;-) > > > > The standard is unknown, male, female, and n/a. Apparently the standard doesn't care about reality. But it all depends on the circumstantial needs. Karsten -- GPG key ID E4071346 @ wwwkeys.pgp.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How to modify ENUM datatypes?
On Thu, 24 Apr 2008 14:02:07 -0400 Merlin Moncure wrote: > On Thu, Apr 24, 2008 at 12:22 PM, Andrew Sullivan <[EMAIL PROTECTED]> wrote: > > The first time I encountered them, I thought enums were a filthy, > > ill-conceived answer to a problem that didn't exist, implemented by people > > who didn't understand relational databases. With considerably more > > experience under my belt than then, I say now that my original estimation > > was too kind. > > I think you're being a little too hard on enums here. No, i don't think, Andrew is too hard here. As said before: in some special cases enum makes sense, like you said: > I was actually in the anti-enum camp until it was demonstrated to me > (and in my own testing) that using enum for natural ordering vs. fielding the > ordering of the type out to a join is can be a huge win in such cases > where it is important. But the problem are really not this special cases, the real problem are all the people who don't understand about enum and just try to use it because "it seems to fit best" - unless they run into problems. So we have a good tool in your hands, we just have to tell/teach the people, how to use it and especially when not to use it. Kind regards -- Andreas 'ads' Scherbaum German PostgreSQL User Group -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How to modify ENUM datatypes?
Tom Lane wrote: > Bruce Momjian <[EMAIL PROTECTED]> writes: > > Yep, updated: > > * Allow adding/removing enumerated values to an existing enumerated > > data > > Renaming an existing value might be interesting too (and would be far > easier than either of the above). TODO updated: * Allow adding/renaming/removing enumerated values to an existing enumerated data type -- Bruce Momjian <[EMAIL PROTECTED]>http://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How to modify ENUM datatypes?
Bruce Momjian <[EMAIL PROTECTED]> writes: > Yep, updated: > * Allow adding/removing enumerated values to an existing enumerated > data Renaming an existing value might be interesting too (and would be far easier than either of the above). regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How to modify ENUM datatypes?
Matthew T. O'Connor wrote: > Bruce Momjian wrote: > > Matthew T. O'Connor wrote: > >> D. Dante Lorenso wrote: > >>> Or, here's another way to look at it ... make it easier to modify ENUM > >>> datatypes because we all know that you will eventually need that > >>> feature whether you males, females, and unknowns think so or not. > >> +1 > > > > Added to TODO: > > > > * Allow adding enumerated values to an existing enumerated data > > type > > And removing values if possible (fail if values are in use?). Yep, updated: * Allow adding/removing enumerated values to an existing enumerated data -- Bruce Momjian <[EMAIL PROTECTED]>http://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How to modify ENUM datatypes?
Alvaro Herrera wrote: D. Dante Lorenso wrote: Or, here's another way to look at it ... make it easier to modify ENUM datatypes because we all know that you will eventually need that feature whether you males, females, and unknowns think so or not. Agreed. Let's keep in mind that the current ENUM implementation is completely new. Here's a use case that I doubt too many are aware of ... what's unique about ENUM is that the data for the ENUM becomes part of the schema of a database. This is actually something I like very much. I have systems where the DEV and PROD databases are separate and must be synchronized when pushing out new features. I currently use non-free tools to synchronize just the schemas for both databases. Often if a new row is added to a lookup table, that row doesn't make it to the QA or PROD databases because it's part of the data of a table and is not part of the schema. For data (like ENUM data) that should be consistent across databases, it helps if it gets deployed with the schema so that lookups will succeed properly. -- Dante -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How to modify ENUM datatypes?
Bruce Momjian wrote: Matthew T. O'Connor wrote: D. Dante Lorenso wrote: Or, here's another way to look at it ... make it easier to modify ENUM datatypes because we all know that you will eventually need that feature whether you males, females, and unknowns think so or not. +1 Added to TODO: * Allow adding enumerated values to an existing enumerated data type And removing values if possible (fail if values are in use?). -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How to modify ENUM datatypes?
[EMAIL PROTECTED] (Andrew Sullivan) writes: > On Wed, Apr 23, 2008 at 07:46:07PM -0400, brian wrote: >> >> Absolutely true. Which is odd, because this example is trotted out >> whenever there's a thread about ENUMs. > > I don't think it's odd at all. In my view, the people who think enums are a > good datatype for databases are exactly the sorts who'd think that their > data is as static as this poor understanding of the vagaries of individuals' > sex (gender is a different problem, given its association with social roles) > would suggest. > > The world moves around in unexpected ways. Your data model needs to > accommodate itself to the world, because the alternative is not going to > happen. By the same token, a limited model, at least in this area, frequently is sufficient to cover the set of ways in which legal systems recognize and consider gender when constructing legislation. And it's not evident that the simplification is a dramatic oversimplification that causes a great deal of legal failures to the extent to which it mandates that every system *needs* to track sex in a more detailed fashion than (male, female, unknown, n/a). I'm not sure, for instance, that I actually know what the word "phenotype" means, and there's reason to imagine I might be somewhat more "generally literate" than average. If I'm not sure, there seems little reason to expect that people with varying levels of comprehension necessarily be able to choose from a more elaborate set of options with accuracy. After spending literal billions of dollars on security efforts, it doesn't appear that security infrastructures that have *enormous* incentive have had much luck successfully identifying who is a terrorist and who is not; they consistently have extraordinary levels of "Type II" (false positive) reporting errors. -- let name="cbbrowne" and tld="acm.org" in name ^ "@" ^ tld;; http://cbbrowne.com/info/linuxdistributions.html "There is no reason anyone would want a computer in their home". -- Ken Olson, Pres. and founder of Digital Equipment Corp. 1977 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How to modify ENUM datatypes?
Merlin Moncure wrote: On Thu, Apr 24, 2008 at 12:22 PM, Andrew Sullivan <[EMAIL PROTECTED]> wrote: The first time I encountered them, I thought enums were a filthy, ill-conceived answer to a problem that didn't exist, implemented by people who didn't understand relational databases. With considerably more experience under my belt than then, I say now that my original estimation was too kind. I think you're being a little too hard on enums here. I was actually in the anti-enum camp until it was demonstrated to me (and in my own testing) that using enum for natural ordering vs. fielding the ordering of the type out to a join is can be a huge win in such cases where it is important. Relational theory is all well and good, but in practical terms things like record size, index size, and query performance are important. Uhm. Sorry what? Can you demonstrate this particular use? When I first saw discussion about enumns I kinda hoped they will be implemented as kind of macro to really map to a table. But here you go. I'm still looking for a good example to demonstrate the usefullness of enums (same for arrays for that matter) Cheers Tino -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How to modify ENUM datatypes?
On Thu, Apr 24, 2008 at 12:22 PM, Andrew Sullivan <[EMAIL PROTECTED]> wrote: > The first time I encountered them, I thought enums were a filthy, > ill-conceived answer to a problem that didn't exist, implemented by people > who didn't understand relational databases. With considerably more > experience under my belt than then, I say now that my original estimation > was too kind. I think you're being a little too hard on enums here. I was actually in the anti-enum camp until it was demonstrated to me (and in my own testing) that using enum for natural ordering vs. fielding the ordering of the type out to a join is can be a huge win in such cases where it is important. Relational theory is all well and good, but in practical terms things like record size, index size, and query performance are important. I'll admit that if computers were infinitely fast, I'd probably use enums less, although I still like them for things like male/female. I think they have a place. The difficulty of manipulating enums is simply a shortcoming of the implementation which could presumably be solved at some later point. merlin -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How to modify ENUM datatypes?
On Thu, Apr 24, 2008 at 10:22 AM, Andrew Sullivan <[EMAIL PROTECTED]> wrote: > > Otherwise, don't use enums. They should be marked (like char(), IMO) in the > manual as, "Warning: you probably don't want to use this datatype. Go think > some more." Good point. I think enums are kind like arrays. Most of the time they're a bad idea. sometimes they're an ok idea, and very very rarely, they are exactly what you need to solve a certain problem. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How to modify ENUM datatypes?
On Thu, Apr 24, 2008 at 11:04:10AM -0500, D. Dante Lorenso wrote: > > Or, here's another way to look at it ... make it easier to modify ENUM > datatypes because we all know that you will eventually need that feature > whether you males, females, and unknowns think so or not. Well, heck, why don't you just store your data in one infinitely wide table with 10,000 nullable fields? Then you can complain about the storage or performance implications. If you are devoted to an enum datatype, then you don't want to normalise your data further. With an incredibly small number of exceptions, I'd argue that means one of two things: either it doesn't need normalisation, in which case a relational database is the wrong tool for this job; or else it does need normalisation, but you don't understand how relational databases work well enough to do it properly. [Later: on re-reading this paragraph, I thought of a third possibility: that you're violating the first rule of optimisation.] The first time I encountered them, I thought enums were a filthy, ill-conceived answer to a problem that didn't exist, implemented by people who didn't understand relational databases. With considerably more experience under my belt than then, I say now that my original estimation was too kind. I do see what someone says upthread: if you have a special case where the storage saving adequately offsets the maintenance cost, this might be worth it. But if you've really analysed your case that carefully, and understand the costs (i.e. you won't carp later when changing is expensive) you already know what you're doing. Otherwise, don't use enums. They should be marked (like char(), IMO) in the manual as, "Warning: you probably don't want to use this datatype. Go think some more." A -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How to modify ENUM datatypes?
Matthew T. O'Connor wrote: > D. Dante Lorenso wrote: > > Andrew Sullivan wrote: > >> I don't think it's odd at all. In my view, the people who think > >> enums are a > >> good datatype for databases are exactly the sorts who'd think that their > >> data is as static as this poor understanding of the vagaries of > >> individuals' > >> sex (gender is a different problem, given its association with social > >> roles) > >> would suggest. > > > > Or, here's another way to look at it ... make it easier to modify ENUM > > datatypes because we all know that you will eventually need that > > feature whether you males, females, and unknowns think so or not. > > +1 Added to TODO: * Allow adding enumerated values to an existing enumerated data type -- Bruce Momjian <[EMAIL PROTECTED]>http://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How to modify ENUM datatypes?
D. Dante Lorenso wrote: > Or, here's another way to look at it ... make it easier to modify ENUM > datatypes because we all know that you will eventually need that feature > whether you males, females, and unknowns think so or not. Agreed. Let's keep in mind that the current ENUM implementation is completely new. -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How to modify ENUM datatypes?
D. Dante Lorenso wrote: Andrew Sullivan wrote: I don't think it's odd at all. In my view, the people who think enums are a good datatype for databases are exactly the sorts who'd think that their data is as static as this poor understanding of the vagaries of individuals' sex (gender is a different problem, given its association with social roles) would suggest. Or, here's another way to look at it ... make it easier to modify ENUM datatypes because we all know that you will eventually need that feature whether you males, females, and unknowns think so or not. +1 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How to modify ENUM datatypes?
Andrew Sullivan wrote: On Wed, Apr 23, 2008 at 07:46:07PM -0400, brian wrote: Absolutely true. Which is odd, because this example is trotted out whenever there's a thread about ENUMs. I don't think it's odd at all. In my view, the people who think enums are a good datatype for databases are exactly the sorts who'd think that their data is as static as this poor understanding of the vagaries of individuals' sex (gender is a different problem, given its association with social roles) would suggest. Or, here's another way to look at it ... make it easier to modify ENUM datatypes because we all know that you will eventually need that feature whether you males, females, and unknowns think so or not. -- Dante -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How to modify ENUM datatypes?
On Thu, Apr 24, 2008 at 9:39 AM, Andrew Sullivan <[EMAIL PROTECTED]> wrote: > On Wed, Apr 23, 2008 at 07:46:07PM -0400, brian wrote: > > > > Absolutely true. Which is odd, because this example is trotted out > > whenever there's a thread about ENUMs. > > I don't think it's odd at all. In my view, the people who think enums are a > good datatype for databases are exactly the sorts who'd think that their > data is as static as this poor understanding of the vagaries of individuals' > sex (gender is a different problem, given its association with social roles) > would suggest. > > The world moves around in unexpected ways. Your data model needs to > accommodate itself to the world, because the alternative is not going to > happen. Mostly true, but there are systems where certain parts really do tend to be static over very long periods, and for those, I might admit to ENUM being an answer. for instance, in the USAF, all units being worked on in our avionics shop were in one of three categories, Awaiting Maintenance, Awaiting Parts, or In Work. They were abbreviated AWM, AWP, and INW. That was back in the 1980s. I'm willing to bet it hasn't changed since then. For those types of problems, enums make a certain amount of sense, especially if you're tracking thousands of line units being worked on every hour of every day across the US. the small saving in space adds up fast. But that's an artificially constructed set of choices. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How to modify ENUM datatypes?
On Wed, Apr 23, 2008 at 07:46:07PM -0400, brian wrote: > > Absolutely true. Which is odd, because this example is trotted out > whenever there's a thread about ENUMs. I don't think it's odd at all. In my view, the people who think enums are a good datatype for databases are exactly the sorts who'd think that their data is as static as this poor understanding of the vagaries of individuals' sex (gender is a different problem, given its association with social roles) would suggest. The world moves around in unexpected ways. Your data model needs to accommodate itself to the world, because the alternative is not going to happen. A -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How to modify ENUM datatypes?
On Wed, 23 Apr 2008 17:18:12 -0600 Scott Marlowe wrote: > I would put it that gender is not so easily defined, which makes it a > poor choice for enum. That's why my original statement had the additional note about special cases. If you write an address book you normally don't want to add information like transgender - just 'male', 'female', 'unknown' seems enough here. If you define a medical database, you have to extend the ENUM values a bit, but even then you know the possible values in advance before creating the ENUM - just that you have some more choices than in your address book. It all depends on the use case, just like ENUM itself. Kind regards -- Andreas 'ads' Scherbaum German PostgreSQL User Group -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How to modify ENUM datatypes?
Scott Marlowe wrote: On Wed, Apr 23, 2008 at 12:10 PM, Karsten Hilbert <[EMAIL PROTECTED]> wrote: On Wed, Apr 23, 2008 at 12:38:48PM +0200, Andreas 'ads' Scherbaum wrote: > Yes. You should/can use ENUM for something like 'gender': > male, female, unknown. You don't need to add other values ever (yeah, i > skipped some special cases). I was gonna say ! :-) Add hermaphrodite transgender with female phenotype transgender with male phenotype and you should be set from current medical science's point of view ;-) Actually, hermaphrodite specifies complete male and female genitalia, which is impossible in humans. While various forms of address are available for people born with parts of both male and female genitalia, the most common and easiest is intersex. The folks here: http://www.isna.org/faq/ have a lot more to say about it, and seeing as how many of them ARE intersex, I'd leave it up to them. Scott, there's absolutely nothing in that FAQ about their database, let alone whether or not they use ENUMs. (heh) While most transgender folks prefer to be referred to as their assumed gender, there are some gender queer folks who prefer other forms of address. I would put it that gender is not so easily defined, which makes it a poor choice for enum. Absolutely true. Which is odd, because this example is trotted out whenever there's a thread about ENUMs. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How to modify ENUM datatypes?
On Wed, Apr 23, 2008 at 12:10 PM, Karsten Hilbert <[EMAIL PROTECTED]> wrote: > On Wed, Apr 23, 2008 at 12:38:48PM +0200, Andreas 'ads' Scherbaum wrote: > > > Yes. You should/can use ENUM for something like 'gender': > > male, female, unknown. You don't need to add other values ever (yeah, i > > skipped some special cases). > I was gonna say ! :-) > > Add > > hermaphrodite > transgender with female phenotype > transgender with male phenotype > > and you should be set from current medical science's point > of view ;-) Actually, hermaphrodite specifies complete male and female genitalia, which is impossible in humans. While various forms of address are available for people born with parts of both male and female genitalia, the most common and easiest is intersex. The folks here: http://www.isna.org/faq/ have a lot more to say about it, and seeing as how many of them ARE intersex, I'd leave it up to them. While most transgender folks prefer to be referred to as their assumed gender, there are some gender queer folks who prefer other forms of address. I would put it that gender is not so easily defined, which makes it a poor choice for enum. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How to modify ENUM datatypes?
On Wed, Apr 23, 2008 at 2:51 PM, Robert Treat <[EMAIL PROTECTED]> wrote: > On Wednesday 23 April 2008 14:10, Karsten Hilbert wrote: > > On Wed, Apr 23, 2008 at 12:38:48PM +0200, Andreas 'ads' Scherbaum wrote: > > > Yes. You should/can use ENUM for something like 'gender': > > > male, female, unknown. You don't need to add other values ever (yeah, i > > > skipped some special cases). > > > > I was gonna say ! :-) > > > > Add > > > > hermaphrodite > > transgender with female phenotype > > transgender with male phenotype > > > > and you should be set from current medical science's point > > of view ;-) > > > > The standard is unknown, male, female, and n/a. Both unknown and n/a sounds like NULL to me. -- Rob Wultsch [EMAIL PROTECTED] wultsch (aim) -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How to modify ENUM datatypes?
On Wednesday 23 April 2008 14:10, Karsten Hilbert wrote:
> On Wed, Apr 23, 2008 at 12:38:48PM +0200, Andreas 'ads' Scherbaum wrote:
> > Yes. You should/can use ENUM for something like 'gender':
> > male, female, unknown. You don't need to add other values ever (yeah, i
> > skipped some special cases).
>
> I was gonna say ! :-)
>
> Add
>
> hermaphrodite
> transgender with female phenotype
> transgender with male phenotype
>
> and you should be set from current medical science's point
> of view ;-)
>
The standard is unknown, male, female, and n/a.
--
Robert Treat
Build A Brighter LAMP :: Linux Apache {middleware} PostgreSQL
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How to modify ENUM datatypes?
On Wed, Apr 23, 2008 at 12:38:48PM +0200, Andreas 'ads' Scherbaum wrote: > Yes. You should/can use ENUM for something like 'gender': > male, female, unknown. You don't need to add other values ever (yeah, i > skipped some special cases). I was gonna say ! :-) Add hermaphrodite transgender with female phenotype transgender with male phenotype and you should be set from current medical science's point of view ;-) Karsten -- GPG key ID E4071346 @ wwwkeys.pgp.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How to modify ENUM datatypes?
On Tue, 22 Apr 2008 15:45:39 -0500 D. Dante Lorenso wrote: > Andreas 'ads' Scherbaum wrote: > > So, the advice here is "don't use ENUM"? Yes. You should/can use ENUM for something like 'gender': male, female, unknown. You don't need to add other values ever (yeah, i skipped some special cases). But if you use ENUM for color names (as example), it's easy to imagine that someone comes around and requests a new color to be added to the list. Here you should use a lookup table and a foreign key instead. It always depends on the situation. The real problem is that people start creating the table with ENUM and "oh, nice, this makes it easy" in mind. But usually things are not as easy as it looks on first sight, so they later run into trouble. Kind regards -- Andreas 'ads' Scherbaum German PostgreSQL User Group -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How to modify ENUM datatypes?
On Tue, Apr 22, 2008 at 6:11 PM, Jeff Davis <[EMAIL PROTECTED]> wrote: > If you store an integer reference instead, joins are not necessarily > expensive. If the number of distinct values is small (which is the > normal use case for ENUM), I would expect the joins to be quite cheap. > Beware of running into bad plans however, or making the optimizer work > too hard (if you have a lot of other joins, too). Necessarily being the operative word here. Think about an enum as part of a composite key for example. It's a lot nicer to rely on enum for natural ordering than doing something like a functional index. Anyways, it's pretty easy to extend an enum...you can manually insert an entry into pg_enum (see the relevent docs). Just watch out for oid overlap. One thing currently that is very difficult currently to do is to alter the order of the enum elements. The current state of things is pretty workable though. Scott's color/mystuff example is generally preferred for a lot of cases. I _really_ prefer this to surrogate style enums where you have color_id...this approach makes your database unreadable IMO. A decent hybrid approach which I have been using lately is "char" (not char) where the choices set is reasonably small, well represented by a single character, and the intrinsic ordering property is not too important (where an enum might be better). In many cases though, the pure natural approach is simply the best. The enum though with is intrinsic ordering and more efficient indexing has an important niche however. merlin -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How to modify ENUM datatypes?
On Tue, 2008-04-22 at 16:34 -0500, D. Dante Lorenso wrote: > I see this might be a > problem with storage since you will need to store the TEXT value for > every row in the 'mystuff' table instead of just storing the reference > to the lookup table as an INTEGER. Over millions of rows, perhaps this > would become a concern? It does use additional storage to store the full text value, rather than a fixed-size integer. However, the difference is not much when the average string length is short. If you store an integer reference instead, joins are not necessarily expensive. If the number of distinct values is small (which is the normal use case for ENUM), I would expect the joins to be quite cheap. Beware of running into bad plans however, or making the optimizer work too hard (if you have a lot of other joins, too). I don't think the performance concerns are major, but worth considering if you have millions of rows. > What is the general consensus by the community about this approach? Is > this de-normalization frowned upon, or is there a performance advantage > here that warrants the usage? This is not de-normalization, at all. Normalization is a formal process, and if this were de-normalization, you could find a specific rule that is violated by this approach. Look here: http://en.wikipedia.org/wiki/Third_normal_form If you go to higher normal forms, you will not find any violations there, either. There is nothing about normalization that requires the use of surrogate keys. The approach suggested by Scott Marlowe is normalized as well as being quite natural and simple. I think often this is overlooked as being "too simple", but it's a quite good design in many cases. Regards, Jeff Davis -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How to modify ENUM datatypes?
Scott Marlowe wrote:
On Tue, Apr 22, 2008 at 2:45 PM, D. Dante Lorenso <[EMAIL PROTECTED]> wrote:
So, the advice here is "don't use ENUM"?
I was really hoping that it would be more efficient to not have to do all
the foreign keys and joins for tables that may have 4-5 enum types.
Just being able to:
SELECT *
FROM tablename
If you use a "lookup table" methodology you still get that. Try this:
smarlowe=# create table choices (color text primary key);
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index
"choices_pkey" for table "choices"
CREATE TABLE
smarlowe=# insert into choices values ('red'),('yellow'),('green'),('blue');
INSERT 0 4
smarlowe=# create table mystuff (id serial primary key, usenam text,
mycolor text references choices(color));
NOTICE: CREATE TABLE will create implicit sequence "mystuff_id_seq"
for serial column "mystuff.id"
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index
"mystuff_pkey" for table "mystuff"
CREATE TABLE
smarlowe=# insert into mystuff (usenam, mycolor) values ('scott','red');
INSERT 0 1
smarlowe=# insert into mystuff (usenam, mycolor) values ('darren','blue');
INSERT 0 1
smarlowe=# insert into mystuff (usenam, mycolor) values ('dan','green');
INSERT 0 1
smarlowe=# insert into mystuff (usenam, mycolor) values ('steve','green');
INSERT 0 1
smarlowe=# insert into mystuff (usenam, mycolor) values ('mike','black');
ERROR: insert or update on table "mystuff" violates foreign key
constraint "mystuff_mycolor_fkey"
DETAIL: Key (mycolor)=(black) is not present in table "choices".
smarlowe=# select * from mystuff;
id | usenam | mycolor
++-
1 | scott | red
2 | darren | blue
3 | dan| green
4 | steve | green
(4 rows)
tada! No enum, and no join. But you can't insert illegal values in mycolor...
This approach is so old-school, I seem to have overlooked the obvious.
Here you've elected to use the foreign key to just control the possible
values inserted but not really to look up the value.
Seems you are storing the values in text form which goes against all the
normalization techniques I've learned in school. I see this might be a
problem with storage since you will need to store the TEXT value for
every row in the 'mystuff' table instead of just storing the reference
to the lookup table as an INTEGER. Over millions of rows, perhaps this
would become a concern?
What is the general consensus by the community about this approach? Is
this de-normalization frowned upon, or is there a performance advantage
here that warrants the usage?
-- Dante
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How to modify ENUM datatypes?
On Tue, 22 Apr 2008 15:45:39 -0500 "D. Dante Lorenso" <[EMAIL PROTECTED]> wrote: > I was really hoping that it would be more efficient to not have to do > all the foreign keys and joins for tables that may have 4-5 enum > types. > > Just being able to: > >SELECT * >FROM tablename > > would be nice if my columns contained enums instead of doing: > >SELECT * >FROM tablename, lookuptable >WHERE tablename.some_id = lookuptable.some_id > > Isn't the join more expensive? You were using natural keys, the join would not be required. Joshua D. Drake > > -- Dante > -- The PostgreSQL Company since 1997: http://www.commandprompt.com/ PostgreSQL Community Conference: http://www.postgresqlconference.org/ United States PostgreSQL Association: http://www.postgresql.us/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How to modify ENUM datatypes?
On Tue, Apr 22, 2008 at 2:45 PM, D. Dante Lorenso <[EMAIL PROTECTED]> wrote:
>
> So, the advice here is "don't use ENUM"?
>
> I was really hoping that it would be more efficient to not have to do all
> the foreign keys and joins for tables that may have 4-5 enum types.
>
> Just being able to:
>
> SELECT *
> FROM tablename
If you use a "lookup table" methodology you still get that. Try this:
smarlowe=# create table choices (color text primary key);
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index
"choices_pkey" for table "choices"
CREATE TABLE
smarlowe=# insert into choices values ('red'),('yellow'),('green'),('blue');
INSERT 0 4
smarlowe=# create table mystuff (id serial primary key, usenam text,
mycolor text references choices(color));
NOTICE: CREATE TABLE will create implicit sequence "mystuff_id_seq"
for serial column "mystuff.id"
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index
"mystuff_pkey" for table "mystuff"
CREATE TABLE
smarlowe=# insert into mystuff (usenam, mycolor) values ('scott','red');
INSERT 0 1
smarlowe=# insert into mystuff (usenam, mycolor) values ('darren','blue');
INSERT 0 1
smarlowe=# insert into mystuff (usenam, mycolor) values ('dan','green');
INSERT 0 1
smarlowe=# insert into mystuff (usenam, mycolor) values ('steve','green');
INSERT 0 1
smarlowe=# insert into mystuff (usenam, mycolor) values ('mike','black');
ERROR: insert or update on table "mystuff" violates foreign key
constraint "mystuff_mycolor_fkey"
DETAIL: Key (mycolor)=(black) is not present in table "choices".
smarlowe=# select * from mystuff;
id | usenam | mycolor
++-
1 | scott | red
2 | darren | blue
3 | dan| green
4 | steve | green
(4 rows)
tada! No enum, and no join. But you can't insert illegal values in mycolor...
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How to modify ENUM datatypes?
On Tue, Apr 22, 2008 at 3:31 PM, D. Dante Lorenso <[EMAIL PROTECTED]> wrote: > All, > > In the past I have used foreign keys to lookup tables for small lists of > values that I now think ENUM could do the job of. I was hoping that by > using ENUM, I would avoid having to do joins in my queries, and that I'd be > making the data more robust and faster. > > I used to have a table for account_status: > > A | Active > B | Billing Failed > C | Closed > D | Deactivated > > account.acct_type CHAR references account_type.acct_type CHAR > > But, now I've converted that to an ENUM: > > ACTIVE > BILLING_FAILED > CLOSED > DEACTIVATED > > account.acct_type ENUM account_type > > The problem is that once I create a column in my account table that uses > this 'account_type' datatype, I can't seem to change or add to it any more. > I want to add a new value or edit/delete an existing one. > > How do you make changes to an ENUM datatype that is already in use? > I agree with others that ENUMs stop looking pretty when you need to modify them... Here's a thread from recent past where this exact problem was discussed... maybe it'll interest you... http://archives.postgresql.org/pgsql-general/2007-12/msg01363.php Best regards, -- [EMAIL PROTECTED] [EMAIL PROTECTED] gmail | hotmail | indiatimes | yahoo }.com EnterpriseDB http://www.enterprisedb.com Mail sent from my BlackLaptop device
Re: [GENERAL] How to modify ENUM datatypes?
On Apr 22, 2008, at 1:45 PM, D. Dante Lorenso wrote: So, the advice here is "don't use ENUM"? I think it's more "Don't use ENUM for a type that you are planning to extend." -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How to modify ENUM datatypes?
Andreas 'ads' Scherbaum wrote: On Tue, 22 Apr 2008 20:58:30 +0800 Craig Ringer wrote: D. Dante Lorenso wrote: The problem is that once I create a column in my account table that uses this 'account_type' datatype, I can't seem to change or add to it any more. I want to add a new value or edit/delete an existing one. How do you make changes to an ENUM datatype that is already in use? As far as I know ENUM is not well suited to uses where new enumeration members may be added later. A lookup table and a foreign key is probably better for this sort of use. I remember the discussions before PG implemented ENUMs at all - some people voted against this "feature" because they knew that questions about modifing the enum values would pop up sooner or later. You *can* add elements to an enum type - sort of - by creating a new type and converting columns. It's ugly, though, and will be hard to get right when the column of interest is referenced by foreign keys and such. If there's one request to modify a specific ENUM column, other requests will follow because the enum set is not as static as it seems in the first place. So i beg that the only advise should be "change the column to a foreign key construct". Converting columns to new data types is much more overhead anyway. So, the advice here is "don't use ENUM"? I was really hoping that it would be more efficient to not have to do all the foreign keys and joins for tables that may have 4-5 enum types. Just being able to: SELECT * FROM tablename would be nice if my columns contained enums instead of doing: SELECT * FROM tablename, lookuptable WHERE tablename.some_id = lookuptable.some_id Isn't the join more expensive? -- Dante -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How to modify ENUM datatypes?
On Tue, 22 Apr 2008 20:58:30 +0800 Craig Ringer wrote: > D. Dante Lorenso wrote: > > > > The problem is that once I create a column in my account table that > > uses this 'account_type' datatype, I can't seem to change or add to it > > any more. I want to add a new value or edit/delete an existing one. > > > > How do you make changes to an ENUM datatype that is already in use? > > > As far as I know ENUM is not well suited to uses where new enumeration > members may be added later. A lookup table and a foreign key is probably > better for this sort of use. I remember the discussions before PG implemented ENUMs at all - some people voted against this "feature" because they knew that questions about modifing the enum values would pop up sooner or later. > You *can* add elements to an enum type - sort of - by creating a new > type and converting columns. It's ugly, though, and will be hard to get > right when the column of interest is referenced by foreign keys and such. If there's one request to modify a specific ENUM column, other requests will follow because the enum set is not as static as it seems in the first place. So i beg that the only advise should be "change the column to a foreign key construct". Converting columns to new data types is much more overhead anyway. Kind regards -- Andreas 'ads' Scherbaum German PostgreSQL User Group -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How to modify ENUM datatypes?
D. Dante Lorenso wrote:
The problem is that once I create a column in my account table that
uses this 'account_type' datatype, I can't seem to change or add to it
any more. I want to add a new value or edit/delete an existing one.
How do you make changes to an ENUM datatype that is already in use?
As far as I know ENUM is not well suited to uses where new enumeration
members may be added later. A lookup table and a foreign key is probably
better for this sort of use.
You *can* add elements to an enum type - sort of - by creating a new
type and converting columns. It's ugly, though, and will be hard to get
right when the column of interest is referenced by foreign keys and such.
One way to do it if you really must:
-- Starting state
CREATE TYPE et1 AS ENUM('yes','no');
CREATE TABLE testtab (
a et
);
INSERT INTO testtab (a) values ('yes');
-- Change
CREATE TYPE et2 AS ENUM('yes','no','filenotfound');
ALTER TABLE testtab ALTER COLUMN a TYPE et2 USING (
CASE a WHEN 'yes' THEN 'yes'::et2 WHEN 'no' THEN 'no'::et2 END
);
-- Alternative ALTER that's suitable if you're not removing anything
from the enum
ALTER TABLE testtab ALTER COLUMN a TYPE et2 USING ( a::text::et2 );
Personally, though, I'd stick to the good 'ol lookup table and foreign key.
--
Craig Ringer
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] How to modify ENUM datatypes?
All, In the past I have used foreign keys to lookup tables for small lists of values that I now think ENUM could do the job of. I was hoping that by using ENUM, I would avoid having to do joins in my queries, and that I'd be making the data more robust and faster. I used to have a table for account_status: A | Active B | Billing Failed C | Closed D | Deactivated account.acct_type CHAR references account_type.acct_type CHAR But, now I've converted that to an ENUM: ACTIVE BILLING_FAILED CLOSED DEACTIVATED account.acct_type ENUM account_type The problem is that once I create a column in my account table that uses this 'account_type' datatype, I can't seem to change or add to it any more. I want to add a new value or edit/delete an existing one. How do you make changes to an ENUM datatype that is already in use? -- Dante -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
