Re: operator exclusion constraints

From: Jeff Davis <pgsql(at)j-davis(dot)com>
To: pgsql-hackers(at)postgresql(dot)org
Subject: Re: operator exclusion constraints
Date: 2009-09-27 21:47:03
Message-ID: 1254088023.5640.192.camel@jdavis
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers

Attached is a new patch. I ran it through filterdiff, but in case that
didn't work for some reason, I attached a gzipped version of the
original unified diff produced by git.

* Changed underlying algorithm to match Tom's suggestion: do the second
index lookup after already inserting into the first.
* Language changes, including the latest "<expression> CHECK WITH <op>"
idea from Tom, seconded by Robert Haas.
* automatically builds index for you, no need to create it separately,
just specify the index AM (or let it default to btree)
* Only one constraint per index is allowed, and the index is treated
entirely as an internal implementation detail.
* Support for predicates (partial constraints/partial index)
* Support for expressions
* Support for other index options, like WITH list and USING INDEX
* Docs updated and improved
* Tests updated
* Full recheck support (the previous recheck mechanism didn't work for
* Make information_schema ignore operator exclusion constraints
* error message improvements

When testing/reviewing, use the documentation from CREATE TABLE, but use
the ALTER TABLE variant instead. Right now the CREATE TABLE variant
doesn't work (see below).

There is still a significant TODO list:

* CREATE TABLE -- right now, it only works for ALTER TABLE, and the
docs are lying. Coming soon.

* psql - haven't updated it to keep up with the language changes

* pg_dump


* Inheritance

* Enforce on existing tuples when the constraint is created -- This is
intertwined with inheritance, I think, and I am still working on that.
Obviously, this is an important TODO item to get the patch ready for

* Deferrability (optional for now) -- I need the trigger to be able to
perform the check as well. It looks like it has most of the information
necessary, but I'm trying to determine where would be the cleanest place
to export the constraint checking function so that it can be called by
the trigger as well as ExecInsertIndexTuples and the bulk checker (that
checks existing tuples at the time the constraint is added).

* GIN support (optional for now) -- I need to create a gingettuple
method. It would have to be a wrapper around gingetbitmap, and would not
be any more efficient than gingetbitmap, but it would allow my patch to
work for GIN indexes.

I think I've made some progress this commitfest, both in terms of
decisions made (thanks to everyone who provided input and direction),
and also in terms of code. I would still appreciate more review during
this commitfest, but realistically, it will still be at least another
week before I can say that I'm done with all open items.

Jeff Davis

Attachment Content-Type Size
operator-exclusion-constraints-20090927.patch.gz application/x-gzip 19.6 KB
operator-exclusion-constraints-20090927.context.patch text/x-patch 80.7 KB

In response to


Browse pgsql-hackers by date

  From Date Subject
Next Message Andrew Dunstan 2009-09-28 00:42:20 handlers for inline code
Previous Message Robert Haas 2009-09-27 21:03:15 Re: syslog_line_prefix