Обсуждение: Daily Rotated Insertion
Hi all: I want to do a daily rotated insertion according the data's date field. The data come from a real time analyzer, which meansit's date field is nearly the current date but not exactly, even not exactly in the date order. The real trouble is it will be a heavy insertion, I don't want to code a complex trigger function. I'd like to simply redirectthe insertion to a "CURRENT" table, and rename it every mid-night. But it's difficult to make 'check', which is requiredby a better query performance. Well, I can create a trigger to redirect insertion to 'yesterday', 'today' and 'tomorrow', but I am still expecting a moresimple and more fast solution. Any idea is appreciated. Thanks in advance. Best regards. liuhaifeng
Hi, On Mon, Mar 26, 2012 at 9:39 AM, Haifeng Liu <liuhaifeng@live.com> wrote: > Well, I can create a trigger to redirect insertion to 'yesterday', 'today' and 'tomorrow', but I am still expecting a moresimple and more fast solution. You can use partitioning by date http://www.postgresql.org/docs/9.1/interactive/ddl-partitioning.html. > > Any idea is appreciated. Thanks in advance. > > > Best regards. > liuhaifeng > -- > Sent via pgsql-admin mailing list (pgsql-admin@postgresql.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.ru@gmail.com Skype: gray-hemp
On Mar 27, 2012, at 12:42 PM, Sergey Konoplev wrote: > Hi, > > On Mon, Mar 26, 2012 at 9:39 AM, Haifeng Liu <liuhaifeng@live.com> wrote: >> Well, I can create a trigger to redirect insertion to 'yesterday', 'today' and 'tomorrow', but I am still expecting amore simple and more fast solution. > > You can use partitioning by date > http://www.postgresql.org/docs/9.1/interactive/ddl-partitioning.html. Yes, that's what I am doing. There are many ways to implement partitioned table, I just want to find an elegant one. Bulkcreate a certain number of partitions is not good in my opinion. What I am trying now is let the trigger which switchesinsertions to catch exceptions and create new partitions on demand, meanwhile. update the trigger itself to use thenew switch rule. This idea can use static statement to switch insertions, dynamic statements are only used to create partition and updatethe 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. 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@postgresql.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.ru@gmail.com Skype: gray-hemp > > -- > Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-admin >
On Tue, Mar 27, 2012 at 10:02 AM, Haifeng Liu <liuhaifeng@live.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. Bulkcreate a certain number of partitions is not good in my opinion. What I am trying now is let the trigger which switchesinsertions to catch exceptions and create new partitions on demand, meanwhile. update the trigger itself to use thenew switch rule. > > This idea can use static statement to switch insertions, dynamic statements are only used to create partition and updatethe 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@postgresql.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.ru@gmail.com Skype: gray-hemp >> >> -- >> Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) >> To make changes to your subscription: >> http://www.postgresql.org/mailpref/pgsql-admin >> > > > -- > Sent via pgsql-admin mailing list (pgsql-admin@postgresql.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.ru@gmail.com Skype: gray-hemp
Вложения
On Mar 27, 2012, at 3:33 PM, Sergey Konoplev wrote: > On Tue, Mar 27, 2012 at 10:02 AM, Haifeng Liu <liuhaifeng@live.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. Bulkcreate a certain number of partitions is not good in my opinion. What I am trying now is let the trigger which switchesinsertions to catch exceptions and create new partitions on demand, meanwhile. update the trigger itself to use thenew switch rule. >> >> This idea can use static statement to switch insertions, dynamic statements are only used to create partition and updatethe 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. Thanks for sharing your script. Mine is done now, my goal is make the trigger more simple for most case, so I use a functionto create trigger function and make the redirect rule static, and if an exception occurred when new DATE is found,the trigger will call the function to recreate itself. Main ddls of my solution is posted as attachment. > >> >> 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@postgresql.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.ru@gmail.com Skype: gray-hemp >>> >>> -- >>> Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) >>> To make changes to your subscription: >>> http://www.postgresql.org/mailpref/pgsql-admin >>> >> >> >> -- >> Sent via pgsql-admin mailing list (pgsql-admin@postgresql.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.ru@gmail.com Skype: gray-hemp > <auto_partitioning.sql> > -- > Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-admin