Re: Auto Partitioning

"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. 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.

At some level not having them is actually a nice thing for DBAs. It gives them
an excuse for not having the constraint that will only cause them maintenance
headaches down the road. But it's dangerous to go too far down that road.

--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com