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 [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 [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
Re: [HACKERS] operator exclusion constraints [was: generalized index constraints]
Jeff Davis pg...@j-davis.com 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 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 pg...@j-davis.com 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: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 name EXCLUSION (a =, b ) USING gist; versus: CONSTRAINT name EXCLUSION (a =, b ) INDEX indexname; 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 pg...@j-davis.com writes: I believe that syntax is possible by specifying the index access method, e.g.: CONSTRAINT name EXCLUSION (a =, b ) USING gist; versus: CONSTRAINT name EXCLUSION (a =, b ) INDEX indexname; 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: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 name EXCLUSION (expr OPERATOR op, ...) USING method; 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 pg...@j-davis.com writes: 1. Constraint syntax, part of CREATE/ALTER TABLE: [CONSTRAINT name] EXCLUSION (expr OPERATOR op, ...) 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
Re: [HACKERS] operator exclusion constraints [was: generalized index constraints]
On Sun, 2009-09-20 at 17:54 -0400, Tom Lane wrote: Jeff Davis pg...@j-davis.com writes: 1. Constraint syntax, part of CREATE/ALTER TABLE: [CONSTRAINT name] EXCLUSION (expr OPERATOR op, ...) 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 name EXCLUSION [USING method] (index_elem CHECK op, ...) [USING INDEX TABLESPACE tablespacename] [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 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. 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 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 pg...@j-davis.com writes:
I am updating the syntax to be:
CONSTRAINT foo_constr
EXCLUSION (a op, ...) { 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
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]
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 da...@fetter.org 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
