Thread: Deferred partial/expression unique constraints
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? Is there any reasons not to store unique indexes in pg_constraint in the future? Greetings, Andres
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