Re: [HACKERS] adding support for zero-attribute unique/etc keys
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 -- 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] adding support for zero-attribute unique/etc keys
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 -- 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] adding support for zero-attribute unique/etc keys
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 -- 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] adding support for zero-attribute unique/etc keys
On 27/03/13 06:14, Darren Duncan wrote: 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 Hmm as a user, I would like at most one row with empty fields covered by a unique index. Logical arguments to the contrary, remind me of the joke of the school boy who told his unlearned father that he had learnt logic and could prove that his father actually had 3 fish in his basket despite both seeing only 2 fish. His unlearned father did not try to argue, and simply said: well your mother can have the first fish, I'll have the second, and that his learned son could have the third...
Re: [HACKERS] adding support for zero-attribute unique/etc keys
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 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. 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 -- 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] adding support for zero-attribute unique/etc keys
Albe Laurenz laurenz.a...@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 -- 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] adding support for zero-attribute unique/etc keys
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 -- 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] adding support for zero-attribute unique/etc keys
On 26 March 2013 05:04, Darren Duncan dar...@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 -- 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] adding support for zero-attribute unique/etc keys
Brendan Jurd dire...@gmail.com writes: On 26 March 2013 05:04, Darren Duncan dar...@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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] 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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
