Re: partitioned table query question

On Dec 10, 2007, at 4:29 PM, Tom Lane wrote:

> Erik Jones <erik@myemma.com> 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@myemma.com
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