Re: Daily Rotated Insertion

On Tue, Mar 27, 2012 at 10:02 AM, Haifeng Liu <liuhaifeng(at)live(dot)com> wrote:
> Yes, that's what I am doing. There are many ways to implement partitioned table, I just want to find an elegant one. Bulk create a certain number of partitions is not good in my opinion. What I am trying now is let the trigger which switches insertions to catch exceptions and create new partitions on demand, meanwhile. update the trigger itself to use the new switch rule.
>
> This idea can use static statement to switch insertions, dynamic statements are only used to create partition and update the trigger, this should be good for performance. And compare to the static partition example given in the document, this solution can auto-update itself, there is no need for cron jobs to maintenance partition.

Okay, I got what you mean. I used to use auto partitioning in one of
my projects but it assumed errors catching on the application server
side. However you can easily adapt the solution for your needs. I
attached the script to the message.

BTW, I created it several years ago. A lot of nice stuff like CREATE
TABLE IF NOT EXISTS were added in the new versions of PostgreSQL. So I
think may be you could get rid of catching exceptions completely with
help of these new features.

Please let me know if you decide to use this approach and modify it.

>
> I am not sure if this idea work, still trying now.
>
>>
>>>
>>> Any idea is appreciated. Thanks in advance.
>>>
>>>
>>> Best regards.
>>> liuhaifeng
>>> --
>>> Sent via pgsql-admin mailing list (pgsql-admin(at)postgresql(dot)org)
>>> To make changes to your subscription:
>>> http://www.postgresql.org/mailpref/pgsql-admin
>>
>>
>>
>> --
>> Sergey Konoplev
>>
>> Blog: http://gray-hemp.blogspot.com
>> LinkedIn: http://ru.linkedin.com/in/grayhemp
>> JID/GTalk: gray(dot)ru(at)gmail(dot)com Skype: gray-hemp
>>
>> --
>> Sent via pgsql-admin mailing list (pgsql-admin(at)postgresql(dot)org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-admin
>>
>
>
> --
> Sent via pgsql-admin mailing list (pgsql-admin(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-admin

--
Sergey Konoplev

Blog: http://gray-hemp.blogspot.com
LinkedIn: http://ru.linkedin.com/in/grayhemp
JID/GTalk: gray(dot)ru(at)gmail(dot)com Skype: gray-hemp