Review: listagg aggregate
in [PgSql]
|
From: Pavel Stehule on 28 Jan 2010 10:32 2010/1/28 Robert Haas <robertmhaas(a)gmail.com>: > On Thu, Jan 28, 2010 at 9:01 AM, Pavel Stehule <pavel.stehule(a)gmail.com> wrote: >> simplest could not be a best. There have to be only a const >> expression. But we have not possibility to check it in pg. > > Well... that's an entirely arbitrary limitation. Â I admit that it > doesn't seem likely that someone would want to have a variable > delimiter, but putting extra effort and code complexity into > preventing it seems pointless. It is only a few lines with zero complexity. The main issue of Takahiro proposal is "unclean" behave. we can have a content c1 c2 ----------- c11, c12, c21, c22 and result of string_agg(c1, c2) have to be ?? c11 c12 c21 or c11 c22 c21 ?? What if some content of c2 will be NULL ?? I checked oracle. Oracle doesn't allow variable as delimiter. We can't check it. But we can fix first value and using it as constant. More - Takahiro proposal has one performance gain. It have to deTOAST separator value in every cycle. Takahiro has true - we can store length of separator and we can add separator to cumulative value as binary value. I prefer original behave with note in documentation - so as separator is used a value on first row, when is used expression and not constant. Regards Pavel > > ...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: Tom Lane on 28 Jan 2010 10:56 Robert Haas <robertmhaas(a)gmail.com> writes: > On Thu, Jan 28, 2010 at 9:01 AM, Pavel Stehule <pavel.stehule(a)gmail.com> wrote: >> simplest could not be a best. There have to be only a const >> expression. But we have not possibility to check it in pg. > Well... that's an entirely arbitrary limitation. I admit that it > doesn't seem likely that someone would want to have a variable > delimiter, but putting extra effort and code complexity into > preventing it seems pointless. Yeah. The real issue here is that in some cases you'd like to have non-aggregated parameters to an aggregate, but SQL has no notation to express that. I think Pavel's underlying complaint is that if the delimiter argument isn't constant, then we're exposing an implementation dependency in terms of just which values get separated by which delimiters. The most practical implementation seems to be that the first-call delimiter isn't actually used at all, and on subsequent calls the delimiter *precedes* the associated value, which is a bit surprising given the order in which one writes them. Not sure if this is worth documenting though. Those two or three people who actually try it will figure it out soon enough. 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
From: Robert Haas on 28 Jan 2010 11:01 On Thu, Jan 28, 2010 at 10:56 AM, Tom Lane <tgl(a)sss.pgh.pa.us> wrote: > Robert Haas <robertmhaas(a)gmail.com> writes: >> On Thu, Jan 28, 2010 at 9:01 AM, Pavel Stehule <pavel.stehule(a)gmail.com> wrote: >>> simplest could not be a best. There have to be only a const >>> expression. But we have not possibility to check it in pg. > >> Well... that's an entirely arbitrary limitation. I admit that it >> doesn't seem likely that someone would want to have a variable >> delimiter, but putting extra effort and code complexity into >> preventing it seems pointless. > > Yeah. The real issue here is that in some cases you'd like to have > non-aggregated parameters to an aggregate, but SQL has no notation > to express that. Right. > I think Pavel's underlying complaint is that if the delimiter > argument isn't constant, then we're exposing an implementation > dependency in terms of just which values get separated by which > delimiters. The most practical implementation seems to be that > the first-call delimiter isn't actually used at all, and on > subsequent calls the delimiter *precedes* the associated value, > which is a bit surprising given the order in which one writes > them. Not sure if this is worth documenting though. Those two > or three people who actually try it will figure it out soon enough. Yeah, I'm thoroughly unworried about it. ....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: Robert Haas on 28 Jan 2010 11:02 On Wed, Jan 27, 2010 at 9:47 PM, Takahiro Itagaki <itagaki.takahiro(a)oss.ntt.co.jp> wrote: > * Do we need better names for string_agg1_transfn and string_agg2_transfn? > They are almost "internal names", but we could have more > like string_agg_with_sep_transfn. Surely the names of the transition and final functions should match the name of the aggregate. But if there's a proposal on the table to call this string_app_with_sep() instead of just string_agg(), +1 from me. ....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: Hitoshi Harada on 28 Jan 2010 12:08
2010/1/29 Pavel Stehule <pavel.stehule(a)gmail.com>: > 2010/1/28 Robert Haas <robertmhaas(a)gmail.com>: >> On Thu, Jan 28, 2010 at 9:01 AM, Pavel Stehule <pavel.stehule(a)gmail.com> wrote: >>> simplest could not be a best. There have to be only a const >>> expression. But we have not possibility to check it in pg. >> >> Well... that's an entirely arbitrary limitation. I admit that it >> doesn't seem likely that someone would want to have a variable >> delimiter, but putting extra effort and code complexity into >> preventing it seems pointless. > > It is only a few lines with zero complexity. > > The main issue of Takahiro proposal is "unclean" behave. > > we can have a content > > c1 c2 > ----------- > c11, c12, > c21, c22 > > and result of string_agg(c1, c2) > > have to be ?? c11 c12 c21 or c11 c22 c21 ?? What if some content of c2 > will be NULL ?? I checked oracle. Oracle doesn't allow variable as > delimiter. We can't check it. But we can fix first value and using it > as constant. What about get_fn_expr_arg_stable() to check if the argument is stable during aggregate? Regards, -- Hitoshi Harada -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers |