From: | Weslee Bilodeau <weslee(dot)bilodeau(at)hypermediasystems(dot)com> |
---|---|
To: | Jim Nasby <decibel(at)decibel(dot)org> |
Cc: | Simon Riggs <simon(at)2ndquadrant(dot)com>, pgsql-hackers(at)postgresql(dot)org |
Subject: | Re: Partitioned tables constraint_exclusion |
Date: | 2007-04-09 21:40:28 |
Message-ID: | 461AB2CC.1030901@hypermediasystems.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
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 majordomo(at)postgresql(dot)org so that your
>> message can get through to the mailing list cleanly
>>
>
> --
> Jim Nasby jim(at)nasby(dot)net
> EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)
>
>
>
Weslee
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2007-04-09 22:52:19 | Re: Group Commit |
Previous Message | Martijn van Oosterhout | 2007-04-09 21:12:24 | Re: [HACKERS] Arrays of Complex Types |