Re: operator exclusion constraints

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Jeff Davis <pgsql(at)j-davis(dot)com>
Cc: Robert Haas <robertmhaas(at)gmail(dot)com>, Peter Eisentraut <peter_e(at)gmx(dot)net>, Simon Riggs <simon(at)2ndquadrant(dot)com>, pgsql-hackers(at)postgresql(dot)org
Subject: Re: operator exclusion constraints
Date: 2009-12-06 15:46:48
Message-ID: 23808.1260114408@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

Jeff Davis <pgsql(at)j-davis(dot)com> writes:
> [ exclusion constraints patch ]

Still working on this patch. I ran into something I didn't like at all:

> + if (newrel != NULL)
> + ereport(ERROR,
> + (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
> + errmsg("cannot rewrite table while adding "
> + "operator exclusion constraint")));

This would be bad enough if the restriction were what the message
alleges, ie, you can't write an ALTER TABLE that both rewrites the heap
and adds an exclusion constraint. However, actually the error also
occurs if you issue a rewriting ALTER TABLE against a table that already
has an exclusion constraint. That raises it from annoyance to
showstopper IMO.

There is not a whole lot that can be done to fix this given the design
that exclusion checking is supposed to be done in ATRewriteTable ---
when that runs, we of course have not built the new index yet, so
there's no way to use it to check the constraint.

I think the right way to go at it is to drop that part of the patch
and instead have index_build execute a separate pass to check the
constraint after it's built an exclusion-constraint index. In the
typical case where you're just doing ALTER TABLE ADD CONSTRAINT EXCLUDE,
this ends up being the same amount of work since there's no need to
run an ATRewriteTable scan at all. It would be extra work if someone
tried to add multiple exclusion constraints in one ALTER; but I have a
hard time getting excited about that, especially in view of the fact
that the cost of the index searches is going to swamp the heapscan
anyway.

This approach would also mean that the constraint is re-verified
if someone does a REINDEX. I think this is appropriate behavior
since reindexing a regular unique index also causes re-verification.
However I can imagine someone objecting on grounds of cost ---
it would probably about double the cost of reindexing compared
to assuming the constraint is still good. We could probably teach
index_build to skip the check pass during REINDEX, but I don't
really want to.

Comments?

regards, tom lane

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Jonas J 2009-12-06 15:54:59 A sniffer for the buffer
Previous Message Simon Riggs 2009-12-06 15:46:40 Re: Allowing DML RULEs that produce Read Only actions during RO xacts