I think you could do overlapping CHECK constraints for the rules for a very short period of time. Querying for just that time will hit both partitions, so it won't be perfect from a performance standpoint, but it would only do that for a very small timeframe. And I can certainly agree it's not ideal :-) //Magnus Mattias.Arbin@xxxxxxxxxxxxxxx wrote: > Hm, > Thanks again for helping out. > I think it would be hard to live without the WHERE statemement in my > case, since I will have a steady flow of inserts and need to have a > partition ready when time passes a partition boundary. I would have to > update the rule at the exact millisecond, wouldn't I? > > I still think it is a bit sad that I cannot find a partitioning solution > that is completely transparent to my application, i.e. an application > (in my case Hibernate) should not have to know/care if a table is > partitioned or not. > > /Mattias > >> -----Original Message----- >> From: Magnus Hagander [mailto:magnus@xxxxxxxxxxxx] >> Sent: den 11 juni 2008 10:43 >> To: Arbin Mattias >> Cc: pgsql-general@xxxxxxxxxxxxxx >> Subject: Re: [GENERAL] Insert into master table ->" 0 rows >> affected" -> Hibernate problems >> >> Ah, in my testing I had a single RULE without a WHERE >> statement. In that case it works. If I add a WHERE statement >> to it, it no longer works. So it works for the case when you >> always want to redirect all new inserts into the same partition. >> >> //Magnus >> >> Mattias.Arbin@xxxxxxxxxxxxxxx wrote: >>> I tried using rules instead. I did something very similar to this: >>> CREATE RULE measurement_insert_y2006m02 AS ON INSERT TO measurement >>> WHERE >>> ( logdate >= DATE '2006-02-01' AND logdate < DATE >> '2006-03-01' ) >>> DO INSTEAD >>> INSERT INTO measurement_y2006m02 VALUES (NEW.*); ... >>> CREATE RULE measurement_insert_y2008m01 AS ON INSERT TO measurement >>> WHERE >>> ( logdate >= DATE '2008-01-01' AND logdate < DATE >> '2008-02-01' ) >>> DO INSTEAD >>> INSERT INTO measurement_y2008m01 VALUES (NEW.*); >>> >>> ... as descibed in the docs: >>> http://www.postgresql.org/docs/8.3/interactive/ddl-partitioning.html >>> >>> Unfortunately, I still get >>> "Query returned successfully: 0 rows affected, 16 ms >> execution time." >>> /Mattias >>> >>> >>> >>>> -----Original Message----- >>>> From: pgsql-general-owner@xxxxxxxxxxxxxx >>>> [mailto:pgsql-general-owner@xxxxxxxxxxxxxx] On Behalf Of Magnus >>>> Hagander >>>> Sent: den 10 juni 2008 10:43 >>>> To: Arbin Mattias >>>> Cc: scott.marlowe@xxxxxxxxx; pgsql-general@xxxxxxxxxxxxxx >>>> Subject: Re: [GENERAL] Insert into master table ->" 0 rows >> affected" >>>> -> Hibernate problems >>>> >>>> I think that if you use a RULE instead of a TRIGGER to >> redirect the >>>> write, it should return the proper number of rows inserted in the >>>> child table. >>>> >>>> //Magnus >>>> >>>> >>>> Mattias.Arbin@xxxxxxxxxxxxxxx wrote: >>>>> Scott, >>>>> You're right, of course. I meant, is there a way to make Postgres >>>>> return the number of rows inserted to any child table _via_ >>>> the master >>>>> table + trigger function? >>>>> I have not been able to find a way to tell Hibernate to >> ignore the >>>>> returned number of rows, unless I insert via a custom >>>> insert statement. >>>>> /Mattias >>>>> >>>>> -----Original Message----- >>>>> From: Scott Marlowe [mailto:scott.marlowe@xxxxxxxxx] >>>>> Sent: den 5 juni 2008 02:01 >>>>> To: Arbin Mattias >>>>> Cc: pgsql-general@xxxxxxxxxxxxxx >>>>> Subject: Re: [GENERAL] Insert into master table ->" 0 rows >>>> affected" >>>>> -> Hibernate problems >>>>> >>>>> On Tue, Jun 3, 2008 at 7:38 AM, >>>> <Mattias.Arbin@xxxxxxxxxxxxxxx> wrote: >>>>>> I have implemented partitioning using inheritance following the >>>>>> proposed solution here (using trigger): >>>>>> >> http://www.postgresql.org/docs/8.3/interactive/ddl-partitioning.html >>>>>> My problem is that when my Hibernate application inserts to the >>>>>> master table, postgres returns "0 rows affected", which causes >>>> Hibernate to >>>>>> throw an exception since it expects the returned row count to be >>>>>> equal to the number of rows inserted. >>>>>> >>>>>> Is there a solution to this, i.e. to get Postgres to return the >>>>>> correct number of rows inserted to the master table? >>>>> PostgreSQL IS reporting the correct number of rows inserted >>>> into the >>>>> master table. 0. >>>>> >>>>> There's some setting in hibernate that will tell it to >> ignore that >>>>> returned number of rows, but I can't remember it right now. >>>>> >>>> -- >>>> Sent via pgsql-general mailing list >>>> (pgsql-general@xxxxxxxxxxxxxx) To make changes to your >> subscription: >>>> http://www.postgresql.org/mailpref/pgsql-general >>>> >>
