On Dec 10, 2007, at 4:29 PM, Tom Lane wrote:
Erik Jones <erik@xxxxxxxxxx> writes:
You beat me to the punch on this one. I was wanting to use modulo
operations for bin style partitioning as well, but this makes things
pretty awkward as well as unintuitive. So, to the postgres gurus:
What are the limitations of check constraints when used with
constraint exclusion? Is this really the intended behavior?
Don't hold your breath. predtest.c has some intelligence about
btree-indexable comparison operators, but none about modulo.
In the particular case here, the reason that
WHERE (foo % 10) = 3
is seen to be incompatible with a check constraint
(foo % 10) = 9
is that the "=" is btree indexable, so predtest knows something about
its semantics; and given that % is an immutable operator, the code is
able to see that these could only both be true if 3 = 9.
I get that.
This
deduction involves exactly zero %-specific knowledge. In particular
it doesn't require assuming that "a=b" implies "(a % c) = (b % c)",
which would involve much more knowledge about the specific operators
involved than is available to the planner. (The fact that an operator
is a btree equality member doesn't mean that it might not consider two
values to be equal that are distinct to some other operators of the
data type. See plus and minus zero in IEEE float arithmetic for one
handy example ... and that's not even considering nonstandard versions
of equality.)
Forgive me if I'm nagging on this, I just want to understand this
better. Why does evaluating a CHECK constraint like 'CHECK some_id %
100 = 32' against WHERE clause like 'WHERE some_id=1132' need to know
anything about equality properites of %? Or, rather, why does it
stop there? Can't it just substitute the given value for some_id in
to the check expression, execute it and check the result value for
TRUE/FALSE?
On a related note, how would you recommend implementing some kind of
bin based (i.e. hash values, round robin, etc...) partitioning scheme
if this won't work? I've tried a number of different approaches with
functions in the check constraint but can't seem to get anything
going there either. I'm the third person this week (all in this
thread and another I had going) that's interested in this approach.
Erik Jones
Software Developer | Emma®
erik@xxxxxxxxxx
800.595.4401 or 615.292.5888
615.292.0777 (fax)
Emma helps organizations everywhere communicate & market in style.
Visit us online at http://www.myemma.com
---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
