Jim Nasby wrote: > 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. >
I'm pretty sure it could easily be broken. But to make it easier for me, I know that the reporting system connects, runs the query, and disconnects. So I'm so far safe using my current system. If the system had persistent connections and changed timezones a lot, it might however cause problems. Its been the only way that I could get it to be smart enough to not use the tables outside its range. With the tables growing 2+ million rows a day, approaching 1 billion rows, its helps performance a lot. This works at least until the ongoing discussion of partitioned tables hopefully improves things in this area. > On Mar 26, 2007, at 3:48 PM, Weslee Bilodeau wrote: >> 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) > > > Weslee ---------------------------(end of broadcast)--------------------------- TIP 5: don't forget to increase your free space map settings
