Deferred partial/expression unique constraints

On 7/12/11 9:46 AM, Andres Freund wrote:

Hi,

I guess $subject wasn't implemented because plain unique indexes aren't
represented in pg_constraint and thus do not have a place to store information
about being deferred?
Other than that I do not see any special complications in implementing it?

Um, I thought that deferrable unique constraints were a 9.0 feature, no?

--
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com

On 12 July 2011 19:26, Josh Berkus <josh@agliodbs.com> wrote:

On 7/12/11 9:46 AM, Andres Freund wrote:

Hi,

I guess $subject wasn't implemented because plain unique indexes aren't
represented in pg_constraint and thus do not have a place to store information
about being deferred?
Other than that I do not see any special complications in implementing it?

Um, I thought that deferrable unique constraints were a 9.0 feature, no?

Yes, but there is no syntax to create a unique constraint on an
expression, and hence to create a deferrable unique expression check.

However, that doesn't seem like such a serious limitation, because the
same functionality can be achieved using an exclusion constraint with
the equality operator.

Regards,
Dean

On Tuesday, July 12, 2011 08:57:44 PM Dean Rasheed wrote:

On 12 July 2011 19:26, Josh Berkus <josh@agliodbs.com> wrote:

On 7/12/11 9:46 AM, Andres Freund wrote:

Hi,

I guess $subject wasn't implemented because plain unique indexes aren't
represented in pg_constraint and thus do not have a place to store
information about being deferred?
Other than that I do not see any special complications in implementing
it?

Um, I thought that deferrable unique constraints were a 9.0 feature, no?

Yes, but there is no syntax to create a unique constraint on an
expression, and hence to create a deferrable unique expression check.

However, that doesn't seem like such a serious limitation, because the
same functionality can be achieved using an exclusion constraint with
the equality operator.

That doesn't solve the issue of a partial index, right? Also I find it that
intuitive to package a expression inside an operator (which needs to be
complicated enough not to be accidentally used and still be expressive...).
Especially if that expression involves more than one column (which isn't that
hard to imagine).

Thanks,

Andres

On 13 July 2011 01:23, Andres Freund <andres@anarazel.de> wrote:

On Tuesday, July 12, 2011 08:57:44 PM Dean Rasheed wrote:

On 12 July 2011 19:26, Josh Berkus <josh@agliodbs.com> wrote:

On 7/12/11 9:46 AM, Andres Freund wrote:

Hi,

I guess $subject wasn't implemented because plain unique indexes aren't
represented in pg_constraint and thus do not have a place to store
information about being deferred?
Other than that I do not see any special complications in implementing
it?

Um, I thought that deferrable unique constraints were a 9.0 feature, no?

Yes, but there is no syntax to create a unique constraint on an
expression, and hence to create a deferrable unique expression check.

However, that doesn't seem like such a serious limitation, because the
same functionality can be achieved using an exclusion constraint with
the equality operator.

That doesn't solve the issue of a partial index, right? Also I find it that
intuitive to package a expression inside an operator (which needs to be
complicated enough not to be accidentally used and still be expressive...).
Especially if that expression involves more than one column (which isn't that
hard to imagine).

Yes, it also appears to cover partial indexes. For example:

CREATE TABLE foo
(
a int,
b int,
CONSTRAINT sum_unique EXCLUDE ((a+b) WITH =) WHERE (a>0 AND b>0)
);
INSERT INTO foo VALUES(3,7);
INSERT INTO foo VALUES(-1,11);
INSERT INTO foo VALUES(2,8);

I agree that expressing that using a UNIQUE constraint would perhaps
be more intuitive, but it would be new non-SQL-spec syntax that AFAICS
wouldn't actually add any new functionality.

Regards,
Dean

Dean Rasheed <dean.a.rasheed@gmail.com> writes:

On 7/12/11 9:46 AM, Andres Freund wrote:

I guess $subject wasn't implemented because plain unique indexes aren't
represented in pg_constraint and thus do not have a place to store
information about being deferred?

I agree that expressing that using a UNIQUE constraint would perhaps
be more intuitive, but it would be new non-SQL-spec syntax that AFAICS
wouldn't actually add any new functionality.

Our standard reason for not implementing UNIQUE constraints on
expressions has been that then you would have a thing that claims to be
a UNIQUE constraint but isn't representable in the information_schema
views that are supposed to show UNIQUE constraints. We avoid this
objection in the current design by shoving all that functionality into
EXCLUDE constraints, which are clearly outside the scope of the spec.

regards, tom lane

On ons, 2011-07-13 at 11:26 -0400, Tom Lane wrote:

Dean Rasheed <dean.a.rasheed@gmail.com> writes:

On 7/12/11 9:46 AM, Andres Freund wrote:

I guess $subject wasn't implemented because plain unique indexes aren't
represented in pg_constraint and thus do not have a place to store
information about being deferred?

I agree that expressing that using a UNIQUE constraint would perhaps
be more intuitive, but it would be new non-SQL-spec syntax that AFAICS
wouldn't actually add any new functionality.

Our standard reason for not implementing UNIQUE constraints on
expressions has been that then you would have a thing that claims to be
a UNIQUE constraint but isn't representable in the information_schema
views that are supposed to show UNIQUE constraints. We avoid this
objection in the current design by shoving all that functionality into
EXCLUDE constraints, which are clearly outside the scope of the spec.

I have never heard that reason before, and I think it's a pretty poor
one. There are a lot of other things that are not representable in the
information schema.

On Fri, 2011-07-22 at 23:35 +0300, Peter Eisentraut wrote:

On ons, 2011-07-13 at 11:26 -0400, Tom Lane wrote:

Our standard reason for not implementing UNIQUE constraints on
expressions has been that then you would have a thing that claims to be
a UNIQUE constraint but isn't representable in the information_schema
views that are supposed to show UNIQUE constraints. We avoid this
objection in the current design by shoving all that functionality into
EXCLUDE constraints, which are clearly outside the scope of the spec.

I have never heard that reason before, and I think it's a pretty poor
one. There are a lot of other things that are not representable in the
information schema.

I think what Tom is saying is that the information_schema might appear
inconsistent to someone following the spec.

Can you give another example where we do something like that?

Regards,
Jeff Davis

On Mon, Jul 25, 2011 at 2:29 PM, Jeff Davis <pgsql@j-davis.com> wrote:

On Fri, 2011-07-22 at 23:35 +0300, Peter Eisentraut wrote:

On ons, 2011-07-13 at 11:26 -0400, Tom Lane wrote:

Our standard reason for not implementing UNIQUE constraints on
expressions has been that then you would have a thing that claims to be
a UNIQUE constraint but isn't representable in the information_schema
views that are supposed to show UNIQUE constraints.  We avoid this
objection in the current design by shoving all that functionality into
EXCLUDE constraints, which are clearly outside the scope of the spec.

I have never heard that reason before, and I think it's a pretty poor
one.  There are a lot of other things that are not representable in the
information schema.

+1.

I think what Tom is saying is that the information_schema might appear
inconsistent to someone following the spec.

Can you give another example where we do something like that?

http://archives.postgresql.org/pgsql-bugs/2010-08/msg00374.php

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company