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 > >> > >
