On Dec 10, 2007, at 5:50 PM, Tom Lane wrote:
Erik Jones <erik@xxxxxxxxxx> writes:
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?
What "given value"?
The where clause in the select query.
What you're missing is that the condition "a = b" does not mean that
"f(a) = f(b)" for every function f. It is possible to define
constraints on equality that would make that true, but such
constraints
would be far stronger than what is required to make btree (or even
hash)
indexes work.
In the table constraint CHECK (some_id % 100 = 32), isn't that just f
(a) = 32?
In the example I gave, we are able to conclude that 3 is unequal to 9
not because of any a-priori knowledge, but because we apply the
specific
operator to the specific constants and find out that it yields false.
Our knowledge of the consistency requirements that are imposed on
btree
equality operators then allows us to determine that the two original
conditions can't be true at the same time.
This does *not* imply assuming that the two constants are really "the
same" in the sense that no other operator in the system could tell
them
apart. This isn't mere academic hairsplitting: there actually are
standard equality operators in the system for which such a conclusion
would fail. I already mentioned float comparison, and numeric
comparison has similar behaviors --- for instance,
regression=# select '0.00'::numeric = '0.0'::numeric;
?column?
----------
t
(1 row)
regression=# select text('0.00'::numeric) = text('0.0'::numeric);
?column?
----------
f
(1 row)
I get your arguments wrt the known (to the planner) semantics of the
equality operator. I guess what I don't understand is that given the
query
SELECT COUNT(*)
FROM table
WHERE some_id=34;
on a table with the much discussed constraint (34 % 100) = 32 isn't
simply evaluated as a one-time filter whenever whatever constraint
exclusion code examines child partition tables' constraints.
Again, though, is there some better way to go about implementing some
kind of hash based partitioning in postgres besides this that would
be more natural wrt queries?
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 4: Have you searched our list archives?
http://archives.postgresql.org/
