WIP: generalized index constraints
in [PgSql]
|
Prev: corrupted double-linked list
Next: Timestamp to time_t
From: Peter Eisentraut on 16 Sep 2009 03:14 On Tue, 2009-09-15 at 12:37 -0400, Robert Haas wrote: > Instead of calling these generalized index constraints, I wonder if we > oughtn't to be calling them something like "don't-overlap constraints" > (that's a bad name, but something along those lines). They're not > really general at all, except compared to uniqueness constraints (and > they aren't called generalized unique-index constraints, just > generalized index constraints). What they should be called is generalized unique constraints, without reference to "index". Because what they generalize is the operator by which uniqueness is determined. Don't all of these have the same effect at the end? CREATE TABLE data (a int UNIQUE); CREATE TABLE data (a int); CREATE UNIQUE INDEX data_a_idx ON data USING btree (a); CREATE TABLE data (a int); CREATE INDEX data_a_idx ON data USING btree (a); ALTER TABLE data ADD CONSTRAINT a_unique (a =) USING INDEX data_a_idx; Which brings me to two concerns about the syntax. First, we have so far been fairly consistent to document that unique indexes are an implementation detail of unique constraints and should usually not be used directly. This new approach basically reverses that and forces you to define your constraints by means of implementation details rather than a logical description. There is nothing in this feature that makes it strikingly different from the existing constraint types in a way that would prevent a reasonable syntax for defining the constraint at table definition time. 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. If you look at the example from the documentation, CREATE TABLE circles(c circle); CREATE INDEX circles_idx ON circles USING gist (c); ALTER TABLE circles ADD CONSTRAINT circles_idx_constr (c &&) USING INDEX circles_idx; the only variable pieces of information that need to be provided to the table are the index type and the operator. So why not just write it like this CREATE TABLE circles (c circle UNIQUE ON gist &&); and let that create the required index. And then traditional unique constraints would fit into this as well: CREATE TABLE data (a int UNIQUE ON btree =); The other problem I see with the syntax is that ALTER TABLE circles ADD CONSTRAINT circles_idx_constr (c &&) USING INDEX circles_idx; doesn't seem very intuitive about what is actually being constrained. For a while I was thinking that it was constraining the table to values that are in the index or something. So using a word such as UNIQUE would help explain what is going on. -- 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: Peter Eisentraut on 16 Sep 2009 03:17 On Tue, 2009-09-15 at 12:22 -0700, Jeff Davis wrote: > I don't like using the word "unique" in the description, I think it > only > adds to the confusion. It would emphasize that a unique constraint is a common special case of the feature. -- 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: tomas on 16 Sep 2009 09:11 -----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 On Tue, Sep 15, 2009 at 10:28:28AM -0700, Jeff Davis 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. One question: does the operator have to be reflexive? I.e. "A op A holds for all A"? I am thinking "proximity" or as you state above "similarity". May be this is a good metaphor, leading to a good name. Regards - -- tomás -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.6 (GNU/Linux) iD8DBQFKsOPyBcgs9XrR2kYRAhsUAJkBICYUMK0tDrycPbctiGF7YKI/9gCeLQzq DPyAAkMgZJFn8BZ7P8119/g= =lVz1 -----END PGP SIGNATURE----- -- 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 16 Sep 2009 11:04 On Tue, Sep 15, 2009 at 7:02 PM, Joshua Tolley <eggyknap(a)gmail.com> wrote: > On Tue, Sep 15, 2009 at 05:52:35PM -0400, Tom Lane wrote: >> Jeff Davis <pgsql(a)j-davis.com> writes: >> > On Tue, 2009-09-15 at 14:42 -0700, Jeff Davis wrote: >> >> operator constraints >> >> operator exclusion constraints >> >> operator conflict constraints >> >> conflict operator constraints >> >> operator index constraints >> >> index constraints >> >> generalized index constraints >> >> something else? >> >> > Just to add a couple more permutations of Robert Haas's suggestions: >> >> > exclusion operator constraints >> > exclusive operator constraints >> >> To my ear, "operator exclusion constraints" or "exclusive operator >> constraints" seem reasonable; the other permutations of that phrase >> simply aren't good English. > > I was having a hard time coming up with a name that was adequately > short-and-sweet, and still conveyed the idea of both "operator" and "index", > which seems important so as to designate between these and the constraints > we've had all along. Perhaps "indexed operator constraints"? Really I suppose they are indexed operator exclusion constraints, but that may be too wordy. ....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 16 Sep 2009 12:45
On Wed, 2009-09-16 at 15:11 +0200, tomas(a)tuxteam.de wrote: > One question: does the operator have to be reflexive? I.e. "A op A holds > for all A"? I don't think that reflexivity is a strict requirement. You could make this a constraint over a boolean attribute such that false conflicts with true and true conflicts with false. That would mean that your table would have to consist of either all false or all true. > I am thinking "proximity" or as you state above "similarity". May be > this is a good metaphor, leading to a good name. That's an interesting idea: "proximity constraint". I like it because (a) "proximity" might reasonably be considered a more general form of the word "unique", which might satisfy Peter's argument; (b) it conveys the use case; and (c) it sounds good. There are a couple bizarre cases where "proximity" doesn't quite fit, like my boolean example above, but I'm OK with that. 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 |