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