Re: Insert into master table ->" 0 rows affected" -> Hibernate problems

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