Thread: adding support for zero-attribute unique/etc keys
From my usage and http://www.postgresql.org/docs/9.2/interactive/sql-createtable.html I see that Postgres requires constraints like unique (and primary) keys, and foreign keys, to range over at least 1 attribute/column. I propose that this be generalized so that constraints may instead be nullary, that is, range over zero or more attributes/columns instead. Supporting this gives us more logical completeness, while not allowing lists of zero attributes is akin to having math without zeroes. The semantics of having a nullary / zero-attribute unique/primary key on a table is to restrict the table to having at most one row at a time. Practically speaking, I find that nullary primary/unique keys are useful in the numerous situations where one might want to have a table consisting of just one record (or zero), because it stores data that there is just a single instance of, such some kinds of config data, rather than a list of such. Such tables let you store such data in a strongly typed and well self-documented manner, same as normal tables. Currently in order to do this, I have to work around the lack of nullary keys by creating a table with a dummy column having a check constraint saying its value must equal a constant, and have a unary unique/primary key on that. But this is an unpleasant kludge and one shouldn't have to do that. Nullary foreign keys can also be useful, or at least should be supported for logical completeness. The semantics of having one is to say that one table may only have rows if another table also has rows. I was also going to say that it is important to be able to declare a composite type with zero attributes, which is a way to make a singleton type. However I'm not sure that we don't already support this so I won't say more on that. The primary request I have is nullary unique/primary key support, which I see has a solid use case. (Note: Logically speaking, if you have a nullary key, it would be the primary key, and all other unique keys are redundant. Except perhaps that others can exist in order to support implementation matters of foreign keys that require their targets to be unique.) How much work would it be to support this? But also important, does anyone either agree it should be supported or does anyone want to counter-argue that it shouldn't be supported? -- Darren Duncan
Darren Duncan wrote: > From my usage and > http://www.postgresql.org/docs/9.2/interactive/sql-createtable.html I see that > Postgres requires constraints like unique (and primary) keys, and foreign keys, > to range over at least 1 attribute/column. > > I propose that this be generalized so that constraints may instead be nullary, > that is, range over zero or more attributes/columns instead. [...] > But also important, does anyone > either agree it should be supported or does anyone want to counter-argue that it > shouldn't be supported? I don't think that a new way to force single-row tables is a good enough use case, and "for completeness' sake" is only a good argument if it simplifies things and makes them more comprehensible, which I don't think is the case here. The semantics seem unclear to me: The standard defines UNIQUE on the basis of the "UNIQUE predicate": <unique predicate> ::= UNIQUE <table subquery> and states: 1) Let T be the result of the <table subquery>. 2) If there are no two rows in T such that the value of each column in one row is non-null and is not distinct from thevalue of the corresponding column in the other row, then the result of the <unique predicate> is *True*; otherwise,the result of the <unique predicate> is *False*. Since an imagined zero-column query would have an empty set of result columns, you could with equal force argue that these columns satisfy the condition or not, because the members of the empty set have all the properties you desire. So I see no compelling argument that such a UNIQUE constraint would force a single-row table. The desired effect can be had today with a unique index: CREATE TABLE singleton (id integer); CREATE UNIQUE INDEX singleton_idx ON singleton((1)); Yours, Laurenz Albe
Albe Laurenz <laurenz.albe@wien.gv.at> writes:
> Darren Duncan wrote:
>> I propose that this be generalized so that constraints may instead be nullary,
>> that is, range over zero or more attributes/columns instead.
> Since an imagined zero-column query would have an empty set of
> result columns, you could with equal force argue that these columns
> satisfy the condition or not, because the members of the empty
> set have all the properties you desire.
The semantics of a zero-column foreign key seem even less well
defined.
regards, tom lane
On 2013.03.25 1:17 AM, Albe Laurenz wrote: > Darren Duncan wrote: >> From my usage and >> http://www.postgresql.org/docs/9.2/interactive/sql-createtable.html I see that >> Postgres requires constraints like unique (and primary) keys, and foreign keys, >> to range over at least 1 attribute/column. >> >> I propose that this be generalized so that constraints may instead be nullary, >> that is, range over zero or more attributes/columns instead. > > I don't think that a new way to force single-row tables is > a good enough use case, and "for completeness' sake" is only > a good argument if it simplifies things and makes them more > comprehensible, which I don't think is the case here. > > The semantics seem unclear to me: > The standard defines UNIQUE on the basis of the "UNIQUE predicate": > <unique predicate> ::= UNIQUE <table subquery> > and states: > 1) Let T be the result of the <table subquery>. > 2) If there are no two rows in T such that the value of each column > in one row is non-null and is not distinct > from the value of the corresponding column in the other row, > then the result of the <unique predicate> is > *True*; otherwise, the result of the <unique predicate> is *False*. > > Since an imagined zero-column query would have an empty set of > result columns, you could with equal force argue that these columns > satisfy the condition or not, because the members of the empty > set have all the properties you desire. > > So I see no compelling argument that such a UNIQUE constraint > would force a single-row table. I do see that compelling argument, and it has to do with identities. The above definition of "UNIQUE predicate" says that the UNIQUE predicate is FALSE iff, for every pair of rows in T, the 2 rows of any pair are the same. Further, 2 rows are the same iff, for every corresponding column, the values in both rows are the same. Further, 2 such values are the same iff they are both not null and are mutually not distinct. So, determining if 2 rows are the same involves an iteration of dyadic logical AND over the predicates for each column comparison. Now logical AND has an identity value, which is TRUE, because "TRUE AND p" (and "p AND TRUE") results in "p" for all "p". Therefore, any 2 rows with zero columns each are the same. Since any 2 rows with zero columns are the same, the "UNIQUE predicate" is FALSE any time there is more than 1 row in a table. Hence, a UNIQUE constraint over zero columns signifies a row-comparison predicate that unconditionally results in TRUE, and so no two rows at all would be allowed in the table with that constraint at once, thus restricting the table to at most one row. Does anyone agree or disagree with this logic? And can the definition of "UNIQUE predicate" reasonably be read the way I stated? And if the SQL definition is ambiguous, could it reasonably be said that the behavior I stated is reasonable for Postgres and would match expectations of people using it? Saying 2 empty rows are the same makes as much sense as saying 2 empty arrays are the same, does it not? > The desired effect can be had today with a unique index: > > CREATE TABLE singleton (id integer); > CREATE UNIQUE INDEX singleton_idx ON singleton((1)); Okay, that is helpful, and less of a kludge than what I was doing, but it is still a kludge compared to what I'm proposing, which I see as elegant. -- Darren Duncan
On 26 March 2013 05:04, Darren Duncan <darren@darrenduncan.net> wrote: > On 2013.03.25 1:17 AM, Albe Laurenz wrote: >> The desired effect can be had today with a unique index: >> >> CREATE TABLE singleton (id integer); >> CREATE UNIQUE INDEX singleton_idx ON singleton((1)); > > Okay, that is helpful, and less of a kludge than what I was doing, but it is > still a kludge compared to what I'm proposing, which I see as elegant. > FWIW I think an index on (TRUE) expresses the intention more clearly than an index on () would. I don't have any objection to the purely logical sense of the zero-attribute key, but it's hard to see the pragmatic value. Cheers, BJ
Brendan Jurd <direvus@gmail.com> writes:
> On 26 March 2013 05:04, Darren Duncan <darren@darrenduncan.net> wrote:
>> On 2013.03.25 1:17 AM, Albe Laurenz wrote:
>>> The desired effect can be had today with a unique index:
>>> CREATE TABLE singleton (id integer);
>>> CREATE UNIQUE INDEX singleton_idx ON singleton((1));
>> Okay, that is helpful, and less of a kludge than what I was doing, but it is
>> still a kludge compared to what I'm proposing, which I see as elegant.
> FWIW I think an index on (TRUE) expresses the intention more clearly
> than an index on () would.
> I don't have any objection to the purely logical sense of the
> zero-attribute key, but it's hard to see the pragmatic value.
Yeah. Other problems include the potential introduction of
non-spec-compliant entries in the information_schema, breakage of
clients that look at the catalogs and aren't expecting to find indexes
of this sort, etc etc. (Not even to mention the bugs that would be
induced in the backend itself; eg I doubt most of the index access
methods would cope with this without changes.)
Given that there's already a perfectly nice albeit non-spec-compliant
way to get the effect, I can't see the value of doing a lot of work
to create a different non-spec-compliant way.
regards, tom lane
Darren Duncan wrote: >> The standard defines UNIQUE on the basis of the "UNIQUE predicate": >> <unique predicate> ::= UNIQUE <table subquery> >> and states: >> 1) Let T be the result of the <table subquery>. >> 2) If there are no two rows in T such that the value of each column >> in one row is non-null and is not distinct >> from the value of the corresponding column in the other row, >> then the result of the <unique predicate> is >> *True*; otherwise, the result of the <unique predicate> is *False*. >> >> Since an imagined zero-column query would have an empty set of >> result columns, you could with equal force argue that these columns >> satisfy the condition or not, because the members of the empty >> set have all the properties you desire. >> >> So I see no compelling argument that such a UNIQUE constraint >> would force a single-row table. > > I do see that compelling argument, and it has to do with identities. > > The above definition of "UNIQUE predicate" says that the UNIQUE predicate is > FALSE iff, for every pair of rows in T, the 2 rows of any pair are the same. I don't understand that sentence. I would say that it is FALSE iff there exist two rows in T that satisy: a) each column in both rows is not-null b) each column in one of the rows is not distinct from the corresponding column in the other row > Further, 2 rows are the same iff, for every corresponding column, the values in > both rows are the same. Further, 2 such values are the same iff they are both > not null and are mutually not distinct. > > So, determining if 2 rows are the same involves an iteration of dyadic logical > AND over the predicates for each column comparison. Now logical AND has an > identity value, which is TRUE, because "TRUE AND p" (and "p AND TRUE") results > in "p" for all "p". Therefore, any 2 rows with zero columns each are the same. > > Since any 2 rows with zero columns are the same, the "UNIQUE predicate" is FALSE > any time there is more than 1 row in a table. > > Hence, a UNIQUE constraint over zero columns signifies a row-comparison > predicate that unconditionally results in TRUE, and so no two rows at all would > be allowed in the table with that constraint at once, thus restricting the table > to at most one row. > > Does anyone agree or disagree with this logic? Yes :^) You could use the same kind of argument like this: UNIQUE is true iff any two rows in T satisfy for each column: the column in row 1 is null OR the column in row 2 is null OR the column in row 1 is distinct from the column in row 2 Now you you iterate your logical AND over this predicate for all columns and come up with TRUE since there are none. Consequently UNIQUE is satisfied, no matter how many rows there are. In a nutshell: All members of the empty set satisfy p, but also: all members of the empty set satisfy the negation of p. You can use this technique to make anything plausible. Yours, Laurenz Albe
On 2013.03.26 1:40 AM, Albe Laurenz wrote: > Darren Duncan wrote: >> So, determining if 2 rows are the same involves an iteration of dyadic logical >> AND over the predicates for each column comparison. Now logical AND has an >> identity value, which is TRUE, because "TRUE AND p" (and "p AND TRUE") results >> in "p" for all "p". Therefore, any 2 rows with zero columns each are the same. >> >> Since any 2 rows with zero columns are the same, the "UNIQUE predicate" is FALSE >> any time there is more than 1 row in a table. >> >> Does anyone agree or disagree with this logic? > > Yes :^) > > You could use the same kind of argument like this: > > UNIQUE is true iff any two rows in T satisfy for each column: > the column in row 1 is null OR the column in row 2 is null OR > the column in row 1 is distinct from the column in row 2 > > Now you you iterate your logical AND over this predicate > for all columns and come up with TRUE since there are none. > Consequently UNIQUE is satisfied, no matter how many rows there are. > > In a nutshell: > All members of the empty set satisfy p, but also: > all members of the empty set satisfy the negation of p. > > You can use this technique to make anything plausible. Consider the context however. We're talking about a UNIQUE constraint and so what we want to do is prevent the existence of multiple tuples in a relation that are the same for some defined subset of their attributes. I would argue that logically, and commonsensically, two tuples with no attributes are the same, and hence a set of distinct tuples having zero attributes could have no more than one member, and so a UNIQUE constraint over zero attributes would say the relation can't have more than one tuple. So unless someone wants to argue that two tuples with no attributes are not the same, my interpretation makes more sense and is clearly the one to follow. -- Darren Duncan
<div class="moz-cite-prefix">On 27/03/13 06:14, Darren Duncan wrote:<br /></div><blockquote cite="mid:5151D77B.5050801@darrenduncan.net"type="cite">On 2013.03.26 1:40 AM, Albe Laurenz wrote: <br /><blockquote type="cite">DarrenDuncan wrote: <br /><blockquote type="cite">So, determining if 2 rows are the same involves an iterationof dyadic logical <br /> AND over the predicates for each column comparison. Now logical AND has an <br /> identityvalue, which is TRUE, because "TRUE AND p" (and "p AND TRUE") results <br /> in "p" for all "p". Therefore, any2 rows with zero columns each are the same. <br /><br /> Since any 2 rows with zero columns are the same, the "UNIQUEpredicate" is FALSE <br /> any time there is more than 1 row in a table. <br /><br /> Does anyone agree or disagreewith this logic? <br /></blockquote><br /> Yes :^) <br /><br /> You could use the same kind of argument like this:<br /><br /> UNIQUE is true iff any two rows in T satisfy for each column: <br /> the column in row 1 is null OR thecolumn in row 2 is null OR <br /> the column in row 1 is distinct from the column in row 2 <br /><br /> Now you you iterateyour logical AND over this predicate <br /> for all columns and come up with TRUE since there are none. <br /> ConsequentlyUNIQUE is satisfied, no matter how many rows there are. <br /><br /> In a nutshell: <br /> All members of theempty set satisfy p, but also: <br /> all members of the empty set satisfy the negation of p. <br /><br /> You can usethis technique to make anything plausible. <br /></blockquote><br /> Consider the context however. We're talking abouta UNIQUE constraint and so what we want to do is prevent the existence of multiple tuples in a relation that are thesame for some defined subset of their attributes. I would argue that logically, and commonsensically, two tuples withno attributes are the same, and hence a set of distinct tuples having zero attributes could have no more than one member,and so a UNIQUE constraint over zero attributes would say the relation can't have more than one tuple. So unlesssomeone wants to argue that two tuples with no attributes are not the same, my interpretation makes more sense andis clearly the one to follow. -- Darren Duncan <br /><br /><br /><br /></blockquote><font size="-1">Hmm as a user, I wouldlike at most one row with empty fields covered by <font size="-1">a unique index</font></font>.<br /><br /> Logicalarguments to the contrary, remind me of the joke of the school boy who told his unlearned father that he had learntlogic and could prove that his father actually had 3 fish in his basket despite both seeing only 2 fish. His unlearnedfather did not try to argue, and simply said: well your mother can have the first fish, I'll have the second, andthat his learned son could have the third...<br /><br /><br />
Darren Duncan wrote:
> Consider the context however. We're talking about a UNIQUE constraint and so
> what we want to do is prevent the existence of multiple tuples in a relation
> that are the same for some defined subset of their attributes. I would argue
> that logically, and commonsensically, two tuples with no attributes are the
> same, and hence a set of distinct tuples having zero attributes could have no
> more than one member, and so a UNIQUE constraint over zero attributes would say
> the relation can't have more than one tuple. So unless someone wants to argue
> that two tuples with no attributes are not the same, my interpretation makes
> more sense and is clearly the one to follow. -- Darren Duncan
What you propose is not an interpretation, but an extension
of the standard. I'm not certain about that "clearly" either;
intuition is a questionable guideline when it comes to the
standard:
SELECT 'Things that feel equal are ' || CASE WHEN NULL = NULL THEN '' ELSE 'not always ' END || 'equal';
?column?
---------------------------------------------Things that feel equal are not always equal
(1 row)
Yours,
Laurenz Albe