Re: [HACKERS] [PATCH] Exorcise "zero-dimensional" arrays (Was: Re: Should array_length() Return NULL)
On Thu, Jun 20, 2013 at 06:28:07PM -0700, Josh Berkus wrote: > Bruce, > > > Well, sometimes we underestimate the impact of changes, sometimes we > > overestimate. The big problem is weighing the short-term problems of > > change but not the long-term benefit of a change. This array problem > > goes back to at least 2008: > > > > http://www.postgresql.org/message-id/28026.1224611...@sss.pgh.pa.us > > > > so we have at least five years of confusion by not changing it then. I > > am not saying we need to change it, but do think we need to weigh both > > issues. > > As much as I hate the current behavior (my first response was "yeah, fix > those babies!"), I think we don't have a choice about creating new > function names and then waiting three years to deprecate the old ones. > We really can't afford to put obstacles in the way of people upgrading, > especially over an issue as minor as this one. Perhaps we need to mark the TODO item as "will not fix". -- Bruce Momjian http://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- 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] [PATCH] Exorcise "zero-dimensional" arrays (Was: Re: Should array_length() Return NULL)
Bruce, > Well, sometimes we underestimate the impact of changes, sometimes we > overestimate. The big problem is weighing the short-term problems of > change but not the long-term benefit of a change. This array problem > goes back to at least 2008: > > http://www.postgresql.org/message-id/28026.1224611...@sss.pgh.pa.us > > so we have at least five years of confusion by not changing it then. I > am not saying we need to change it, but do think we need to weigh both > issues. As much as I hate the current behavior (my first response was "yeah, fix those babies!"), I think we don't have a choice about creating new function names and then waiting three years to deprecate the old ones. We really can't afford to put obstacles in the way of people upgrading, especially over an issue as minor as this one. -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com -- 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] [PATCH] Exorcise "zero-dimensional" arrays (Was: Re: Should array_length() Return NULL)
On Thu, Jun 20, 2013 at 07:13:48PM -0500, Merlin Moncure wrote: > On Thu, Jun 20, 2013 at 6:40 PM, Bruce Momjian wrote: > >> Kinda -- what I'm saying is you just don't go around changing function > >> behaviors to make them 'better' unless the affected behavior was > >> specifically reserved as undefined. The fact is nobody knows how many > >> users will be affected and the extent of the ultimate damage (pro tip: > >> it's always more and worse than expected); I'm astonished it's even > >> being considered. > > > > Well, I think the question is how many people have such arrays that will > > be effected. If we don't do something, we live with this odd behavior > > forever. We have been willing to make some bold decisions in the past > > to improve user experience, and it mostly has worked out well. I > > disagree that it is always worse than expected. > > Well, you can have the last word (although 'bold' was an interesting > word choice, heh) -- I feel guilty enough about beating up Brendan > already. I feel this way every time compatibility changes come up, so > it's nothing specific to this patch really. Well, sometimes we underestimate the impact of changes, sometimes we overestimate. The big problem is weighing the short-term problems of change but not the long-term benefit of a change. This array problem goes back to at least 2008: http://www.postgresql.org/message-id/28026.1224611...@sss.pgh.pa.us so we have at least five years of confusion by not changing it then. I am not saying we need to change it, but do think we need to weigh both issues. -- Bruce Momjian http://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- 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] [PATCH] Exorcise "zero-dimensional" arrays (Was: Re: Should array_length() Return NULL)
On Thu, Jun 20, 2013 at 6:40 PM, Bruce Momjian wrote: >> Kinda -- what I'm saying is you just don't go around changing function >> behaviors to make them 'better' unless the affected behavior was >> specifically reserved as undefined. The fact is nobody knows how many >> users will be affected and the extent of the ultimate damage (pro tip: >> it's always more and worse than expected); I'm astonished it's even >> being considered. > > Well, I think the question is how many people have such arrays that will > be effected. If we don't do something, we live with this odd behavior > forever. We have been willing to make some bold decisions in the past > to improve user experience, and it mostly has worked out well. I > disagree that it is always worse than expected. Well, you can have the last word (although 'bold' was an interesting word choice, heh) -- I feel guilty enough about beating up Brendan already. I feel this way every time compatibility changes come up, so it's nothing specific to this patch really. merlin -- 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] [PATCH] Exorcise "zero-dimensional" arrays (Was: Re: Should array_length() Return NULL)
On Thu, Jun 20, 2013 at 03:33:24PM -0500, Merlin Moncure wrote: > On Thu, Jun 20, 2013 at 2:58 PM, Bruce Momjian wrote: > > On Thu, Jun 13, 2013 at 11:57:27AM -0500, Merlin Moncure wrote: > >> >> But, couldn't that be solved by deprecating that function and > >> >> providing a more sensible alternatively named version? > >> > > >> > And what would you name that function? array_dims2? I can't think of > >> > a name that makes the difference in behaviour apparent. Can you > >> > imagine the documentation for that? > >> > >> I don't know the answer to that, but I think it's hard to argue that > >> deprecating and documenting a few functions is a heavier burden on > >> your users than having to sift through older arcane code before > >> upgrading to the latest version of the database. We're not the only > >> ones stuck with lousy old functions (C finally ditched gets() in the > >> 2011 standard). I also happen to think the current array_api function > >> names are not particularly great (especially array_upper/array_lower) > >> so I won't shed too many tears. > > > > Sorry to be late on this, but are you saying people have code that is > > testing: > > > > select array_dims('{}'::int[]) > > > > for a NULL return, and they would need to change that to test for zero? > > Kinda -- what I'm saying is you just don't go around changing function > behaviors to make them 'better' unless the affected behavior was > specifically reserved as undefined. The fact is nobody knows how many > users will be affected and the extent of the ultimate damage (pro tip: > it's always more and worse than expected); I'm astonished it's even > being considered. Well, I think the question is how many people have such arrays that will be effected. If we don't do something, we live with this odd behavior forever. We have been willing to make some bold decisions in the past to improve user experience, and it mostly has worked out well. I disagree that it is always worse than expected. -- Bruce Momjian http://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- 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] [PATCH] Exorcise "zero-dimensional" arrays (Was: Re: Should array_length() Return NULL)
On Thu, Jun 20, 2013 at 2:58 PM, Bruce Momjian wrote: > On Thu, Jun 13, 2013 at 11:57:27AM -0500, Merlin Moncure wrote: >> >> But, couldn't that be solved by deprecating that function and >> >> providing a more sensible alternatively named version? >> > >> > And what would you name that function? array_dims2? I can't think of >> > a name that makes the difference in behaviour apparent. Can you >> > imagine the documentation for that? >> >> I don't know the answer to that, but I think it's hard to argue that >> deprecating and documenting a few functions is a heavier burden on >> your users than having to sift through older arcane code before >> upgrading to the latest version of the database. We're not the only >> ones stuck with lousy old functions (C finally ditched gets() in the >> 2011 standard). I also happen to think the current array_api function >> names are not particularly great (especially array_upper/array_lower) >> so I won't shed too many tears. > > Sorry to be late on this, but are you saying people have code that is > testing: > > select array_dims('{}'::int[]) > > for a NULL return, and they would need to change that to test for zero? Kinda -- what I'm saying is you just don't go around changing function behaviors to make them 'better' unless the affected behavior was specifically reserved as undefined. The fact is nobody knows how many users will be affected and the extent of the ultimate damage (pro tip: it's always more and worse than expected); I'm astonished it's even being considered. merlin -- 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] [PATCH] Exorcise "zero-dimensional" arrays (Was: Re: Should array_length() Return NULL)
On Thu, Jun 13, 2013 at 11:57:27AM -0500, Merlin Moncure wrote: > >> But, couldn't that be solved by deprecating that function and > >> providing a more sensible alternatively named version? > > > > And what would you name that function? array_dims2? I can't think of > > a name that makes the difference in behaviour apparent. Can you > > imagine the documentation for that? > > I don't know the answer to that, but I think it's hard to argue that > deprecating and documenting a few functions is a heavier burden on > your users than having to sift through older arcane code before > upgrading to the latest version of the database. We're not the only > ones stuck with lousy old functions (C finally ditched gets() in the > 2011 standard). I also happen to think the current array_api function > names are not particularly great (especially array_upper/array_lower) > so I won't shed too many tears. Sorry to be late on this, but are you saying people have code that is testing: select array_dims('{}'::int[]) for a NULL return, and they would need to change that to test for zero? -- Bruce Momjian http://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- 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] [PATCH] Exorcise "zero-dimensional" arrays (Was: Re: Should array_length() Return NULL)
On 14 June 2013 03:53, David E. Wheeler wrote: > Similar things should have dissimilar names. I propose: > > > > Old |New > --+-- > array_dims | array_desc array_bounds? > array_ndims | array_depth > array_length | array_size > array_lower | array_start > array_upper | array_finish > > The last two are meh, but it’s a place to start… I think that even with the most dissimilar names we can come up with, this is going to confuse people. But it is still better than doing nothing. I wonder whether, if we go in this direction, we could still use some of the work I did on deprecating zero-D arrays. Let's say the old functions keep doing what they do now, and we teach them to treat all empty arrays the same way they currently treat zero-D arrays (return NULL). The new functions treat zero-D arrays as though they were 1-D empty with default bounds, and we add CARDINALITY per ArrayGetNItems. This way, applications would not be broken by upgrading, and we'd be giving people a way to opt-in to a better API. 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] [PATCH] Exorcise "zero-dimensional" arrays (Was: Re: Should array_length() Return NULL)
On Jun 12, 2013, at 8:00 PM, Brendan Jurd wrote: > array_dims - Returns the dimensions of the array, unless it is empty > in which case NULL. > array_proper_dims - Returns the dimensions of the array. > array_ndims - Returns the number of dimension, unless it is empty in > which case NULL. > array_proper_ndims - Returns the number of dimensions. > > ... and so on for _length, _upper and _lower. Similar things should have dissimilar names. I propose: Old |New --+-- array_dims | array_desc array_ndims | array_depth array_length | array_size array_lower | array_start array_upper | array_finish The last two are meh, but it’s a place to start… David -- 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] [PATCH] Exorcise "zero-dimensional" arrays (Was: Re: Should array_length() Return NULL)
On Wed, Jun 12, 2013 at 10:00 PM, Brendan Jurd wrote: > On 13 June 2013 04:26, Merlin Moncure wrote: >> On Wed, Jun 12, 2013 at 1:20 PM, Tom Lane wrote: >>> Josh Berkus writes: On 06/12/2013 11:01 AM, Tom Lane wrote: > I'm going to be disappointed if all we can get out of this is > a cardinality() function, and nothing is done about the empty-array > semantics. > > I would be disappointed too, but on the other hand, CARDINALITY is > required by the spec and anything would be better than nothing. certainly. >> But, couldn't that be solved by deprecating that function and >> providing a more sensible alternatively named version? > > And what would you name that function? array_dims2? I can't think of > a name that makes the difference in behaviour apparent. Can you > imagine the documentation for that? I don't know the answer to that, but I think it's hard to argue that deprecating and documenting a few functions is a heavier burden on your users than having to sift through older arcane code before upgrading to the latest version of the database. We're not the only ones stuck with lousy old functions (C finally ditched gets() in the 2011 standard). I also happen to think the current array_api function names are not particularly great (especially array_upper/array_lower) so I won't shed too many tears. merlin -- 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] [PATCH] Exorcise "zero-dimensional" arrays (Was: Re: Should array_length() Return NULL)
On 13 June 2013 04:26, Merlin Moncure wrote: > On Wed, Jun 12, 2013 at 1:20 PM, Tom Lane wrote: >> Josh Berkus writes: >>> On 06/12/2013 11:01 AM, Tom Lane wrote: I'm going to be disappointed if all we can get out of this is a cardinality() function, and nothing is done about the empty-array semantics. I would be disappointed too, but on the other hand, CARDINALITY is required by the spec and anything would be better than nothing. >> Meh. Robert was pretty vocal about it, but it wasn't clear to me that >> his was the majority opinion, and in any case there wasn't much >> consideration given to compromises falling somewhere between "no >> changes" and the rather drastic solution Brendan proposed. I'm all for looking into possible compromises, and will happily take any improvements to this mess I think I can get past the compatibility maximalist caucus. >> regression=# select array_dims('{}'::int[]) is null; >> ?column? >> -- >> t >> (1 row) >> >> Whatever you think the dimensions of that are, surely they're not >> unknown. I don't think anyone has actually tried to defend the behaviour of the array functions w.r.t. empty arrays. Even the opponents of the original proposal agreed that the behaviour was silly, they just didn't want to fix it, on account of the upgrade burden. > But, couldn't that be solved by deprecating that function and > providing a more sensible alternatively named version? And what would you name that function? array_dims2? I can't think of a name that makes the difference in behaviour apparent. Can you imagine the documentation for that? array_dims - Returns the dimensions of the array, unless it is empty in which case NULL. array_proper_dims - Returns the dimensions of the array. array_ndims - Returns the number of dimension, unless it is empty in which case NULL. array_proper_ndims - Returns the number of dimensions. ... and so on for _length, _upper and _lower. 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] [PATCH] Exorcise "zero-dimensional" arrays (Was: Re: Should array_length() Return NULL)
On 2013.06.12 10:11 AM, Josh Berkus wrote: Multidim arrays are why we can't have nice things. Yeah, I think that was not our best moment. :-( Actually, if you think about it, we've never had "arrays" in PostgreSQL ... we have always had matrixes. If you think about things that way, most of the current functionality makes sense. If the type system were changed so that arrays were were always just one-dimensional, you can define your matrix simply as a binary relation type whose primary key attribute has the type of a fixed-length array of integers, where the number of elements in the array is the number of dimensions in the matrix, and the array elements themselves defined the coordinates in the matrix. This design confers a number of benefits. Also, the case of the zero-dimension matrix needs no special treatment; the key array has zero elements. Would that not work? -- 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] [PATCH] Exorcise "zero-dimensional" arrays (Was: Re: Should array_length() Return NULL)
On Wed, Jun 12, 2013 at 1:20 PM, Tom Lane wrote: > Josh Berkus writes: >> On 06/12/2013 11:01 AM, Tom Lane wrote: >>> I'm going to be disappointed if all we can get out of this is >>> a cardinality() function, and nothing is done about the empty-array >>> semantics. > >> Well, we can't change the zero-dim behavior without breaking backwards >> compatibility. And enough people piled on to say NO to that, that it >> went by the wayside. > > Meh. Robert was pretty vocal about it, but it wasn't clear to me that > his was the majority opinion, and in any case there wasn't much > consideration given to compromises falling somewhere between "no > changes" and the rather drastic solution Brendan proposed. For > instance, it's really hard to believe that this is a good thing: > > regression=# select array_dims('{}'::int[]) is null; > ?column? > -- > t > (1 row) > > Whatever you think the dimensions of that are, surely they're not > unknown. But, couldn't that be solved by deprecating that function and providing a more sensible alternatively named version? merlin -- 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] [PATCH] Exorcise "zero-dimensional" arrays (Was: Re: Should array_length() Return NULL)
Josh Berkus writes: > On 06/12/2013 11:01 AM, Tom Lane wrote: >> I'm going to be disappointed if all we can get out of this is >> a cardinality() function, and nothing is done about the empty-array >> semantics. > Well, we can't change the zero-dim behavior without breaking backwards > compatibility. And enough people piled on to say NO to that, that it > went by the wayside. Meh. Robert was pretty vocal about it, but it wasn't clear to me that his was the majority opinion, and in any case there wasn't much consideration given to compromises falling somewhere between "no changes" and the rather drastic solution Brendan proposed. For instance, it's really hard to believe that this is a good thing: regression=# select array_dims('{}'::int[]) is null; ?column? -- t (1 row) Whatever you think the dimensions of that are, surely they're not unknown. 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] [PATCH] Exorcise "zero-dimensional" arrays (Was: Re: Should array_length() Return NULL)
On 06/12/2013 11:01 AM, Tom Lane wrote: > Arguably, the only thing wrong with multidim arrays is that they're not > what the SQL standard asks for. However, the original point in this > thread was that we have some very bizarre corner-case behavior for empty > arrays. I'm going to be disappointed if all we can get out of this is > a cardinality() function, and nothing is done about the empty-array > semantics. Well, we can't change the zero-dim behavior without breaking backwards compatibility. And enough people piled on to say NO to that, that it went by the wayside. -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com -- 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] [PATCH] Exorcise "zero-dimensional" arrays (Was: Re: Should array_length() Return NULL)
Josh Berkus writes: > Actually, if you think about it, we've never had "arrays" in PostgreSQL > ... we have always had matrixes. If you think about things that way, > most of the current functionality makes sense. Arguably, the only thing wrong with multidim arrays is that they're not what the SQL standard asks for. However, the original point in this thread was that we have some very bizarre corner-case behavior for empty arrays. I'm going to be disappointed if all we can get out of this is a cardinality() function, and nothing is done about the empty-array semantics. 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] [PATCH] Exorcise "zero-dimensional" arrays (Was: Re: Should array_length() Return NULL)
>> Multidim arrays are why we can't have nice things. > > Yeah, I think that was not our best moment. :-( They were one of those hacks which looked really clever at the time, but proved not to be so. Unfortunately, they *are* useful, and are being used; I use MD arrays all the time to push data in and out of PL/R., and now that we have a JSON type I'm using them to generate arrays-of-arrays using JSON conversion functions. I'm sure many others are doing the same, so there's no killing the feature. Actually, if you think about it, we've never had "arrays" in PostgreSQL ... we have always had matrixes. If you think about things that way, most of the current functionality makes sense. -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com -- 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] [PATCH] Exorcise "zero-dimensional" arrays (Was: Re: Should array_length() Return NULL)
On Wed, Jun 12, 2013 at 5:05 AM, Brendan Jurd wrote: > On 12 June 2013 18:22, Dean Rasheed wrote: >> +1 for having a function to return the total number of elements in an >> array, because that's something that's currently missing from SQL. >> >> However, I think that CARDINALITY() should be that function. >> >> I'm not convinced that having CARDINALITY() return the length of the >> first dimension is more spec-compatible, since our multi-dimensional >> arrays aren't nested arrays, and it seems unlikely that they ever will >> be. I'd argue that it's at least equally spec-compatible to have >> CARDINALITY() return the total number of elements in the array, if you >> think of a multi-dimensional array as a collection of elements >> arranged in a regular pattern. > > It's true that our multidims aren't nested, but they are the nearest > thing we have. If we want to keep the door open for future attempts > to nudge multidim arrays into closer approximation of nested arrays, > it would be better to have the nested interpretation of CARDINALITY. I think there's just about zero chance of something like that ever happening. The problem is that our type system just can't support it. A function or operator that takes an array needs to declare whether it's going to return an array or whether it's going to return the base type. It can't decide to return one or the other at run-time depending on the dimensionality of the array. For this to really work, we'd need the number of dimensions to be baked into the array type. The obvious implementation would be to have N array types per base type rather than 1, each with a different number of dimensions. Then a subscripting function which took a 1-dimensional array could return anyelement, and the similarly named function which took a 2-dimensional array could return a 1-dimensional array. I believe the reason it wasn't done this way initially was because of pg_type bloat; having 6 extra type definitions for every type we support is unappealing. We could force them to be explicitly declared as we do for range types. Or we could rewrite a whole lotta code to understand a "type" as something more complex than "an OID from pg_type", so that we don't need pre-defined entries in pg_type for array types in the first place. But none of these things are nudges. Making any real improvement in this area is going to take major surgery, not a nudge. > Multidim arrays are why we can't have nice things. Yeah, I think that was not our best moment. :-( -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- 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] [PATCH] Exorcise "zero-dimensional" arrays (Was: Re: Should array_length() Return NULL)
On 12 June 2013 18:22, Dean Rasheed wrote: > +1 for having a function to return the total number of elements in an > array, because that's something that's currently missing from SQL. > > However, I think that CARDINALITY() should be that function. > > I'm not convinced that having CARDINALITY() return the length of the > first dimension is more spec-compatible, since our multi-dimensional > arrays aren't nested arrays, and it seems unlikely that they ever will > be. I'd argue that it's at least equally spec-compatible to have > CARDINALITY() return the total number of elements in the array, if you > think of a multi-dimensional array as a collection of elements > arranged in a regular pattern. It's true that our multidims aren't nested, but they are the nearest thing we have. If we want to keep the door open for future attempts to nudge multidim arrays into closer approximation of nested arrays, it would be better to have the nested interpretation of CARDINALITY. Given what we've just gone through with array_length, it seems that once we select a behaviour for CARDINALITY, we will be stuck with it permanently. The problem with thinking of our multidim arrays as just a weirdly crumpled arrangement of a single collection, is that we've already abused the nesting syntax for declaring them. > Also, the spec describes CARDINALITY() and UNNEST() using the same > language, and I think it's implicit in a couple of places that > CARDINALITY() should match the number of rows returned by UNNEST(), > which we've already implemented as fully unnesting every element. > > We're about to add ORDINALITY to UNNEST(), and to me it would be very > odd to have the resulting maximum ordinality exceed the array's > cardinality. Yeah, that makes sense. Well the good news is that either way, CARDINALITY will do what people want in the most common case where the array is one-dimensional. Multidim arrays are why we can't have nice things. 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] [PATCH] Exorcise "zero-dimensional" arrays (Was: Re: Should array_length() Return NULL)
On 12 June 2013 04:50, David E. Wheeler wrote: > On Jun 11, 2013, at 3:09 PM, Brendan Jurd wrote: > >> There have been attempts to add a cardinality function in the past, as >> it is required by the SQL spec, but these attempts have stalled when >> trying to decide how it should handle multidim arrays. Having it >> return the length of the first dimension is the more spec-compatible >> way to go, but some folks argued that it should work as >> ArrayGetNItems, because we don't already have a function for that at >> the SQL level. Therefore I propose we add cardinality() per the spec, >> and another function to expose ArrayGetNItems. >> >> And that's about where we got to, when the whole discussion was put on >> a time-out to make room for the beta. >> >> I am withdrawing the original zero-D patch in favour of the proposed >> new functions. If you have an opinion about that, please do chime in. >> Depending on how that goes I may post a patch implementing my new >> proposal in the next few days. > > +1 to this proposal. Modulo function names, perhaps. I don’t much care what > they're called, as long as the work as you describe here. > +1 for having a function to return the total number of elements in an array, because that's something that's currently missing from SQL. However, I think that CARDINALITY() should be that function. I'm not convinced that having CARDINALITY() return the length of the first dimension is more spec-compatible, since our multi-dimensional arrays aren't nested arrays, and it seems unlikely that they ever will be. I'd argue that it's at least equally spec-compatible to have CARDINALITY() return the total number of elements in the array, if you think of a multi-dimensional array as a collection of elements arranged in a regular pattern. Also, the spec describes CARDINALITY() and UNNEST() using the same language, and I think it's implicit in a couple of places that CARDINALITY() should match the number of rows returned by UNNEST(), which we've already implemented as fully unnesting every element. We're about to add ORDINALITY to UNNEST(), and to me it would be very odd to have the resulting maximum ordinality exceed the array's cardinality. Regards, Dean -- 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] [PATCH] Exorcise "zero-dimensional" arrays (Was: Re: Should array_length() Return NULL)
On Jun 11, 2013, at 3:09 PM, Brendan Jurd wrote: > There have been attempts to add a cardinality function in the past, as > it is required by the SQL spec, but these attempts have stalled when > trying to decide how it should handle multidim arrays. Having it > return the length of the first dimension is the more spec-compatible > way to go, but some folks argued that it should work as > ArrayGetNItems, because we don't already have a function for that at > the SQL level. Therefore I propose we add cardinality() per the spec, > and another function to expose ArrayGetNItems. > > And that's about where we got to, when the whole discussion was put on > a time-out to make room for the beta. > > I am withdrawing the original zero-D patch in favour of the proposed > new functions. If you have an opinion about that, please do chime in. > Depending on how that goes I may post a patch implementing my new > proposal in the next few days. +1 to this proposal. Modulo function names, perhaps. I don’t much care what they're called, as long as the work as you describe here. Best, David Array Complainer -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers