Re: Auto Partitioning

On Wednesday 04 April 2007 21:17, Gregory Stark wrote:
> "Simon Riggs" <simon(at)2ndquadrant(dot)com> writes:
> >> If we don't have multi-table indexes how do we enforce a primary key
> >> against a partitioned set? What about non primary keys that are just
> >> UNIQUE? What about check constraints that aren't apart of the exclusion?
> >
> > I can come up with arbitrary examples that require them, but I've not
> > seen one that makes sense in a real business app. Calling columns a, b
> > and c disguises the validity of the example, IMHO.
>
> Usually it comes with a situation where you want to do something like
> "partition invoices by invoice_date" while simultaneously "use invoice_num"
> as the primary key".
>
> Normally the invoices numbers will be incrementing chronologically but
> there's no constraint or any mechanism to enforce that or to enforce that
> an old invoice number from an old partition isn't reused.
>
> In practice I think this isn't really a serious problem though. The old
> partitions are going to be read-only so you can just check that the invoice
> number doesn't already exist without worrying about race conditions.

In practice many people need a PK on the table not just as a unique identifier
for the row, but to act as a parent in a FK relationship. If you start your
schema with one table and have to break it up into partitions later, this
will raise a number of red flags.

> And in
> most cases it's being sequence-generated or something equally reliable so
> the constraints are really just there as a backstop; you're not depending
> on them for correctness.
>

With that argument why have unique constraints at all?

--
Robert Treat
Build A Brighter LAMP :: Linux Apache {middleware} PostgreSQL