[BUGS] string_agg delimiter having no effect with order by
I'd like to report a potential bug (or just my misunderstanding), but I couldn't find any mention in the TODO or on the mailing list. I'm using PostgreSQL 9.0 beta 3 on Gentoo x64 (sorry, I don't have beta 4 yet). I attempted to use string_agg to get values into a comma-separated list as follows. test=# create table agg_test ( id serial, thing integer, stuff text); NOTICE: CREATE TABLE will create implicit sequence "agg_test_id_seq" for serial column "agg_test.id" CREATE TABLE test=# insert into agg_test (thing, stuff) values (1,'meow'),(1,'bark'); INSERT 0 2 test=# select thing, string_agg(stuff order by stuff, ',') from agg_test group by thing; thing | string_agg ---+ 1 | barkmeow (1 row) test=# select thing, string_agg(stuff order by thing, ',') from agg_test group by thing; thing | string_agg ---+ 1 | meowbark (1 row) As you can see, the output of string_agg isn't delimited. But if I remove order by, it works: test=# select thing, string_agg(stuff, ',') from agg_test group by thing; thing | string_agg ---+ 1 | meow,bark (1 row) The reason I expect this to work is because of what is stated in the documentation: http://www.postgresql.org/docs/9.0/static/functions-aggregate.html "This ordering is unspecified by default, but can be controlled by writing an ORDER BY clause within the aggregate call, as shown in Section 4.2.7. " Thanks -- Thom Brown Registered Linux user: #516935 -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] string_agg delimiter having no effect with order by
On 4 August 2010 10:36, Thom Brown wrote: > I'd like to report a potential bug (or just my misunderstanding), but > I couldn't find any mention in the TODO or on the mailing list. > > I'm using PostgreSQL 9.0 beta 3 on Gentoo x64 (sorry, I don't have > beta 4 yet). I attempted to use string_agg to get values into a > comma-separated list as follows. > > test=# create table agg_test ( > id serial, > thing integer, > stuff text); > NOTICE: CREATE TABLE will create implicit sequence "agg_test_id_seq" > for serial column "agg_test.id" > CREATE TABLE > > test=# insert into agg_test (thing, stuff) values (1,'meow'),(1,'bark'); > INSERT 0 2 > > test=# select thing, string_agg(stuff order by stuff, ',') from > agg_test group by thing; > thing | string_agg > ---+ > 1 | barkmeow > (1 row) > > test=# select thing, string_agg(stuff order by thing, ',') from > agg_test group by thing; > thing | string_agg > ---+ > 1 | meowbark > (1 row) > > As you can see, the output of string_agg isn't delimited. But if I > remove order by, it works: > > test=# select thing, string_agg(stuff, ',') from agg_test group by thing; > thing | string_agg > ---+ > 1 | meow,bark > (1 row) > > The reason I expect this to work is because of what is stated in the > documentation: > http://www.postgresql.org/docs/9.0/static/functions-aggregate.html > > "This ordering is unspecified by default, but can be controlled by > writing an ORDER BY clause within the aggregate call, as shown in > Section 4.2.7. " > > Thanks > > -- > Thom Brown > Registered Linux user: #516935 > I also notice that there are no regression tests for use of string_agg with both ORDER BY and a delimiter. Thom -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] string_agg delimiter having no effect with order by
On 4 August 2010 10:44, Thom Brown wrote: > On 4 August 2010 10:36, Thom Brown wrote: >> I'd like to report a potential bug (or just my misunderstanding), but >> I couldn't find any mention in the TODO or on the mailing list. >> >> I'm using PostgreSQL 9.0 beta 3 on Gentoo x64 (sorry, I don't have >> beta 4 yet). I attempted to use string_agg to get values into a >> comma-separated list as follows. >> >> test=# create table agg_test ( >> id serial, >> thing integer, >> stuff text); >> NOTICE: CREATE TABLE will create implicit sequence "agg_test_id_seq" >> for serial column "agg_test.id" >> CREATE TABLE >> >> test=# insert into agg_test (thing, stuff) values (1,'meow'),(1,'bark'); >> INSERT 0 2 >> >> test=# select thing, string_agg(stuff order by stuff, ',') from >> agg_test group by thing; >> thing | string_agg >> ---+ >> 1 | barkmeow >> (1 row) >> >> test=# select thing, string_agg(stuff order by thing, ',') from >> agg_test group by thing; >> thing | string_agg >> ---+ >> 1 | meowbark >> (1 row) >> >> As you can see, the output of string_agg isn't delimited. But if I >> remove order by, it works: >> >> test=# select thing, string_agg(stuff, ',') from agg_test group by thing; >> thing | string_agg >> ---+ >> 1 | meow,bark >> (1 row) >> >> The reason I expect this to work is because of what is stated in the >> documentation: >> http://www.postgresql.org/docs/9.0/static/functions-aggregate.html >> >> "This ordering is unspecified by default, but can be controlled by >> writing an ORDER BY clause within the aggregate call, as shown in >> Section 4.2.7. " >> >> Thanks >> >> -- >> Thom Brown >> Registered Linux user: #516935 >> > > I also notice that there are no regression tests for use of string_agg > with both ORDER BY and a delimiter. > > Thom > Actually, this rings a bell. I think this may have been raised before, something to do with the delimiter being accepted as one of the order by values. If this isn't really a bug, could someone mention it in the docs somewhere? Thom -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] string_agg delimiter having no effect with order by
On Wed, Aug 4, 2010 at 6:03 AM, Thom Brown wrote: > Actually, this rings a bell. I think this may have been raised > before, something to do with the delimiter being accepted as one of > the order by values. If this isn't really a bug, could someone > mention it in the docs somewhere? Oh, yeah. I guess you need this: select thing, string_agg(stuff, ',' order by stuff) from agg_test group by thing; Rather than this: select thing, string_agg(stuff order by stuff, ',') from agg_test group by thing; It's all kinds of not obvious to me what the second one is supposed to mean, but I remember this was discussed before. Perhaps we need a somewhere about multi-argument aggregates. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
[BUGS] In 8.2, shutdown wrongly caused automatic restart
Hi, In my customer's system using v8.2.5, even though no process exited abnormally, the fast shutdown wrongly caused the automatic restart of a database. The server log is as follows: Jul 18 16:21:51 postgres[26624]: [1-1] LOG: received fast shutdown request Jul 18 16:21:51 postgres[26624]: [2-1] LOG: aborting any active transactions Jul 18 16:21:51 postgres[3475]: [1-1] FATAL: terminating connection due to administrator command Jul 18 16:21:51 postgres[6978]: [1-1] FATAL: terminating connection due to administrator command Jul 18 16:21:51 postgres[30868]: [1-1] FATAL: terminating connection due to administrator command Jul 18 16:21:51 postgres[15980]: [1-1] FATAL: terminating connection due to administrator command Jul 18 16:21:51 postgres[27501]: [1-1] FATAL: terminating connection due to administrator command Jul 18 16:21:51 postgres[24341]: [1-1] FATAL: terminating connection due to administrator command Jul 18 16:21:51 postgres[28270]: [1-1] LOG: shutting down Jul 18 16:21:51 postgres[28270]: [2-1] LOG: database system is shut down Jul 18 16:21:53 postgres[11776]: [3-1] FATAL: the database system is shutting down Jul 18 16:21:53 postgres[11779]: [3-1] FATAL: the database system is shutting down Jul 18 16:21:53 postgres[11780]: [3-1] FATAL: the database system is shutting down Jul 18 16:21:53 postgres[11781]: [3-1] FATAL: the database system is shutting down Jul 18 16:21:53 postgres[26624]: [3-1] LOG: background writer process (PID 28270) exited with exit code 0 Jul 18 16:22:01 postgres[26624]: [4-1] LOG: terminating any other active server processes Jul 18 16:22:04 postgres[26624]: [5-1] LOG: all server processes terminated; reinitializing Jul 18 16:22:07 postgres[11801]: [6-1] LOG: database system was shut down at 2010-07-18 16:21:51 JST As far as I read the source code, this problem seems to have happened as follows. 1. After postmaster received SIGINT, it forcibly terminated all the backends. 2. After postmaster confirmed that no backend is running, it sent SIGUSR2 to the bgwriter. 3. After the bgwriter received SIGUSR2, it started the shutdown checkpoint. 4. Postmaster accepted new connection from a client and forked new backend. 5. The bgwriter completed the checkpoint and ended normally before the backend was rejected due to database state. 6. Now, the bgwriter had already ended even though there was a backend in progress. Postmaster regarded this situation as abnormal and caused the recovery. In 8.3 or later, since postmaster doesn't regard that situation as abnormal and just waits for all backends to exit again, the problem doesn't happen. I think that this is a bug in 8.2 and should be fixed in the same way as 8.3 does. Thought? Regards, -- Fujii Masao NIPPON TELEGRAPH AND TELEPHONE CORPORATION NTT Open Source Software Center -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] string_agg delimiter having no effect with order by
On 4 August 2010 14:04, Robert Haas wrote: > On Wed, Aug 4, 2010 at 6:03 AM, Thom Brown wrote: >> Actually, this rings a bell. I think this may have been raised >> before, something to do with the delimiter being accepted as one of >> the order by values. If this isn't really a bug, could someone >> mention it in the docs somewhere? > > Oh, yeah. I guess you need this: > > select thing, string_agg(stuff, ',' order by stuff) from agg_test > group by thing; > > Rather than this: > > select thing, string_agg(stuff order by stuff, ',') from agg_test > group by thing; > > It's all kinds of not obvious to me what the second one is supposed to > mean, but I remember this was discussed before. Perhaps we need a > somewhere about multi-argument aggregates. > Yes, that works with the order clause. That's really weird! It looks like part of the delimiter parameter, and that's undocumented, or at least impossible to gleen from the documentation. This should be clarified as it looks like having ORDER BY *or* a delimiter is supported, but not both. It's horribly unintuitive! This is one of the very few cases where MySQL's version actually makes more sense. Thom -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] string_agg delimiter having no effect with order by
2010/8/4 Thom Brown : > On 4 August 2010 14:04, Robert Haas wrote: >> On Wed, Aug 4, 2010 at 6:03 AM, Thom Brown wrote: >>> Actually, this rings a bell. I think this may have been raised >>> before, something to do with the delimiter being accepted as one of >>> the order by values. If this isn't really a bug, could someone >>> mention it in the docs somewhere? >> >> Oh, yeah. I guess you need this: >> >> select thing, string_agg(stuff, ',' order by stuff) from agg_test >> group by thing; >> >> Rather than this: >> >> select thing, string_agg(stuff order by stuff, ',') from agg_test >> group by thing; >> >> It's all kinds of not obvious to me what the second one is supposed to >> mean, but I remember this was discussed before. Perhaps we need a >> somewhere about multi-argument aggregates. >> > > Yes, that works with the order clause. That's really weird! It looks > like part of the delimiter parameter, and that's undocumented, or at > least impossible to gleen from the documentation. > > This should be clarified as it looks like having ORDER BY *or* a > delimiter is supported, but not both. It's horribly unintuitive! > This is one of the very few cases where MySQL's version actually makes > more sense. this goes from ANSI SQL standard :( - I agree, this isn't intuitive and pg can do better diagnostic now. But it has a sense. ORDER BY hasn't sense for one parameter - only for complete function, so is wrong to write ORDER BY over a some interesting parameter Regards Pavel Stehule > > Thom > > -- > Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-bugs > -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] string_agg delimiter having no effect with order by
On 4 August 2010 14:24, Pavel Stehule wrote: > 2010/8/4 Thom Brown : >> On 4 August 2010 14:04, Robert Haas wrote: >>> On Wed, Aug 4, 2010 at 6:03 AM, Thom Brown wrote: Actually, this rings a bell. I think this may have been raised before, something to do with the delimiter being accepted as one of the order by values. If this isn't really a bug, could someone mention it in the docs somewhere? >>> >>> Oh, yeah. I guess you need this: >>> >>> select thing, string_agg(stuff, ',' order by stuff) from agg_test >>> group by thing; >>> >>> Rather than this: >>> >>> select thing, string_agg(stuff order by stuff, ',') from agg_test >>> group by thing; >>> >>> It's all kinds of not obvious to me what the second one is supposed to >>> mean, but I remember this was discussed before. Perhaps we need a >>> somewhere about multi-argument aggregates. >>> >> >> Yes, that works with the order clause. That's really weird! It looks >> like part of the delimiter parameter, and that's undocumented, or at >> least impossible to gleen from the documentation. >> >> This should be clarified as it looks like having ORDER BY *or* a >> delimiter is supported, but not both. It's horribly unintuitive! >> This is one of the very few cases where MySQL's version actually makes >> more sense. > > this goes from ANSI SQL standard :( - I agree, this isn't intuitive > and pg can do better diagnostic now. But it has a sense. ORDER BY > hasn't sense for one parameter - only for complete function, so is > wrong to write ORDER BY over a some interesting parameter > > Regards > > Pavel Stehule > So really, should the documentation be changed from: string_agg(expression [, delimiter ] ) to string_agg(expression [, delimiter ] [ GROUP BY expression [, ...] ] ) ? -- Thom Brown Registered Linux user: #516935 -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] string_agg delimiter having no effect with order by
2010/8/4 Thom Brown : > On 4 August 2010 14:24, Pavel Stehule wrote: >> 2010/8/4 Thom Brown : >>> On 4 August 2010 14:04, Robert Haas wrote: On Wed, Aug 4, 2010 at 6:03 AM, Thom Brown wrote: > Actually, this rings a bell. I think this may have been raised > before, something to do with the delimiter being accepted as one of > the order by values. If this isn't really a bug, could someone > mention it in the docs somewhere? Oh, yeah. I guess you need this: select thing, string_agg(stuff, ',' order by stuff) from agg_test group by thing; Rather than this: select thing, string_agg(stuff order by stuff, ',') from agg_test group by thing; It's all kinds of not obvious to me what the second one is supposed to mean, but I remember this was discussed before. Perhaps we need a somewhere about multi-argument aggregates. >>> >>> Yes, that works with the order clause. That's really weird! It looks >>> like part of the delimiter parameter, and that's undocumented, or at >>> least impossible to gleen from the documentation. >>> >>> This should be clarified as it looks like having ORDER BY *or* a >>> delimiter is supported, but not both. It's horribly unintuitive! >>> This is one of the very few cases where MySQL's version actually makes >>> more sense. >> >> this goes from ANSI SQL standard :( - I agree, this isn't intuitive >> and pg can do better diagnostic now. But it has a sense. ORDER BY >> hasn't sense for one parameter - only for complete function, so is >> wrong to write ORDER BY over a some interesting parameter >> >> Regards >> >> Pavel Stehule >> > > So really, should the documentation be changed from: > > string_agg(expression [, delimiter ] ) > > to > > string_agg(expression [, delimiter ] [ GROUP BY expression [, ...] ] ) This syntax is available for all aggregate functions - this feature isn't specific for string_agg but there can be more descriptive example. Regards Pavel > > ? > > -- > Thom Brown > Registered Linux user: #516935 > -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
[BUGS] BUG #5598: Compatibility modes
The following bug has been logged online: Bug reference: 5598 Logged by: Shine Email address: shine.prak...@icasework.com PostgreSQL version: 8.4 Operating system: Windows XP Description:Compatibility modes Details: Hi, We are planning to upgrade from Postgresql 8.2 to 8.4, and we have hit the following error when running our application against it. org.postgresql.util.PSQLException: ERROR: operator does not exist: character varying = integer We have noticed many people have reported this issue and that this was an intentional change on your side. We would like to know if there is a workaround built into postgres by setting some kind of COMPATIBILITY variable (similar to SQL SERVER 2005 / 2008)... This would be helpful in getting our system deployed using the new database, otherwise we would have to push back our release to a later date. Regards, Shine Prakash Software Consultant iCasework Ltd. -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] string_agg delimiter having no effect with order by
Robert Haas writes: > Oh, yeah. I guess you need this: > select thing, string_agg(stuff, ',' order by stuff) from agg_test > group by thing; > Rather than this: > select thing, string_agg(stuff order by stuff, ',') from agg_test > group by thing; > It's all kinds of not obvious to me what the second one is supposed to > mean, but I remember this was discussed before. Perhaps we need a > somewhere about multi-argument aggregates. Done: + + When dealing with multiple-argument aggregate functions, note that the + ORDER BY clause goes after all the aggregate arguments. + For example, this: + + SELECT string_agg(a, ',' ORDER BY a) FROM table; + + not this: + + SELECT string_agg(a ORDER BY a, ',') FROM table; -- not what you want + + The latter syntax will be accepted, but ',' will be + treated as a (useless) sort key. + regards, tom lane -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] In 8.2, shutdown wrongly caused automatic restart
Fujii Masao writes: > 6. ... the bgwriter had already ended even though there was a backend in >progress. Postmaster regarded this situation as abnormal and caused the >recovery. > In 8.3 or later, since postmaster doesn't regard that situation as abnormal > and just waits for all backends to exit again, the problem doesn't happen. > I think that this is a bug in 8.2 and should be fixed in the same way as 8.3 > does. Thought? My recollection is that that change was associated with some pretty significant revisions to the postmaster state machine. I'm concerned about the risks involved in back-patching that. This seems to be a corner case with pretty minimal consequences anyway, so I'm inclined to leave 8.2 alone. Now, if I'm wrong about that and you can produce a simple and obviously correct patch for 8.2, go ahead. regards, tom lane -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] string_agg delimiter having no effect with order by
On Wed, Aug 4, 2010 at 11:29 AM, Tom Lane wrote: > Robert Haas writes: >> Oh, yeah. I guess you need this: > >> select thing, string_agg(stuff, ',' order by stuff) from agg_test >> group by thing; > >> Rather than this: > >> select thing, string_agg(stuff order by stuff, ',') from agg_test >> group by thing; > >> It's all kinds of not obvious to me what the second one is supposed to >> mean, but I remember this was discussed before. Perhaps we need a >> somewhere about multi-argument aggregates. > > Done: > > + > + When dealing with multiple-argument aggregate functions, note that the > + ORDER BY clause goes after all the aggregate arguments. > + For example, this: > + > + SELECT string_agg(a, ',' ORDER BY a) FROM table; > + > + not this: > + > + SELECT string_agg(a ORDER BY a, ',') FROM table; -- not what you want > + > + The latter syntax will be accepted, but ',' will be > + treated as a (useless) sort key. > + Oh, right, that's what it's supposed to mean. Thanks for adding this. I suppose this confusion is only possible because string_agg has both a one-argument and a two-argument form. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5598: Compatibility modes
On Wed, Aug 4, 2010 at 10:53 AM, Shine wrote: > We are planning to upgrade from Postgresql 8.2 to 8.4, and we have hit the > following error when running our application against it. > > org.postgresql.util.PSQLException: ERROR: operator does not exist: character > varying = integer > > We have noticed many people have reported this issue and that this was an > intentional change on your side. > > We would like to know if there is a workaround built into postgres by > setting some kind of COMPATIBILITY variable (similar to SQL SERVER 2005 / > 2008)... Sorry, there is no such mode... -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] string_agg delimiter having no effect with order by
Robert Haas writes: > I suppose this confusion is only possible because string_agg has both > a one-argument and a two-argument form. Right, or at least that's what allows the mistake to go through without reporting any error. regards, tom lane -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] In 8.2, shutdown wrongly caused automatic restart
Excerpts from Tom Lane's message of mié ago 04 12:37:23 -0400 2010: > Fujii Masao writes: > > 6. ... the bgwriter had already ended even though there was a backend in > >progress. Postmaster regarded this situation as abnormal and caused the > >recovery. > > > In 8.3 or later, since postmaster doesn't regard that situation as abnormal > > and just waits for all backends to exit again, the problem doesn't happen. > > I think that this is a bug in 8.2 and should be fixed in the same way as 8.3 > > does. Thought? > > My recollection is that that change was associated with some pretty > significant revisions to the postmaster state machine. I'm concerned > about the risks involved in back-patching that. This seems to be a > corner case with pretty minimal consequences anyway, so I'm inclined > to leave 8.2 alone. IIRC this is the kind of thing that "dead-end backends" were invented for. It was too a large patch for backpatching, IMHO. -- Álvaro Herrera The PostgreSQL Company - Command Prompt, Inc. PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] string_agg delimiter having no effect with order by
On Wed, Aug 4, 2010 at 12:44 PM, Tom Lane wrote: > Robert Haas writes: >> I suppose this confusion is only possible because string_agg has both >> a one-argument and a two-argument form. > > Right, or at least that's what allows the mistake to go through without > reporting any error. No, that's what lets the correct form go through without reporting any error. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5598: Compatibility modes
On Wed, Aug 4, 2010 at 10:42, Robert Haas wrote: > On Wed, Aug 4, 2010 at 10:53 AM, Shine wrote: >> We would like to know if there is a workaround built into postgres by >> setting some kind of COMPATIBILITY variable (similar to SQL SERVER 2005 / >> 2008)... > > Sorry, there is no such mode... However, you can add them back if you must: http://petereisentraut.blogspot.com/2008/03/readding-implicit-casts-in-postgresql.html Be aware, they were removed for a reason. See http://www.postgresql.org/docs/8.4/static/release-8-3.html section E.17.2.1 for more. -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] string_agg delimiter having no effect with order by
Robert Haas writes: > On Wed, Aug 4, 2010 at 12:44 PM, Tom Lane wrote: >> Robert Haas writes: >>> I suppose this confusion is only possible because string_agg has both >>> a one-argument and a two-argument form. >> >> Right, or at least that's what allows the mistake to go through without >> reporting any error. > No, that's what lets the correct form go through without reporting any error. Really? IMO the reason Thom had a problem was he thought he was invoking the two-argument form of string_agg, but he was really invoking the one-argument form. If we were a bit earlier in the 9.0 cycle I would suggest that this confusion is a sufficient reason to drop the one-argument form of string_agg. It's too late now though. regards, tom lane -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5598: Compatibility modes
Alex Hunsaker writes: > On Wed, Aug 4, 2010 at 10:42, Robert Haas wrote: >> On Wed, Aug 4, 2010 at 10:53 AM, Shine wrote: >>> We would like to know if there is a workaround built into postgres by >>> setting some kind of COMPATIBILITY variable (similar to SQL SERVER 2005 / >>> 2008)... >> >> Sorry, there is no such mode... > However, you can add them back if you must: > http://petereisentraut.blogspot.com/2008/03/readding-implicit-casts-in-postgresql.html Note that that hack actually does not work very well: it has unpleasant side effects. You'd be far better advised to fix your application. regards, tom lane -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] string_agg delimiter having no effect with order by
On Wed, Aug 4, 2010 at 1:04 PM, Tom Lane wrote: > Robert Haas writes: >> On Wed, Aug 4, 2010 at 12:44 PM, Tom Lane wrote: >>> Robert Haas writes: I suppose this confusion is only possible because string_agg has both a one-argument and a two-argument form. >>> >>> Right, or at least that's what allows the mistake to go through without >>> reporting any error. > >> No, that's what lets the correct form go through without reporting any error. > > Really? IMO the reason Thom had a problem was he thought he was > invoking the two-argument form of string_agg, but he was really > invoking the one-argument form. I had my head tilted a slightly different way, but, yes. > If we were a bit earlier in the 9.0 cycle I would suggest that this > confusion is a sufficient reason to drop the one-argument form of > string_agg. It's too late now though. Agreed on both points. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] string_agg delimiter having no effect with order by
On Wed, Aug 4, 2010 at 11:04, Tom Lane wrote: > If we were a bit earlier in the 9.0 cycle I would suggest that this > confusion is a sufficient reason to drop the one-argument form of > string_agg. It's too late now though. FWIW I think we can still change it. Isn't this type of issue part of what beta is for? If we were in RC that would be a different story :) -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] string_agg delimiter having no effect with order by
Alex Hunsaker wrote: > On Wed, Aug 4, 2010 at 11:04, Tom Lane wrote: >> If we were a bit earlier in the 9.0 cycle I would suggest that >> this confusion is a sufficient reason to drop the one-argument >> form of string_agg. It's too late now though. > > FWIW I think we can still change it. Isn't this type of issue > part of what beta is for? If we were in RC that would be a > different story I like to think I'm pretty serious about controlling scope creep to prevent a release dragging out, but this one seems like beta testing uncovered a flaw in new code for the release. In my book, that makes it fair game to balance the risk of breaking things by changing it now against the problems we'll have long term if we leave it alone. I'm not sure if that was the basis of saying it was too late, or some other consideration. -Kevin -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Drop one-argument string_agg? (was Re: [BUGS] string_agg delimiter having no effect with order by)
Alex Hunsaker writes: > On Wed, Aug 4, 2010 at 11:04, Tom Lane wrote: >> If we were a bit earlier in the 9.0 cycle I would suggest that this >> confusion is a sufficient reason to drop the one-argument form of >> string_agg. It's too late now though. > FWIW I think we can still change it. Isn't this type of issue part > of what beta is for? If we were in RC that would be a different story > :) Well, it'd take an initdb to get rid of it. In the past we've avoided forcing initdb post-beta1 unless it was Really Necessary. OTOH, we seem to be in the mode of encouraging beta testers to test pg_upgrade, so maybe that concern isn't worth much at the moment. I am right, am I not, in thinking that we invented string_agg out of whole cloth? I don't see it in SQL:2008. If there is a compatibility- with-other-products reason to support the one-argument form, that would be a consideration here. I don't see a whole lot of functionality gain from having the one-argument form, though. BTW, as far as I can tell from checking in the system catalogs, there are no other built-in aggregates that come in differing-numbers-of-arguments variants. So string_agg is the only one presenting this hazard. regards, tom lane -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: Drop one-argument string_agg? (was Re: [BUGS] string_agg delimiter having no effect with order by)
On Wed, Aug 4, 2010 at 13:11, Tom Lane wrote: > Alex Hunsaker writes: >> On Wed, Aug 4, 2010 at 11:04, Tom Lane wrote: >>> If we were a bit earlier in the 9.0 cycle I would suggest that this >>> confusion is a sufficient reason to drop the one-argument form of >>> string_agg. It's too late now though. > >> FWIW I think we can still change it. Isn't this type of issue part >> of what beta is for? If we were in RC that would be a different story >> :) > > Well, it'd take an initdb to get rid of it. I think forcing an initdb might be more trouble than this wart is worth. > In the past we've avoided > forcing initdb post-beta1 unless it was Really Necessary. OTOH, we seem > to be in the mode of encouraging beta testers to test pg_upgrade, so > maybe that concern isn't worth much at the moment. I have one or two 9.0-beta databases, a forced initdb would defiantly motivate me to try pg_upgrade :). To me, the question is are we planning on releasing a new beta anyway? Maybe its worth it then. If we were planning on going RC after this last beta (and I dont think we were?), I agree with Kevin, its not something worth pushing the release 9.0 for. By that I mean I assume if we force an initdb that we would want to do another beta regardless. Either way, I don't have strong feelings on this other than if we dont fix it now when will we? Maybe we will get "lucky" and someone will find an issue that we have to initdb for anyways :). -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: Drop one-argument string_agg? (was Re: [BUGS] string_agg delimiter having no effect with order by)
On Wed, Aug 4, 2010 at 3:25 PM, Alex Hunsaker wrote: > I think forcing an initdb might be more trouble than this wart is worth. +1. I would not make this change unless we have to force an initdb anyway. And I really hope we don't, because I'm sort of hoping the next 9.0 release will be rc1. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: Drop one-argument string_agg? (was Re: [BUGS] string_agg delimiter having no effect with order by)
Alex Hunsaker writes: > Either way, I don't have strong feelings on this other than if we dont > fix it now when will we? Well, we won't. If 9.0 ships with both forms of string_agg, we're stuck with it IMO. It's not exactly a bug, so I won't cry if that's how things go; but it is striking that already two different people have gotten confused enough to file bug reports because of this. If we don't pull the one-argument form then I think we can look forward to many more of those in future years. regards, tom lane -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: Drop one-argument string_agg? (was Re: [BUGS] string_agg delimiter having no effect with order by)
On 4 August 2010 20:25, Alex Hunsaker wrote: > On Wed, Aug 4, 2010 at 13:11, Tom Lane wrote: >> Alex Hunsaker writes: >>> On Wed, Aug 4, 2010 at 11:04, Tom Lane wrote: If we were a bit earlier in the 9.0 cycle I would suggest that this confusion is a sufficient reason to drop the one-argument form of string_agg. It's too late now though. >> >>> FWIW I think we can still change it. Isn't this type of issue part >>> of what beta is for? If we were in RC that would be a different story >>> :) >> >> Well, it'd take an initdb to get rid of it. > > I think forcing an initdb might be more trouble than this wart is worth. > >> In the past we've avoided >> forcing initdb post-beta1 unless it was Really Necessary. OTOH, we seem >> to be in the mode of encouraging beta testers to test pg_upgrade, so >> maybe that concern isn't worth much at the moment. > > I have one or two 9.0-beta databases, a forced initdb would defiantly > motivate me to try pg_upgrade :). To me, the question is are we > planning on releasing a new beta anyway? Maybe its worth it then. If > we were planning on going RC after this last beta (and I dont think we > were?), I agree with Kevin, its not something worth pushing the > release 9.0 for. By that I mean I assume if we force an initdb that > we would want to do another beta regardless. > > Either way, I don't have strong feelings on this other than if we dont > fix it now when will we? Maybe we will get "lucky" and someone will > find an issue that we have to initdb for anyways :). > I think it should be left exactly how it is. It only needed clarification in the documentation to explain its usage for the scenario in question, and probably a couple entries in the regression tests as they're lacking at the moment. I wish I had held back on mentioning it as I remembered later that this has already been discussed to a degree, and I'd probably have kept my mouth shut upon recalling it. -- Thom Brown Registered Linux user: #516935 -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: Drop one-argument string_agg? (was Re: [BUGS] string_agg delimiter having no effect with order by)
Robert Haas writes: > On Wed, Aug 4, 2010 at 3:25 PM, Alex Hunsaker wrote: >> I think forcing an initdb might be more trouble than this wart is worth. > +1. I would not make this change unless we have to force an initdb > anyway. And I really hope we don't, because I'm sort of hoping the > next 9.0 release will be rc1. Hm? I don't think that an initdb here would have any impact on whether we can call the next drop RC1 or not. We're talking about removing a single built-in entry in pg_proc --- it's one of the safest changes we could possibly make. The only argument I can see against it is not wanting to force beta testers through an initdb. But it seems like that might actually be a positive thing not a negative one, in this cycle, because we're trying to get test coverage on pg_upgrade. regards, tom lane -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [HACKERS] Drop one-argument string_agg? (was Re: [BUGS] string_agg delimiter having no effect with order by)
> Well, it'd take an initdb to get rid of it. In the past we've avoided > forcing initdb post-beta1 unless it was Really Necessary. OTOH, we seem > to be in the mode of encouraging beta testers to test pg_upgrade, so > maybe that concern isn't worth much at the moment. If it's causing bugs, drop it now. If we include it in 9.0, we're stuck with it for years. I'm OK with forcing an initDB for RC1. -- -- Josh Berkus PostgreSQL Experts Inc. http://www.pgexperts.com -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [HACKERS] Drop one-argument string_agg? (was Re: [BUGS] string_agg delimiter having no effect with order by)
Josh Berkus writes: > If it's causing bugs, drop it now. If we include it in 9.0, we're stuck > with it for years. Well, it's causing bug reports, which is not exactly the same thing as bugs. But yeah, I'm thinking we should get rid of it. regards, tom lane -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [HACKERS] Drop one-argument string_agg? (was Re: [BUGS] string_agg delimiter having no effect with order by)
04.Ağu.2010 tarihinde 22:44 saatinde, Josh Berkus şunları yazdı: I'm OK with forcing an initDB for RC1. I think beta5 will be a better choice than RC 1 here. -- Devrim GÜNDÜZ PostgreSQL DBA @ Akinon/Markafoni, Red Hat Certified Engineer devrim~gunduz.org, devrim~PostgreSQL.org, devrim.gunduz~linux.org.tr http://www.gunduz.org Twitter: http://twitter.com/devrimgunduz -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: Drop one-argument string_agg? (was Re: [BUGS] string_agg delimiter having no effect with order by)
On Wed, Aug 4, 2010 at 13:42, Tom Lane wrote: > Robert Haas writes: >> On Wed, Aug 4, 2010 at 3:25 PM, Alex Hunsaker wrote: >>> I think forcing an initdb might be more trouble than this wart is worth. > >> +1. I would not make this change unless we have to force an initdb >> anyway. And I really hope we don't, because I'm sort of hoping the >> next 9.0 release will be rc1. > > Hm? I don't think that an initdb here would have any impact on whether > we can call the next drop RC1 or not. We're talking about removing a > single built-in entry in pg_proc --- it's one of the safest changes we > could possibly make. Great, I was afraid people would want another beta if we forced an initdb. So a hearty +1 for fixing it and not doing another beta (pending other bugs obviously). -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [HACKERS] Re: Drop one-argument string_agg? (was Re: [BUGS] string_agg delimiter having no effect with order by)
> Great, I was afraid people would want another beta if we forced an > initdb. So a hearty +1 for fixing it and not doing another beta > (pending other bugs obviously). And, btw, there has been a lot of testing of pg_upgrade due to the initdbs and otherwise. I think 9.0 is going to have a pretty darned solid pg_upgrade because of it. -- -- Josh Berkus PostgreSQL Experts Inc. http://www.pgexperts.com -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [HACKERS] Re: Drop one-argument string_agg? (was Re: [BUGS] string_agg delimiter having no effect with order by)
On 4 August 2010 20:58, Josh Berkus wrote: > >> Great, I was afraid people would want another beta if we forced an >> initdb. So a hearty +1 for fixing it and not doing another beta >> (pending other bugs obviously). > > And, btw, there has been a lot of testing of pg_upgrade due to the > initdbs and otherwise. I think 9.0 is going to have a pretty darned > solid pg_upgrade because of it. > Leave my name off the commit comment then ;) People who have been waiting for this will burn me as a heretical witch... er.. man witch... warlock? -- Thom Brown Registered Linux user: #516935 -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [HACKERS] Re: Drop one-argument string_agg? (was Re: [BUGS] string_agg delimiter having no effect with order by)
On Wed, Aug 04, 2010 at 09:02:43PM +0100, Thom Brown wrote: > On 4 August 2010 20:58, Josh Berkus wrote: > > > >> Great, I was afraid people would want another beta if we forced > >> an initdb. So a hearty +1 for fixing it and not doing another > >> beta (pending other bugs obviously). > > > > And, btw, there has been a lot of testing of pg_upgrade due to the > > initdbs and otherwise. I think 9.0 is going to have a pretty > > darned solid pg_upgrade because of it. > > > > Leave my name off the commit comment then ;) People who have been > waiting for this will burn me as a heretical witch... er.. man > witch... warlock? Witch. Cheers, David. -- David Fetter http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: Drop one-argument string_agg? (was Re: [BUGS] string_agg delimiter having no effect with order by)
On Wed, Aug 4, 2010 at 3:11 PM, Tom Lane wrote: > Alex Hunsaker writes: >> On Wed, Aug 4, 2010 at 11:04, Tom Lane wrote: >>> If we were a bit earlier in the 9.0 cycle I would suggest that this >>> confusion is a sufficient reason to drop the one-argument form of >>> string_agg. It's too late now though. > >> FWIW I think we can still change it. Isn't this type of issue part >> of what beta is for? If we were in RC that would be a different story >> :) > > Well, it'd take an initdb to get rid of it. In the past we've avoided > forcing initdb post-beta1 unless it was Really Necessary. OTOH, we seem > to be in the mode of encouraging beta testers to test pg_upgrade, so > maybe that concern isn't worth much at the moment. I vote fix it. This is going to be a high travel function, and should be right. merlin -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [HACKERS] Drop one-argument string_agg? (was Re: [BUGS] string_agg delimiter having no effect with order by)
I wrote:
> Hm? I don't think that an initdb here would have any impact on whether
> we can call the next drop RC1 or not. We're talking about removing a
> single built-in entry in pg_proc --- it's one of the safest changes we
> could possibly make.
Well, I forgot that an aggregate involves more than one catalog row ;-).
So it's a bit bigger patch than that, but still pretty small and safe.
See attached.
What we are doing here, IMO, is not just changing string_agg() but
instituting a project policy that we are not going to offer built-in
aggregates with the same names and different numbers of arguments ---
otherwise the problem will come right back. Therefore, the attached
patch adds an opr_sanity regression test that will complain if anyone
tries to add such. Of course, this isn't preventing users from creating
such aggregates, but it's on their own heads to not get confused if they
do.
This policy also implies that we are never going to allow default
arguments for aggregates, or at least never have any built-in ones
that use such a feature.
By my count the following people had offered an opinion on making
this change:
for: tgl, josh, badalex, mmoncure
against: rhaas, thom
Anybody else want to vote, or change their vote after seeing the patch?
regards, tom lane
Index: doc/src/sgml/func.sgml
===
RCS file: /cvsroot/pgsql/doc/src/sgml/func.sgml,v
retrieving revision 1.522
diff -c -r1.522 func.sgml
*** doc/src/sgml/func.sgml 29 Jul 2010 19:34:40 - 1.522
--- doc/src/sgml/func.sgml 4 Aug 2010 22:01:12 -
***
*** 9731,9737
Function
! Argument Type
Return Type
Description
--- 9731,9737
Function
! Argument Type(s)
Return Type
Description
***
*** 9901,9917
string_agg
! string_agg(expression
! [, delimiter ] )
!text
text
! input values concatenated into a string, optionally with delimiters
--- 9901,9917
string_agg
! string_agg(expression,
! delimiter)
!text, text
text
! input values concatenated into a string, separated by delimiter
Index: doc/src/sgml/syntax.sgml
===
RCS file: /cvsroot/pgsql/doc/src/sgml/syntax.sgml,v
retrieving revision 1.149
diff -c -r1.149 syntax.sgml
*** doc/src/sgml/syntax.sgml 4 Aug 2010 15:27:57 - 1.149
--- doc/src/sgml/syntax.sgml 4 Aug 2010 22:01:12 -
***
*** 1589,1598
not this:
! SELECT string_agg(a ORDER BY a, ',') FROM table; -- not what you want
! The latter syntax will be accepted, but ',' will be
! treated as a (useless) sort key.
--- 1589,1599
not this:
! SELECT string_agg(a ORDER BY a, ',') FROM table; -- incorrect
! The latter is syntactically valid, but it represents a call of a
! single-argument aggregate function with two ORDER BY keys
! (the second one being rather useless since it's a constant).
Index: src/backend/utils/adt/varlena.c
===
RCS file: /cvsroot/pgsql/src/backend/utils/adt/varlena.c,v
retrieving revision 1.177
diff -c -r1.177 varlena.c
*** src/backend/utils/adt/varlena.c 26 Feb 2010 02:01:10 - 1.177
--- src/backend/utils/adt/varlena.c 4 Aug 2010 22:01:12 -
***
*** 3320,3326
/*
* string_agg - Concatenates values and returns string.
*
! * Syntax: string_agg(value text, delimiter text = '') RETURNS text
*
* Note: Any NULL values are ignored. The first-call delimiter isn't
* actually used at all, and on subsequent calls the delimiter precedes
--- 3320,3326
/*
* string_agg - Concatenates values and returns string.
*
! * Syntax: string_agg(value text, delimiter text) RETURNS text
*
* Note: Any NULL values are ignored. The first-call delimiter isn't
* actually used at all, and on subsequent calls the delimiter precedes
***
*** 3359,3386
state = PG_ARGISNULL(0) ? NULL : (StringInfo) PG_GETARG_POINTER(0);
- /* Append the element unless null. */
- if (!PG_ARGISNULL(1))
- {
- if (state == NULL)
- state = makeStringAggState(fcinfo);
- appendStringInfoText(state, PG_GETARG_TEXT_PP(1)); /* value */
- }
-
- /*
- * The transition type for string_agg() is declared to be "internal",
- * which is a pass-by-value type the same size as a pointer.
- */
- PG_RETURN_POINTER(state);
- }
-
- Datum
- string_agg_delim_transfn(PG_FUNCTION_ARGS)
- {
- StringInfo state;
-
-
Re: [HACKERS] Drop one-argument string_agg? (was Re: [BUGS] string_agg delimiter having no effect with order by)
On 4 August 2010 23:19, Tom Lane wrote: > I wrote: >> Hm? I don't think that an initdb here would have any impact on whether >> we can call the next drop RC1 or not. We're talking about removing a >> single built-in entry in pg_proc --- it's one of the safest changes we >> could possibly make. > > Well, I forgot that an aggregate involves more than one catalog row ;-). > So it's a bit bigger patch than that, but still pretty small and safe. > See attached. > > What we are doing here, IMO, is not just changing string_agg() but > instituting a project policy that we are not going to offer built-in > aggregates with the same names and different numbers of arguments --- > otherwise the problem will come right back. Therefore, the attached > patch adds an opr_sanity regression test that will complain if anyone > tries to add such. Of course, this isn't preventing users from creating > such aggregates, but it's on their own heads to not get confused if they > do. Yes, I think that's sensible. > > This policy also implies that we are never going to allow default > arguments for aggregates, or at least never have any built-in ones > that use such a feature. > > By my count the following people had offered an opinion on making > this change: > for: tgl, josh, badalex, mmoncure > against: rhaas, thom > Anybody else want to vote, or change their vote after seeing the patch? > > regards, tom lane > > I was afraid that the function would be pulled completely, but from looking at the patch, you're only removing the function with a single-parameter signature, which is quite innocuous. So I'm "for" now. -- Thom Brown Registered Linux user: #516935 -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [HACKERS] Drop one-argument string_agg? (was Re: [BUGS] string_agg delimiter having no effect with order by)
Thom Brown writes: > I was afraid that the function would be pulled completely, but from > looking at the patch, you're only removing the function with a > single-parameter signature, which is quite innocuous. Yes, of course, sorry if I confused anyone. It's the combination of having both one- and two-argument forms that is the problem. Since the one-argument form isn't doing anything but offering a rather useless default, we won't lose functionality if we pull it. regards, tom lane -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [HACKERS] Drop one-argument string_agg? (was Re: [BUGS] string_agg delimiter having no effect with order by)
On Wed, Aug 04, 2010 at 06:19:49PM -0400, Tom Lane wrote: > I wrote: > > Hm? I don't think that an initdb here would have any impact on whether > > we can call the next drop RC1 or not. We're talking about removing a > > single built-in entry in pg_proc --- it's one of the safest changes we > > could possibly make. > > Well, I forgot that an aggregate involves more than one catalog row ;-). > So it's a bit bigger patch than that, but still pretty small and safe. > See attached. > > What we are doing here, IMO, is not just changing string_agg() but > instituting a project policy that we are not going to offer built-in > aggregates with the same names and different numbers of arguments --- > otherwise the problem will come right back. Therefore, the attached > patch adds an opr_sanity regression test that will complain if anyone > tries to add such. Of course, this isn't preventing users from creating > such aggregates, but it's on their own heads to not get confused if they > do. > > This policy also implies that we are never going to allow default > arguments for aggregates, or at least never have any built-in ones > that use such a feature. > > By my count the following people had offered an opinion on making > this change: > for: tgl, josh, badalex, mmoncure > against: rhaas, thom > Anybody else want to vote, or change their vote after seeing the patch? +1 for removing the single-argument version. Cheers, David. -- David Fetter http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [HACKERS] Drop one-argument string_agg? (was Re: [BUGS] string_agg delimiter having no effect with order by)
On Wed, Aug 4, 2010 at 16:33, Thom Brown wrote: > I was afraid that the function would be pulled completely, but from > looking at the patch, you're only removing the function with a > single-parameter signature, which is quite innocuous. So I'm "for" > now. Ahh, Now I see why you were worried about people calling you a witch :) On another note, I do wonder if we could avoid more confusion by just reordering the arguments. In-fact I bet the original argument ordering was done precisely so it would match the 1 argument version. I dunno about anyone else but (a, ',' order by a) just looks weird. Or in other words, any thoughts on: select string_agg(delim, expression); I don't want to stretch this out, but while we are making changes... -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [HACKERS] Drop one-argument string_agg? (was Re: [BUGS] string_agg delimiter having no effect with order by)
Alex Hunsaker writes: > I dunno about anyone else but (a, ',' order by a) just looks weird. I suppose, but aren't you just focusing on the argument being constant? In the more general case I don't think there's anything unnatural about this syntax. > Or in other words, any thoughts on: > select string_agg(delim, expression); That looks pretty weird to me anyway, with or without use of ORDER BY. Nobody would think to write the delimiter first. Usually you put the "most important" argument first, and no one would see the delimiter as the most important one. regards, tom lane -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5595: Documentation is not installs from VPATH build.
On 04/08/10 16:55, Tom Lane wrote: You're right, I did. Perhaps the presence of prebuilt docs in the source tree confuses something --- anybody wanna test? The files that seem to be causing the confusion are: /doc/src/sgml/html-stamp /doc/src/sgm/man-stamp A src tree 'maintainer-clean' removes then, but any lesser level of clean doesn't. Hmm - shouldn't a VPATH build look at its *own* doc/src/sgml/*-stamp files to see if it needs to build the docs? Note that it does *create* them in there after a successful build... Cheers Mark -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [HACKERS] Drop one-argument string_agg? (was Re: [BUGS] string_agg delimiter having no effect with order by)
On Wed, Aug 4, 2010 at 7:07 PM, Tom Lane wrote: >> Or in other words, any thoughts on: >> select string_agg(delim, expression); > > That looks pretty weird to me anyway, with or without use of ORDER BY. > Nobody would think to write the delimiter first. Usually you put the > "most important" argument first, and no one would see the delimiter > as the most important one. Well, it would match the syntax of things like perl's join(). But I think that's probably not enough reason to go fiddling with it. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [HACKERS] Drop one-argument string_agg? (was Re: [BUGS] string_agg delimiter having no effect with order by)
On Wed, Aug 4, 2010 at 17:07, Tom Lane wrote:
> Alex Hunsaker writes:
>> I dunno about anyone else but (a, ',' order by a) just looks weird.
>
> I suppose, but aren't you just focusing on the argument being constant?
Yes.
>> Or in other words, any thoughts on:
>> select string_agg(delim, expression);
>
> That looks pretty weird to me anyway, with or without use of ORDER BY.
> Nobody would think to write the delimiter first. Usually you put the
> "most important" argument first, and no one would see the delimiter
> as the most important one.
No argument about the most important arg first. I think we have a
difference of opinion on what the important argument is :-)
It might just be my perl upbringing talking, for example you join(',',
...) or split(',', ) in perl.
Either way *shrug* Im happy to leave it alone.
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
Re: [HACKERS] Drop one-argument string_agg? (was Re: [BUGS] string_agg delimiter having no effect with order by)
On Wed, Aug 4, 2010 at 6:19 PM, Tom Lane wrote: > for: tgl, josh, badalex, mmoncure > against: rhaas, thom > Anybody else want to vote, or change their vote after seeing the patch? If we're not regarding this as beta-forcing, I abstain. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
[BUGS] BUG #5599: Vacuum fails due to index corruption issues
The following bug has been logged online: Bug reference: 5599 Logged by: Hitesh Bhambhani Email address: hite...@asg.com PostgreSQL version: 8.2.9-1 Operating system: Microsoft Windows Server 2003, Enterprise Edition Description:Vacuum fails due to index corruption issues Details: Hi, We are seeing a problem in our application where the table indexes get corrupted. This application is a Java Webapp with postgre as the backend. The Webapp kicks off Vacuum at regular intervals. After running the application for a while, one of our customers noted that the Vacuum fails and Webapp gets very slow. A re-index of the full database works fine and resolves the issue. But it re-occurs within a day. Based on some logs in the Webapp I can see that there were some errors in truncating relations. Once those errors disappear the index corruption errors start. I'm not sure if there is a connection here. Here is a sample log message from the Webapp that shows the truncate error: 2010-07-08 06:29:54,641 WARN DefaultQuartzScheduler_Worker-4 maintenance.PostgreSqlVacuumer:32 - runVacuumFull(): running VACUUM FULL VERBOSE 2010-07-08 06:29:56,672 ERROR DefaultQuartzScheduler_Worker-4 core.JobRunShell:211 - Job DEFAULT.postgreSqlVacuumJob threw an unhandled Exception: org.springframework.jdbc.BadSqlGrammarException: Hibernate-related JDBC operation; bad SQL grammar []; nested exception is org.postgresql.util.PSQLException: ERROR: could not truncate relation 1663/16403/41274 to 30 blocks: Permission denied After a couple of such truncate errors the Vacuum starts failing due to 'failed to re-find parent key in index', as seen in sample error log below: 2010-07-08 06:44:56,060 ERROR DefaultQuartzScheduler_Worker-1 core.JobRunShell:2 11 - Job DEFAULT.postgreSqlVacuumJob threw an unhandled Exception: org.springframework.jdbc.UncategorizedSQLException: Hibernate-related JDBC operation; uncategorized SQLException for SQL []; SQL state [XX000]; error code [0]; ERROR: failed to re-find parent key in index "pmoinstance_idx_pmotypeid" for deletion target page 30; nested exception is org.postgresql.util.PSQLException: ERROR: failed to re-find parent key in index "pmoinstance_idx_pmotypeid" for deletion target page 30 Here the index "pmoinstance_idx_pmotypeid" is one of several application specific indexes. Once this index corruption issue occurs, the Vacuum job keeps failing until a re-index is done. In the long run, we don't want to keep re-indexing the database as a scheduled job so we would like your help to get to the bottom of this. So how can we avoid these index corruption errors and are there any known causes? Also, please let me know if there is a direct link between the truncate relation errors that I saw preceded the index corruption errors? At the time these Webapp logs were collected, the database was set to produce verbose logging so I don't have database logs, sorry. Please do let me know what other information I can provide to help you diagnose this situation. Thanks for your time. Regards, Hitesh -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
[BUGS] Re: [HACKERS] Drop one-argument string_agg? (was Re: string_agg delimiter having no effect with order by)
On Wed, Aug 4, 2010 at 11:19 PM, Tom Lane wrote: > What we are doing here, IMO, is not just changing string_agg() but > instituting a project policy that we are not going to offer built-in > aggregates with the same names and different numbers of arguments --- > otherwise the problem will come right back. Well I think this can be a pretty soft policy. The thing is that for string_agg it's a pretty weak argument for the one-argument form anyways so there's not much loss in losing the 1-argument form. In other cases the extra arguments might be for very obscure cases or there may be lots of precedent for the variadic form and users might expect to have it. In which case we could decide the cost/benefit calculation comes down the other way. -- greg -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5599: Vacuum fails due to index corruption issues
On Wed, Aug 4, 2010 at 10:47 PM, Hitesh Bhambhani wrote: > > PostgreSQL version: 8.2.9-1 Firstly, the current release of 8.2 is 8.2.17. There are a long list of bugs fixed in those intervening releases including one involving vacuum truncating relations. I don't think it's the same problem but I would recommend updating immediately to 8.2.17. > 2010-07-08 06:29:54,641 WARN DefaultQuartzScheduler_Worker-4 > maintenance.PostgreSqlVacuumer:32 - runVacuumFull(): running VACUUM FULL > VERBOSE Secondly we don't recommend running VACUUM FULL routinely. It should only be necessary in extraordinary circumstances. Normally a plain VACUUM (or VACUUM ANALYZE or VACUUM VERBOSE) should be sufficient as long as it's being run regularly. Regular VACUUM without the "FULL" has much less impact on the system. > 2010-07-08 06:29:56,672 ERROR DefaultQuartzScheduler_Worker-4 > core.JobRunShell:211 - Job DEFAULT.postgreSqlVacuumJob threw an unhandled > Exception: > org.springframework.jdbc.BadSqlGrammarException: Hibernate-related JDBC > operation; > bad SQL grammar []; nested exception is org.postgresql.util.PSQLException: > ERROR: could not truncate relation 1663/16403/41274 to 30 blocks: Permission > denied "Permission denied" smells like a Windows problem with concurrent file operations. Are you sure you're not running any anti-virus software or backup software which could have these files open and prevent Postgres from performing regular file operations on its files? Many people have reported other problems with anti-virus software in particular. > Also, please let me know if there is a direct link between the truncate > relation errors that I saw preceded the index corruption errors? I'm a bit surprised at that, but I haven't checked what exactly happens when an error occurs truncating files myself so I'm not sure if I should be or not. -- greg -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] Re: [HACKERS] Drop one-argument string_agg? (was Re: string_agg delimiter having no effect with order by)
Greg Stark writes: > On Wed, Aug 4, 2010 at 11:19 PM, Tom Lane wrote: >> What we are doing here, IMO, is not just changing string_agg() but >> instituting a project policy that we are not going to offer built-in >> aggregates with the same names and different numbers of arguments --- >> otherwise the problem will come right back. > Well I think this can be a pretty soft policy. Sure, we could break it given good cause. What I intend with the opr_sanity test is just that people won't be able to put something in without being reminded of this consideration. regards, tom lane -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5595: Documentation is not installs from VPATH build.
Excerpts from Mark Kirkwood's message of mié ago 04 19:14:07 -0400 2010: > On 04/08/10 16:55, Tom Lane wrote: > > > > You're right, I did. Perhaps the presence of prebuilt docs in the > > source tree confuses something --- anybody wanna test? > > > > > > The files that seem to be causing the confusion are: > > /doc/src/sgml/html-stamp > /doc/src/sgm/man-stamp > > A src tree 'maintainer-clean' removes then, but any lesser level of > clean doesn't. Hmm - shouldn't a VPATH build look at its *own* > doc/src/sgml/*-stamp files to see if it needs to build the docs? Note > that it does *create* them in there after a successful build... I think the VPATH mechanism in gmake looks for files in srcdir and then in builddir, by default. Not sure if that can be overridden easily. Maybe those two files should be deleted from the tarball. -- Álvaro Herrera The PostgreSQL Company - Command Prompt, Inc. PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5599: Vacuum fails due to index corruption issues
Excerpts from Hitesh Bhambhani's message of mié ago 04 17:47:12 -0400 2010: > Based on some logs in the Webapp I can see that there were some errors in > truncating relations. Once those errors disappear the index corruption > errors start. I'm not sure if there is a connection here. There probably is. What kind of relation are the ones unable to truncate? Please see in pg_class where relfilenode = '41274' in this case: > bad SQL grammar []; nested exception is org.postgresql.util.PSQLException: > ERROR: could not truncate relation 1663/16403/41274 to 30 blocks: Permission > denied -- Álvaro Herrera The PostgreSQL Company - Command Prompt, Inc. PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5595: Documentation is not installs from VPATH build.
Alvaro Herrera writes: > I think the VPATH mechanism in gmake looks for files in srcdir and then > in builddir, by default. Not sure if that can be overridden easily. > Maybe those two files should be deleted from the tarball. That's not going to work: it would mean that make would try to rebuild the docs from scratch instead of installing the prebuilt ones like it's supposed to. I'm inclined to think the right fix is that the docs should be built into $srcdir even in a VPATH build. I'm no make guru though. Peter? regards, tom lane -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] string_agg delimiter having no effect with order by
2010/8/4 Kevin Grittner : > Alex Hunsaker wrote: >> On Wed, Aug 4, 2010 at 11:04, Tom Lane wrote: >>> If we were a bit earlier in the 9.0 cycle I would suggest that >>> this confusion is a sufficient reason to drop the one-argument >>> form of string_agg. It's too late now though. >> The same problem can be with custom aggregates :( so this syntax isn't too robust. We can support Oracle's syntax in future releases, where syntax divide aggregate call and ORDER BY clause. >> FWIW I think we can still change it. Isn't this type of issue >> part of what beta is for? If we were in RC that would be a >> different story > > I like to think I'm pretty serious about controlling scope creep to > prevent a release dragging out, but this one seems like beta testing > uncovered a flaw in new code for the release. In my book, that > makes it fair game to balance the risk of breaking things by > changing it now against the problems we'll have long term if we > leave it alone. I'm not sure if that was the basis of saying it was > too late, or some other consideration. It is just removing some from one perspective problematic code. This doesn't add any feature - so it cannot be a precedents. we can look on this situation from two views: a) it is good, because we can document this feature/behave - without one param aggregates people will repeat same situation with custom aggregates- and this will not be documented. b) it is bad, because lot of users will be confused. I prefer @a Regards Pavel > > -Kevin > > -- > Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-bugs > -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
[BUGS] BUG #5601: cannot create language plperl;
The following bug has been logged online: Bug reference: 5601 Logged by: Rene Novotny Email address: novotn...@egp.cz PostgreSQL version: 9.0 beta 4 Operating system: Win 7 64 bit Description:cannot create language plperl; Details: ERROR: Could not load library c:/Program Files/PostgreSQL/9.0/lib/plperl.dll unknown error 126 For a memory reasons we need to use 64 bit Windows postgresql , you finally made one. But when we installed Activestate 64 bit perl 5.12 perl ( for 64 bit postgres i suppose 64 bit perl ) it doesn't work. We have thousands of Postgres plperl functions and without working plperl we need to throw away years of work. For us this is a serious bug in PostgreSQL functionality ( plperl and plperlu is the best language that postgres has over all commercial databases ). Please let me know , with what perl you compiled the plperl.dll on windows ( i suppose that ActiveState ). Before the final release of Postgres 9.0 we have to have it solved otherwise it is a disaster for planning engaging Postgres in the infrastructure of our company. Thhanks a lot Rene Novotny UJV REZ software development -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
