Thread: Insert into master table ->" 0 rows affected" -> Hibernate problems
I have implemented partitioning using inheritance following the proposed solution here (using trigger):
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?
On Tue, Jun 3, 2008 at 7:38 AM, <Mattias.Arbin@tietoenator.com> 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.
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@gmail.com] Sent: den 5 juni 2008 02:01 To: Arbin Mattias Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] Insert into master table ->" 0 rows affected" -> Hibernate problems On Tue, Jun 3, 2008 at 7:38 AM, <Mattias.Arbin@tietoenator.com> 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.
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@tietoenator.com 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@gmail.com] > Sent: den 5 juni 2008 02:01 > To: Arbin Mattias > Cc: pgsql-general@postgresql.org > Subject: Re: [GENERAL] Insert into master table ->" 0 rows affected" -> > Hibernate problems > > On Tue, Jun 3, 2008 at 7:38 AM, <Mattias.Arbin@tietoenator.com> 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. >
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@postgresql.org
> [mailto:pgsql-general-owner@postgresql.org] On Behalf Of
> Magnus Hagander
> Sent: den 10 juni 2008 10:43
> To: Arbin Mattias
> Cc: scott.marlowe@gmail.com; pgsql-general@postgresql.org
> 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@tietoenator.com 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@gmail.com]
> > Sent: den 5 juni 2008 02:01
> > To: Arbin Mattias
> > Cc: pgsql-general@postgresql.org
> > Subject: Re: [GENERAL] Insert into master table ->" 0 rows
> affected"
> > -> Hibernate problems
> >
> > On Tue, Jun 3, 2008 at 7:38 AM,
> <Mattias.Arbin@tietoenator.com> 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@postgresql.org) To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>
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@tietoenator.com 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@postgresql.org >> [mailto:pgsql-general-owner@postgresql.org] On Behalf Of >> Magnus Hagander >> Sent: den 10 juni 2008 10:43 >> To: Arbin Mattias >> Cc: scott.marlowe@gmail.com; pgsql-general@postgresql.org >> 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@tietoenator.com 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@gmail.com] >>> Sent: den 5 juni 2008 02:01 >>> To: Arbin Mattias >>> Cc: pgsql-general@postgresql.org >>> Subject: Re: [GENERAL] Insert into master table ->" 0 rows >> affected" >>> -> Hibernate problems >>> >>> On Tue, Jun 3, 2008 at 7:38 AM, >> <Mattias.Arbin@tietoenator.com> 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@postgresql.org) To make changes to your subscription: >> http://www.postgresql.org/mailpref/pgsql-general >>
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@hagander.net] > Sent: den 11 juni 2008 10:43 > To: Arbin Mattias > Cc: pgsql-general@postgresql.org > 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@tietoenator.com 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@postgresql.org > >> [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Magnus > >> Hagander > >> Sent: den 10 juni 2008 10:43 > >> To: Arbin Mattias > >> Cc: scott.marlowe@gmail.com; pgsql-general@postgresql.org > >> 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@tietoenator.com 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@gmail.com] > >>> Sent: den 5 juni 2008 02:01 > >>> To: Arbin Mattias > >>> Cc: pgsql-general@postgresql.org > >>> Subject: Re: [GENERAL] Insert into master table ->" 0 rows > >> affected" > >>> -> Hibernate problems > >>> > >>> On Tue, Jun 3, 2008 at 7:38 AM, > >> <Mattias.Arbin@tietoenator.com> 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@postgresql.org) To make changes to your > subscription: > >> http://www.postgresql.org/mailpref/pgsql-general > >> > >
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@tietoenator.com 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@hagander.net] >> Sent: den 11 juni 2008 10:43 >> To: Arbin Mattias >> Cc: pgsql-general@postgresql.org >> 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@tietoenator.com 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@postgresql.org >>>> [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Magnus >>>> Hagander >>>> Sent: den 10 juni 2008 10:43 >>>> To: Arbin Mattias >>>> Cc: scott.marlowe@gmail.com; pgsql-general@postgresql.org >>>> 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@tietoenator.com 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@gmail.com] >>>>> Sent: den 5 juni 2008 02:01 >>>>> To: Arbin Mattias >>>>> Cc: pgsql-general@postgresql.org >>>>> Subject: Re: [GENERAL] Insert into master table ->" 0 rows >>>> affected" >>>>> -> Hibernate problems >>>>> >>>>> On Tue, Jun 3, 2008 at 7:38 AM, >>>> <Mattias.Arbin@tietoenator.com> 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@postgresql.org) To make changes to your >> subscription: >>>> http://www.postgresql.org/mailpref/pgsql-general >>>> >>