Re: WIP: generalized index constraints - Mailing list pgsql-hackers

On Sat, 2009-09-19 at 15:26 -0400, Tom Lane wrote:
> I haven't read the patch, but this whole discussion sounds to me like
> it means you're trying to plug things in at the wrong level.  Indexes
> generally don't care where the values they are storing came from ---
> whether it's a simple column or a expression result, it's all the same
> to the index.  I don't see why that shouldn't be true for exclusion
> constraints too.

The design is that one backend needs to be able to see values being
inserted by other backends before commit. There are two ways I can see
to do this:

(a) have all concurrent inserters serialize doing something like: 1. acquire exclusive LWLock 2. search index for
conflictswith dirty snapshot and recheck if    necessary 3. insert into index 4. release exclusive LWLock
 

(b) do what I do now, which is to: 1. acquire exlusive LWLock 2. put self in table of concurrent inserters, along with
TIDof heap     tuple I'm inserting 3. release exclusive LWLock 4. acquire shared LWLock 5. copy potential conflicts to
localmemory 6. release shared LWLock 7. test for real conflicts between my heap tuple and the potentially
conflictingheap tuple (which can be found by TID). 8. search index with dirty snapshot for conflicts and recheck if
necessary9. insert tuple into index10. acquire exclusive LWLock11. remove self from table of concurrent inserters12.
releaseexclusive LWLock
 

Design (b) offers better concurrency because all conflict testing, index
searching, and index insertion take place without a lock at all. So, I
chose design (b). This has been out there for quite a long time[1][2],
and if it is an unacceptable design I need to know soon in order for
this feature to make it.

However, the consequence of (b) is that ExecInsertIndexTuples needs to
know about the translation from a heap tuple to an index tuple so that
the conflicts can be checked.

> BTW, further betraying that I've not read the patch: what exactly are
> you doing about the information_schema views?  If we are treating these
> things as SQL constraints, one would expect them to show up in
> information_schema; but I don't see how to represent them there in any
> adequate fashion, even without the expression-index angle.

Nothing right now. I think they should just be omitted from
information_schema, which can only (almost by definition) represent the
lowest common denominator features.

> On the whole
> I think we'd be a lot better off to NOT consider them to be constraints,
> but just another option for CREATE INDEX.

You suggested allowing an ALTER TABLE representation[3] and that design
has floated around for quite some time as well.

ALTER TABLE also has a major advantage: multiple constraints can use the
same index. For instance, an index on (a, b, c) can be used to enforce
both (a =, b =) and (a =, c =). You can't do that with btree, and it
could be a powerful feature that might cause some people to choose my
mechanism for a regular UNIQUE constraint over btree's existing
uniqueness mechanism.

So, I actually switched over the ALTER TABLE as my primary syntactic
representation, and dropped the CREATE INDEX variant (I think that would
be worthwhile to bring back as an extra option, but I haven't yet). If I
need to drop ALTER TABLE, I need to know soon.

Regards,Jeff Davis

[1] http://archives.postgresql.org/pgsql-hackers/2008-06/msg00404.php
[2] http://archives.postgresql.org/pgsql-hackers/2009-07/msg00302.php
[3] http://archives.postgresql.org/pgsql-hackers/2009-07/msg00406.php