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
>> 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 5: don't forget to increase your free space map settings

Reply via email to