Message-ID: <4D788AE2.7010004@intesens.com>
Date: Thu, 10 Mar 2011 09:25:06 +0100
From: Samba GUEYE <samba.gueye@intesens.com>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 6.1; fr;
	rv:1.9.2.15) Gecko/20110303 Thunderbird/3.1.9
MIME-Version: 1.0
To: Jim Nasby <jim@nasby.net>
CC: Postgre Performance <pgsql-performance@postgresql.org>
Subject: Re: Table partitioning problem
References: <4D764F04.7020400@intesens.com>
	<04931568-A1CA-4B5D-B907-E061713E1B54@nasby.net>
In-Reply-To: <04931568-A1CA-4B5D-B907-E061713E1B54@nasby.net>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 8bit

Hi jim thanks for your answer,

The database model is some' like that :
Measure(Id, numbering,Date, crcCorrect, sensorId) and a SimpleMeasure 
(Id, doubleValue) and GenericMeasure (Id, BlobValue, numberOfElements)
and in the UML model SimpleMeasure and GenericMeasure inherits from the 
Measure class so in the database, the foreign key of SimpleMeasure and 
GenericMeasure points to the Measure Table which is partitionned by sensor.

The measure insertion is successful but problems raise up when inserting 
in the simpleMeasure table because it can't find the foreign key 
inserted the measure table and do not look at the partitionned tables

ERROR:  insert or update on table "simpleMeasure" violates foreign key constraint "fk_measure_id"
DETAIL:  Key(measure_id)=(1) is not present in table Measure


The inheritance is just used to set the Postgre's partionning and the 
limitation of the partitioning comes from here

The same problem is also related in the following post :

http://archives.postgresql.org/pgsql-performance/2008-07/msg00224.php 
and this
http://archives.postgresql.org/pgsql-admin/2007-09/msg00031.php

Best Regards


Le 09/03/2011 23:01, Jim Nasby a écrit :
> On Mar 8, 2011, at 9:45 AM, Samba GUEYE wrote:
>> I have a problem with table partitioning because i have a foreign key applied on the partionned table and it throw a constraint violation error during inserts.
>> I saw on the manual (http://www.postgresql.org/docs/8.4/interactive/ddl-inherit.html caveats section) that it's a limitation due to postgrsql table inheritance select queries performance are really bad without partitionning and i'm looking for a workaround to this foreign key problem or another solution for improve performance for larges tables.
> Actually, this sounds more like having a foreign key pointed at a parent table in an inheritance tree; which flat-out doesn't do what you'd want.
>
> Can you tell us what the foreign key constraint actually is, and what the inheritance setup for the tables in the FK is?
> --
> Jim C. Nasby, Database Architect                   jim@nasby.net
> 512.569.9461 (cell)                         http://jim.nasby.net
>
>