Re: WIP: generalized index constraints

From: Jeff Davis <pgsql(at)j-davis(dot)com>
To: Brendan Jurd <direvus(at)gmail(dot)com>
Cc: Heikki Linnakangas <heikki(dot)linnakangas(at)enterprisedb(dot)com>, pgsql-hackers(at)postgresql(dot)org
Subject: Re: WIP: generalized index constraints
Date: 2009-09-15 08:46:57
Message-ID: 1253004417.24770.47.camel@jdavis
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers

On Sun, 2009-09-13 at 19:08 +1000, Brendan Jurd wrote:
> Any update on this patch?

Attached is the latest version.


* Merged with HEAD
* Changed from storing the information in pg_index to pg_constraint.
This required rewriting a large portion of the patch, so it's not a
clean diff from the previous patch.
* Implemented the language using ALTER TABLE to add the constraint, as
discussed (with a slight change to avoid the extra "INDEX" keyword).
* Added docs
* Added tests
* All relations with relindconstraints == 0 do not pass through the
index constraints enforcement code at all. I did this a little
differently than what I laid out in the design, but the idea is the
same and it should avoid any problems.

That's the good news. The bad news:

* No pg_dump support yet (shouldn't be too hard)
* Creating a new constraint does not check the existing data for
* Doesn't work like the new deferrable unique constraints yet (also
shouldn't be too hard).
* I didn't make any changes to the behavior of LIKE (also not hard).
* Can't be specified at CREATE INDEX time. I don't think this is a
showstopper, and it will take some significant effort. The advantage
of allowing this is that the constraints can be checked more quickly
(in theory) while building the index, and it also might be handy
shorthand. However, it suffers from a number of problems:
1. Extra syntax that is almost entirely redundant.
2. More work.
3. The performance gains will probably be pretty marginal. We have
to do N index scans anyway; the savings would only be due to
the better caching impact and the fact that the index in the
process of being built is smaller than an already-built index.
So, right now I'm not in a hurry to fix this last point.

I realize that some of the things missing make the patch uncomittable in
its current form. However, I would still appreciate a review of what I
have ready.

Jeff Davis

Attachment Content-Type Size
generalized-index-constraints-20090915.patch.gz application/x-gzip 14.5 KB

In response to


Browse pgsql-hackers by date

  From Date Subject
Next Message Pavel Stehule 2009-09-15 08:51:50 Re: Issues for named/mixed function notation patch
Previous Message Pierre Frédéric Caillaud 2009-09-15 07:28:29 Re: Bulk Inserts