DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com;
	h=received:message-id:date:from:reply-to:to:subject:mime-version:content-type;
	b=P/X2hgqh1rjmUfQGbOCsq+p5aDuuT1896ZDblpkdeCw1gtA9j77a+E3BFumAczcT2QoOSHqbfTAIAp7S9YWIh4WfGmDy2IzKAUyojOtDE1WYtuKjPK4yFk+UNYXUf+uQSPUlxOKTdtcboyd74+CNXsbQv/NpOuqjS1V7BEJ01oE=
Message-ID: <430d92a20607070051k514e5325q87f434ccc666c01d@mail.gmail.com>
Date: Fri, 7 Jul 2006 03:51:38 -0400
From: Gene <genekhart@gmail.com>
Reply-To: genekhart@gmail.com
To: pgsql-performance@postgresql.org
Subject: Update INSERT RULE while running for Partitioning
MIME-Version: 1.0
Content-Type: multipart/alternative;
	boundary="----=_Part_44462_22743121.1152258698188"

------=_Part_44462_22743121.1152258698188
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

I'm using PostgreSQL 8.1.4 in a Hibernate Application and I am attempting to
use partitioning via Inherited tables. At first I was going to create a rule
per sub-table based on a date range, but found out with multiple rules
postgres will only return the affected-row count on the last rule which
gives Hibernate problems. So now I'm thinking the way to do it is just have
one rule at a time and when I want to start appending data to a new
partition, just change the rule on the parent table and also update the
constraint on the last table to reflect the date ranges contained so that
constraint_exclusion will work. this should perform better also. For
instance

Starting off with:

Parent (Rule on insert instead insert into Child2)
  Child1 (Constraint date <= somedate1)
  Child2 (Constraint date > somedate1)

Now I want to create another Partition:

 Create Table Child3
BEGIN
Update Parent Rule( instead insert into Child3)
somedate2 = max(date) from Child2
Update Child2 Constraint( date > somedate1 AND date <= somedate2 )
Set Constraint Child3 (date > somedate2)
END

Which ends up with:

Parent (Rule on insert instead insert into Child2)
  Child1 (Constraint date <= somedate1)
  Child2 (Constraint date > somedate1 AND date <= somedate2)
  Child3 (Constraint date > somedate2)

Anyone else tried this or expect it to work consistently (without stopping
db)? Is it possible that there could be a race condition for the insertion
and constraints or will the transaction prevent that from occurring? I've
done some testing and it seems to work but I could just get lucky so far and
not lose any data :)

Thanks for any help,
Gene

------=_Part_44462_22743121.1152258698188
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

I'm using PostgreSQL 8.1.4 in a Hibernate Application and I am
attempting to use partitioning via Inherited tables. At first I was
going to create a rule per sub-table based on a date range, but found
out with multiple rules postgres will only return the affected-row
count on the last rule which gives Hibernate problems. So now I'm
thinking the way to do it is just have one rule at a time and when I
want to start appending data to a new partition, just change the rule
on the parent table and also update the constraint on the last table to
reflect the date ranges contained so that constraint_exclusion will
work. this should perform better also. For instance<br>
<br><span style="font-style: italic;">Starting off with:<br></span><br><div style="margin-left: 40px;">
Parent (Rule on insert instead insert into Child2)<br>
&nbsp; Child1 (Constraint date &lt;= somedate1)<br>
&nbsp; Child2 (Constraint date &gt; somedate1)<br></div>
<br>
<span style="font-style: italic;">Now I want to create another Partition:<br>
<br>
</span>
<div style="margin-left: 40px;">Create Table Child3<br>
BEGIN<br>
Update Parent Rule( instead insert into Child3)<br>
somedate2 = max(date) from Child2<br>
Update Child2 Constraint( date &gt; somedate1 AND date &lt;= somedate2 )<br>
Set Constraint Child3 (date &gt; somedate2)<br>
END<br>
</div>
<br><span style="font-style: italic;">Which ends up with:</span><br><br>
Parent (Rule on insert instead insert into Child2)<br>

&nbsp; Child1 (Constraint date &lt;= somedate1)<br>

&nbsp; Child2 (Constraint date &gt; somedate1 AND date &lt;= somedate2)<br>&nbsp; Child3 (Constraint date &gt; somedate2)<br><br>Anyone else tried this or expect it to work consistently (without stopping db)? Is it possible that there could be a race condition for the insertion and constraints or will the transaction prevent that from occurring? I've done some testing and it seems to work but I could just get lucky so far and not lose any data :)
<br><br>Thanks for any help,<br>Gene<br>


------=_Part_44462_22743121.1152258698188--