Re: partitioned table query question

Erik Jones <erik(at)myemma(dot)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. 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.)

regards, tom lane