Re: [HACKERS] operator exclusion constraints [was: generalized index constraints]
Jeff Davis 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 [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]
On Sun, Sep 27, 2009 at 1:08 PM, Tom Lane wrote: > Robert Haas writes: >> On Wed, Sep 23, 2009 at 1:47 PM, Tom Lane 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 [was: generalized index constraints]
Robert Haas writes: > On Wed, Sep 23, 2009 at 1:47 PM, Tom Lane 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 Wed, Sep 23, 2009 at 1:47 PM, Tom Lane wrote: > Jeff Davis 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]
Jeff Davis 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 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]
On Sun, 2009-09-20 at 19:42 -0400, Tom Lane wrote: > Jeff Davis 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 Sun, 2009-09-20 at 19:42 -0400, Tom Lane wrote: > BTW, are you sure EXCLUSION doesn't have to become a reserved word for > this? I notice that FOREIGN, CHECK, and UNIQUE all are, which makes me > suspicious ... All of those (except FOREIGN) can be used as a column constraint as well, and that might be necessary for a reason similar to the reason I need to use a reserved word (i.e. they can come after a DEFAULT expression). Is it possible that FOREIGN doesn't really have to be a reserved word, but was just included because the others were? I'm not an expert on the matter, but it does appear to compile and recognize the grammar with EXCLUSION as an unreserved keyword. I'm in the middle of changing a lot of things around, so I can't say that it works beyond that. 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 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. I'm not that thrilled with CHECK either, mainly because it seems like it ought to check that the operator condition *does* hold, whereas you're going to check that it *doesn't* hold. But perhaps the EXCLUSION up front will be enough to set people straight. BTW, are you sure EXCLUSION doesn't have to become a reserved word for this? I notice that FOREIGN, CHECK, and UNIQUE all are, which makes me suspicious ... 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 17:54 -0400, Tom Lane wrote: > Jeff Davis writes: > > 1. Constraint syntax, part of CREATE/ALTER TABLE: > > > [CONSTRAINT ] EXCLUSION ( OPERATOR , ...) > > Have you actually built this grammar? I don't think it avoids the > problem, because OPERATOR is possible within a_expr. > > Also, don't forget the possibility of wanting a nondefault opclass. > (I'm wondering a bit if anyone will want a WHERE clause, too, though > adding that later shouldn't pose any big syntactic obstacles.) 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? Also, I should allow for a tablespace, as well. Because it's specified with UNIQUE as "USING INDEX TABLESPACE foo", to be consistent I need to move the "USING method" ahead like so: CONSTRAINT EXCLUSION [USING method] ( CHECK , ...) [USING INDEX TABLESPACE ] [DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ] Having the method before the attribute list makes it more consistent with CREATE INDEX, as well. > That's really a separate issue, but I think we need to do something to > make it more consistent. My first thought is that anything made > via CONSTRAINT syntax ought to be copied by LIKE INCLUDING CONSTRAINTS, > while LIKE INCLUDING INDEXES should copy anything you made via CREATE > INDEX. Works for me. > But note this assumes that there is a clear distinction between > the two. The constraint-depending-on-index design that you started > with would not permit such a rule, or at least it would mean that > INCLUDING CONSTRAINTS EXCLUDING INDEXES would have failure cases. Sounds reasonable. If we decide to support that kind of thing in the future, we can handle that case somehow (an error seems reasonable 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 [was: generalized index constraints]
Jeff Davis writes: > 1. Constraint syntax, part of CREATE/ALTER TABLE: > [CONSTRAINT ] EXCLUSION ( OPERATOR , ...) Have you actually built this grammar? I don't think it avoids the problem, because OPERATOR is possible within a_expr. Also, don't forget the possibility of wanting a nondefault opclass. (I'm wondering a bit if anyone will want a WHERE clause, too, though adding that later shouldn't pose any big syntactic obstacles.) > Brendan made a strong argument[15] that the behavior of LIKE > with UNIQUE is wrong, but I don't know if we want to try to fix that > now. I'd like some more input before I actually take care of this item. That's really a separate issue, but I think we need to do something to make it more consistent. My first thought is that anything made via CONSTRAINT syntax ought to be copied by LIKE INCLUDING CONSTRAINTS, while LIKE INCLUDING INDEXES should copy anything you made via CREATE INDEX. But note this assumes that there is a clear distinction between the two. The constraint-depending-on-index design that you started with would not permit such a rule, or at least it would mean that INCLUDING CONSTRAINTS EXCLUDING INDEXES would have failure cases. 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
[HACKERS] operator exclusion constraints [was: generalized index constraints]
Update on operator exclusion constraints (OXC for short): After a lot of discussion, I think a lot of progress has been made. Here is my summary, please let me know if I've left anything out or not addressed some concern. 1. Constraint syntax, part of CREATE/ALTER TABLE: [CONSTRAINT ] EXCLUSION ( OPERATOR , ...) USING [ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]; Table constraint syntax was chosen because of the ability to support DEFERRABLE[1] and the interest in a more declarative syntax[2]. We omitted the [INDEX ] clause because the usefulness of defining multiple constraints using one index or defining the constraint separately from the index was judged to be too marginal[3][4][5]. Some brief benchmarks showed some promise[6], perhaps interesting to explore later. Also, we introduce the OPERATOR keyword in between the expression and the operator to disambiguate the syntax[5]. Nobody has affirmed the use of OPERATOR for the disambiguation, but it seems like the obvious choice to me. 2. information_schema We omit operator exclusion constraints from the information schema, on the grounds that there is no way to represent them usefully there[7][8]. 3. Simplify the constraint checking procedure itself Tom suggested a simpler constraint-checking procedure[9]. It introduces the rare possibility of deadlocks[10], but that possibility exists for other constraints anyway[11]. My scheme for avoiding deadlocks was significantly more complex, and would become even more complex for deferrable constraints. 4. is an expression over the table's attributes and will be used to generate a functional index with the same expression to enforce the constraint. 5. We reject non-symmetric operators[12], like >, but allow non-reflexive operators[13] like <>. 6. Semantics of constraint[14] are such that for any two tuples A and B, and for a constraint: EXCLUSION (e1 OPERATOR , ..., eN OPERATOR ) the constraint is violated if: A.e1 B.e1 AND ... AND A.eN B.eN 7. LIKE is still unresolved. I don't have a strong opinion here. When INCLUDING CONSTRAINTS and INCLUDING INDEXES are both specified: a. copy all OXCs and indexes b. copy no OXCs or indexes When INCLUDING CONSTRAINTS is specified but not INCLUDING INDEXES: a. copy all OXCs and indexes b. copy no OXCs or indexes When INCLUDING INDEXES is specified but not INCLUDING CONSTRAINTS: a. copy all OXCs, including indexes b. copy all indexes created implicitly for OXCs, but not the constraints themselves c. copy no OXCs or indexes We can also emit various types of messages if we think the user is making a mistake. UNIQUE behavior here doesn't provide a good cue, because the constraint is implemented inside the index, so copying the index does copy the constraint. Brendan made a strong argument[15] that the behavior of LIKE with UNIQUE is wrong, but I don't know if we want to try to fix that now. I'd like some more input before I actually take care of this item. The rest of the issues were mostly non-controversial. I will start making some of these changes and post an updated patch and TODO list. Regards, Jeff Davis [1] http://archives.postgresql.org/pgsql-hackers/2009-09/msg01352.php [2] http://archives.postgresql.org/pgsql-hackers/2009-09/msg01018.php [3] http://archives.postgresql.org/pgsql-hackers/2009-09/msg01348.php [4] http://archives.postgresql.org/pgsql-hackers/2009-09/msg01355.php [5] http://archives.postgresql.org/pgsql-hackers/2009-09/msg01360.php [6] http://archives.postgresql.org/pgsql-hackers/2009-09/msg01369.php [7] http://archives.postgresql.org/pgsql-hackers/2009-09/msg01310.php [8] http://archives.postgresql.org/pgsql-hackers/2009-09/msg01356.php [9] http://archives.postgresql.org/pgsql-hackers/2009-09/msg01315.php [10] http://archives.postgresql.org/pgsql-hackers/2009-09/msg01317.php [11] http://archives.postgresql.org/pgsql-hackers/2009-09/msg01347.php [12] http://archives.postgresql.org/pgsql-hackers/2009-09/msg00977.php [13] http://archives.postgresql.org/pgsql-hackers/2009-09/msg01039.php [14] http://archives.postgresql.org/pgsql-hackers/2009-09/msg00971.php [15] http://archives.postgresql.org/pgsql-hackers/2009-09/msg00755.php -- 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 13:49 -0400, Tom Lane wrote: > I'd vote for only supporting the former. Ok. I just did some brief non-scientific in-memory benchmarks. I think it has promise, but for now I think it can safely be set aside. Results appended. > What worries me more about that syntax is the postfix-operator ambiguity > --- I think it'll be hard to expand it to expressions. It might be > better to put the operator at the front; or maybe you need an extra > keyword in there. How about "OPERATOR", like: CONSTRAINT EXCLUSION ( OPERATOR , ...) USING ; I like it because it connects back to the name "operator exclusion constraint". Regards, Jeff Davis --- Results (oversimplified benchmark): As a control, two unique btrees (using old uniqueness mechanism) takes 37s. DDL (old syntax, haven't changed it yet): create table one(a int, b int, c int); create index one_a_b_c_idx on one(a,b,c); alter table one add constraint one_a_b_constr exclusion (a =, b =) using one_a_b_c_idx; alter table one add constraint one_a_c_constr exclusion (a =, c =) index one_a_b_c_idx; create table two(a int, b int, c int); create index two_a_b_idx on two(a,b); create index two_a_c_idx on two(a,c); alter table two add constraint two_a_c_constr exclusion (a =, c =) index two_a_c_idx; alter table two add constraint two_a_b_constr exclusion (a =, b =) index two_a_b_idx; Tests are of the form: -- test inserting into table with one big index with 10 "b" -- values per "a" value insert into one select g1, g2, g2 from generate_series(1,10) g1, generate_series(1,10) g2; n: number of "a" values per "b" value t1: results for one-index solution t2: results for two-index solution n t1 t2 ---+--+--- 1000 | 105s | 57s 100 | 47s | 54s 10 | 44s | 53s 1 | 42s | 56s So, the one-index solution shows about 10-20% benefit over the two-index solution when the number of "b" values per "a" value drops to around 100. Not bad, but nothing to write home about, because it's still outperformed by the existing btree enforcement mechinism. I think it has promise for some situations though; such as larger key size, leaf pages not in memory, etc. -- 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 writes: > I believe that syntax is possible by specifying the index access method, > e.g.: > CONSTRAINT EXCLUSION (a =, b &&) USING gist; > versus: > CONSTRAINT EXCLUSION (a =, b &&) INDEX ; > And the former could build the index implicitly. I haven't written the > code yet, but I don't see any major problems. > So, should I eliminate the latter syntax and only support the former, or > should I support both? I'd vote for only supporting the former. What worries me more about that syntax is the postfix-operator ambiguity --- I think it'll be hard to expand it to expressions. It might be better to put the operator at the front; or maybe you need an extra keyword in 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 [was: generalized index constraints]
On Sun, 2009-09-20 at 13:28 -0400, Tom Lane wrote: > What I'm arguing for is a syntax in which the question doesn't even > arise, ie, a CONSTRAINT doesn't reference an existing index at all. > If that's not possible for whatever reason, then I think that > disallowing multiple references isn't going to buy any simplicity. I believe that syntax is possible by specifying the index access method, e.g.: CONSTRAINT EXCLUSION (a =, b &&) USING gist; versus: CONSTRAINT EXCLUSION (a =, b &&) INDEX ; And the former could build the index implicitly. I haven't written the code yet, but I don't see any major problems. So, should I eliminate the latter syntax and only support the former, or should I support 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 [was: generalized index constraints]
Jeff Davis writes: > On Sun, 2009-09-20 at 13:13 -0400, Tom Lane wrote: >> You're right, it still seems remarkably marginal. I'm rethinking >> my position on use of CONSTRAINT syntax because of the deferrability >> issue, but I'm still unconvinced that we need to allow the constraints >> to be decoupled from the indexes. > Ok, should I explicitly disallow multiple constraints on one index then? What I'm arguing for is a syntax in which the question doesn't even arise, ie, a CONSTRAINT doesn't reference an existing index at all. If that's not possible for whatever reason, then I think that disallowing multiple references isn't going to buy any simplicity. 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 13:13 -0400, Tom Lane wrote: > You're right, it still seems remarkably marginal. I'm rethinking > my position on use of CONSTRAINT syntax because of the deferrability > issue, but I'm still unconvinced that we need to allow the constraints > to be decoupled from the indexes. Ok, should I explicitly disallow multiple constraints on one index then? 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 writes: > 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. You're right, it still seems remarkably marginal. I'm rethinking my position on use of CONSTRAINT syntax because of the deferrability issue, but I'm still unconvinced that we need to allow the constraints to be decoupled from the indexes. 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
Re: [HACKERS] operator exclusion constraints [was: generalized index constraints]
Jeff Davis 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 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]
On Sat, Sep 19, 2009 at 04:40:19PM -0700, Jeff Davis wrote: > On Sat, 2009-09-19 at 18:35 -0400, Tom Lane wrote: > > I'm still acutely uncomfortable with using CONSTRAINT syntax for this. > > It is not a constraint per standard, because it's not going to be > > displayable in information_schema. Furthermore, by extending > > standardized syntax you run the risk of being blindsided by future > > additions to the standard. > > Ok. It just occurred to me that SQL:2008 ASSERTION might already fit this feature. :) Cheers, David. -- David Fetter http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com 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 [was: generalized index constraints]
On Sat, 2009-09-19 at 18:35 -0400, Tom Lane wrote: > I'm still acutely uncomfortable with using CONSTRAINT syntax for this. > It is not a constraint per standard, because it's not going to be > displayable in information_schema. Furthermore, by extending > standardized syntax you run the risk of being blindsided by future > additions to the standard. Ok. > The point about being able to support multiple constraints with one > index is kind of interesting, but I don't actually think that that's > so useful that it should override all other considerations about what > syntax we should pick. I think we should drop the whole thing and > just treat this as an extension to the CREATE INDEX syntax. Perhaps ALTER INDEX ADD EXCLUSION CONSTRAINT or some other command? And CREATE INDEX can offer the ability as a shorthand? 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 think either of these still tie the concept to implementation, because creating the index is always explicit. Peter seemed concerned about that, and I think that concern is valid, but I can live with it. If we really want them to be declarative, we could invent a new command. 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 writes: > I am updating the syntax to be: > CONSTRAINT foo_constr > EXCLUSION (a , ...) { USING method | INDEX foo_idx }; I'm still acutely uncomfortable with using CONSTRAINT syntax for this. It is not a constraint per standard, because it's not going to be displayable in information_schema. Furthermore, by extending standardized syntax you run the risk of being blindsided by future additions to the standard. > ... Peter had the following concern: > "Another problem this would lead to is that a say dump of a table > definition wouldn't actually contain all the constraints that apply to > the table anymore, because there might be additional stuff such as this > that can't be expressed that way." [1] > I don't think that's a serious problem, That objection is completely bogus. pg_dump does not, and AFAIR never has, promised to emit everything in the CREATE TABLE command. It's far more efficient and practical to emit indexes and constraints as separate commands later, after the data has been loaded. In the case of say foreign key constraints, it's absolutely necessary to do it separately, else you can't implement circular constraint references. Besides, we already have many cases where indexes have to be emitted separately because they don't fit into the CONSTRAINT syntax: expression indexes and nondefault opclasses to name two. The point about being able to support multiple constraints with one index is kind of interesting, but I don't actually think that that's so useful that it should override all other considerations about what syntax we should pick. I think we should drop the whole thing and just treat this as an extension to the CREATE INDEX syntax. 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
[HACKERS] operator exclusion constraints [was: generalized index constraints]
On Sat, 2009-09-19 at 10:48 -0700, Jeff Davis wrote: > CONSTRAINT foo_constr (a , ...) > { USING INDEX foo_idx | USING method } I am updating the syntax to be: CONSTRAINT foo_constr EXCLUSION (a , ...) { USING method | INDEX foo_idx }; First, I think EXCLUSION makes a perfect noun to fit in that place (like "FOREIGN KEY"). Second, this makes it possible to avoid specifying the index, and the system can create one for you by knowing the access method. That makes the feature a little more declarative. However, it still doesn't provide a way to express two constraints using one index all within CREATE TABLE, because the index would need to be defined before the constraints in that case. I don't see that as a problem, but Peter had the following concern: "Another problem this would lead to is that a say dump of a table definition wouldn't actually contain all the constraints that apply to the table anymore, because there might be additional stuff such as this that can't be expressed that way." [1] I don't think that's a serious problem, I just need to ensure that indexes referenced by a constraint are dumped before the constraint itself. Then, I can dump the operator exclusion constraints (OXCs) as ALTER TABLEs. The "-t" option to pg_dump appears to already dump constraints as separate ALTER TABLEs. Is there something that I'm missing? Regards, Jeff Davis [1] http://archives.postgresql.org/pgsql-hackers/2009-09/msg01018.php -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers