Re: [GENERAL] PG wire protocol question
2016-05-17 15:29 keltezéssel, Albe Laurenz írta: Boszormenyi Zoltan wrote: it was a long time I have read this list or written to it. Now, I have a question. This blog post was written about 3 years ago: https://aphyr.com/posts/282-jepsen-postgres Basically, it talks about the client AND the server as a system and if the network is cut between sending COMMIT and receiving the answer for it, the client has no way to know whether the transaction was actually committed. The client connection may just timeout and a reconnect would give it a new connection but it cannot pick up its old connection where it left. So it cannot really know whether the old transaction was committed or not, possibly without doing expensive queries first. Has anything changed on that front? That blog post seems ill-informed - that has nothing to do with two-phase commit. In the blog post 2pc was mentioned related to the communication, not as a transaction control inside the database. I wouldn't call it misinformed. After all, terminology can mean different things in different contexts. The problem - that the server may commit a transaction, but the client never receives the server's response - is independent of whether two-phase commit is used or not. This is not a problem of PostgreSQL, it is a generic problem of communication. Indeed. What would be the alternative? That the server has to wait for the client to receive the commit response? Not quite. That would mean constantly sending an ack that the other received the last ack, which would be silly. If the network connection is cut, the client should be able to reconnect to the old backend and query the last state and continue where it left, maybe confirming via some key or UUID that it was indeed the client that connected previously. But what if the client received the message and the server or the network go down before the server learns of the fact? You see that this would lead to an infinite regress. Yours, Laurenz Albe -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Thoughts on "Love Your Database"
> On Tue, May 17, 2016 at 1:54 PM, Raymond O'Donnell wrote: > > Having said all that, I've rarely had any trouble with pgAdmin 3 on > > Windows 7 and XP, Ubuntu and Debian; just a very occasional crash (maybe > > one every six months). So just to chime in, it has not been at all that stable for my team and I. It's not bad when connections are all stable or you're connecting to a local instance, but if the connection between you and the server isn't perfect... it's one of the least stable pieces of software I've ever used. I'm sure for those who's servers are hosted locally, and you connect through a lan, or those with a decent provider, it works much better than for me. My team is all remote though, and we don't have an office. Each of my employees connects using their own internet connection, with varying reliability. Servers hosted on a cloud provider. Crashes happen multiple times a day for most. Just wanted to share my experience.
Re: [GENERAL] Thoughts on "Love Your Database"
On Wednesday, May 18, 2016, Adam Brusselback wrote: > > On Tue, May 17, 2016 at 1:54 PM, Raymond O'Donnell > wrote: > >> > Having said all that, I've rarely had any trouble with pgAdmin 3 on > >> > Windows 7 and XP, Ubuntu and Debian; just a very occasional crash (maybe > >> > one every six months). > > So just to chime in, it has not been at all that stable for my team and > I. It's not bad when connections are all stable or you're connecting to a > local instance, but if the connection between you and the server isn't > perfect... it's one of the least stable pieces of software I've ever used. > > I'm sure for those who's servers are hosted locally, and you connect > through a lan, or those with a decent provider, it works much better than > for me. > My team is all remote though, and we don't have an office. Each of my > employees connects using their own internet connection, with varying > reliability. Servers hosted on a cloud provider. Crashes happen multiple > times a day for most. > > Have you tried interjecting pg_bouncer into the flow? Probably on the client machine. David J.
[GENERAL] Use of array_agg and array string on inner query
I have the following piece of code: DROP SCHEMA IF EXISTS s CASCADE; CREATE SCHEMA s; CREATE TABLE "s"."t1" ( "c1" BigSerial PRIMARY KEY, "c2" BigInt NOT NULL, "c3" BigInt ) WITH (OIDS=FALSE); INSERT INTO s.t1 (c2) VALUES (10); INSERT INTO s.t1 (c2, c3) VALUES (20, 10); INSERT INTO s.t1 (c2, c3) VALUES (30, 10); /* 1. */ SELECT t3.c1 FROM s.t1 as t3 JOIN s.t1 as t2 ON t3.c3 = t2.c2; /* 2. */ SELECT t1.c1, ARRAY_TO_STRING(ARRAY_AGG((t2.c1)), ',') FROM s.t1 LEFT JOIN s.t1 as t2 ON t2.c3 = t1.c2 GROUP BY t1.c1; /* 3. */ SELECT c1, c2, ARRAY_TO_STRING(ARRAY_AGG((SELECT t3.c1 FROM s.t1 as t3 JOIN s.t1 as t2 ON t3.c3 = t2.c2)), ',') FROM s.t1 t1 GROUP BY c1; DROP SCHEMA s CASCADE; The output for 1 query: c1 2 3 (2 rows) 2 Query: c1 | array_to_string +- 1 | 2,3 2 | 3 | (3 rows) 3 Query gives me a error: psql:/tmp/aggregate.sql:24: ERROR: more than one row returned by a subquery used as an expression The 3 query uses 1 query as inner query. Is there a way to make Query 3 work with inner query as 1 rather than reverting to 2. 3 output should be same as 2. I understand that the error message says query 1 when used as sub query of 3 cannot return more than one row. Pardon my limited knowledge of database. I have tried out: SELECT c1, c2, ARRAY_TO_STRING((SELECT ARRAY_AGG(t3.c1) FROM s.t1 as t3 JOIN s.t1 as t2 ON t3.c3 = t2.c2), ',') FROM s.t1 t1 GROUP BY c1; Output is : c1 | c2 | array_to_string ++- 2 | 20 | 2,3 1 | 10 | 2,3 3 | 30 | 2,3 Could one of you help me with the correct query. Thanks -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Thoughts on "Love Your Database"
Hi, Le 17/05/2016 10:44, John R Pierce a écrit : On 5/17/2016 1:34 AM, Pierre Chevalier Géologue wrote: On this matter, I hear *very* often from such guys that the only reproach they have to PostgreSQL is that it does not come with a slick GUI like Access. Access is a lot more than a slick GUI, Yes; some claim that it is even a database. I was just trying to mean that the GUI part of Access (to design forms, work with data almost like in a spreadsheet) was what these fellows were missing. its a rapid application development system based on VisualBasic. Well, I wouldn't say exactly so. VB was incorporated a while after the release of VB... 2, was it? I can't remember well. Sure, you can put VB code in your Access .mdb (or .mda), but you can certainly design a whole GUI for your database without the need of any VB line of code. À+ Pierre -- Pierre Chevalier PChGEI: Pierre Chevalier Géologue Et Informaticien Partenaire DALIBO Mesté Duran 32100 Condom Tél+fax :09 75 27 45 62 06 37 80 33 64 Émail : pierrechevaliergeolCHEZfree.fr icq# : 10432285 jabber: pierre.chevalier1...@jabber.fr http://pierremariechevalier.free.fr/pierre_chevalier_geologue -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Use of array_agg and array string on inner query
On Thu, May 19, 2016 at 1:09 AM shankha wrote: > I have the following piece of code: > > DROP SCHEMA IF EXISTS s CASCADE; > CREATE SCHEMA s; > > CREATE TABLE "s"."t1" > ( > "c1" BigSerial PRIMARY KEY, > "c2" BigInt NOT NULL, > "c3" BigInt > ) > WITH (OIDS=FALSE); > > INSERT INTO s.t1 (c2) VALUES (10); > INSERT INTO s.t1 (c2, c3) VALUES (20, 10); > INSERT INTO s.t1 (c2, c3) VALUES (30, 10); > > /* 1. */ SELECT t3.c1 FROM s.t1 as t3 JOIN s.t1 as t2 ON t3.c3 = t2.c2; > > /* 2. */ SELECT t1.c1, ARRAY_TO_STRING(ARRAY_AGG((t2.c1)), ',') > FROM s.t1 LEFT JOIN s.t1 as t2 > ON t2.c3 = t1.c2 GROUP BY t1.c1; > > /* 3. */ SELECT c1, c2, > ARRAY_TO_STRING(ARRAY_AGG((SELECT t3.c1 FROM s.t1 as t3 JOIN s.t1 > as t2 ON t3.c3 = t2.c2)), ',') > FROM s.t1 t1 > GROUP BY c1; > DROP SCHEMA s CASCADE; > The query SELECT t3.c1 FROM s.t1 as t3 JOIN s.t1 as t2 ON t3.c3 = t2.c2 produces multiple rows. Since you are calling the aggregate function on the result set and not as part of the expression, you are not able to get single row as an output. > The output for 1 query: > > c1 > > 2 > 3 > (2 rows) > > 2 Query: > > c1 | array_to_string > +- >1 | 2,3 >2 | >3 | > (3 rows) > > 3 Query gives me a error: > >psql:/tmp/aggregate.sql:24: ERROR: more than one row returned > by a subquery used as an expression > > > The 3 query uses 1 query as inner query. Is there a way to make Query > 3 work with inner query as 1 rather than reverting to 2. > > 3 output should be same as 2. > > I understand that the error message says query 1 when used as sub > query of 3 cannot return more than one row. > > Pardon my limited knowledge of database. > > > I have tried out: > > SELECT c1, c2, > ARRAY_TO_STRING((SELECT ARRAY_AGG(t3.c1) FROM s.t1 as t3 JOIN s.t1 > as t2 ON t3.c3 = t2.c2), ',') > FROM s.t1 t1 > GROUP BY c1; > > This would work since the aggregate function has been used on the column. > Output is : > > c1 | c2 | array_to_string > ++- > 2 | 20 | 2,3 > 1 | 10 | 2,3 > 3 | 30 | 2,3 > > Could one of you help me with the correct query. > > > May you should share some more details of exactly what you are expecting and what is the output/corelation you want in the result of the query. > Thanks > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general > -- -- Best Regards Sameer Kumar | DB Solution Architect *ASHNIK PTE. LTD.* 101 Cecil Street, #11-11 Tong Eng Building, Singapore 069 533 T: +65 6438 3504 | M: +65 8110 0350 | www.ashnik.com
Re: [GENERAL] Thoughts on "Love Your Database"
Le 17/05/2016 11:25, Geoff Winkless a écrit : but they are still looking for a sort of Holy Grail that would definitely convince them. A standard client tool that would come with any PostgreSQL installation would please them. Some sort of psqlGUI, I guess. Why reinvent the wheel? I would say that putting the development effort into the OpenOffice Base app would be time better spent. True. But I must say that I would be looking towards something a bit lighter. LibreOffice is a bit of a heavyweight, IMHO. The "Base" is kind of bizarre to use, I always miss some of its logic, somehow. I have to try its latest version, see if it's decently usable for a lambda user. Sorry if I missed something but what's wrong with pgadmin3 ? There's nothing wrong with pgadmin as such. Yes, nothing wrong at all. It is not meant to make UI. Just some basic features are implemented for table data editing. From the very email you just quoted: they want to design their forms ie (as I understand it) they're after some sort of RAD tool. Yes, that's it, in a way. Although designing forms is not what I would call AD (application development)... When you look back at dBase III or IV, the text user interface which allowed to interact with the data was very simple, and efficient. Designing forms (I didn't do much of these, and I can't remember well) was quite straightforward, and was not at all what I would call an "application development". The latter would be made by using clipper, most often. À+ Pierre -- Pierre Chevalier PChGEI: Pierre Chevalier Géologue Et Informaticien Partenaire DALIBO Mesté Duran 32100 Condom Tél+fax :09 75 27 45 62 06 37 80 33 64 Émail : pierrechevaliergeolCHEZfree.fr icq# : 10432285 jabber: pierre.chevalier1...@jabber.fr http://pierremariechevalier.free.fr/pierre_chevalier_geologue -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Thoughts on "Love Your Database"
Interesting point of view. Time to make a bug report, isn't it? ;-) À+ Pierre Le 18/05/2016 17:44, Adam Brusselback a écrit : On Tue, May 17, 2016 at 1:54 PM, Raymond O'Donnellmailto:r...@iol.ie>> wrote: Having said all that, I've rarely had any trouble with pgAdmin 3 on Windows 7 and XP, Ubuntu and Debian; just a very occasional crash (maybe one every six months). So just to chime in, it has not been at all that stable for my team and I. It's not bad when connections are all stable or you're connecting to a local instance, but if the connection between you and the server isn't perfect... it's one of the least stable pieces of software I've ever used. I'm sure for those who's servers are hosted locally, and you connect through a lan, or those with a decent provider, it works much better than for me. My team is all remote though, and we don't have an office. Each of my employees connects using their own internet connection, with varying reliability. Servers hosted on a cloud provider. Crashes happen multiple times a day for most. Just wanted to share my experience. -- Pierre Chevalier PChGEI: Pierre Chevalier Géologue Et Informaticien Partenaire DALIBO Mesté Duran 32100 Condom Tél+fax :09 75 27 45 62 06 37 80 33 64 Émail : pierrechevaliergeolCHEZfree.fr icq# : 10432285 jabber: pierre.chevalier1...@jabber.fr http://pierremariechevalier.free.fr/pierre_chevalier_geologue -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Use of array_agg and array string on inner query
The original table is : c1 c2 c3 110 220 10 320 10 So c3 of row 3 and row 2 are equal to c2 of row 1. The output I am looking for is : c1 | array_to_string +- 1 | 2,3 2 | 3 | (3 rows) How Can I modify this query : SELECT c1, c2, ARRAY_TO_STRING(ARRAY_AGG((SELECT t3.c1 FROM s.t1 as t3 JOIN s.t1 as t2 ON t3.c3 = t2.c2)), ',') FROM s.t1 t1 GROUP BY c1; DROP SCHEMA s CASCADE; to get me the output desired. Thanks Shankha Banerjee On Wed, May 18, 2016 at 1:57 PM, Sameer Kumar wrote: > > > On Thu, May 19, 2016 at 1:09 AM shankha wrote: >> >> I have the following piece of code: >> >> DROP SCHEMA IF EXISTS s CASCADE; >> CREATE SCHEMA s; >> >> CREATE TABLE "s"."t1" >> ( >> "c1" BigSerial PRIMARY KEY, >> "c2" BigInt NOT NULL, >> "c3" BigInt >> ) >> WITH (OIDS=FALSE); >> >> INSERT INTO s.t1 (c2) VALUES (10); >> INSERT INTO s.t1 (c2, c3) VALUES (20, 10); >> INSERT INTO s.t1 (c2, c3) VALUES (30, 10); >> >> /* 1. */ SELECT t3.c1 FROM s.t1 as t3 JOIN s.t1 as t2 ON t3.c3 = >> t2.c2; >> >> /* 2. */ SELECT t1.c1, ARRAY_TO_STRING(ARRAY_AGG((t2.c1)), ',') >> FROM s.t1 LEFT JOIN s.t1 as t2 >> ON t2.c3 = t1.c2 GROUP BY t1.c1; >> >> /* 3. */ SELECT c1, c2, >> ARRAY_TO_STRING(ARRAY_AGG((SELECT t3.c1 FROM s.t1 as t3 JOIN s.t1 >> as t2 ON t3.c3 = t2.c2)), ',') >> FROM s.t1 t1 >> GROUP BY c1; >> DROP SCHEMA s CASCADE; > > > The query > > SELECT t3.c1 FROM s.t1 as t3 JOIN s.t1 as t2 ON t3.c3 = t2.c2 produces > multiple rows. Since you are calling the aggregate function on the result > set and not as part of the expression, you are not able to get single row as > an output. > > >> >> The output for 1 query: >> >> c1 >> >> 2 >> 3 >> (2 rows) >> >> 2 Query: >> >> c1 | array_to_string >> +- >>1 | 2,3 >>2 | >>3 | >> (3 rows) >> >> 3 Query gives me a error: >> >>psql:/tmp/aggregate.sql:24: ERROR: more than one row returned >> by a subquery used as an expression >> >> >> The 3 query uses 1 query as inner query. Is there a way to make Query >> 3 work with inner query as 1 rather than reverting to 2. >> >> 3 output should be same as 2. >> >> I understand that the error message says query 1 when used as sub >> query of 3 cannot return more than one row. >> >> Pardon my limited knowledge of database. >> >> >> I have tried out: >> >> SELECT c1, c2, >> ARRAY_TO_STRING((SELECT ARRAY_AGG(t3.c1) FROM s.t1 as t3 JOIN s.t1 >> as t2 ON t3.c3 = t2.c2), ',') >> FROM s.t1 t1 >> GROUP BY c1; >> > > This would work since the aggregate function has been used on the column. > >> >> Output is : >> >> c1 | c2 | array_to_string >> ++- >> 2 | 20 | 2,3 >> 1 | 10 | 2,3 >> 3 | 30 | 2,3 >> >> Could one of you help me with the correct query. >> >> > > May you should share some more details of exactly what you are expecting and > what is the output/corelation you want in the result of the query. > >> >> Thanks >> >> >> -- >> Sent via pgsql-general mailing list (pgsql-general@postgresql.org) >> To make changes to your subscription: >> http://www.postgresql.org/mailpref/pgsql-general > > -- > -- > Best Regards > Sameer Kumar | DB Solution Architect > ASHNIK PTE. LTD. > > 101 Cecil Street, #11-11 Tong Eng Building, Singapore 069 533 > > T: +65 6438 3504 | M: +65 8110 0350 | www.ashnik.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Use of array_agg and array string on inner query
On Wed, May 18, 2016 at 1:07 PM, shankha wrote: > > /* 3. */ SELECT c1, c2, > ARRAY_TO_STRING(ARRAY_AGG((SELECT t3.c1 FROM s.t1 as t3 JOIN s.t1 > as t2 ON t3.c3 = t2.c2)), ',') > FROM s.t1 t1 > GROUP BY c1; > DROP SCHEMA s CASCADE; > The following adjustments should work: array_to_string(select array_agg(t3.c1) FROM ... WHERE t2.c1 = t1.c1), ',' The array_agg needs to moved into the subquery - which causes an implicit GROUP BY to be added to the subselect thus ensuring only one row is returned for processing by the array_to_string function. You need to add the where clause to make the subquery correlate to the outer query. You may need to play with it a bit as I didn't try running your example. David J.
Re: [GENERAL] Thoughts on "Love Your Database"
On 5/18/2016 11:05 AM, Pierre Chevalier Géologue wrote: Yes, that's it, in a way. Although designing forms is not what I would call AD (application development)... When you look back at dBase III or IV, the text user interface which allowed to interact with the data was very simple, and efficient. Designing forms (I didn't do much of these, and I can't remember well) was quite straightforward, and was not at all what I would call an "application development". The latter would be made by using clipper, most often. MS Access is all about forms, reports, and data, with visualbasic macros for data manipulation. -- john r pierce, recycling bits in santa cruz
Re: [GENERAL] Use of array_agg and array string on inner query
On Thu, 19 May 2016, 2:07 a.m. shankha, wrote: > The original table is : > > c1 c2 c3 > 110 > 220 10 > 320 10 > > So c3 of row 3 and row 2 are equal to c2 of row 1. > > > The output I am looking for is : > c1 | array_to_string > +- >1 | 2,3 >2 | >3 | > (3 rows) > > How Can I modify this query : > > SELECT c1, c2, > ARRAY_TO_STRING(ARRAY_AGG((SELECT t3.c1 FROM s.t1 as t3 JOIN s.t1 > as t2 ON t3.c3 = t2.c2)), ',') > FROM s.t1 t1 > GROUP BY c1; > DROP SCHEMA s CASCADE; > Move array_agg call around the column name instead of calling it on the select output. The 4th query you have used seems to be working except that it 'kind of' does a cross product or lateral join. You might want to use a CTE instead if bested select and use that with OUTER JOIN or may be in the inner query use a correlated where clause (where t1.c2=t2.c2) > to get me the output desired. > > Thanks > Shankha Banerjee > > > On Wed, May 18, 2016 at 1:57 PM, Sameer Kumar > wrote: > > > > > > On Thu, May 19, 2016 at 1:09 AM shankha > wrote: > >> > >> I have the following piece of code: > >> > >> DROP SCHEMA IF EXISTS s CASCADE; > >> CREATE SCHEMA s; > >> > >> CREATE TABLE "s"."t1" > >> ( > >> "c1" BigSerial PRIMARY KEY, > >> "c2" BigInt NOT NULL, > >> "c3" BigInt > >> ) > >> WITH (OIDS=FALSE); > >> > >> INSERT INTO s.t1 (c2) VALUES (10); > >> INSERT INTO s.t1 (c2, c3) VALUES (20, 10); > >> INSERT INTO s.t1 (c2, c3) VALUES (30, 10); > >> > >> /* 1. */ SELECT t3.c1 FROM s.t1 as t3 JOIN s.t1 as t2 ON t3.c3 = > >> t2.c2; > >> > >> /* 2. */ SELECT t1.c1, ARRAY_TO_STRING(ARRAY_AGG((t2.c1)), ',') > >> FROM s.t1 LEFT JOIN s.t1 as t2 > >> ON t2.c3 = t1.c2 GROUP BY t1.c1; > >> > >> /* 3. */ SELECT c1, c2, > >> ARRAY_TO_STRING(ARRAY_AGG((SELECT t3.c1 FROM s.t1 as t3 JOIN s.t1 > >> as t2 ON t3.c3 = t2.c2)), ',') > >> FROM s.t1 t1 > >> GROUP BY c1; > >> DROP SCHEMA s CASCADE; > > > > > > The query > > > > SELECT t3.c1 FROM s.t1 as t3 JOIN s.t1 as t2 ON t3.c3 = t2.c2 produces > > multiple rows. Since you are calling the aggregate function on the result > > set and not as part of the expression, you are not able to get single > row as > > an output. > > > > > >> > >> The output for 1 query: > >> > >> c1 > >> > >> 2 > >> 3 > >> (2 rows) > >> > >> 2 Query: > >> > >> c1 | array_to_string > >> +- > >>1 | 2,3 > >>2 | > >>3 | > >> (3 rows) > >> > >> 3 Query gives me a error: > >> > >>psql:/tmp/aggregate.sql:24: ERROR: more than one row returned > >> by a subquery used as an expression > >> > >> > >> The 3 query uses 1 query as inner query. Is there a way to make Query > >> 3 work with inner query as 1 rather than reverting to 2. > >> > >> 3 output should be same as 2. > >> > >> I understand that the error message says query 1 when used as sub > >> query of 3 cannot return more than one row. > >> > >> Pardon my limited knowledge of database. > >> > >> > >> I have tried out: > >> > >> SELECT c1, c2, > >> ARRAY_TO_STRING((SELECT ARRAY_AGG(t3.c1) FROM s.t1 as t3 JOIN s.t1 > >> as t2 ON t3.c3 = t2.c2), ',') > >> FROM s.t1 t1 > >> GROUP BY c1; > >> > > > > This would work since the aggregate function has been used on the column. > > > >> > >> Output is : > >> > >> c1 | c2 | array_to_string > >> ++- > >> 2 | 20 | 2,3 > >> 1 | 10 | 2,3 > >> 3 | 30 | 2,3 > >> > >> Could one of you help me with the correct query. > >> > >> > > > > May you should share some more details of exactly what you are expecting > and > > what is the output/corelation you want in the result of the query. > > > >> > >> Thanks > >> > >> > >> -- > >> Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > >> To make changes to your subscription: > >> http://www.postgresql.org/mailpref/pgsql-general > > > > -- > > -- > > Best Regards > > Sameer Kumar | DB Solution Architect > > ASHNIK PTE. LTD. > > > > 101 Cecil Street, #11-11 Tong Eng Building, Singapore 069 533 > > > > T: +65 6438 3504 | M: +65 8110 0350 | www.ashnik.com > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general > -- -- Best Regards Sameer Kumar | DB Solution Architect *ASHNIK PTE. LTD.* 101 Cecil Street, #11-11 Tong Eng Building, Singapore 069 533 T: +65 6438 3504 | M: +65 8110 0350 | www.ashnik.com
Re: [GENERAL] Use of array_agg and array string on inner query
I cannot move the array_agg to around the column name. It has to work as a inner query. I will try out your other suggestion. Thanks Shankha Banerjee On Wed, May 18, 2016 at 2:26 PM, Sameer Kumar wrote: > > > On Thu, 19 May 2016, 2:07 a.m. shankha, wrote: >> >> The original table is : >> >> c1 c2 c3 >> 110 >> 220 10 >> 320 10 >> >> So c3 of row 3 and row 2 are equal to c2 of row 1. >> >> >> The output I am looking for is : >> c1 | array_to_string >> +- >>1 | 2,3 >>2 | >>3 | >> (3 rows) >> >> How Can I modify this query : >> >> SELECT c1, c2, >> ARRAY_TO_STRING(ARRAY_AGG((SELECT t3.c1 FROM s.t1 as t3 JOIN s.t1 >> as t2 ON t3.c3 = t2.c2)), ',') >> FROM s.t1 t1 >> GROUP BY c1; >> DROP SCHEMA s CASCADE; > > > Move array_agg call around the column name instead of calling it on the > select output. > > The 4th query you have used seems to be working except that it 'kind of' > does a cross product or lateral join. You might want to use a CTE instead if > bested select and use that with OUTER JOIN or may be in the inner query use > a correlated where clause (where t1.c2=t2.c2) > >> >> to get me the output desired. >> >> Thanks >> Shankha Banerjee >> >> >> On Wed, May 18, 2016 at 1:57 PM, Sameer Kumar >> wrote: >> > >> > >> > On Thu, May 19, 2016 at 1:09 AM shankha >> > wrote: >> >> >> >> I have the following piece of code: >> >> >> >> DROP SCHEMA IF EXISTS s CASCADE; >> >> CREATE SCHEMA s; >> >> >> >> CREATE TABLE "s"."t1" >> >> ( >> >> "c1" BigSerial PRIMARY KEY, >> >> "c2" BigInt NOT NULL, >> >> "c3" BigInt >> >> ) >> >> WITH (OIDS=FALSE); >> >> >> >> INSERT INTO s.t1 (c2) VALUES (10); >> >> INSERT INTO s.t1 (c2, c3) VALUES (20, 10); >> >> INSERT INTO s.t1 (c2, c3) VALUES (30, 10); >> >> >> >> /* 1. */ SELECT t3.c1 FROM s.t1 as t3 JOIN s.t1 as t2 ON t3.c3 = >> >> t2.c2; >> >> >> >> /* 2. */ SELECT t1.c1, ARRAY_TO_STRING(ARRAY_AGG((t2.c1)), ',') >> >> FROM s.t1 LEFT JOIN s.t1 as t2 >> >> ON t2.c3 = t1.c2 GROUP BY t1.c1; >> >> >> >> /* 3. */ SELECT c1, c2, >> >> ARRAY_TO_STRING(ARRAY_AGG((SELECT t3.c1 FROM s.t1 as t3 JOIN s.t1 >> >> as t2 ON t3.c3 = t2.c2)), ',') >> >> FROM s.t1 t1 >> >> GROUP BY c1; >> >> DROP SCHEMA s CASCADE; >> > >> > >> > The query >> > >> > SELECT t3.c1 FROM s.t1 as t3 JOIN s.t1 as t2 ON t3.c3 = t2.c2 produces >> > multiple rows. Since you are calling the aggregate function on the >> > result >> > set and not as part of the expression, you are not able to get single >> > row as >> > an output. >> > >> > >> >> >> >> The output for 1 query: >> >> >> >> c1 >> >> >> >> 2 >> >> 3 >> >> (2 rows) >> >> >> >> 2 Query: >> >> >> >> c1 | array_to_string >> >> +- >> >>1 | 2,3 >> >>2 | >> >>3 | >> >> (3 rows) >> >> >> >> 3 Query gives me a error: >> >> >> >>psql:/tmp/aggregate.sql:24: ERROR: more than one row returned >> >> by a subquery used as an expression >> >> >> >> >> >> The 3 query uses 1 query as inner query. Is there a way to make Query >> >> 3 work with inner query as 1 rather than reverting to 2. >> >> >> >> 3 output should be same as 2. >> >> >> >> I understand that the error message says query 1 when used as sub >> >> query of 3 cannot return more than one row. >> >> >> >> Pardon my limited knowledge of database. >> >> >> >> >> >> I have tried out: >> >> >> >> SELECT c1, c2, >> >> ARRAY_TO_STRING((SELECT ARRAY_AGG(t3.c1) FROM s.t1 as t3 JOIN s.t1 >> >> as t2 ON t3.c3 = t2.c2), ',') >> >> FROM s.t1 t1 >> >> GROUP BY c1; >> >> >> > >> > This would work since the aggregate function has been used on the >> > column. >> > >> >> >> >> Output is : >> >> >> >> c1 | c2 | array_to_string >> >> ++- >> >> 2 | 20 | 2,3 >> >> 1 | 10 | 2,3 >> >> 3 | 30 | 2,3 >> >> >> >> Could one of you help me with the correct query. >> >> >> >> >> > >> > May you should share some more details of exactly what you are expecting >> > and >> > what is the output/corelation you want in the result of the query. >> > >> >> >> >> Thanks >> >> >> >> >> >> -- >> >> Sent via pgsql-general mailing list (pgsql-general@postgresql.org) >> >> To make changes to your subscription: >> >> http://www.postgresql.org/mailpref/pgsql-general >> > >> > -- >> > -- >> > Best Regards >> > Sameer Kumar | DB Solution Architect >> > ASHNIK PTE. LTD. >> > >> > 101 Cecil Street, #11-11 Tong Eng Building, Singapore 069 533 >> > >> > T: +65 6438 3504 | M: +65 8110 0350 | www.ashnik.com >> >> >> -- >> Sent via pgsql-general mailing list (pgsql-general@postgresql.org) >> To make changes to your subscription: >> http://www.postgresql.org/mailpref/pgsql-general > > -- > -- > Best Regards > Sameer Kumar | DB Solution Architect > ASHNIK PTE. LTD. > > 101 Cecil Street, #11-11 Tong Eng Building, Singapore 069 533 > > T: +65 6438 3504 | M: +65
Re: [GENERAL] Use of array_agg and array string on inner query
On Wed, May 18, 2016 at 2:30 PM, shankha wrote: > I cannot move the array_agg to around the column name. It has to work > as a inner query > . > The following form is used to make an array from a subquery: SELECT ARRAY(SELECT i FROM ( VALUES (1), (2), (3) ) vals (i) ); http://www.postgresql.org/docs/9.5/static/sql-expressions.html 4.2.12; last example Not the most obvious place... David J.
[GENERAL] Londiste 3 pgq events_1_1 table huge
Hi, I couldn't find a mailing list or forum to ask londiste related questions, so I hope someone from this list can help me with this. I have a Londiste based replication setup that is working perfectly since last year. Now I noted the events_1_1 table grew too much (almost exactly the same size of the whole database), so I'm asking how can I clean up the events table without breaking the replication?. Regards, -- Leonardo M. Ramé Medical IT - Griensu S.A. Av. Colón 636 - Piso 8 Of. A X5000EPT -- Córdoba Tel.: +54(351)4246924 +54(351)4247788 +54(351)4247979 int. 19 Cel.: +54 9 (011) 40871877 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Londiste 3 pgq events_1_1 table huge
Hi, Check for long running Idle in transaction sessions. Idle in transaction sessions may holding events table from cleaning itself up. If there is more then days long running idle in transaction sessions, kill them, event table should be cleaned automatically after that. "select pid,state, query_start from pg_stat_activity where state='idle in transaction';" for checking sessions. Rene From: pgsql-general-ow...@postgresql.org on behalf of Leonardo M. Ramé Sent: Wednesday, May 18, 2016 10:23 PM To: PostgreSql-general Subject: [GENERAL] Londiste 3 pgq events_1_1 table huge Hi, I couldn't find a mailing list or forum to ask londiste related questions, so I hope someone from this list can help me with this. I have a Londiste based replication setup that is working perfectly since last year. Now I noted the events_1_1 table grew too much (almost exactly the same size of the whole database), so I'm asking how can I clean up the events table without breaking the replication?. Regards, -- Leonardo M. Ramé Medical IT - Griensu S.A. Av. Colón 636 - Piso 8 Of. A X5000EPT -- Córdoba Tel.: +54(351)4246924 +54(351)4247788 +54(351)4247979 int. 19 Cel.: +54 9 (011) 40871877 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] first_value/last_value
Hello: Is there a plan for 9.7 to enable using the two aggregate function as non-window function? i.e. enabling getting the first/last row in single sql without using window features. There is actually a C-extension for first()/last(). I am wondering if 9.7 would make them built-in function like max/min Thanks
Re: [GENERAL] first_value/last_value
Is there a reason you can't do that now with a limit 1/order by/union all? Just have it ordered one way on the first query and the other on the bottom. That will give you two rows that are the first / last in your set based on whatever column you order on. On May 18, 2016 8:47 PM, "Tom Smith" wrote: > Hello: > > Is there a plan for 9.7 to enable using the two aggregate function > as non-window function? i.e. enabling getting the first/last row > in single sql without using window features. > There is actually a C-extension for first()/last(). > I am wondering if 9.7 would make them built-in function like max/min > > Thanks >
Re: [GENERAL] first_value/last_value
On Wednesday, May 18, 2016, Tom Smith wrote: > Hello: > > Is there a plan for 9.7 to enable using the two aggregate function > as non-window function? i.e. enabling getting the first/last row > in single sql without using window features. > There is actually a C-extension for first()/last(). > I am wondering if 9.7 would make them built-in function like max/min > > The "distinct on" capability provides this for at least some use cases. Given this is first I've even seen a gripe on this, and the window versions have existed for something like 6 years, I'd have to say this isn't high on anyone's radar. David J.
Re: [GENERAL] first_value/last_value
I need to use both in single sql. On Wed, May 18, 2016 at 9:08 PM, Adam Brusselback wrote: > Is there a reason you can't do that now with a limit 1/order by/union all? > Just have it ordered one way on the first query and the other on the > bottom. That will give you two rows that are the first / last in your set > based on whatever column you order on. > On May 18, 2016 8:47 PM, "Tom Smith" wrote: > >> Hello: >> >> Is there a plan for 9.7 to enable using the two aggregate function >> as non-window function? i.e. enabling getting the first/last row >> in single sql without using window features. >> There is actually a C-extension for first()/last(). >> I am wondering if 9.7 would make them built-in function like max/min >> >> Thanks >> >
Re: [GENERAL] first_value/last_value
Here is an example that works in a single query. Since you have two different orders you want the data back in, you need to use subqueries to get the proper data back, but it works, and is very fast. CREATE TEMPORARY TABLE foo AS SELECT generate_series as bar FROM generate_series(1, 100); CREATE INDEX idx_foo_bar ON foo (bar); SELECT * FROM ( SELECT bar FROM foo ORDER BY bar asc LIMIT 1 ) x UNION ALL SELECT * FROM ( SELECT bar FROM foo ORDER BY bar desc LIMIT 1 ) y; DROP TABLE foo;
Re: [GENERAL] first_value/last_value
On Wed, May 18, 2016 at 10:36 PM, Adam Brusselback < adambrusselb...@gmail.com> wrote: > Here is an example that works in a single query. Since you have two > different orders you want the data back in, you need to use subqueries to > get the proper data back, but it works, and is very fast. > > CREATE TEMPORARY TABLE foo AS > SELECT generate_series as bar > FROM generate_series(1, 100); > > CREATE INDEX idx_foo_bar ON foo (bar); > > > SELECT * > FROM ( > SELECT bar > FROM foo > ORDER BY bar asc > LIMIT 1 > ) x > UNION ALL > SELECT * > FROM ( > SELECT bar > FROM foo > ORDER BY bar desc > LIMIT 1 > ) y; > > DROP TABLE foo; > Seems to me SELECT min(), max() FROM deja.vu ; would also work. -- *Melvin Davidson* I reserve the right to fantasize. Whether or not you wish to share my fantasy is entirely up to you.
Re: [GENERAL] first_value/last_value
It would really save all the troubles for many people if postgresql has a built-in first/last function along with sum/avg. There is already a C extension and a wiki sample and implemented for window function. I am curious why these two functions were not added along their window implementation counter part, for completness and consistency On Wed, May 18, 2016 at 10:42 PM, Melvin Davidson wrote: > > > On Wed, May 18, 2016 at 10:36 PM, Adam Brusselback < > adambrusselb...@gmail.com> wrote: > >> Here is an example that works in a single query. Since you have two >> different orders you want the data back in, you need to use subqueries to >> get the proper data back, but it works, and is very fast. >> >> CREATE TEMPORARY TABLE foo AS >> SELECT generate_series as bar >> FROM generate_series(1, 100); >> >> CREATE INDEX idx_foo_bar ON foo (bar); >> >> >> SELECT * >> FROM ( >> SELECT bar >> FROM foo >> ORDER BY bar asc >> LIMIT 1 >> ) x >> UNION ALL >> SELECT * >> FROM ( >> SELECT bar >> FROM foo >> ORDER BY bar desc >> LIMIT 1 >> ) y; >> >> DROP TABLE foo; >> > > Seems to me SELECT min(), max() FROM deja.vu ; would also > work. > > > -- > *Melvin Davidson* > I reserve the right to fantasize. Whether or not you > wish to share my fantasy is entirely up to you. >
