Re: WIP: generalized index constraints

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. Jeff's complaint about total order is not an
argument against having an index, it's just pointing out that btree is
not the only possible type of index. It's perfectly legitimate to
imagine using a hash index to enforce uniqueness, for example. If hash
indexes had better performance we'd probably already have been looking
for a way to do that, and wanting some outside-the-AM mechanism for it
so we didn't have to duplicate code from btree.

Also, if hash indexes were a realistic alternative to btree for this,
we'd already have come up against the problem that the CONSTRAINT syntax
doesn't provide any way to specify what kind of index you want to use
underneath the constraint. I think it might be interesting to turn
around Jeff's syntax sketch and provide a way to say that a CONSTRAINT
declaration should depend on some previously added index, eg
something like

ALTER TABLE tab ADD CONSTRAINT UNIQUE (col1, col2) USING index

Not sure how that squares exactly with the question of variant
definitions of uniqueness semantics (as opposed to purely implementation
decisions like hash vs btree). But it's a different way to come at it.

regards, tom lane