Tom Lane wrote:
Andrew Dunstan and...@dunslane.net writes:
Robert Haas wrote:
I like the idea of being able to modify enums on the fly, but I'm
skeptical of an implementation that won't always work. Maybe it's
still better than what we have now, but it seems grotty.
I'd be perfectly
Bruce Momjian br...@momjian.us writes:
Sorry to be commenting late, but don't most people want to add to the
end or beginning of the enum list, rather than in the middle, and can't
we support that already?
We could allow adding a value, but we couldn't guarantee where it would
appear in the
Tom Lane wrote:
Bruce Momjian br...@momjian.us writes:
Sorry to be commenting late, but don't most people want to add to the
end or beginning of the enum list, rather than in the middle, and can't
we support that already?
We could allow adding a value, but we couldn't guarantee where it
Bruce Momjian br...@momjian.us writes:
Well, we don't need the enum value to map into the entire oid range.
Can't we just add one to the top-most value and see if there is a
conflict?
If you don't use the OID counter to generate the new value, you're going
to have problems with race
Tom Lane wrote:
Bruce Momjian br...@momjian.us writes:
Well, we don't need the enum value to map into the entire oid range.
Can't we just add one to the top-most value and see if there is a
conflict?
If you don't use the OID counter to generate the new value, you're going
to have
Tom Lane wrote:
Andrew Dunstan and...@dunslane.net writes:
Another thought: could we add a column to pg_type with a flag that's
true if the oids are in sort order? Then the comparison routines could
just look that up in the type cache and if it's true (as it often will
be) just return
Andrew Dunstan and...@dunslane.net writes:
Tom Lane wrote:
Well, having to do a cache lookup already makes it a couple orders of
magnitude more expensive than an OID comparison. However, it's hard to
say how much that matters in terms of total application performance.
We really could do with
Tom Lane wrote:
Adding cache
lookups for the enum rows to the comarison routines made a REINDEX on a
1m row table where the index is on an enum column (the enum has 500
randomly ordered labels) jump from around 10s to around 70s.
Hmmm... that's bad, but I bet it's still less than the
Peter Geoghegan peter.geoghega...@gmail.com wrote:
In my experience, lookup tables generally have two columns, an
integer PK and a description/state.
Eek. If that's what you consider a lookup table, I wouldn't
advocate their use for anything. Ever. Period.
-Kevin
--
Sent via
On Mon, 2010-06-21 at 12:04 -0500, Kevin Grittner wrote:
Peter Geoghegan peter.geoghega...@gmail.com wrote:
In my experience, lookup tables generally have two columns, an
integer PK and a description/state.
Eek. If that's what you consider a lookup table, I wouldn't
advocate their
Tom Lane wrote:
Andrew Dunstan and...@dunslane.net writes:
Tom Lane wrote:
Well, having to do a cache lookup already makes it a couple orders of
magnitude more expensive than an OID comparison. However, it's hard to
say how much that matters in terms of total application
Ahem. That is what a natural key is for :)
Well, they have their own drawbacks that don't make them particularly
appealing to use with lookup tables to ape enums. How many lookup
tables have you seen in the wild with a natural key?
People sometimes represent things like US states as enums. This
Peter Geoghegan wrote:
How many lookup tables have you seen in the wild with a natural
key?
Me? Personally? A few hundred.
People sometimes represent things like US states as enums. This is
probably a mistake, because you cannot control or predict if
there'll be a new US state,
People sometimes represent things like US states as enums. This is
probably a mistake, because you cannot control or predict if
there'll be a new US state, unlikely though that me be.
More importantly, you're likely to need to associate properties with
the state. Sales tax info, maybe a
Tom Lane wrote:
Andrew Dunstan and...@dunslane.net writes:
Tom Lane wrote:
Insert a sort order column into pg_enum, and rearrange the values in
that whenever the user wants to add a new value in a particular place.
You give up cheap comparisons in exchange for flexibility. I think
Andrew Dunstan and...@dunslane.net writes:
Another thought: could we add a column to pg_type with a flag that's
true if the oids are in sort order? Then the comparison routines could
just look that up in the type cache and if it's true (as it often will
be) just return the oid comparison.
On Fri, Jun 18, 2010 at 12:59 PM, Andrew Dunstan and...@dunslane.netwrote:
David E. Wheeler wrote:
On Jun 18, 2010, at 9:34 AM, Andrew Dunstan wrote:
I'd be perfectly happy to hear a reasonable alternative. Assuming we use
some integer representation, given two labels represented by n
Gurjeet Singh wrote:
This is very similar to Andrew's original suggestion of splitting 32
bits into 16+16, but managed by the machine hence no complicated
comparison algos needed on our part. Also, since this is all
transparent to the SQL interface, our dump-reload cycle or Slony
On Sat, Jun 19, 2010 at 4:55 PM, Andrew Dunstan and...@dunslane.net wrote:
Gurjeet Singh wrote:
This is very similar to Andrew's original suggestion of splitting 32 bits
into 16+16, but managed by the machine hence no complicated comparison algos
needed on our part. Also, since this is all
On Fri, 2010-06-18 at 11:50 -0400, Andrew Dunstan wrote:
Thoughts?
enum types exist as an optimisation-by-avoidance of referential
integrity.
We're a relational database, so IMHO we should spend time performance
tuning RI.
--
Simon Riggs www.2ndQuadrant.com
PostgreSQL
Simon Riggs wrote:
On Fri, 2010-06-18 at 11:50 -0400, Andrew Dunstan wrote:
Thoughts?
enum types exist as an optimisation-by-avoidance of referential
integrity.
We're a relational database, so IMHO we should spend time performance
tuning RI.
I don't accept your initial
Thoughts?
enum types exist as an optimisation-by-avoidance of referential
integrity.
We're a relational database, so IMHO we should spend time performance
tuning RI.
I take the view that they exist as a way of representing enumerations
of application/domain values - if it's hard coded in
On Sun, 2010-06-20 at 03:42 +0100, Peter Geoghegan wrote:
Thoughts?
It's very cool to be able to write queries like SELECT * FROM payments
WHERE payment_type = 'cash', rather than having to recall time and
again what the PK of cash is within your lookup table.
Ahem. That is what a natural
On Fri, Jun 18, 2010 at 11:50 AM, Andrew Dunstan and...@dunslane.net wrote:
Then set the
first value at 8 * p, then next at 9* p and so on. This is designed to
allow more space to add labels at the beginning and end of the list, where
this is more likely. Adding a label would be a matter of
Robert Haas wrote:
On Fri, Jun 18, 2010 at 11:50 AM, Andrew Dunstan and...@dunslane.net wrote:
Then set the
first value at 8 * p, then next at 9* p and so on. This is designed to
allow more space to add labels at the beginning and end of the list, where
this is more likely. Adding a label
On Jun 18, 2010, at 9:07 AM, Robert Haas wrote:
Then set the
first value at 8 * p, then next at 9* p and so on. This is designed to
allow more space to add labels at the beginning and end of the list, where
this is more likely. Adding a label would be a matter of finding the labels
adjacent
On Jun 18, 2010, at 9:34 AM, Andrew Dunstan wrote:
I'd be perfectly happy to hear a reasonable alternative. Assuming we use some
integer representation, given two labels represented by n and n+1, we can't
add a label between them without rewriting the tables that use the type,
whether it's
On Fri, 2010-06-18 at 12:34 -0400, Andrew Dunstan wrote:
Robert Haas wrote:
On Fri, Jun 18, 2010 at 11:50 AM, Andrew Dunstan and...@dunslane.net
wrote:
Then set the
first value at 8 * p, then next at 9* p and so on. This is designed to
allow more space to add labels at the
Andrew Dunstan and...@dunslane.net writes:
Robert Haas wrote:
I like the idea of being able to modify enums on the fly, but I'm
skeptical of an implementation that won't always work. Maybe it's
still better than what we have now, but it seems grotty.
I'd be perfectly happy to hear a
David E. Wheeler wrote:
What's the likelihood of a failure?
Constructing a failure case would be simple. In practice, I suspect it
would be very low.
And would the position of the new label (represented by its internal number) be
predictive? IOW, would updating the same varenumtype in
David E. Wheeler wrote:
On Jun 18, 2010, at 9:34 AM, Andrew Dunstan wrote:
I'd be perfectly happy to hear a reasonable alternative. Assuming we use some
integer representation, given two labels represented by n and n+1, we can't add
a label between them without rewriting the tables that
Tom Lane wrote:
Andrew Dunstan and...@dunslane.net writes:
Robert Haas wrote:
I like the idea of being able to modify enums on the fly, but I'm
skeptical of an implementation that won't always work. Maybe it's
still better than what we have now, but it seems grotty.
I'd
On Fri, Jun 18, 2010 at 12:59 PM, Andrew Dunstan and...@dunslane.net wrote:
You are just bumping up the storage cost. Part of the attraction of enums is
their efficiency.
What's efficient about them? Aren't we using 4 bytes to store a value
that will nearly always fit in 2, if not 1?
--
Robert Haas wrote:
On Fri, Jun 18, 2010 at 12:59 PM, Andrew Dunstan and...@dunslane.net wrote:
You are just bumping up the storage cost. Part of the attraction of enums is
their efficiency.
What's efficient about them? Aren't we using 4 bytes to store a value
that will nearly
On Fri, Jun 18, 2010 at 1:59 PM, Andrew Dunstan and...@dunslane.net wrote:
This was debated when we implemented enums. As between 1,2 and 4 there is
often not much to choose, as alignment padding makes it pretty much the
same. But any of them are more efficient than storing a numeric value or
On Fri, Jun 18, 2010 at 6:17 PM, Andrew Dunstan and...@dunslane.net wrote:
Tom Lane wrote:
Insert a sort order column into pg_enum, and rearrange the values in
that whenever the user wants to add a new value in a particular place.
+1 I was going to say exactly the same thing.
You give up
Andrew Dunstan and...@dunslane.net writes:
Tom Lane wrote:
Insert a sort order column into pg_enum, and rearrange the values in
that whenever the user wants to add a new value in a particular place.
You give up cheap comparisons in exchange for flexibility. I think lots
of people would
On Fri, Jun 18, 2010 at 1:59 PM, Andrew Dunstan and...@dunslane.net wrote:
Robert Haas wrote:
On Fri, Jun 18, 2010 at 12:59 PM, Andrew Dunstan and...@dunslane.net
wrote:
You are just bumping up the storage cost. Part of the attraction of enums
is
their efficiency.
What's efficient
Joseph Adams wrote:
Also, I can't seem to find the original debates from when enums were
implemented. Does anyone have a link to that thread in the archives?
Thanks.
Start here
http://archives.postgresql.org/pgsql-hackers/2006-08/msg00979.php
cheers
andrew
--
Sent via pgsql-hackers
Excerpts from Joseph Adams's message of vie jun 18 18:17:50 -0400 2010:
Couldn't enum_eq just use get_fn_expr_argtype to determine the type of
enum input rather than rely on it being stored in the value (either
implicitly via OID or explicitly as a word half)?
Also, I can't seem to find the
Joseph Adams joeyadams3.14...@gmail.com writes:
Couldn't enum_eq just use get_fn_expr_argtype to determine the type of
enum input rather than rely on it being stored in the value
No. Support functions have to work in many contexts where there is no
side channel such as get_fn_expr_argtype.
Tom Lane wrote:
And how would it be per data-type?
Well, there'd be two kinds of enums, just as you were saying before.
I'm not sure how we'd expose that to users exactly, or whether there
could be provisions for switching a type's behavior after creation.
42 matches
Mail list logo
