Re: Table partitioning problem

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(at)nasby(dot)net
> 512.569.9461 (cell) http://jim.nasby.net
>
>