See Simon's reply... timestamptz math is *not* IMMUTABLE, because
sessions are free to change their timezone at any time. I bet you can
get some invalid results using that function with a clever test case.
On Mar 26, 2007, at 3:48 PM, Weslee Bilodeau wrote:
Weslee Bilodeau wrote:
Mainly its because the value comes from a reporting system that has
minimal brains, it passes values it gets from the user directly
into a
query.
IE, they enter '1 month', which I use to populate the interval value,
"ts > ( NOW() - $VALUE )"
But, in the example I did a "timestamp - interval", the exact
date, not
NOW() - Still didn't work.
I'm guessing anything that has to think, math, etc is not valid for
constrain_exclusion?
Its not in the docs anywhere, so trying to isolate what can and
can't be
done.
This works -
CREATE FUNCTION now_interval( interval ) RETURNS timestamptz IMMUTABLE
STRICT AS $$ SELECT NOW() - $1 $$ LANGUAGE SQL ;
SELECT count(*) FROM master WHERE var_ts > now_interval( '1 month' );
This doesn't work -
SELECT count(*) FROM master WHERE var_ts > ( NOW() - '1
month'::interval );
This works for me, as the reporting system I know doesn't change
timezones, and function cache doesn't last longer then the current
select?
But, its basically the exact same logic in both cases?
Weslee
---------------------------(end of
broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that
your
message can get through to the mailing list cleanly
--
Jim Nasby [EMAIL PROTECTED]
EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
