From: Samba GUEYE <samba.gueye@intesens.com>
To: Jim Nasby <jim@nasby.net>
Cc: Postgre Performance <pgsql-performance@postgresql.org>
Subject: Re: Table partitioning problem
Date: Thu, 10 Mar 2011 09:25:06 +0100
Message-ID: <4D788AE2.7010004@intesens.com> (raw)
In-Reply-To: <04931568-A1CA-4B5D-B907-E061713E1B54@nasby.net>
References: <4D764F04.7020400@intesens.com>
<04931568-A1CA-4B5D-B907-E061713E1B54@nasby.net>
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
>
>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: samba.gueye@intesens.com, jim@nasby.net
Subject: Re: Table partitioning problem
In-Reply-To: <4D788AE2.7010004@intesens.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox