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

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(dot)Arbin(at)tietoenator(dot)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(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
>>>>
>>