Re: [HACKERS] operator exclusion constraints
On Thu, Mar 11, 2010 at 5:29 AM, Tom Lane t...@sss.pgh.pa.us wrote: Indexes: foo_pkey PRIMARY KEY, btree (f1), tablespace ts1 foo_f2_exclusion btree (f2), tablespace ts1 foo_f3_exclusion btree (f3) DEFERRABLE INITIALLY DEFERRED Exclusion constraints: foo_f2_exclusion EXCLUDE USING btree (f2 WITH =) foo_f3_exclusion EXCLUDE USING btree (f3 WITH =) DEFERRABLE INITIALLY DEFERRED This might have been defensible back when the idea was to keep constraints decoupled from indexes, but now it just looks bizarre. The only really bizarre part is the DEFERRABLE INITIALLY DEFERRED on the index. We should either get rid of the Exclusion constraints: display and attach the info to the index entries, or hide indexes that are attached to exclusion constraints. I lean to the former on the grounds of the precedent for unique/pkey indexes --- which is not totally arbitrary, since an index is usable as a query index regardless of its function as a constraint. It's probably a debatable point though. There is a third option -- print PRIMARY keys twice, once as a btree index and again as a constraint where it says somehting like USING index foo_pkey I think in the long term that would be best -- especially if we combine it with a patch to be able to create a new primary key constraint using an existing index. That's something people have been asking for anyways and I think it's a somewhat important property that these lines can be copy pasted and run nearly as-is to recreate the objects. I definitely agree that your other proposed way to go is worse. I think people need a list of indexes in one place. So given the current syntax for creating these I think your proposed change is the least worst alternative. -- greg -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] operator exclusion constraints
Greg Stark gsst...@mit.edu writes: There is a third option -- print PRIMARY keys twice, once as a btree index and again as a constraint where it says somehting like USING index foo_pkey No, that's exactly what I hate about the current behavior for exclusion constraints, and I'd like it even less for more-common options like primary or unique constraints. \d is too d*mn verbose already; there is no percentage in making it even longer by printing redundant entries for many indexes. One thing I did think about was converting PK/UNIQUE indexes to be printed by pg_get_constraintdef() too, rather than assembling an ad-hoc output for them as we do now. This would make the code a bit simpler but would involve some small changes in the output --- in particular, you wouldn't see any indication that they were btrees, since there's no place for that in standard constraint syntax. On balance it didn't seem like an improvement, although it would partially respond to your desire to have the output be cut-and-pasteable. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] operator exclusion constraints
On Thu, 2010-03-11 at 00:29 -0500, Tom Lane wrote: Patch changes: Indexes: foo_pkey PRIMARY KEY, btree (f1), tablespace ts1 foo_f2_exclusion btree (f2), tablespace ts1 foo_f3_exclusion btree (f3) DEFERRABLE INITIALLY DEFERRED Exclusion constraints: foo_f2_exclusion EXCLUDE USING btree (f2 WITH =) foo_f3_exclusion EXCLUDE USING btree (f3 WITH =) DEFERRABLE INITIALLY DEFERRED To: Indexes: foo_pkey PRIMARY KEY, btree (f1), tablespace ts1 foo_f2_exclusion EXCLUDE USING btree (f2 WITH =), tablespace ts1 foo_f3_exclusion EXCLUDE USING btree (f3 WITH =) DEFERRABLE INITIALLY DEFERRED Any objections? Looks good to me. Regards, Jeff Davis -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] operator exclusion constraints
Awhile back I wrote:
* I'm not too satisfied with the behavior of psql's \d:
regression=# create table foo (f1 int primary key using index tablespace ts1,
regression(# f2 int, EXCLUDE USING btree (f2 WITH =) using index tablespace
ts1,
regression(# f3 int, EXCLUDE USING btree (f3 WITH =) DEFERRABLE INITIALLY
DEFERRED);
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index foo_pkey for
table foo
NOTICE: CREATE TABLE / EXCLUDE will create implicit index foo_f2_exclusion
for table foo
NOTICE: CREATE TABLE / EXCLUDE will create implicit index foo_f3_exclusion
for table foo
CREATE TABLE
regression=# \d foo
Table public.foo
Column | Type | Modifiers
+-+---
f1 | integer | not null
f2 | integer |
f3 | integer |
Indexes:
foo_pkey PRIMARY KEY, btree (f1), tablespace ts1
foo_f2_exclusion btree (f2), tablespace ts1
foo_f3_exclusion btree (f3) DEFERRABLE INITIALLY DEFERRED
Exclusion constraints:
foo_f2_exclusion EXCLUDE USING btree (f2 WITH =)
foo_f3_exclusion EXCLUDE USING btree (f3 WITH =) DEFERRABLE INITIALLY
DEFERRED
regression=#
This might have been defensible back when the idea was to keep constraints
decoupled from indexes, but now it just looks bizarre. We should either
get rid of the Exclusion constraints: display and attach the info to
the index entries, or hide indexes that are attached to exclusion
constraints. I lean to the former on the grounds of the precedent for
unique/pkey indexes --- which is not totally arbitrary, since an index
is usable as a query index regardless of its function as a constraint.
It's probably a debatable point though.
Attached is a patch against HEAD that folds exclusion constraints into
\d's regular indexes list. With this, the above example produces
Table public.foo
Column | Type | Modifiers
+-+---
f1 | integer | not null
f2 | integer |
f3 | integer |
Indexes:
foo_pkey PRIMARY KEY, btree (f1), tablespace ts1
foo_f2_exclusion EXCLUDE USING btree (f2 WITH =), tablespace ts1
foo_f3_exclusion EXCLUDE USING btree (f3 WITH =) DEFERRABLE INITIALLY
DEFERRED
Any objections?
regards, tom lane
? psql
Index: describe.c
===
RCS file: /cvsroot/pgsql/src/bin/psql/describe.c,v
retrieving revision 1.240
diff -c -r1.240 describe.c
*** describe.c 11 Mar 2010 04:36:43 - 1.240
--- describe.c 11 Mar 2010 05:18:28 -
***
*** 1105,
bool hasrules;
bool hastriggers;
bool hasoids;
- bool hasexclusion;
Oid tablespace;
char *reloptions;
char *reloftype;
--- 1105,1110
***
*** 1128,1135
printfPQExpBuffer(buf,
SELECT c.relchecks, c.relkind, c.relhasindex, c.relhasrules,
c.relhastriggers, c.relhasoids,
! %s, c.reltablespace, c.relhasexclusion,
! CASE WHEN c.reloftype = 0 THEN '' ELSE c.reloftype::pg_catalog.regtype::text END\n
FROM pg_catalog.pg_class c\n
LEFT JOIN pg_catalog.pg_class tc ON (c.reltoastrelid = tc.oid)\n
WHERE c.oid = '%s'\n,
--- 1127,1134
printfPQExpBuffer(buf,
SELECT c.relchecks, c.relkind, c.relhasindex, c.relhasrules,
c.relhastriggers, c.relhasoids,
! %s, c.reltablespace,
! CASE WHEN c.reloftype = 0 THEN '' ELSE c.reloftype::pg_catalog.regtype::pg_catalog.text END\n
FROM pg_catalog.pg_class c\n
LEFT JOIN pg_catalog.pg_class tc ON (c.reltoastrelid = tc.oid)\n
WHERE c.oid = '%s'\n,
***
*** 1207,1216
strdup(PQgetvalue(res, 0, 6)) : 0;
tableinfo.tablespace = (pset.sversion = 8) ?
atooid(PQgetvalue(res, 0, 7)) : 0;
! tableinfo.hasexclusion = (pset.sversion = 9) ?
! strcmp(PQgetvalue(res, 0, 8), t) == 0 : false;
! tableinfo.reloftype = (pset.sversion = 9 strcmp(PQgetvalue(res, 0, 9), ) != 0) ?
! strdup(PQgetvalue(res, 0, 9)) : 0;
PQclear(res);
res = NULL;
--- 1206,1213
strdup(PQgetvalue(res, 0, 6)) : 0;
tableinfo.tablespace = (pset.sversion = 8) ?
atooid(PQgetvalue(res, 0, 7)) : 0;
! tableinfo.reloftype = (pset.sversion = 9 strcmp(PQgetvalue(res, 0, 8), ) != 0) ?
! strdup(PQgetvalue(res, 0, 8)) : 0;
PQclear(res);
res = NULL;
***
*** 1545,1571
appendPQExpBuffer(buf, i.indisvalid, );
else
appendPQExpBuffer(buf, true as indisvalid, );
! appendPQExpBuffer(buf, pg_catalog.pg_get_indexdef(i.indexrelid, 0, true));
if (pset.sversion = 9)
appendPQExpBuffer(buf,
! ,\n (NOT i.indimmediate) AND
! EXISTS (SELECT 1 FROM pg_catalog.pg_constraint
! WHERE conrelid = i.indrelid AND
! conindid = i.indexrelid AND
! contype IN ('p','u','x') AND
! condeferrable) AS condeferrable
! ,\n (NOT
Re: [HACKERS] operator exclusion constraints
On Mon, 2009-12-07 at 00:26 -0500, Tom Lane wrote: Jeff Davis pg...@j-davis.com writes: [ exclusion constraint patch ] Applied after quite a lot of editorialization. For future reference, here is a summary of what I did: Thank you for the suggestions, and the other constructive criticism during development. Regards, Jeff Davis -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] operator exclusion constraints
Jeff Davis pg...@j-davis.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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] operator exclusion constraints
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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] operator exclusion constraints
Jeff Davis pg...@j-davis.com writes: 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: It fails for me regardless of which column is actually modified. It could be this is a consequence of other changes I've been making, but given the way ALTER TABLE works it's hard to see why the specific column being modified would matter. Anything that forces a table rewrite would lead to running through this code path. I don't really agree with your argument that it's okay to reject the case of altering the underlying column type, anyhow. There's enough commonality of operator names that it's sensible to try to preserve the constraint across a change. Consider replacing a circle with a polygon, say. A no-overlap restriction is still sensible. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] operator exclusion constraints
On Sun, 2009-12-06 at 14:06 -0500, Tom Lane wrote: It fails for me regardless of which column is actually modified. It could be this is a consequence of other changes I've been making, but given the way ALTER TABLE works it's hard to see why the specific column being modified would matter. Anything that forces a table rewrite would lead to running through this code path. In ATAddOperatorExclusionConstraint(): tab-constraints = lappend(tab-constraints, newcon); if that isn't done, it doesn't go into the code path with that error message at all. I don't really agree with your argument that it's okay to reject the case of altering the underlying column type, anyhow. There's enough commonality of operator names that it's sensible to try to preserve the constraint across a change. Consider replacing a circle with a polygon, say. A no-overlap restriction is still sensible. Let's say someone is changing from box to a postgis geometry representing a box. For boxes, = actually means equal area (aside: this is insane); but for polygons, = mean equal. Changing in the other direction is bad, as well. I know operators mostly follow convention most of the time, but I'm concerned with the subtle (and surprising) differences. But if someone is changing a column type, which causes a table rewrite, hopefully they've planned it. I'll look into doing it as you suggest. Regards, Jeff Davis -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] operator exclusion constraints
Jeff Davis pg...@j-davis.com writes: ... I'll look into doing it as you suggest. I'm already working with a pretty-heavily-editorialized version. Don't worry about it. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] operator exclusion constraints
Jeff Davis pg...@j-davis.com writes: [ exclusion constraint patch ] Applied after quite a lot of editorialization. For future reference, here is a summary of what I did: * Reworded and renamed stuff to try to be consistent about calling these things exclusion constraints. The original code and docs bore quite a few traces of the various other terminologies, which is not too surprising given the history but would have been confusing to future readers of the code. * Moved the verification of new exclusion constraints into index_build processing as per discussion. * Unified the EXCLUDE parsing path with the existing unique/pkey path by the expedient of adding an excludeOpNames list to IndexStmt. This got rid of quite a lot of duplicated code, and fixed a number of bizarre corner cases like the bogus wording of the index creation NOTICE messages. * Cleaned up some things that didn't really meet project practices. To mention a couple: one aspect of the try to make the patch look like it had always been there rule is to insert new stuff in unsurprising places. Adding code at the end of a list or file very often doesn't meet this test. I tried to put the EXCLUDE constraint stuff beside UNIQUE/PRIMARY KEY handling where possible. Another pet peeve that was triggered a lot in this patch is that you seemed to be intent on fitting ereport() calls into 80 columns no matter what, and would break the error message texts in random places to make it fit. There's a good practical reason not to do that: it makes it hard to grep the source code for an error message. You can break at phrase boundaries if you must, but in general I prefer to just let the text go past the right margin. There are a couple of loose ends yet: * I made CREATE...LIKE processing handle exclusion constraints the same as unique/pkey constraints, ie, they're processed by INCLUDING INDEXES. There was some discussion of rearranging things to make these be processed by INCLUDING CONSTRAINTS instead, but no consensus that I recall. In any case, failing to copy them at all is clearly no good. * I'm not too satisfied with the behavior of psql's \d: regression=# create table foo (f1 int primary key using index tablespace ts1, regression(# f2 int, EXCLUDE USING btree (f2 WITH =) using index tablespace ts1, regression(# f3 int, EXCLUDE USING btree (f3 WITH =) DEFERRABLE INITIALLY DEFERRED); NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index foo_pkey for table foo NOTICE: CREATE TABLE / EXCLUDE will create implicit index foo_f2_exclusion for table foo NOTICE: CREATE TABLE / EXCLUDE will create implicit index foo_f3_exclusion for table foo CREATE TABLE regression=# \d foo Table public.foo Column | Type | Modifiers +-+--- f1 | integer | not null f2 | integer | f3 | integer | Indexes: foo_pkey PRIMARY KEY, btree (f1), tablespace ts1 foo_f2_exclusion btree (f2), tablespace ts1 foo_f3_exclusion btree (f3) DEFERRABLE INITIALLY DEFERRED Exclusion constraints: foo_f2_exclusion EXCLUDE USING btree (f2 WITH =) foo_f3_exclusion EXCLUDE USING btree (f3 WITH =) DEFERRABLE INITIALLY DEFERRED regression=# This might have been defensible back when the idea was to keep constraints decoupled from indexes, but now it just looks bizarre. We should either get rid of the Exclusion constraints: display and attach the info to the index entries, or hide indexes that are attached to exclusion constraints. I lean to the former on the grounds of the precedent for unique/pkey indexes --- which is not totally arbitrary, since an index is usable as a query index regardless of its function as a constraint. It's probably a debatable point though. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] operator exclusion constraints
On Fri, Dec 04, 2009 at 11:35:52AM -0500, Tom Lane wrote: Robert Haas robertmh...@gmail.com writes: On Thu, Dec 3, 2009 at 7:42 PM, Jeff Davis pg...@j-davis.com wrote: On Thu, 2009-12-03 at 19:00 -0500, Tom Lane wrote: I'm starting to go through this patch now. �I thought the consensus was to refer to them as just exclusion constraints? �I'm not seeing that the word operator really adds anything. I assume you're referring to the name used in documentation and error messages. I didn't see a clear consensus, but the relevant thread is here: http://archives.postgresql.org/message-id/1258227283.708.108.ca...@jdavis Exclusion Constraints is fine with me, as are the other options listed in that email. Yeah, I don't remember any such consensus either, but it's not a dumb name. I have been idly wondering throughout this process whether we should try to pick a name that conveys the fact that these constraints are inextricably tied to the opclass/index machinery - but I'm not sure it's possible to really give that flavor in a short phrase, or that it's actually important to do so. IOW... whatever. :-) Well, unique constraints are tied to the opclass/index machinery too. Unless there's loud squawks I'm going to exercise committer's prerogative and make all the docs and messages just say exclusion constraint. We have constraint exclusion already, which could make this confusing. How about either the original operator exclusion constraint or the slightly easier whatever constraint names? Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] operator exclusion constraints
Robert Haas robertmh...@gmail.com writes: On Thu, Dec 3, 2009 at 7:42 PM, Jeff Davis pg...@j-davis.com wrote: On Thu, 2009-12-03 at 19:00 -0500, Tom Lane wrote: I'm starting to go through this patch now. I thought the consensus was to refer to them as just exclusion constraints? I'm not seeing that the word operator really adds anything. I assume you're referring to the name used in documentation and error messages. I didn't see a clear consensus, but the relevant thread is here: http://archives.postgresql.org/message-id/1258227283.708.108.ca...@jdavis Exclusion Constraints is fine with me, as are the other options listed in that email. Yeah, I don't remember any such consensus either, but it's not a dumb name. I have been idly wondering throughout this process whether we should try to pick a name that conveys the fact that these constraints are inextricably tied to the opclass/index machinery - but I'm not sure it's possible to really give that flavor in a short phrase, or that it's actually important to do so. IOW... whatever. :-) Well, unique constraints are tied to the opclass/index machinery too. Unless there's loud squawks I'm going to exercise committer's prerogative and make all the docs and messages just say exclusion constraint. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] operator exclusion constraints
On Fri, 2009-12-04 at 11:35 -0500, Tom Lane wrote: Unless there's loud squawks I'm going to exercise committer's prerogative and make all the docs and messages just say exclusion constraint. Sounds fine to me. Regards, Jeff Davis -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] operator exclusion constraints
On Dec 4, 2009, at 11:35 AM, Tom Lane t...@sss.pgh.pa.us wrote: Robert Haas robertmh...@gmail.com writes: On Thu, Dec 3, 2009 at 7:42 PM, Jeff Davis pg...@j-davis.com wrote: On Thu, 2009-12-03 at 19:00 -0500, Tom Lane wrote: I'm starting to go through this patch now. I thought the consensus was to refer to them as just exclusion constraints? I'm not seeing that the word operator really adds anything. I assume you're referring to the name used in documentation and error messages. I didn't see a clear consensus, but the relevant thread is here: http://archives.postgresql.org/message-id/1258227283.708.108.ca...@jdavis Exclusion Constraints is fine with me, as are the other options listed in that email. Yeah, I don't remember any such consensus either, but it's not a dumb name. I have been idly wondering throughout this process whether we should try to pick a name that conveys the fact that these constraints are inextricably tied to the opclass/index machinery - but I'm not sure it's possible to really give that flavor in a short phrase, or that it's actually important to do so. IOW... whatever. :-) Well, unique constraints are tied to the opclass/index machinery too. Unless there's loud squawks I'm going to exercise committer's prerogative and make all the docs and messages just say exclusion constraint. Go for it. Membership has its privileges. :-) ...Robert -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] operator exclusion constraints
Jeff Davis pg...@j-davis.com writes: On Tue, 2009-12-01 at 23:19 -0500, Robert Haas wrote: For parity with unique constraints, I think that the message: operator exclusion constraint violation detected: %s should be changed to: conflicting key value violates operator exclusion constraint %s Done, and updated tests. I'm starting to go through this patch now. I thought the consensus was to refer to them as just exclusion constraints? I'm not seeing that the word operator really adds anything. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] operator exclusion constraints
On Thu, 2009-12-03 at 19:00 -0500, Tom Lane wrote: I'm starting to go through this patch now. I thought the consensus was to refer to them as just exclusion constraints? I'm not seeing that the word operator really adds anything. I assume you're referring to the name used in documentation and error messages. I didn't see a clear consensus, but the relevant thread is here: http://archives.postgresql.org/message-id/1258227283.708.108.ca...@jdavis Exclusion Constraints is fine with me, as are the other options listed in that email. Regards, Jeff Davis -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] operator exclusion constraints
On Thu, Dec 3, 2009 at 7:42 PM, Jeff Davis pg...@j-davis.com wrote: On Thu, 2009-12-03 at 19:00 -0500, Tom Lane wrote: I'm starting to go through this patch now. I thought the consensus was to refer to them as just exclusion constraints? I'm not seeing that the word operator really adds anything. I assume you're referring to the name used in documentation and error messages. I didn't see a clear consensus, but the relevant thread is here: http://archives.postgresql.org/message-id/1258227283.708.108.ca...@jdavis Exclusion Constraints is fine with me, as are the other options listed in that email. Yeah, I don't remember any such consensus either, but it's not a dumb name. I have been idly wondering throughout this process whether we should try to pick a name that conveys the fact that these constraints are inextricably tied to the opclass/index machinery - but I'm not sure it's possible to really give that flavor in a short phrase, or that it's actually important to do so. IOW... whatever. :-) ...Robert -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] operator exclusion constraints
On Dec 3, 2009, at 6:26 PM, Robert Haas wrote: Yeah, I don't remember any such consensus either, but it's not a dumb name. I have been idly wondering throughout this process whether we should try to pick a name that conveys the fact that these constraints are inextricably tied to the opclass/index machinery - but I'm not sure it's possible to really give that flavor in a short phrase, or that it's actually important to do so. IOW... whatever. :-) Whatever constraints? Operator Whatevers? WhatEVERs? I like it. David -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] operator exclusion constraints
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Thu, Dec 03, 2009 at 08:38:06PM -0800, David E. Wheeler wrote: [...] Whatever constraints? Operator Whatevers? WhatEVERs? I like it. drigting serioulsy off-topic: there's precedent for that in the most venerable piece of free software; TeX has a whatsit node (basically an extension mechanism). Regards - -- tomás -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (GNU/Linux) iD8DBQFLGKM7Bcgs9XrR2kYRAuHFAJ0ZZYzlXHJEgwEbsraAlKVI58yLAgCfU4Cz n+0vobY0HxROigSHUGog7QI= =MWH+ -END PGP SIGNATURE- -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] operator exclusion constraints
On Wed, Dec 2, 2009 at 12:18 AM, Jeff Davis pg...@j-davis.com wrote: On Tue, 2009-12-01 at 23:19 -0500, Robert Haas wrote: For parity with unique constraints, I think that the message: operator exclusion constraint violation detected: %s should be changed to: conflicting key value violates operator exclusion constraint %s Done, and updated tests. In ATAddOperatorExclusionConstraint, streatagy is misspelled. Fixed. Other than that, it looks good to me. Great, thanks for the detailed review! Marked as Ready for Committer. ...Robert -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] operator exclusion constraints
On Fri, Nov 27, 2009 at 10:18 PM, Jeff Davis pg...@j-davis.com wrote: On Thu, 2009-11-26 at 01:33 -0800, Jeff Davis wrote: Remaining issues: * represent operator IDs in catalog, rather than strategy numbers Done, attached. * if someone thinks it's an issue, support search strategies that require binary-incompatible casts of the inputs I've already solved the original problem involving ANYARRAY. If someone comes up with a good use case, or provides me with a little more guidance, I'll reconsider this problem again. Otherwise, I feel like I'm solving a problem that doesn't exist (after all, none of the contrib modules seem to have a problem with the current assumptions, nor does postgis, nor does my PERIOD module). So, I'm considering this a non-issue until further notice. To summarize, the problem as I understand it is this: You have two types, T1 and T2, and there's an implicit cast (with function or with inout) from T1 to T2. And you have an opclass like: CREATE OPERATOR CLASS t1_ops FOR TYPE t1 ... OPERATOR 17 %%(t2, t2) ... And then you have an exclusion constraint like: CREATE TABLE foo ( a t1, EXCLUDE (a t1_ops WITH %%) ); What should the behavior be in the following two cases? 1. Only operator %%(t2, t2) exists. 2. Operator %%(t1, t1) and %%(t2, t2) exist. If left unsolved, #1 results in an error because the operator requires binary-incompatible coercion. #2 results in an error because %%(t1, t1) is not in the opclass. Arguably either one of them could succeed by finding %%(t2, t2) and performing the appropriate conversion; but I think it's fair to just say the opclass is poorly defined. Note that if the opclass is on type t2, you can simply cast a to t2 explicitly in the expression, like so: EXCLUDE((a::t2) t2_ops WITH %%) For parity with unique constraints, I think that the message: operator exclusion constraint violation detected: %s should be changed to: conflicting key value violates operator exclusion constraint %s In ATAddOperatorExclusionConstraint, streatagy is misspelled. Other than that, it looks good to me. ...Robert -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] operator exclusion constraints
On Thu, Nov 26, 2009 at 4:33 AM, Jeff Davis pg...@j-davis.com wrote: Thanks, I applied it. The only significant thing I changed was that I did not inline the index_elem because it made it fairly hard to read. Instead, I renamed it exclude_elem to make it a little more meaningful, which I assume may have been your motivation for inlining it. No, it was really just that I didn't see any point in defining a token that was only used in one place. It seems like it just represents a jumble of tokens without any real connection between them, so I didn't really see why we should break out that piece as opposed to anything else. Changes this patch: * doc changes * changed constraint violation message to be more like btree unique violation * improved error message when an operator is specified that doesn't have a search strategy Remaining issues: * represent operator IDs in catalog, rather than strategy numbers * if someone thinks it's an issue, support search strategies that require binary-incompatible casts of the inputs I'm (still) not an expert on this topic, but here's one more thought: maybe we should try to set this up so that it works in any situation in which an opclass itself would work. IOW, if you manage to define an opclass, you should probably be able to define an operator-exclusion constraint against it, rather than having some opclasses work and others arbitrarily fail. Just my $0.02, ...Robert -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] operator exclusion constraints
On Thu, 2009-11-26 at 16:31 -0500, Robert Haas wrote: On Thu, Nov 26, 2009 at 4:33 AM, Jeff Davis pg...@j-davis.com wrote: Thanks, I applied it. The only significant thing I changed was that I did not inline the index_elem because it made it fairly hard to read. Instead, I renamed it exclude_elem to make it a little more meaningful, which I assume may have been your motivation for inlining it. No, it was really just that I didn't see any point in defining a token that was only used in one place. It seems like it just represents a jumble of tokens without any real connection between them, so I didn't really see why we should break out that piece as opposed to anything else. table_constraint and column_constraint are only used one place. I found it convenient to do so because, in the common case, exclude_elem is just a column name. So the user clearly sees: exclude_elem WITH operator and the pairing isn't obscured by a bunch of optional stuff. I'm (still) not an expert on this topic, but here's one more thought: maybe we should try to set this up so that it works in any situation in which an opclass itself would work. IOW, if you manage to define an opclass, you should probably be able to define an operator-exclusion constraint against it, rather than having some opclasses work and others arbitrarily fail. That's what I was aiming for, but it's theoretically possible for that case to require casts. I will do a little more investigation and write a test case. If it looks reasonable, I'll refactor a little and just do it. It is a pretty obscure case (seeing as I have yet to observe it, including all contrib modules plus postgis), but I might as well do it right as long as it's reasonable to do. If it gets even messier to implement, maybe I'll reconsider. Regards, Jeff Davis -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] operator exclusion constraints
On Wed, Nov 25, 2009 at 3:23 AM, Jeff Davis pg...@j-davis.com wrote: I was thinking maybe you call BuildIndexValueDescription twice and make the error message say something like output of first call conflicts with output of second call. Do you really think that's a better error message, or are you just trying to re-use similar code? Let's start from how the error message should read, and then see if we can re-use some code to make it look that way. It's one of the most visible aspects of the feature, and it needs to be reasonably concise and understandable in the simple case, but contain all of the necessary information. I think it's better to avoid the = when describing the conflict. I tend to read it as equals even though it's just punctuation in this case, so it would be distracting. I could change it to a colon, I suppose. I disagree wholeheartedly. :-) My ideal error message is something like: DETAIL: (a, b, c)=(1, 2, 3) conflicts with (a, b, c)=(4, 5, 6) In particular, I think it's very important that we only emit the columns which are part of the operator exclusion constraints, and NOT all the columns of the tuple. The entire tuple could be very large - one of the columns not involved in the constraint could be a 4K block of text, for example, and spitting that out only obscures the real source of the problem. You could argue that one of the columns involved in the constraint could be a 4K block text, too, but in practice I think the constraint columns are likely to be short nine times out of ten. The equals sign is equating the column names to the column values, not the values to each other, so I don't see that as confusing. create table test (a int4[], exclude using gist (a with =)); ERROR: operator does not exist: integer[] = integer[] Thanks, fixed. I am now using compatible_oper_opid(), which will find any operators that don't require binary-incompatible coercion of the operands. Do you think there's any reason to support binary-incompatible coercion of the operands? I can't think of a single use case, and if you really need to, you can coerce the types explicitly in the expression. My operator-class-fu is insufficient to render judgment on this point. I think the thing to do is look at a bunch of non-built-in opclasses and check for POLA violations. ...Robert -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] operator exclusion constraints
On Wed, 2009-11-25 at 09:02 -0500, Robert Haas wrote: I disagree wholeheartedly. :-) My ideal error message is something like: DETAIL: (a, b, c)=(1, 2, 3) conflicts with (a, b, c)=(4, 5, 6) In particular, I think it's very important that we only emit the columns which are part of the operator exclusion constraints, and NOT all the columns of the tuple. The entire tuple could be very large - one of the columns not involved in the constraint could be a 4K block of text, for example, and spitting that out only obscures the real source of the problem. You could argue that one of the columns involved in the constraint could be a 4K block text, too, but in practice I think the constraint columns are likely to be short nine times out of ten. The equals sign is equating the column names to the column values, not the values to each other, so I don't see that as confusing. Ok, fair enough. But how do you feel about: (a: 1, b: 2, c: 3) as a tuple representation instead? I think it's closer to the way people expect a tuple to be represented. I can change it in one place so that the unique violations are reported that way, as well (as long as there are no objections). It still doesn't contain the operators, but they can look at the constraint definition for that. My operator-class-fu is insufficient to render judgment on this point. I think the thing to do is look at a bunch of non-built-in opclasses and check for POLA violations. Ok, I'll consider this more. Regards, Jeff Davis -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] operator exclusion constraints
Jeff Davis pg...@j-davis.com writes: On Wed, 2009-11-25 at 09:02 -0500, Robert Haas wrote: I disagree wholeheartedly. :-) My ideal error message is something like: DETAIL: (a, b, c)=(1, 2, 3) conflicts with (a, b, c)=(4, 5, 6) Ok, fair enough. But how do you feel about: (a: 1, b: 2, c: 3) as a tuple representation instead? This seems like change for the sake of change. We've been reporting this type of error (in the context of foreign keys) using the first syntax for a very long time. I don't feel a need to rearrange it. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] operator exclusion constraints
On Wed, 2009-11-25 at 15:59 -0800, Jeff Davis wrote: My operator-class-fu is insufficient to render judgment on this point. I think the thing to do is look at a bunch of non-built-in opclasses and check for POLA violations. Ok, I'll consider this more. In cases where the operator class type is different from the search operator's operands' types, one of the following is usually true: * There is a binary cast from the opclass type (same as expression type) to the search operator's operands' types, or it is otherwise compatible (e.g. ANYARRAY). * There is a candidate function that's a better match (e.g. there may be an =(int8, int8) on int2_ops, but there's also =(int2, int2)). * The left and right operand types are different, and therefore do not work with operator exclusion constraints anyway (e.g. full text search @@). After installing all contrib modules, plus my period module, and postgis, there appear to be no instances that violate these assumptions (according to a join query and some manual testing). In theory there could be, however. It's kind of ugly to make it work, and a challenge to test it, so for now I'll only accept operators returned by compatible_oper(). If you disagree, I can make it work. Regards, Jeff Davis -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] operator exclusion constraints
On Fri, Nov 20, 2009 at 01:36:59PM +0900, Josh Berkus wrote: RObert, I guess I'm going to have to vote -1 on this proposal. I code see inventing a pgsql-specific SQLSTATE value for exclusion constraints, since they will be a pgsql-specific extension, but reusing the unique key violation value seems misleading. I admit it may help in a limited number of cases, but IMHO it's not worth the confusion. I'd rather have a new one than just using contstraint violation which is terribly non-specific, and generally makes the application developer think that a value is too large. What, if anything, does the standard have to say about violations of ASSERTIONs? I know these aren't ASSERTIONs, but they much more closely resemble them than they do UNIQUE constraints. For example, if the operator is instead of =, a violation is actually the opposite of a UNIQUE violation. Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] operator exclusion constraints
On sön, 2009-11-22 at 16:03 -0800, David Fetter wrote: What, if anything, does the standard have to say about violations of ASSERTIONs? I know these aren't ASSERTIONs, but they much more closely resemble them than they do UNIQUE constraints. An assertion is by definition a constraint that is a schema component independent of a table. Which an exclusion constraint may or may not be, but it's an independent issue. (To clarify: It currently can't be, because assertions are not implemented, but when they are, it could be.) For the same reason, assertions don't have separate error codes, because they are just constraints after all. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] operator exclusion constraints
On Wed, Nov 18, 2009 at 9:00 AM, Jeff Davis pg...@j-davis.com wrote: I'm in Tokyo right now, so please excuse my abbreviated reply. On Tue, 2009-11-17 at 23:13 -0500, Robert Haas wrote: Forgive me if this is discussed before, but why does this store the strategy numbers of the relevant operators instead of the operators themselves? At constraint definition time, I need to make sure that the strategy numbers can be identified anyway, so it wouldn't save any work in ATAddOperatorExclusionConstraint. At the time it seemed slightly more direct to use strategy numbers in index_check_constraint, but it's probably about the same. It sets off a red flag for me any time I see code that asks for A from the user and then actually stores B under the hood, for fear that the relationship that A and B might change. However... It seems like this could lead to surprising behavior if the user modifies the definition of the operator class. Right now, operator classes can't be modified in any meaningful way. Am I missing something? ...poking at it, I have to agree that at least as things stand right now, I can't find a way to break it. Not sure if it's future-proof. I'm wondering if we can't use the existing BuildIndexValueDescription() rather than the new function tuple_as_string(). I realize there are two tuples, but maybe it makes sense to just call it twice? Are you suggesting I change the error output, or reorganize the code to try to reuse BuildIndexValueDescription, or both? I was thinking maybe you call BuildIndexValueDescription twice and make the error message say something like output of first call conflicts with output of second call. One other thing I noticed tonight while poking at this. If I install contrib/citext, I can do this: create table test (a citext, exclude using hash (a with =)); But if I install contrib/intarray, I can't do this: create table test (a int4[], exclude using gist (a with =)); ERROR: operator does not exist: integer[] = integer[] Not sure if I'm doing something wrong, or if this is a limitation of the design, or if it's a bug, but it seems strange. I'm guessing it's because intarray uses the anyarray operator rather than a dedicated operator for int[], but it seems like that ought to work. ...Robert -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] operator exclusion constraints
On Wed, Nov 18, 2009 at 9:21 AM, Josh Berkus j...@agliodbs.com wrote: All, FWIW, I'm doing a redesign of a client's production web application right now. I was able, by combining OEC and the Period type from pgfoundry, to make a set of constraints for declaratively asserting in a sports database: That the same player couldn't belong to two different teams at the same time; That the same player couldn't belong to the same team in two different positions with overlapping time periods. This worked as spec'd, and would be extremely useful for this real-world app if it was ready to use in production now. However, I do have an issue with the SQLSTATE returned from the OEC violation. Currently it returns constraint violation, which, from the perspective of an application developer, is not useful. OECs are, in application terms, materially identical to UNIQUE constraints and serve the same purpose. As such, I'd far rather see OECs return unique key violation instead, as any existing application error-trapping code would handle the violation more intelligently if it did. I guess I'm going to have to vote -1 on this proposal. I code see inventing a pgsql-specific SQLSTATE value for exclusion constraints, since they will be a pgsql-specific extension, but reusing the unique key violation value seems misleading. I admit it may help in a limited number of cases, but IMHO it's not worth the confusion. That's just my $0.02, though. ...Robert -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] operator exclusion constraints
RObert, I guess I'm going to have to vote -1 on this proposal. I code see inventing a pgsql-specific SQLSTATE value for exclusion constraints, since they will be a pgsql-specific extension, but reusing the unique key violation value seems misleading. I admit it may help in a limited number of cases, but IMHO it's not worth the confusion. I'd rather have a new one than just using contstraint violation which is terribly non-specific, and generally makes the application developer think that a value is too large. --Josh BErkus -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] operator exclusion constraints
I'm in Tokyo right now, so please excuse my abbreviated reply. On Tue, 2009-11-17 at 23:13 -0500, Robert Haas wrote: Forgive me if this is discussed before, but why does this store the strategy numbers of the relevant operators instead of the operators themselves? At constraint definition time, I need to make sure that the strategy numbers can be identified anyway, so it wouldn't save any work in ATAddOperatorExclusionConstraint. At the time it seemed slightly more direct to use strategy numbers in index_check_constraint, but it's probably about the same. It seems like this could lead to surprising behavior if the user modifies the definition of the operator class. Right now, operator classes can't be modified in any meaningful way. Am I missing something? I'm wondering if we can't use the existing BuildIndexValueDescription() rather than the new function tuple_as_string(). I realize there are two tuples, but maybe it makes sense to just call it twice? Are you suggesting I change the error output, or reorganize the code to try to reuse BuildIndexValueDescription, or both? Regards, Jeff Davis -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] operator exclusion constraints
Robert Haas robertmh...@gmail.com writes: Forgive me if this is discussed before, but why does this store the strategy numbers of the relevant operators instead of the operators themselves? It seems like this could lead to surprising behavior if the user modifies the definition of the operator class. Wild guess: http://www.postgresql.org/docs/8.4/static/xindex.html 34.14.2. Index Method Strategies The operators associated with an operator class are identified by strategy numbers, which serve to identify the semantics of each operator within the context of its operator class. For example, B-trees impose a strict ordering on keys, lesser to greater, and so operators like less than and greater than or equal to are interesting with respect to a B-tree. Because PostgreSQL allows the user to define operators, PostgreSQL cannot look at the name of an operator (e.g., or =) and tell what kind of comparison it is. Instead, the index method defines a set of strategies, which can be thought of as generalized operators. Each operator class specifies which actual operator corresponds to each strategy for a particular data type and interpretation of the index semantics. Regards, -- dim -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] operator exclusion constraints
All, FWIW, I'm doing a redesign of a client's production web application right now. I was able, by combining OEC and the Period type from pgfoundry, to make a set of constraints for declaratively asserting in a sports database: That the same player couldn't belong to two different teams at the same time; That the same player couldn't belong to the same team in two different positions with overlapping time periods. This worked as spec'd, and would be extremely useful for this real-world app if it was ready to use in production now. However, I do have an issue with the SQLSTATE returned from the OEC violation. Currently it returns constraint violation, which, from the perspective of an application developer, is not useful. OECs are, in application terms, materially identical to UNIQUE constraints and serve the same purpose. As such, I'd far rather see OECs return unique key violation instead, as any existing application error-trapping code would handle the violation more intelligently if it did. --Josh Berkus -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] operator exclusion constraints
On Sat, Nov 14, 2009 at 2:27 PM, Jeff Davis pg...@j-davis.com wrote:
New patches attached.
Forgive me if this is discussed before, but why does this store the
strategy numbers of the relevant operators instead of the operators
themselves? It seems like this could lead to surprising behavior if
the user modifies the definition of the operator class.
I'm wondering if we can't use the existing
BuildIndexValueDescription() rather than the new function
tuple_as_string(). I realize there are two tuples, but maybe it makes
sense to just call it twice?
I'm attaching a revised doc patch for your consideration.
...Robert
diff --git a/doc/src/sgml/ref/create_table.sgml b/doc/src/sgml/ref/create_table.sgml
index 082dfe4..a73c015 100644
--- a/doc/src/sgml/ref/create_table.sgml
+++ b/doc/src/sgml/ref/create_table.sgml
@@ -51,10 +51,13 @@ CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } ] TABLE replaceable class=PAR
PRIMARY KEY ( replaceable class=PARAMETERcolumn_name/replaceable [, ... ] ) replaceable class=PARAMETERindex_parameters/replaceable |
CHECK ( replaceable class=PARAMETERexpression/replaceable ) |
FOREIGN KEY ( replaceable class=PARAMETERcolumn_name/replaceable [, ... ] ) REFERENCES replaceable class=PARAMETERreftable/replaceable [ ( replaceable class=PARAMETERrefcolumn/replaceable [, ... ] ) ]
-[ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ] [ ON DELETE replaceable class=parameteraction/replaceable ] [ ON UPDATE replaceable class=parameteraction/replaceable ] }
+[ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ] [ ON DELETE replaceable class=parameteraction/replaceable ] [ ON UPDATE replaceable class=parameteraction/replaceable ] |
+ EXCLUDE [ USING replaceable class=parameterindex_method/replaceable ]
+( { replaceable class=parametercolumn/replaceable | replaceable class=parametercolumn/replaceable | ( replaceable class=parameterexpression/replaceable ) } [ replaceable class=parameteropclass/replaceable ] [ ASC | DESC ] [ NULLS { FIRST | LAST } ] WITH replaceable class=parameteroperator/replaceable [, ... ] )
+replaceable class=parameterindex_parameters/replaceable [ WHERE ( replaceable class=parameterpredicate/replaceable ) ] }
[ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]
-phrasereplaceable class=PARAMETERindex_parameters/replaceable in literalUNIQUE/literal and literalPRIMARY KEY/literal constraints are:/phrase
+phrasereplaceable class=PARAMETERindex_parameters/replaceable in literalUNIQUE/literal, literalPRIMARY KEY/literal, and literalEXCLUDE/literal constraints are:/phrase
[ WITH ( replaceable class=PARAMETERstorage_parameter/replaceable [= replaceable class=PARAMETERvalue/replaceable] [, ... ] ) ]
[ USING INDEX TABLESPACE replaceable class=PARAMETERtablespace/replaceable ]
@@ -547,6 +550,43 @@ CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } ] TABLE replaceable class=PAR
/varlistentry
varlistentry
+termliteralEXCLUDE [ USING replaceable class=parameterindex_method/replaceable ]
+ ( {replaceable class=parametercolumn/replaceable | replaceable class=parametercolumn/replaceable | (replaceable class=parameterexpression/replaceable ) } [ replaceable class=parameteropclass/replaceable ] [ ASC | DESC ] [ NULLS { FIRST | LAST } ] WITH replaceable class=parameteroperator/replaceable [, ... ] )
+ replaceable class=parameterindex_parameters/replaceable [ WHERE ( replaceable class=parameterpredicate/replaceable ) ] }/literal/term
+listitem
+ para
+ The literalEXCLUDE/ clause specifies an operator exclusion
+ constraint. An operator exclusion constraint guarantees that if any two
+ tuples are compared on the specified columns or expressions using the
+ specified operators, at least one such comparison will return false.
+ If all of the specified operators test for equality, it is equivalent
+ to a UNIQUE constraint, although an ordinary unique constraint will
+ normally be faster. However, operator exclusion constraints can use
+ index methods other than btree, and can specify more general constraints.
+ For instance, you can specify the constraint that no two tuples in the
+ table contain overlapping circles
+ (see xref linkend=datatype-geometric) by using the
+ literal/literal operator.
+ /para
+
+ para
+ Operator exclusion constraints are implemented internally using
+ an index, so the specified operators must be associated with an
+ appropriate operator class for the given access method, and the
+ access method must support amgettuple (see xref linkend=indexam
+ for details). The operators are also required to be their own
+ commutators (see xref linkend=sql-createoperator).
+ /para
+
+ para
+ The replaceable class=parameterpredicate/replaceable
+ allows you to specify a constraint on a subset of the table,
+ internally using a partial index.
+ /para
+/listitem
+ /varlistentry
+
+
Re: [HACKERS] operator exclusion constraints
On Nov 13, 2009, at 8:39 PM, Robert Haas wrote: alter table foo add constraint bar exclude (a check with =, b check with =); I've been meaning to comment on this syntax one more time; apologies for the bike-shedding. But I'm wondering if the CHECK is strictly necessary there, since the WITH seems adequate, and there was some discussion before about the CHECK keyword possibly causing confusion with check constraints. Best, David -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] operator exclusion constraints
David E. Wheeler da...@kineticode.com writes: On Nov 13, 2009, at 8:39 PM, Robert Haas wrote: alter table foo add constraint bar exclude (a check with =, b check with =); I've been meaning to comment on this syntax one more time; apologies for the bike-shedding. But I'm wondering if the CHECK is strictly necessary there, since the WITH seems adequate, and there was some discussion before about the CHECK keyword possibly causing confusion with check constraints. I had been manfully restraining myself from re-opening this discussion, but yeah I was thinking the same thing. The original objection to using just WITH was that it wasn't very clear what you were doing with the operator; but that was back when we had a different initial keyword for the construct. EXCLUDE ... WITH ... seems to match up pretty naturally. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] operator exclusion constraints
On Nov 14, 2009, at 8:55 AM, Tom Lane wrote: I've been meaning to comment on this syntax one more time; apologies for the bike-shedding. But I'm wondering if the CHECK is strictly necessary there, since the WITH seems adequate, and there was some discussion before about the CHECK keyword possibly causing confusion with check constraints. I had been manfully restraining myself from re-opening this discussion, but yeah I was thinking the same thing. The original objection to using just WITH was that it wasn't very clear what you were doing with the operator; but that was back when we had a different initial keyword for the construct. EXCLUDE ... WITH ... seems to match up pretty naturally. You're more man than I, Tom, but yeah, with EXCLUDE, WITH works well on its own, methinks. Best, David -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] operator exclusion constraints
On Sat, Nov 14, 2009 at 12:11 PM, David E. Wheeler da...@kineticode.com wrote: On Nov 14, 2009, at 8:55 AM, Tom Lane wrote: I've been meaning to comment on this syntax one more time; apologies for the bike-shedding. But I'm wondering if the CHECK is strictly necessary there, since the WITH seems adequate, and there was some discussion before about the CHECK keyword possibly causing confusion with check constraints. I had been manfully restraining myself from re-opening this discussion, but yeah I was thinking the same thing. The original objection to using just WITH was that it wasn't very clear what you were doing with the operator; but that was back when we had a different initial keyword for the construct. EXCLUDE ... WITH ... seems to match up pretty naturally. You're more man than I, Tom, but yeah, with EXCLUDE, WITH works well on its own, methinks. I haven't thought about this too deeply, but could we allow the with = part to be optional? And would it be a good idea? Seems like you would commonly have one or more keys that exclude on equality and then the last one would use an overlap-type operator. ...Robert -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] operator exclusion constraints
Robert Haas robertmh...@gmail.com writes: I haven't thought about this too deeply, but could we allow the with = part to be optional? And would it be a good idea? I don't think so. We generally do not believe in defaulting operators based on name. If there were a way to select the standard exclusion operator based on opclass membership it might make sense, but almost by definition this facility is going to be working with unusual opclasses that might not even have an equality slot. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] operator exclusion constraints
On Fri, 2009-11-13 at 23:39 -0500, Robert Haas wrote: [ reviewing ] Thank you for the comments so far. In index_create(), the elog() where relopxconstraints 0 should just complain about the value being negative, I think, rather than listing the value. If you just say the value is -3, it doesn't give the user a clue why that's bad. Hopefully the user never sees that message -- it's almost an Assert. PostgreSQL uses elog(ERROR,...) in many places that should be unreachable, but might happen due to bugs in distant places or corruption. I'm not sure the exact convention there, but I figure that some details are appropriate. I tried to make all user-visible errors into ereport()s. In ATAddOperatorExclusionConstraint(), the message method %s does not support gettuple seems a bit user-unfriendly. Can we explain the problem by referring to the functionality of getttuple(), rather than the name of it? How about operator exclusion constraints don't support method X? Then perhaps have a detail-level message to explain that the access method needs to support the gettuple interface. Trying to describe what gettuple does doesn't help a humble user much. All they care about is can't use gin. I don't really like this message, for a number of reasons. alter table foo add constraint bar exclude (a check with =, b check with =); ERROR: operator exclusion constraint violation detected: foo_a_exclusion DETAIL: Tuple (1, 1, 2) conflicts with existing tuple (1, 1, 3). The corresponding error for a UNIQUE index is: could not create unique index bar, which I like better. Only the relevant columns from the tuples are dumped, and the tuple is not surrounded by double quotes; any reason not to parallel that here? By relevant columns I assume you mean the entire index tuple. That means we need to have column names represented somewhere, because we don't want the user to have to match up ordinal index columns. Also, with exclusion constraints, both values are always relevant, not just the one being inserted. What if the user just wants to adjust their request slightly to avoid an overlap -- how do they know how far to go? I know this could be accomplished with extra queries, as well, but that doesn't always work for someone looking through the logs after the fact, when the original values may be gone. So, the kind of error message you're suggesting starts to get awkward: (a: 1 = 1, b: 1 = 1) or something? And then with more complex type output functions, and expression indexes, it starts to look very complex. What do you think is the cleanest approach? Also, the message is all lower-case. I know the error conventions are documented somewhere, but I completely forgot where. Can you please point me to the right place? I thought most error messages were supposed to be lower case, and detail messages were supposed to read like sentences. Similarly, for an insert/update situation, it seems that a message like key value violates exclusion constraint \%s\ would be better than the existing message. I can certainly simplify it, but I was trying to match the usefulness of UNIQUE constraint error messages. As a quick performance test, I inserted a million 3-integer tuples into a 3-column table with a unique constraint or an operator exclusion constraint over all three columns. The former took ~ 15 s, the latter ~ 21.5 s. That seems acceptable. Great news. I had similar results, though they depend on the conflict percentage as well (I assume you had zero conflicts). Regards, Jeff Davis -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] operator exclusion constraints
2009/11/15 Jeff Davis pg...@j-davis.com: I know the error conventions are documented somewhere, but I completely forgot where. Can you please point me to the right place? I thought most error messages were supposed to be lower case, and detail messages were supposed to read like sentences. http://www.postgresql.org/docs/current/static/error-style-guide.html And you are correct. Cheers, BJ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] operator exclusion constraints
On Sat, Nov 14, 2009 at 6:00 PM, Jeff Davis pg...@j-davis.com wrote: Hopefully the user never sees that message -- it's almost an Assert. PostgreSQL uses elog(ERROR,...) in many places that should be unreachable, but might happen due to bugs in distant places or corruption. I'm not sure the exact convention there, but I figure that some details are appropriate. Yeah, I think that's right. I think part of the rationale is that if the admin mucks around with catalog tables or does some DDL with inconsistent definitions (like an operator class that isn't internally consistent for example) then we don't treat those errors as user-visible errors that need to be translated but they shouldn't cause a database crash either. If it's possible for the case to arrive through users doing things through entirely supported means then they might need to be real ereports(). But I guess there are grey areas. -- greg -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] operator exclusion constraints
On Sat, Nov 14, 2009 at 1:58 PM, Greg Stark gsst...@mit.edu wrote: On Sat, Nov 14, 2009 at 6:00 PM, Jeff Davis pg...@j-davis.com wrote: Hopefully the user never sees that message -- it's almost an Assert. PostgreSQL uses elog(ERROR,...) in many places that should be unreachable, but might happen due to bugs in distant places or corruption. I'm not sure the exact convention there, but I figure that some details are appropriate. Yeah, I think that's right. I think part of the rationale is that if the admin mucks around with catalog tables or does some DDL with inconsistent definitions (like an operator class that isn't internally consistent for example) then we don't treat those errors as user-visible errors that need to be translated but they shouldn't cause a database crash either. If it's possible for the case to arrive through users doing things through entirely supported means then they might need to be real ereports(). But I guess there are grey areas. I guess my point wasn't that the message was likely to be exercised, but rather that it isn't obvious that it's describing an error condition at all. If you get this message: relation whatever has relopxconstraints = -3 ...you can't even tell that it's an error message unless it happens to be preceded by ERROR: . If you get something like: relation whatever has invalid relopxconstraints value (-3) ...it's much more clear that this is not just informational. ...Robert -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] operator exclusion constraints
On Sat, 2009-11-14 at 09:11 -0800, David E. Wheeler wrote: On Nov 14, 2009, at 8:55 AM, Tom Lane wrote: I had been manfully restraining myself from re-opening this discussion, but yeah I was thinking the same thing. The original objection to using just WITH was that it wasn't very clear what you were doing with the operator; but that was back when we had a different initial keyword for the construct. EXCLUDE ... WITH ... seems to match up pretty naturally. You're more man than I, Tom, but yeah, with EXCLUDE, WITH works well on its own, methinks. Changed in new patch here: http://archives.postgresql.org/message-id/1258226849.708.97.ca...@jdavis Regards, Jeff Davis -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] operator exclusion constraints
On Sat, 2009-11-14 at 14:36 -0500, Robert Haas wrote: I guess my point wasn't that the message was likely to be exercised, but rather that it isn't obvious that it's describing an error condition at all. If you get this message: relation whatever has relopxconstraints = -3 I pretty much copied similar code for relchecks, see pg_constraint.c:570. If you have a suggestion, I'll make the change. It doesn't sound too urgent though, to me. Regards, Jeff Davis -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] operator exclusion constraints
On Sat, Nov 14, 2009 at 2:39 PM, Jeff Davis pg...@j-davis.com wrote: If you have a suggestion, I'll make the change. It doesn't sound too urgent though, to me. Yeah, probably not. ...Robert -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] operator exclusion constraints
On Mon, 2009-11-09 at 09:12 -0800, David E. Wheeler wrote: On Nov 8, 2009, at 7:43 PM, Jeff Davis wrote: Either of those names are fine with me, too. The current name is a somewhat shortened version of the name operator-based exclusion constraints, so we can also just use that name. Or, just exclusion constraints. (exclusion constraints)++ Ok, I guess this is another issue that requires consensus. Note: this is purely for documentation, release notes, and user-visible error messages. This does not have any impact on the syntax, I think we've got a strong consensus on that already and I would prefer not to break that discussion open again. 1. Operator Exclusion Constraints (current) 2. Generic/Generalized/General Exclusion Constraints 3. Exclusion Constraints (has the potential to cause confusion with constraint_exclusion) Regards, Jeff Davis -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] operator exclusion constraints
On Sun, Nov 8, 2009 at 4:41 PM, Jeff Davis pg...@j-davis.com wrote: On Sat, 2009-11-07 at 10:56 -0800, Jeff Davis wrote: EXCLUDE probably flows most nicely with the optional USING clause or without. My only complaint was that it's a transitive verb, so it seems to impart more meaning than it actually can. I doubt anyone would actually be more confused in practice, though. If a couple of people agree, I'll change it to EXCLUDE. It looks like EXCLUDE is the winner. Updated patch attached. The feature is still called operator exclusion constraints, and the docs still make reference to that name, but the syntax specification has been updated. [ reviewing ] First thoughts: I think the create_table documentation gets into a little too much gorey detail. I'm willing to take a pass at improving it, if you'd like, but generally I think it should avoid discussion of implementation details. For example, saying that it's not as fast as a UNIQUE constraint is good; the fact that an extra index lookup is involved is probably overkill. Incidentally, the wording in the first paragraph fails to take into account the possibility that there are multiple operators. In index_create(), the elog() where relopxconstraints 0 should just complain about the value being negative, I think, rather than listing the value. If you just say the value is -3, it doesn't give the user a clue why that's bad. There is a spurious diff hunk for reindex_relation(). In ATRewriteTable() you reindent a bunch of variable declarations; pg_indent will undo this, so you should nix this part. In ATAddOperatorExclusionConstraint(), the message method %s does not support gettuple seems a bit user-unfriendly. Can we explain the problem by referring to the functionality of getttuple(), rather than the name of it? I don't really like this message, for a number of reasons. alter table foo add constraint bar exclude (a check with =, b check with =); ERROR: operator exclusion constraint violation detected: foo_a_exclusion DETAIL: Tuple (1, 1, 2) conflicts with existing tuple (1, 1, 3). The corresponding error for a UNIQUE index is: could not create unique index bar, which I like better. Only the relevant columns from the tuples are dumped, and the tuple is not surrounded by double quotes; any reason not to parallel that here? Also, the message is all lower-case. Similarly, for an insert/update situation, it seems that a message like key value violates exclusion constraint \%s\ would be better than the existing message. alter table X add constraint Y exclude (...) seems to ignore the value of Y and create both the constraint and the index with a name of its own choosing. As a quick performance test, I inserted a million 3-integer tuples into a 3-column table with a unique constraint or an operator exclusion constraint over all three columns. The former took ~ 15 s, the latter ~ 21.5 s. That seems acceptable. I think preprocessOpExConstraints should be called transformOpxConstraints - opx rather than opex because that's what you most frequently use elsewhere in the patch, and transform rather than preprocess for consistency with other, similar functions. In ruleutils.c, the prototype for pg_get_opxdef_worker() has a small whitespace inconsistency relative to the surrounding declarations. I haven't really grokked the substantive things that this patch is doing yet - these are just preliminary comments based on a quick read-through. I'll write more after I have a chance to look at it in more detail. ...Robert -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] operator exclusion constraints
On Nov 8, 2009, at 7:43 PM, Jeff Davis wrote: Either of those names are fine with me, too. The current name is a somewhat shortened version of the name operator-based exclusion constraints, so we can also just use that name. Or, just exclusion constraints. (exclusion constraints)++ David -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] operator exclusion constraints
On Mon, Nov 9, 2009 at 5:12 PM, David E. Wheeler da...@kineticode.com wrote: On Nov 8, 2009, at 7:43 PM, Jeff Davis wrote: Either of those names are fine with me, too. The current name is a somewhat shortened version of the name operator-based exclusion constraints, so we can also just use that name. Or, just exclusion constraints. (exclusion constraints)++ Out of curiosity, is this feature at all similar to SQL assertions? What would we be missing to turn this into them? -- greg -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] operator exclusion constraints
Greg Stark escribió: Out of curiosity, is this feature at all similar to SQL assertions? What would we be missing to turn this into them? I see no relationship to assertions. Those are not tied to any particular table, and are defined with any random expression you care to think of. -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] operator exclusion constraints
Tom Lane escribió: Andrew Dunstan and...@dunslane.net writes: This is a pretty good short explanation of how to deal with shift/reduce problems in bison. With your permission I'm going to copy it to the Wiki If you like, but I think the part about figuring out which production is the problem seemed to be at least as important for Jeff ... I agree that it would be worth an entry here http://wiki.postgresql.org/wiki/Developer_FAQ -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] operator exclusion constraints
On Mon, 2009-11-09 at 18:03 +, Greg Stark wrote: Out of curiosity, is this feature at all similar to SQL assertions? What would we be missing to turn this into them? I addressed that here: http://archives.postgresql.org/pgsql-hackers/2009-11/msg00049.php The exclusion constraint mechanism can enforce a subset of the constraints that ASSERT can express; although the same goes for all other constraints, because ASSERT is very general. The exclusion constraint mechanism requires finding the physical tuples that cause a conflict, so that we know when to wait and on which transaction to wait. Otherwise, we have to wait on all transactions; i.e. serialize. The problem with ASSERT is that it expresses a constraint based on a query, which can return arbitrary logical records after an arbitrary amount of manipulation. So there's no way to work backwards. If we try, we'll end up either: (a) supporting only a tiny subset, and throwing bizarre errors that users don't understand when they try to work outside the template; or (b) deciding to serialize when we can't do better, and again, users will be confused about the performance and locking characteristics. Regards, Jeff Davis -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] operator exclusion constraints
On Sun, 2009-11-08 at 13:41 -0800, Jeff Davis wrote: On Sat, 2009-11-07 at 10:56 -0800, Jeff Davis wrote: EXCLUDE probably flows most nicely with the optional USING clause or without. My only complaint was that it's a transitive verb, so it seems to impart more meaning than it actually can. I doubt anyone would actually be more confused in practice, though. If a couple of people agree, I'll change it to EXCLUDE. It looks like EXCLUDE is the winner. Updated patch attached. The feature is still called operator exclusion constraints, and the docs still make reference to that name, but the syntax specification has been updated. Don't think that name is very useful either... sounds like you want to exclude operators, which is why I got lost in the first place. I'd call them generic exclusion constraints or user-defined exclusion constraints. Sorry for this. -- Simon Riggs www.2ndQuadrant.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] operator exclusion constraints
On Sun, 2009-11-08 at 22:03 +, Simon Riggs wrote: Don't think that name is very useful either... sounds like you want to exclude operators, which is why I got lost in the first place. I'd call them generic exclusion constraints or user-defined exclusion constraints. Sorry for this. Either of those names are fine with me, too. The current name is a somewhat shortened version of the name operator-based exclusion constraints, so we can also just use that name. Or, just exclusion constraints. I'll leave the current patch as-is for now, and wait for some reviewer comments. This is purely a documentation issue, so there are bound to be a few of these things that I can clarify at once. Regards, Jeff Davis -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] operator exclusion constraints
On Fri, 2009-11-06 at 21:23 -0500, Tom Lane wrote: Or maybe forget about it and go to EXCLUDE or EXCLUDING? I left it as EXCLUSION for now. EXCLUDING USING ... and EXCLUSIVE USING ... both sound a little awkward to me. Either could be improved by moving the USING clause around, but that just creates more grammar headaches. EXCLUDE probably flows most nicely with the optional USING clause or without. My only complaint was that it's a transitive verb, so it seems to impart more meaning than it actually can. I doubt anyone would actually be more confused in practice, though. If a couple of people agree, I'll change it to EXCLUDE. Regards, Jeff Davis -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] operator exclusion constraints
Jeff Davis pg...@j-davis.com writes: EXCLUDE probably flows most nicely with the optional USING clause or without. My only complaint was that it's a transitive verb, so it seems to impart more meaning than it actually can. I doubt anyone would actually be more confused in practice, though. If a couple of people agree, I'll change it to EXCLUDE. EXCLUDE sounds good to me. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] operator exclusion constraints
On Sat, Nov 7, 2009 at 1:56 PM, Jeff Davis pg...@j-davis.com wrote: On Fri, 2009-11-06 at 21:23 -0500, Tom Lane wrote: Or maybe forget about it and go to EXCLUDE or EXCLUDING? I left it as EXCLUSION for now. EXCLUDING USING ... and EXCLUSIVE USING ... both sound a little awkward to me. Either could be improved by moving the USING clause around, but that just creates more grammar headaches. EXCLUDE probably flows most nicely with the optional USING clause or without. My only complaint was that it's a transitive verb, so it seems to impart more meaning than it actually can. I doubt anyone would actually be more confused in practice, though. If a couple of people agree, I'll change it to EXCLUDE. Personally, I think that this is all rather a matter of opinion, and of course bikeshedding. CHECK is a verb, which might suggest that EXCLUDE is the best choice, and it has a nice declarative sound to it. But the other example is FOREIGN KEY, which is not a verb at all, which seems to me to more closely parallel EXCLUSION or perhaps EXCLUDING. I think I like EXCLUSIVE the least of the four, but at the end of the day, I don't think we can really go far wrong. I also don't think there's anything wrong with EXCLUDING USING, nor anything more wrong EXCLUSIVE USING than there is with EXCLUSIVE alone. Nor do I think there's any problem with EXCLUDE being transitive because, of course, we're going to follow it with a description of what we want to exclude, which may be thought of as its direct object. Once again, I don't think we can go far wrong. Honestly, I'd probably be in favor of breaking the virtual tie in favor of whichever word is already a keyword, rather than trying to decide on (IMHO extremely tenuous) grammatical grounds. But I can't get worked up about that one way or the other either. ...Robert -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] operator exclusion constraints
On Nov 7, 2009, at 11:08 AM, Tom Lane wrote: EXCLUDE probably flows most nicely with the optional USING clause or without. My only complaint was that it's a transitive verb, so it seems to impart more meaning than it actually can. I doubt anyone would actually be more confused in practice, though. If a couple of people agree, I'll change it to EXCLUDE. EXCLUDE sounds good to me. +1 David -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] operator exclusion constraints
On Sat, 2009-11-07 at 14:11 -0500, Robert Haas wrote: Honestly, I'd probably be in favor of breaking the virtual tie in favor of whichever word is already a keyword The ones that are already keywords are EXCLUSIVE and EXCLUDING, which are also the least desirable, so that rule doesn't work as a tie-breaker. I think that EXCLUSION and EXCLUDE are the options still in the running here. Regards, Jeff Davis -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] operator exclusion constraints
Jeff Davis pg...@j-davis.com writes: On Sat, 2009-11-07 at 14:11 -0500, Robert Haas wrote: Honestly, I'd probably be in favor of breaking the virtual tie in favor of whichever word is already a keyword The ones that are already keywords are EXCLUSIVE and EXCLUDING, which are also the least desirable, so that rule doesn't work as a tie-breaker. I think it doesn't really matter now that we've succeeded in making the keyword unreserved. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] operator exclusion constraints
On Nov 5, 2009, at 11:09 AM, Jeff Davis wrote: I think EXCLUDING conflicts with the EXCLUDING in LIKE. Also, it becomes a little more difficult to place the access method clause, because EXCLUDING USING gist doesn't sound great. Well that's clearly a verb. So perhaps EXCLUDE USING gist (EXCLUDING USING gist is a little weirder). Best, David -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] operator exclusion constraints
On Thu, 2009-11-05 at 10:30 -0800, David E. Wheeler wrote: But that doesn't read as well to my eye as: EXCLUDE (...) BY ... I think EXCLUDE might be a little *too* specific. It sounds like whatever is on the right hand side will be excluded, but that's not really what happens. EXCLUSION is vague about what is doing the excluding and what is being excluded. I think that's good in this case, because the actual meaning can't easily be expressed with a couple keywords, so suggesting the behavior is about as close as we can get (unless someone comes up with a new idea). EXCLUDING (...) BY ... I think that's better, but still sounds a little wrong to me. Regards, Jeff Davis -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] operator exclusion constraints
On Thu, 2009-11-05 at 11:16 -0800, David E. Wheeler wrote: Well that's clearly a verb. So perhaps EXCLUDE USING gist (EXCLUDING USING gist is a little weirder). That's not bad. As I just said in my other email, I think the word EXCLUDE is a little bit too specific, but the other ideas out there aren't perfect, either. Regards, Jeff Davis -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] operator exclusion constraints
On Thu, 2009-11-05 at 09:56 -0500, Tom Lane wrote: Robert Haas robertmh...@gmail.com writes: Ooh, that's kind of neat. But I think you'd need EXCLUSIVE (a, b) BY (=, =), since it could equally well be EXCLUSIVE (a, b) BY (=, ). Yeah, we definitely want some parentheses delimiting the expression. EXCLUSIVE still feels like the wrong part-of-speech though. How about EXCLUDING (...) BY ... instead? I think EXCLUDING conflicts with the EXCLUDING in LIKE. Also, it becomes a little more difficult to place the access method clause, because EXCLUDING USING gist doesn't sound great. Regards, Jeff Davis -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] operator exclusion constraints
On Mon, 2009-11-02 at 18:28 +, Simon Riggs wrote: I like the NOT here because CHECK NOT = seems to convey pretty clearly what it is you are checking for. Because NOT is reserved and can't appear as a connective, I think that this approach might allow a non-reserved leading word, thus possibly the second variant would work without reserving CONSTRAIN. I have not tested whether bison agrees with me though ;-). In any case I think CHECK NOT = reads pretty well, and don't feel a strong urge to use some other word there. Peter, do any of these ideas work for you? It looks like this opens the door to using a word other than CHECK. CONSTRAIN NOT is a little awkward, is there another word that might work better? I'm not excited about using NOT, because I think it has a hint of a double-negative when combined with EXCLUSION. The original idea was to specify the way to find tuples mutually exclusive with the new tuple; and NOT makes that a little less clear, in my opinion. But I'm fine with it if that's what everyone else thinks is best. Regards, Jeff Davis -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] operator exclusion constraints
Jeff Davis pg...@j-davis.com writes: I'm not excited about using NOT, because I think it has a hint of a double-negative when combined with EXCLUSION. Well, the choice of EXCLUSION isn't set in stone either ... regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] operator exclusion constraints
2009/11/3 Tom Lane t...@sss.pgh.pa.us: Jeff Davis pg...@j-davis.com writes: I'm not excited about using NOT, because I think it has a hint of a double-negative when combined with EXCLUSION. Well, the choice of EXCLUSION isn't set in stone either ... Is this really a generalized uniqueness constraint, extended to support operators other than = ? Perhaps sticking with the word UNIQUE might be more suggestive of this: UNIQUE (room_number WITH = , during WITH ) or: UNIQUE (room_number , during USING ) - Dean -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] operator exclusion constraints
On Tue, Nov 3, 2009 at 5:05 PM, Jeff Davis pg...@j-davis.com wrote: We already have some reasonable agreement around EXCLUSION ... CHECK WITH. We should stick with the current syntax unless there's a good consensus around some other specific proposal. Yeah. I don't like the inflexibility of the current syntax, but that's mostly because I wish the feature itself could be made more general. It would be nice to be able to write constraints of the form: forall vars : expression For example, a uniqueness constraint on a column c is: forall x,y : x.c != y.c And a does-not overlap constraint might look like this: forall x,y : NOT (x.c y.c) Note that an ordinary check constraint is a special case of this where there is only one rowvar and it is implicit. HOWEVER, this is probably a lot more work than what you've already done, and what you've already done is really good, and we shouldn't hesitate to commit on the grounds that it won't cure diabetes and balance the budget. I don't think there is any really beautiful syntax for the feature as it stands, but considering how useful it is I am not inclined to stand on ceremony... ...Robert -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] operator exclusion constraints
On Tue, 2009-11-03 at 21:31 +, Dean Rasheed wrote: Is this really a generalized uniqueness constraint, extended to support operators other than = ? That has been discussed in the past: http://archives.postgresql.org/message-id/1253119552.24770.203.ca...@jdavis http://archives.postgresql.org/message-id/1253122946.24770.250.ca...@jdavis However, some constraints allowed by this feature are the *opposite* of unique: consider . Personally, I don't like to use the word UNIQUE to describe a constraint that may reject unique values or permit duplicates. We already have some reasonable agreement around EXCLUSION ... CHECK WITH. We should stick with the current syntax unless there's a good consensus around some other specific proposal. Regards, Jeff Davis -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] operator exclusion constraints
On Sun, 2009-11-01 at 22:42 +, Simon Riggs wrote: The current patch writes the syntax like this EXCLUSION USING gist (c CHECK WITH ) makes it look like a table constraint, yet it clearly refers to a single column. That looks very clumsy to read, to my eyes. I think the word CHECK should be avoided completely in this syntax, to avoid confusion with CHECK constraints. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] operator exclusion constraints
On Sun, 2009-11-01 at 18:07 -0500, Tom Lane wrote: Simon Riggs si...@2ndquadrant.com writes: The syntax be easier to read if it was stated as a comparison e.g. in the circle example CHECK ( NOT (NEW.c c)) USING GIST I don't think this is a good idea at all. NEW is a nonstandard Postgres-ism, and introducing it into this syntax doesn't seem very future-proof to me. What's more, the above is not in the least analogous to a regular CHECK constraint, because there's some implicit notion of c ranging over all other rows, which is not what is meant by the same column reference in a CHECK constraint. I agree that the proposed syntax is a bit awkward, but this isn't better. Agreed. Just looking for readable, future-proof syntax. -- Simon Riggs www.2ndQuadrant.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] operator exclusion constraints
On Mon, 2009-11-02 at 07:38 +, Simon Riggs wrote: It bothers me that we would have completely separate syntax for this feature as opposed to normal SQL. It also doesn't make it easy to interpret from the business statement to the implementation. Notice that the , above means AND. Yes, in that way, it's similar to a UNIQUE constraint, e.g. UNIQUE (a, b). The more columns you add, the more permissive the constraint. How would we use an OR conditional? Specify multiple constraints. How would we express the wish to use a partial index? EXCLUSION (...) WHERE (...) The perens are actually required around the predicate in this case, due to syntactic problems. How would I express a bidding rule: Only allow bids that are better than the highest bid so far EXCLUSION (item CHECK WITH =, bid_price CHECK WITH ) That would be a cool feature, unfortunately it won't work in the current form. This constraint is only enforced on index insert -- imagine what confusion would be caused when: UPDATE foo SET third_column = 7 ... If that's a HOT update, it wouldn't re-check the bid_price. If it turns into a cold update, it would reject the update because the bid_price is no longer the highest. Did I get the the right way around? The above problem essentially means we only allow commutative operators, which avoids this source of confusion. Interestingly, reflexive operators aren't required. So, if is searchable, you can have the opposite of unique: all values must be the same. That might be interesting for something like: EXCLUSION(room CHECK WITH =, during CHECK WITH , student_grade CHECK WITH ) To ensure that a shared room isn't shared between students of different grades. Not the most compelling use case, but I could imagine something along these lines being useful. Maybe a better example would involve sheep and wolves ;) How would I specify a tree that has only 2 down branches at any node, 'left' and 'right'? I'm not sure I understand this exactly. If the left or right is explcitly a part of the tuple, I think it can be done with unique. If not, and you're looking for a maximum of two tuples, you can see my ill-fated extension to this feature here: http://archives.postgresql.org/pgsql-hackers/2009-11/msg00016.php As Tom pointed out, most use cases would not have a constant limit throughout the table. If you do have such a use case, you can revive the proposal. Not sure that if we submitted this to SQL Standard committee that it would be accepted as is. There are implementation details bubbling up to the user-visible behavior, and I share your concern. The SQL committee would never care about these implementation details, and I wish we didn't have to, either. The machinism that I've employed searches (using a dirty snapshot) for all of the physical tuples that cause a logical conflict with the physical tuple currently being added. If found, it uses physical information from those tuples, like visibility information, to determine whether to wait, and on whom to wait. After waiting it may either proceed or abort. If we move closer to a nice, clean query to express the constraint, it gets very difficult to tell which physical tuple is responsible for the conflict. If we don't know what physical tuple is causing the conflict, we have to serialize all writes. Additionally, the more it looks like a query, the more we have to tell users follow this template -- which will just lead to confusion and disappointment for users who think we've implemented SQL ASSERT (which we haven't). Although the current syntax isn't great, it is declarative, and it does allow a variety of constraints. I certainly welcome ideas that will make a better trade-off here. At the end, I just want a feature that can implement temporal keys. Regards, Jeff Davis -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] operator exclusion constraints
On Mon, 2009-11-02 at 08:25 +, Peter Eisentraut wrote: I think the word CHECK should be avoided completely in this syntax, to avoid confusion with CHECK constraints. This is an easy change. I don't have a strong opinion, so the only thing I can think to do is ask for a vote. Do you have a specific alternative in mind? How about just WITH? Regards, Jeff Davis -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] operator exclusion constraints
Jeff Davis pg...@j-davis.com writes: On Mon, 2009-11-02 at 08:25 +, Peter Eisentraut wrote: I think the word CHECK should be avoided completely in this syntax, to avoid confusion with CHECK constraints. This is an easy change. I don't have a strong opinion, so the only thing I can think to do is ask for a vote. Do you have a specific alternative in mind? How about just WITH? I think we had that discussion already, and rejected using WITH by itself because it was so totally devoid of suggestion of what it was the system would do with the expression or operator. If we don't want to introduce a new reserved word it's difficult to find alternatives :-(. One thing that just came to mind is that we might be able to do something like EXCLUSION (expr CHECK NOT operator) or EXCLUSION (expr CONSTRAIN NOT operator) I like the NOT here because CHECK NOT = seems to convey pretty clearly what it is you are checking for. Because NOT is reserved and can't appear as a connective, I think that this approach might allow a non-reserved leading word, thus possibly the second variant would work without reserving CONSTRAIN. I have not tested whether bison agrees with me though ;-). In any case I think CHECK NOT = reads pretty well, and don't feel a strong urge to use some other word there. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] operator exclusion constraints
On Mon, 2009-11-02 at 13:12 -0500, Tom Lane wrote: Jeff Davis pg...@j-davis.com writes: On Mon, 2009-11-02 at 08:25 +, Peter Eisentraut wrote: I think the word CHECK should be avoided completely in this syntax, to avoid confusion with CHECK constraints. This is an easy change. I don't have a strong opinion, so the only thing I can think to do is ask for a vote. Do you have a specific alternative in mind? How about just WITH? I think we had that discussion already, and rejected using WITH by itself because it was so totally devoid of suggestion of what it was the system would do with the expression or operator. If we don't want to introduce a new reserved word it's difficult to find alternatives :-(. One thing that just came to mind is that we might be able to do something like EXCLUSION (expr CHECK NOT operator) or EXCLUSION (expr CONSTRAIN NOT operator) I like the NOT here because CHECK NOT = seems to convey pretty clearly what it is you are checking for. Because NOT is reserved and can't appear as a connective, I think that this approach might allow a non-reserved leading word, thus possibly the second variant would work without reserving CONSTRAIN. I have not tested whether bison agrees with me though ;-). In any case I think CHECK NOT = reads pretty well, and don't feel a strong urge to use some other word there. Yep, like the NOT. Other ideas EXCLUSION (expr NOT operator) CONSTRAINT (expr NOT operator ALL ROWS) -- Simon Riggs www.2ndQuadrant.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] operator exclusion constraints
On Sun, 2009-11-01 at 10:53 -0800, Jeff Davis wrote: New patch attached. After reading the docs in the patch I don't believe you're going to all this trouble to ensure two circles don't overlap. Can you give some better examples of what you're trying to achieve and why anyone else would care? (I'm busy, so are others). I can probably guess, but my feeling is I shouldn't have to. I feel like this might be a truly great feature, but I'm worried that either it isn't at all or it is and yet will be overlooked. Does this project link in with other planned developments in various plugins? The current patch writes the syntax like this EXCLUSION USING gist (c CHECK WITH ) makes it look like a table constraint, yet it clearly refers to a single column. That looks very clumsy to read, to my eyes. The syntax be easier to read if it was stated as a comparison e.g. in the circle example CHECK ( NOT (NEW.c c)) USING GIST where NEW is the incoming row. This is similar to the way I would write the constraint if I wanted to ensure the values in two columns did not match/overlap etc CHECK ( NOT (col1 col2)) and is also not such a radical departure from existing SQL Standard syntax. We only need the NOT when there isn't a clear negator defined, so in most cases I would hope to read something like this CHECK (NEW.pkey != pkey) USING btree which should be equivalent to the UNIQUE constraint I don't think its too late to change the syntax. -- Simon Riggs www.2ndQuadrant.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] operator exclusion constraints
After reading the docs in the patch I don't believe you're going to all this trouble to ensure two circles don't overlap. Can you give some better examples of what you're trying to achieve and why anyone else would care? (I'm busy, so are others). Non overlapping time intervals is one use case - think about room scheduling. I personally want to use it to ensure the consistency of genomic annotations. -Nathan -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] operator exclusion constraints
Simon Riggs si...@2ndquadrant.com writes: The syntax be easier to read if it was stated as a comparison e.g. in the circle example CHECK ( NOT (NEW.c c)) USING GIST I don't think this is a good idea at all. NEW is a nonstandard Postgres-ism, and introducing it into this syntax doesn't seem very future-proof to me. What's more, the above is not in the least analogous to a regular CHECK constraint, because there's some implicit notion of c ranging over all other rows, which is not what is meant by the same column reference in a CHECK constraint. I agree that the proposed syntax is a bit awkward, but this isn't better. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] operator exclusion constraints
On Sun, 2009-11-01 at 22:42 +, Simon Riggs wrote: After reading the docs in the patch I don't believe you're going to all this trouble to ensure two circles don't overlap. Can you give some better examples of what you're trying to achieve and why anyone else would care? (I'm busy, so are others). Non-overlapping periods of time. I couldn't document that, because the PERIOD type doesn't exist in core (yet). I can probably guess, but my feeling is I shouldn't have to. I feel like this might be a truly great feature, but I'm worried that either it isn't at all or it is and yet will be overlooked. Does this project link in with other planned developments in various plugins? Absolutely: http://archives.postgresql.org/pgsql-hackers/2009-10/msg01813.php The current patch writes the syntax like this EXCLUSION USING gist (c CHECK WITH ) makes it look like a table constraint, yet it clearly refers to a single column. That looks very clumsy to read, to my eyes. It is a table constraint, and you can specify multiple columns. I don't see much point in allowing this as a column constraint, because that's not the typical case. Most of the time, there will be two columns like: EXCLUSION(room_number CHECK WITH =, during CHECK WITH ) In other words, usually there is both a resource and a period of time for the reservation. It is of course possible to use it for a column constraint, and I'll add syntax if there's demand for it. The syntax be easier to read if it was stated as a comparison e.g. in the circle example CHECK ( NOT (NEW.c c)) USING GIST where NEW is the incoming row. This is similar to the way I would write the constraint if I wanted to ensure the values in two columns did not match/overlap etc CHECK ( NOT (col1 col2)) and is also not such a radical departure from existing SQL Standard syntax. We've already had very extensive discussion about the syntax. Your idea is interesting, but I agree with Tom that it's not ideal, either. NEW might be OK, but Tom's observation about the new meaning of c (ranging over the entire table) is a compelling problem. Consider: CHECK ( NOT (NEW.c c OR c d)) The right side of the OR could either mean c overlaps d or forall c, d: c overlaps d. I can't come up with a way to treat c consistently between the left and right side of the OR (put another way, is c free or bound?). We could allow subselects in CHECK, but it's difficult to infer from arbitrary queries what I can enforce with an operator exclusion constraint, and what I can't. If you want to re-open the syntax discussion, we can (right is better than soon). However, it is late in the cycle, so I'll need something very clear quite soon if this is going to make it into 8.5. Personally I think the current syntax is pretty good. Regards, Jeff Davis -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] operator exclusion constraints
On Sun, 2009-11-01 at 15:42 -0800, Jeff Davis wrote: Most of the time, there will be two columns like: EXCLUSION(room_number CHECK WITH =, during CHECK WITH ) Now that's a great example. Looks like the classic don't allow the same room to be booked more than once at the same time. It bothers me that we would have completely separate syntax for this feature as opposed to normal SQL. It also doesn't make it easy to interpret from the business statement to the implementation. Notice that the , above means AND. How would we use an OR conditional? How would we express the wish to use a partial index? How would I express a bidding rule: Only allow bids that are better than the highest bid so far EXCLUSION (item CHECK WITH =, bid_price CHECK WITH ) Did I get the the right way around? How would I specify a tree that has only 2 down branches at any node, 'left' and 'right'? If you want to re-open the syntax discussion, we can I don't *want* to and I don't want to derail a good feature. But we'll be looking at this for years and believe me if you introduce even a minor bump you'll hear it repeated endlessly. Personally I think the current syntax is pretty good. The feature sounds great, regrettably the syntax doesn't seem very clean, as an objective observer. I apologise if this causes you trouble, I have no axe to grind here. Not sure that if we submitted this to SQL Standard committee that it would be accepted as is. (You made a few other points which I regrettably skimmed over in my reply). -- Simon Riggs www.2ndQuadrant.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] operator exclusion constraints [was: generalized index constraints]
On Sun, 2009-09-20 at 17:54 -0400, Tom Lane wrote: (I'm wondering a bit if anyone will want a WHERE clause, too, though adding that later shouldn't pose any big syntactic obstacles.) Where should I put the WHERE clause? My current syntax (with patch) is: [ CONSTRAINT constraint_name ] EXCLUSION [USING index_method] (expression CHECK WITH operator [, ...]) index_parameters } [ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ] [ WHERE predicate ] That's a little awkward to document, because WHERE is only supported for operator exclusion constraints, so it doesn't just fit along side CHECK and FOREIGN KEY. My only concern is that it would make the CREATE TABLE syntax slightly harder to read. We could allow the WHERE clause to be syntactically correct for all the other constraints, but throw a not implemented error if they try to use it. I'm not sure if that fits nicely with the spec or not. I tried to move the WHERE clause right before or after the index_parameters, but that resulted in shift/reduce conflicts. Thoughts? Regards, Jeff Davis -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] operator exclusion constraints [was: generalized index constraints]
Jeff Davis pg...@j-davis.com writes: I tried to move the WHERE clause right before or after the index_parameters, but that resulted in shift/reduce conflicts. You could avoid the conflicts in at least two ways: * require parens around the WHERE expression * stick the WHERE inside the EXCLUSION ( ... ) bit, so that its expression is terminated by the outer right paren. Not sure if either of these is less ugly than putting it at the end, though :-(. They both seem a bit surprising. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] operator exclusion constraints
On Sun, Sep 27, 2009 at 11:31 PM, Jeff Davis pg...@j-davis.com wrote: On Sun, 2009-09-27 at 22:40 -0400, Robert Haas wrote: Apparently, CommitFest no longer means a time when people put aside their own patches to review those of others; it seems now to mean a time when 87% of the patch authors either continue development or ignore the CommitFest completely. Well, I'm not completely innocent here, either. I also spent time making progress on my patch, both in terms of code and discussion so that I would at least have enough information to get it ready during the next development cycle. I don't see any problem with that. As long as everyone is willing to spend SOME time on their own patch and SOME time reviewing the work of others, the system works. After all, the time to review a patch is, IME, far shorter than the time to develop one. But right now we have a large majority of patch authors who are not contributing ANYTHING to the CommitFest, and that's not so good. ...Robert -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] operator exclusion constraints [was: generalized index constraints]
On Wed, Sep 23, 2009 at 1:47 PM, Tom Lane t...@sss.pgh.pa.us wrote: Jeff Davis pg...@j-davis.com writes: We can either eliminate the USING variant from opt_class (unless it's necessary for some reason or I missed it in the documentation), or we can use another word (e.g. WITH or WITH OPERATOR) if you don't like CHECK. Hmm ... we don't seem to have documented the USING noise-word, so it probably would be safe to remove it; but why take a chance? I don't particularly agree with Peter's objection to CHECK. There are plenty of examples in SQL of the same keyword being used for different purposes in nearby places. Indeed you could make about the same argument to object to USING, since it'd still be there in USING access_method elsewhere in the same command. I think that USING is just about as content-free as WITH in this particular example --- it doesn't give you any hint about what the purpose of the operator is. USING might be just as content-free as WITH, but USING OPERATOR seems clearly better, at least IMO. Also, this patch has not been updated in a week, and the clock is ticking: if we don't have an updated version RSN, we need to move this to Returned with Feedback and wait until next CommitFest. That would be too bad; this is an awesome feature. Thanks, ...Robert -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] operator exclusion constraints [was: generalized index constraints]
Robert Haas robertmh...@gmail.com writes: On Wed, Sep 23, 2009 at 1:47 PM, Tom Lane t...@sss.pgh.pa.us wrote: I think that USING is just about as content-free as WITH in this particular example --- it doesn't give you any hint about what the purpose of the operator is. USING might be just as content-free as WITH, but USING OPERATOR seems clearly better, at least IMO. It's not enough better to justify the conflict with USING opclass, IMO. An idea that just struck me is CHECK WITH, ie EXCLUSION (expr CHECK WITH operator) regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] operator exclusion constraints [was: generalized index constraints]
On Sun, Sep 27, 2009 at 1:08 PM, Tom Lane t...@sss.pgh.pa.us wrote: Robert Haas robertmh...@gmail.com writes: On Wed, Sep 23, 2009 at 1:47 PM, Tom Lane t...@sss.pgh.pa.us wrote: I think that USING is just about as content-free as WITH in this particular example --- it doesn't give you any hint about what the purpose of the operator is. USING might be just as content-free as WITH, but USING OPERATOR seems clearly better, at least IMO. It's not enough better to justify the conflict with USING opclass, IMO. An idea that just struck me is CHECK WITH, ie EXCLUSION (expr CHECK WITH operator) I don't like that as well as USING OPERATOR, but I like it far better than any of the single-word choices, so maybe it's a reasonable compromise. ...Robert -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] operator exclusion constraints
On Sun, Sep 27, 2009 at 5:47 PM, Jeff Davis pg...@j-davis.com wrote: 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 TABLESPACE * Docs updated and improved * Tests updated * Full recheck support (the previous recheck mechanism didn't work for expressions) * 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 * LIKE * 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 commit. * 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. In that case, I think we should target this for the next CommitFest. Especially given the number and complexity of the patches remaining for this CommitFest, I feel very uncomfortable with the idea of waiting another week for a new patch version, and then possibly still needing further changes before it is finally committed. While we allow patches to be resubmitted for the same CommitFest, this is intended to be for minor adjustments, not significant rewrites. So I'm going to mark this Returned with Feedback. ...Robert -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] operator exclusion constraints
On Sun, 2009-09-27 at 21:38 -0400, Robert Haas wrote: In that case, I think we should target this for the next CommitFest. Especially given the number and complexity of the patches remaining for this CommitFest, I feel very uncomfortable with the idea of waiting another week for a new patch version, and then possibly still needing further changes before it is finally committed. While we allow patches to be resubmitted for the same CommitFest, this is intended to be for minor adjustments, not significant rewrites. OK, I expected that to be the case. I got significant feedback at the beginning of this commitfest that required some substantial language changes. I did find this commitfest extremely productive for my feature. Right now I'm trying to provide some useful feedback to Paval for his patch. Regards, Jeff Davis -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] operator exclusion constraints
On Sun, Sep 27, 2009 at 10:13 PM, Jeff Davis pg...@j-davis.com wrote: On Sun, 2009-09-27 at 21:38 -0400, Robert Haas wrote: In that case, I think we should target this for the next CommitFest. Especially given the number and complexity of the patches remaining for this CommitFest, I feel very uncomfortable with the idea of waiting another week for a new patch version, and then possibly still needing further changes before it is finally committed. While we allow patches to be resubmitted for the same CommitFest, this is intended to be for minor adjustments, not significant rewrites. OK, I expected that to be the case. I got significant feedback at the beginning of this commitfest that required some substantial language changes. I did find this commitfest extremely productive for my feature. Excellent, glad to hear it. Right now I'm trying to provide some useful feedback to Paval for his patch. Thanks, I deeply appreciate that. I believe that there are 29 people who submitted patches for this CommitFest, and that 4 of them are reviewing, yourself included. Furthermore, patches and feature proposals from people who are not themselves helping with the CommitFest have continued to roll in during this CommitFest. Personally, I find this quite objectionable. Apparently, CommitFest no longer means a time when people put aside their own patches to review those of others; it seems now to mean a time when 87% of the patch authors either continue development or ignore the CommitFest completely. Fortunately, a number of very competent people who did NOT submit patches nevertheless volunteered to help review, so we may be OK. But I am not sure this is a very sustainable solution. If everyone who submitted a pach for this CF had also reviewed one, every patch would now have a review and there would even be enough reviewers for major patches to have two each. Instead, we are still struggling to get every patch looked at once. ...Robert -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] operator exclusion constraints
On Sun, 2009-09-27 at 22:40 -0400, Robert Haas wrote: Apparently, CommitFest no longer means a time when people put aside their own patches to review those of others; it seems now to mean a time when 87% of the patch authors either continue development or ignore the CommitFest completely. Well, I'm not completely innocent here, either. I also spent time making progress on my patch, both in terms of code and discussion so that I would at least have enough information to get it ready during the next development cycle. We don't have a clear design review stage that allows developers an opportunity to get thorough feedback during the development cycle, so the commitfest is also the design review stage by default. I got some comments when I posted my design on 8/16, but it didn't really get hashed out until a month later when the commitfest was underway. The ideal is to propose, design, implement, and then submit for the commitfest. The reality is that the commitfest is often the first time the developer gets thorough feedback on the design. So, as a developer, I'm hesitant to polish a patch before the commitfest because I know significant changes will be required. Hopefully that didn't waste too much of Brendan's time. That's just an observation from my experience with my patch. I know it's easy to point at little inefficiencies from afar, so I'm not suggesting we change our process. Overall, real progress is being made for the project in general and my patch in particular, so I'm more than willing to set my minor frustrations aside as long as that continues. Regards, Jeff Davis -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] operator exclusion constraints [was: generalized index constraints]
On Sun, 2009-09-20 at 19:42 -0400, Tom Lane wrote: Jeff Davis pg...@j-davis.com writes: I suppose I should just allow any index_elem. The only way I was able to make the grammar for that work is by using a reserved keyword. The possibilities that make the most sense to me are: index_elem WITH any_operator index_elem WITH OPERATOR any_operator index_elem CHECK any_operator index_elem CHECK OPERATOR any_operator Do any of these look acceptable? I'd vote for CHECK, out of that list. WITH has no mnemonic value whatever. Using CHECK as part of the syntax of an EXCLUSION constraint will surely confuse the whole thing with CHECK constraints. USING OPERATOR is available, I think. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] operator exclusion constraints [was: generalized index constraints]
On Wed, 2009-09-23 at 15:10 +0300, Peter Eisentraut wrote: Using CHECK as part of the syntax of an EXCLUSION constraint will surely confuse the whole thing with CHECK constraints. USING OPERATOR is available, I think. USING won't work because one of the ways to specify the opclass in an index_elem is something like: CREATE INDEX foo_idx on foo (i USING int4_ops); which appears to be undocumented, and it's not obvious to me why that is useful. The normal way is just: CREATE INDEX foo_idx on foo (i int4_ops); Because I am allowing any index_elem for exclusion constraints, that conflicts with the word USING. We can either eliminate the USING variant from opt_class (unless it's necessary for some reason or I missed it in the documentation), or we can use another word (e.g. WITH or WITH OPERATOR) if you don't like CHECK. Regards, Jeff Davis -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] operator exclusion constraints [was: generalized index constraints]
Jeff Davis pg...@j-davis.com writes: We can either eliminate the USING variant from opt_class (unless it's necessary for some reason or I missed it in the documentation), or we can use another word (e.g. WITH or WITH OPERATOR) if you don't like CHECK. Hmm ... we don't seem to have documented the USING noise-word, so it probably would be safe to remove it; but why take a chance? I don't particularly agree with Peter's objection to CHECK. There are plenty of examples in SQL of the same keyword being used for different purposes in nearby places. Indeed you could make about the same argument to object to USING, since it'd still be there in USING access_method elsewhere in the same command. I think that USING is just about as content-free as WITH in this particular example --- it doesn't give you any hint about what the purpose of the operator is. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] operator exclusion constraints [was: generalized index constraints]
On Sat, 2009-09-19 at 19:23 -0700, David Fetter wrote: It just occurred to me that SQL:2008 ASSERTION might already fit this feature. :) I think I would only be able to enforce very specific types of assertions that match the template. As I said to Robert, I think I'm going to use ALTER INDEX for the syntax because it appears to be the path of least resistance. Regards, Jeff Davis -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] operator exclusion constraints [was: generalized index constraints]
Jeff Davis pg...@j-davis.com writes: I would still really like to decouple this from CREATE INDEX because of two reasons: 1. Cannot support multiple constraints per index very easily. I think this is a significant feature. 2. Must decide to make constraint at the same time as making the index, and once it's there, you can't remove it without dropping the index. I don't actually find either of those arguments to be credible in the least. I don't think that people will find it useful to enforce multiple constraints with one index, and I don't believe that they'll design an index without knowledge of the constraint they will enforce with it. The closest precedent we have is the UNIQUE constraint. How often have we had requests to add or drop UNIQUE in an existing index? Maybe there were more than zero, but not by a lot. As an example of why I don't believe the first item, consider something like create index ... (a = , b = ) (or whatever the syntax is to exclude equality on each column separately). Yeah, it will work, but have you considered the efficiency implications? Searching such an index for b, independently of a, is going to suck to such an extent that you'd be *far* better off building two separate indexes. We do not have, and are unlikely ever to have, index types in which a search that doesn't constrain the first index column is efficient. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] operator exclusion constraints [was: generalized index constraints]
On Sun, 2009-09-20 at 12:45 -0400, Tom Lane wrote: How often have we had requests to add or drop UNIQUE in an existing index? Maybe there were more than zero, but not by a lot. Ok. As an example of why I don't believe the first item, consider something like create index ... (a = , b = ) (or whatever the syntax is to exclude equality on each column separately). Yeah, it will work, but have you considered the efficiency implications? Searching such an index for b, independently of a, is going to suck to such an extent that you'd be *far* better off building two separate indexes. We do not have, and are unlikely ever to have, index types in which a search that doesn't constrain the first index column is efficient. My use case was something else: An index on (a, b, c) enforcing the constraints UNIQUE(a, b) and UNIQUE(a, c). UNIQUE(a, b) can be enforced efficiently. UNIQUE(a, c) might be less efficient depending on the selectivity of a, but as long as a is selective I think it's useful. The alternative is updating two indices on every insert. You may still think this use case is too marginal to bother supporting, but I never made an argument for the use case you described above. If we move away from multiple constraints per index, are you suggesting that I also move the constraints out of pg_constraint and back into pg_index? Regards, Jeff Davis -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
