WIP: generalized index constraints
in [PgSql]
|
Prev: corrupted double-linked list
Next: Timestamp to time_t
From: Brendan Jurd on 15 Sep 2009 13:21 2009/9/16 Robert Haas <robertmhaas(a)gmail.com>: > On Tue, Sep 15, 2009 at 1:14 PM, Brendan Jurd <direvus(a)gmail.com> wrote: >> 2009/9/16 Robert Haas <robertmhaas(a)gmail.com>: >>> On Tue, Sep 15, 2009 at 12:54 PM, Jeff Davis <pgsql(a)j-davis.com> wrote: >>>> I don't want to call them "don't overlap constraints", because it's not >>>> limited to a non-overlapping constraint. >>> >>> Oh. What else can you do with it? >> >> Anything that there is an operator for. > > Uhh.... so what happens if I create an index constraint using the > +(integer, integer) operator? Okay, so my first answer was a simplification. You can use any operator that has an appropriate index strategy entry. -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
From: Jeff Davis on 15 Sep 2009 13:28 On Tue, 2009-09-15 at 13:16 -0400, Robert Haas wrote: > Uhh.... so what happens if I create an index constraint using the > +(integer, integer) operator? You can use any operator that has an index search strategy. Overlaps is probably the most useful, but you could imagine other operators, like a bi-directional containment operator (either LHS is contained in RHS, or vice-versa). You can also get creative and have a "similarity" operator that determines whether two tuples are "too similar". As long as it is symmetric, the feature will work. Or just use wrap random() in an operator and see what happens ;) Regards, Jeff Davis -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
From: Robert Haas on 15 Sep 2009 13:48 On Tue, Sep 15, 2009 at 1:28 PM, Jeff Davis <pgsql(a)j-davis.com> wrote: > On Tue, 2009-09-15 at 13:16 -0400, Robert Haas wrote: >> Uhh.... so what happens if I create an index constraint using the >> +(integer, integer) operator? > > You can use any operator that has an index search strategy. Overlaps is > probably the most useful, but you could imagine other operators, like a > bi-directional containment operator (either LHS is contained in RHS, or > vice-versa). > > You can also get creative and have a "similarity" operator that > determines whether two tuples are "too similar". As long as it is > symmetric, the feature will work. So it allows us to create constraints of the following form? For all A in the index, there exists no B in the index such that the given operator (which must be a binary operator returning boolean) holds of A and B. If that's correct, I think we should definitely at least mention the word "overlap" somewhere in the documentation, because that's what people are going to want to use it for, and it's hard to conceptualize without examples, at least for me. You may already be doing this, I haven't read the patch. Also, there are certainly other things you could want to do that can't be handled by this approach. Perhaps you'd like to create a constraint that a given value can appear at most twice, or a two column index (A, B) such that for any A the smallest value of B is less than A. These are certainly less common requirements than what you're talking about here, and I don't think it's important to try to support them - at least not at this point - but the word "generalized" doesn't give me a clue that I won't be able to do those things but I will be able to make an index that prevents my users from handing out duplicate IP blocks. ....Robert -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
From: Jeff Davis on 15 Sep 2009 14:31 On Tue, 2009-09-15 at 13:48 -0400, Robert Haas wrote: > So it allows us to create constraints of the following form? > > For all A in the index, there exists no B in the index such that the > given operator (which must be a binary operator returning boolean) > holds of A and B. Yes. And it's slightly more complicated for multi-column constraints: For all tuples A in the index with attributes 1 to N, there exists no tuple B such that: A1 op1 B1 AND A2 op2 B2 AND ... AN op2 BN If all operators are "=", and the index implements searching on equality, it's semantically equivalent to a unique index. > > If that's correct, I think we should definitely at least mention the > word "overlap" somewhere in the documentation, because that's what > people are going to want to use it for, and it's hard to conceptualize > without examples, at least for me. You may already be doing this, I > haven't read the patch. My current example uses "overlaps", but I will expand the documentation to provide more clarity. > Also, there are certainly other things you could want to do that can't > be handled by this approach. Perhaps you'd like to create a > constraint that a given value can appear at most twice, or a two > column index (A, B) such that for any A the smallest value of B is > less than A. The first is a good example, and actually I think that could be an add-on to my patch without much difficulty. The second can't be enforced with an index in nearly the same way because deleting a tuple could violate the constraint. Also, it seems like it would be hard to express that kind of constraint. But I agree that, in principle, it is an index-enforceable constraint. > These are certainly less common requirements than what > you're talking about here, and I don't think it's important to try to > support them - at least not at this point - but the word "generalized" > doesn't give me a clue that I won't be able to do those things but I > will be able to make an index that prevents my users from handing out > duplicate IP blocks. As far as the name goes, the best I've got so far are "index constraints" and "generalized index constraints". I'm happy to change the name if you have a reasonable suggestion. I don't think the word "generalized" implies that it can do absolutely anything possible. For the list discussion, I think it's appropriate to use the term "generalized", because my patch generalizes index constraints. However, I agree that we shouldn't use that too much in the code/docs because someone might think of something more general later. Regards, Jeff Davis -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
From: Tom Lane on 15 Sep 2009 14:49
Jeff Davis <pgsql(a)j-davis.com> writes: > On Tue, 2009-09-15 at 13:16 -0400, Robert Haas wrote: >> Uhh.... so what happens if I create an index constraint using the >> +(integer, integer) operator? > You can use any operator that has an index search strategy. Overlaps is > probably the most useful, but you could imagine other operators, like a > bi-directional containment operator (either LHS is contained in RHS, or > vice-versa). Does it behave sanely for operators that are non-commutative, such as '>'? (I'm not even very sure that I know what "sanely" would be in such a case.) regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers |