WIP: generalized index constraints
in [PgSql]
|
Prev: corrupted double-linked list
Next: Timestamp to time_t
From: David Fetter on 15 Sep 2009 15:03 On Tue, Sep 15, 2009 at 11:31:48AM -0700, Jeff Davis wrote: > 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. Interesting :) I take it op1..opN (it's opN, not op2, right?) need to commute? > > 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. Here's a couple: * "generalized-uniqueness constraints" the hyphen disambiguates * "operator-based constraints" A little math-ier, but talks about the API rather than details of the server implementation. Cheers, David. -- David Fetter <david(a)fetter.org> http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fetter(a)gmail.com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- 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 15:04 On Tue, Sep 15, 2009 at 3:03 PM, David Fetter <david(a)fetter.org> wrote: > * "operator-based constraints" > A little math-ier, but talks about the API rather than details of > the server implementation. Or operator-exclusion constraints? Operator-based exclusion constraints? I'm feeling exclusive. ....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 15:18 On Tue, 2009-09-15 at 14:49 -0400, Tom Lane wrote: > 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.) One of the requirements is commutativity (I called it "symmetry" in the docs, for some reason, I will change that). I haven't explored in too much detail, but using "x >" (or maybe its "<" ?) would basically mean that you can only insert increasing values of x. There most likely be some serious problems there, for instance, if you HOT update an old tuple's "y" attribute, everything is fine; if you cold update it you would get an error. Not exactly intuitive, but if you have lots of other requirements about how things are updated, then I suppose it might be useful to someone. If you try it, my current patch won't stop you. Maybe I should detect the fact that the commutator of an operator is not the operator itself, and throw an ERROR? Probably would be a good idea. 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: Jeff Davis on 15 Sep 2009 15:22 On Tue, 2009-09-15 at 12:03 -0700, David Fetter wrote: > Interesting :) I take it op1..opN (it's opN, not op2, right?) need to > commute? Yeah, it's opN. And they should commute, but my current patch won't stop you. I think I should stop that though, it's pretty difficult to think of a good use-case for that and there is all kinds of danger. > * "generalized-uniqueness constraints" > the hyphen disambiguates I don't like using the word "unique" in the description, I think it only adds to the confusion. > * "operator-based constraints" > A little math-ier, but talks about the API rather than details of > the server implementation. I like this much better. Maybe "index operator constraints" or "operator index constraints"? 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 15:30
Jeff Davis <pgsql(a)j-davis.com> writes: > On Tue, 2009-09-15 at 14:49 -0400, Tom Lane wrote: >> 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.) > If you try it, my current patch won't stop you. Maybe I should detect > the fact that the commutator of an operator is not the operator itself, > and throw an ERROR? Probably would be a good idea. +1. Otherwise people *will* try it, and then send us bug reports when it doesn't behave sanely. 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 |