Re: partitioning question -- how to guarantee uniqueness across partitions

Thanks, Tom.

Let me give a little more detail on my actual data rather than the
simple example I sent.

I have a 60GB table of loan balances, which I've partitioned into 26 tables.

The loan id's are a sequence of 6 characters, so the partitioning rule
I've used is the first character of the loan id, which yields roughly
equal sized partitions of 2.8 GB or so.

Each loan can only have one balance per month, so the primary key on
each partition is set to be loan_id and asofdate.

However, this data is meant to be available via a rails application,
hence, the need for a surrogate key of integers which is unique across
the entire set of partitions.

Creation of new rows in the partitioned tables should not be an issue
under normal circumstances because I see that all of the child tables
use the same sequence for generating new id's.

However, what makes me nervous is that there is no explicit constraint
in the database that prevents duplicate id's from being created, and
I'm not sure how the rails app would react if for whatever reason
duplicate id keys wound up in the table.

Any suggestions?

Thanks,
Whit

On Sun, Jun 28, 2009 at 1:27 PM, Tom Lane<tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Whit Armstrong <armstrong(dot)whit(at)gmail(dot)com> writes:
>> I have a simple example copied from the 8.3 manual on partitioning
>> (http://www.postgresql.org/docs/8.3/interactive/ddl-partitioning.html).
>
>> My question is, if you create a serial type in the parent table which
>> is meant to be the primary key across all the partitions, how does one
>> guarantee uniqueness of that key?
>
> One doesn't.  That is not an appropriate way to set up a partitioned
> table.  You need a primary key that can actually be used as a meaningful
> partitioning key.  In this example, the id is completely useless and
> what you should be looking at is making the data_value be the primary
> key.
>
>                        regards, tom lane
>