Re: WIP: generalized index constraints

On Tue, 2009-07-07 at 13:22 -0400, Tom Lane wrote:
> Jeff Davis <pgsql(at)j-davis(dot)com> writes:
> > On Mon, 2009-07-06 at 18:27 +0100, Simon Riggs wrote:
> >> In many cases, people add unique indexes solely to allow replication to
> >> work correctly. The index itself may never be used, especially in high
> >> volume applications.
>
> > Interesting. Maybe we should at least try to leave room for this feature
> > to be added later. I agree that, from a theoretical perspective,
> > requiring a UNIQUE constraint to use an index is wrong. For one thing,
> > you can't ensure the uniqueness without defining some total order
> > (although you can define an arbitrary total order for cases with no
> > meaningful total order).
>
> This seems a bit pointless. There is certainly not any use case for a
> constraint without an enforcement mechanism (or at least none the PG
> community is likely to consider legitimate ;-)). And it's not very
> realistic to suppose that you'd check a constraint by doing a seqscan
> every time. Therefore there has to be an index underlying the
> constraint somehow.

I think the idea has been misconstrued.

Obviously a constraint requires an enforcement mechanism. That doesn't
imply that the enforcement mechanism must be fully usable as an index.

The example being discussed was enforcing uniqueness on monotonically
increasing columns. If we knew that a column value was GENERATED ALWAYS
using a sequence, then we could simply skip the uniqueness check
altogether. No index, yet an enforced unique constraint.

Yes, we would need to understand the relationship between the sequence
and the table and throw an error in certain sequence update cases (and
we may need to check those with a seq scan). But that seems a small
price to pay for the avoidance of a potentially very large index that
may have no purpose.

--
Simon Riggs www.2ndQuadrant.com
PostgreSQL Training, Services and Support