Re: operator exclusion constraints - Mailing list pgsql-hackers

On Sun, 2009-12-06 at 10:46 -0500, Tom Lane wrote:
> 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.

The following works as expected for me:
 create table foo(i int, j int, k int);
 -- the following causes the error in question: alter table foo add exclude(i with =),   alter column k type text;
 alter table foo add exclude(i with =); -- works alter table foo alter column k type text; -- works

So the workaround is simply to break the ALTER into two statements.

(Aside: the error message should probably have a DETAIL component
telling the user to break up the ALTER commands into separate actions.)

Aha -- I think I see the problem you're having: if you try to rewrite
one of the columns contained in the exclusion constraint, you get that
error:
 create table bar_exclude(i int, exclude (i with =));
 -- raises same error, which is confusing alter table bar_exclude alter column i type text;

Compared with UNIQUE: create table bar_unique(i int unique); alter table bar_unique alter column i type text; -- works

However, I think we _want_ exclusion constraints to fail in that case.
Unique constraints can succeed because both types support UNIQUE, and
the semantics are the same. But in the case of exclusion constraints,
it's quite likely that the semantics will be different or the named
operator won't exist at all. I think it's more fair to compare with a
unique index on an expression:
 create table bar_unique2(i int); create unique index bar_unique2_idx on bar_unique2 ((i + 1));
 alter table bar_unique2 alter column i type text; ERROR:  operator does not exist: text + integer

You could make the argument that we should do the same thing: try to
re-apply the expression on top of the new column. The only situation
where I can imagine that would be useful is if you are using exclusion
constraints in place of UNIQUE (even then, it's different, because it
uses operator names, not BTEqualStrategyNumber for the default btree
opclass).

If the user alters the type of a column that's part of an exclusion
constraint, the semantics are complex enough that I think we should
inform them that they need to drop the constraint, change the column,
and re-add it. So, my personal opinion is that we need to change the
error message (and probably have two distinct error messages for the two
cases) rather than changing the algorithm.

Comments?

Regards,Jeff Davis