Re: [HACKERS] proposal - psql - show longest tables
* Tom Lane (t...@sss.pgh.pa.us) wrote: Even if we thought the functionality was worth the trouble, which I continue to doubt, this particular syntax proposal is a disaster. Agreed. While there might be things worthwhile to add to psql's backslash commands, this isn't one of those. Thanks, Stephen signature.asc Description: Digital signature
Re: [HACKERS] proposal - psql - show longest tables
On Sun, Jul 21, 2013 at 12:47 AM, Pavel Stehule pavel.steh...@gmail.com wrote: Hello all I very often use a little bit adjusted psql system queries to detection TOP N sized tables. I am thinking so it can be useful for all users I propose a few new commands \dts [N|size] ... show N largest tables | show tables larger than size ordered by size \dis [N|size] ... show N largest indexes | show indexes larger than size ordered by size \dtst [N|size] ... show N largest total size | show tables where total size is larger than size ordered by total size \dtr [N] ... show N largest tables (ordered by rows) example: \dts 10 --- top 10 tables ordered by size \dts 10MB -- tables larger than 10MB ordered by size Schema |Name | Type |Owner| Size +-+---+-+---+- public | eshop_users | table | eshop_owner | 16 kB public | zamestnanci | table | eshop_owner | 16 kB What do you think about this proposal? Comments, notes? I think our \d commands are in inscrutable morass of indecipherable gobbledygook as it is, and this is only one more step down the road to complete insanity. :-( Rather than just continuing to add more imposible-to-remember syntax, we really need a better design here. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] proposal - psql - show longest tables
2013/7/22 Robert Haas robertmh...@gmail.com: On Sun, Jul 21, 2013 at 12:47 AM, Pavel Stehule pavel.steh...@gmail.com wrote: Hello all I very often use a little bit adjusted psql system queries to detection TOP N sized tables. I am thinking so it can be useful for all users I propose a few new commands \dts [N|size] ... show N largest tables | show tables larger than size ordered by size \dis [N|size] ... show N largest indexes | show indexes larger than size ordered by size \dtst [N|size] ... show N largest total size | show tables where total size is larger than size ordered by total size \dtr [N] ... show N largest tables (ordered by rows) example: \dts 10 --- top 10 tables ordered by size \dts 10MB -- tables larger than 10MB ordered by size Schema |Name | Type |Owner| Size +-+---+-+---+- public | eshop_users | table | eshop_owner | 16 kB public | zamestnanci | table | eshop_owner | 16 kB What do you think about this proposal? Comments, notes? I think our \d commands are in inscrutable morass of indecipherable gobbledygook as it is, and this is only one more step down the road to complete insanity. :-( these commands are targeted to advanced users, and four chars should not be a problem. It has a same advantage and disadvantage as vim UI. it is fast for advanced users, and strange for beginners :(. Rather than just continuing to add more imposible-to-remember syntax, we really need a better design here. do you have any tip? Pavel -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] proposal - psql - show longest tables
On Mon, Jul 22, 2013 at 2:03 PM, Robert Haas robertmh...@gmail.com wrote: On Sun, Jul 21, 2013 at 12:47 AM, Pavel Stehule pavel.steh...@gmail.com wrote: Hello all I very often use a little bit adjusted psql system queries to detection TOP N sized tables. I am thinking so it can be useful for all users I propose a few new commands \dts [N|size] ... show N largest tables | show tables larger than size ordered by size \dis [N|size] ... show N largest indexes | show indexes larger than size ordered by size \dtst [N|size] ... show N largest total size | show tables where total size is larger than size ordered by total size \dtr [N] ... show N largest tables (ordered by rows) example: \dts 10 --- top 10 tables ordered by size \dts 10MB -- tables larger than 10MB ordered by size Schema |Name | Type |Owner| Size +-+---+-+---+- public | eshop_users | table | eshop_owner | 16 kB public | zamestnanci | table | eshop_owner | 16 kB What do you think about this proposal? Comments, notes? I think our \d commands are in inscrutable morass of indecipherable gobbledygook as it is, and this is only one more step down the road to complete insanity. :-( Rather than just continuing to add more imposible-to-remember syntax, we really need a better design here. These type of administrative tasks should be implemented as stored procedures or functions, not enhancements to psql. That way non-psql clients can leverage them and you can integrate them to other queries. Another advantage is that they can be implemented as extension. SELECT * from top5(); Is a little more of a pain to type. But with my psql-fu I can cut that down with \i if I'm so inclined. merlin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] proposal - psql - show longest tables
On Mon, Jul 22, 2013 at 3:13 PM, Merlin Moncure mmonc...@gmail.com wrote: On Mon, Jul 22, 2013 at 2:03 PM, Robert Haas robertmh...@gmail.com wrote: On Sun, Jul 21, 2013 at 12:47 AM, Pavel Stehule pavel.steh...@gmail.com wrote: Hello all I very often use a little bit adjusted psql system queries to detection TOP N sized tables. I am thinking so it can be useful for all users I propose a few new commands \dts [N|size] ... show N largest tables | show tables larger than size ordered by size \dis [N|size] ... show N largest indexes | show indexes larger than size ordered by size \dtst [N|size] ... show N largest total size | show tables where total size is larger than size ordered by total size \dtr [N] ... show N largest tables (ordered by rows) example: \dts 10 --- top 10 tables ordered by size \dts 10MB -- tables larger than 10MB ordered by size Schema |Name | Type |Owner| Size +-+---+-+---+- public | eshop_users | table | eshop_owner | 16 kB public | zamestnanci | table | eshop_owner | 16 kB What do you think about this proposal? Comments, notes? I think our \d commands are in inscrutable morass of indecipherable gobbledygook as it is, and this is only one more step down the road to complete insanity. :-( Rather than just continuing to add more imposible-to-remember syntax, we really need a better design here. These type of administrative tasks should be implemented as stored procedures or functions, not enhancements to psql. That way non-psql clients can leverage them and you can integrate them to other queries. Another advantage is that they can be implemented as extension. SELECT * from top5(); Is a little more of a pain to type. But with my psql-fu I can cut that down with \i if I'm so inclined. Yeah, I think that's a very reasonable approach to this kind of problem. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] proposal - psql - show longest tables
2013/7/22 Merlin Moncure mmonc...@gmail.com: On Mon, Jul 22, 2013 at 2:03 PM, Robert Haas robertmh...@gmail.com wrote: On Sun, Jul 21, 2013 at 12:47 AM, Pavel Stehule pavel.steh...@gmail.com wrote: Hello all I very often use a little bit adjusted psql system queries to detection TOP N sized tables. I am thinking so it can be useful for all users I propose a few new commands \dts [N|size] ... show N largest tables | show tables larger than size ordered by size \dis [N|size] ... show N largest indexes | show indexes larger than size ordered by size \dtst [N|size] ... show N largest total size | show tables where total size is larger than size ordered by total size \dtr [N] ... show N largest tables (ordered by rows) example: \dts 10 --- top 10 tables ordered by size \dts 10MB -- tables larger than 10MB ordered by size Schema |Name | Type |Owner| Size +-+---+-+---+- public | eshop_users | table | eshop_owner | 16 kB public | zamestnanci | table | eshop_owner | 16 kB What do you think about this proposal? Comments, notes? I think our \d commands are in inscrutable morass of indecipherable gobbledygook as it is, and this is only one more step down the road to complete insanity. :-( Rather than just continuing to add more imposible-to-remember syntax, we really need a better design here. These type of administrative tasks should be implemented as stored procedures or functions, not enhancements to psql. That way non-psql clients can leverage them and you can integrate them to other queries. Another advantage is that they can be implemented as extension. SELECT * from top5(); Is not a problem for any advanced user write these queries. But it is hard use a parametrized query inside psql. Is a little more of a pain to type. But with my psql-fu I can cut that down with \i if I'm so inclined. you cannot use parameters - then I have to have prepared files like top10, top20, ... what is not too friendly Regards Pavel merlin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] proposal - psql - show longest tables
On 07/22/2013 03:11 PM, Pavel Stehule wrote: 2013/7/22 Robert Haas robertmh...@gmail.com: Rather than just continuing to add more imposible-to-remember syntax, we really need a better design here. do you have any tip? I agree with Robert. My tip is this: when you're in a hole, the first thing to do is to stop digging. cheers andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] proposal - psql - show longest tables
On Mon, Jul 22, 2013 at 12:40 PM, Andrew Dunstan and...@dunslane.net wrote: On 07/22/2013 03:11 PM, Pavel Stehule wrote: 2013/7/22 Robert Haas robertmh...@gmail.com: Rather than just continuing to add more imposible-to-remember syntax, we really need a better design here. do you have any tip? I agree with Robert. My tip is this: when you're in a hole, the first thing to do is to stop digging. I don't think that Pavel believes himself to be in a hole. After setting up my .psqlrc file as I normally do, I could do this: :rtsize limit 10; But it doesn't have the 'MB' feature, and if I want to help someone else I first have to explain to them how to set their .psqlrc file the same as mine, which is rather a bummer. Is looking for the biggest tables a common enough thing that it should be available to everyone, without needing custom customization? Cheers, Jeff -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] proposal - psql - show longest tables
Pavel Stehule pavel.steh...@gmail.com writes: SELECT * from top5(); $ TABLE top5; -- add a view on top of the SRF you cannot use parameters - then I have to have prepared files like top10, top20, ... what is not too friendly The SRF could be using custom GUCs so that you can parametrize it, or just even classic parameters… $ TABLE top(5); -- needs a patch to accept SRF here… $ TABLE top LIMIT 5; $ SET top.limit = 5; $ TABLE top; Regards, -- Dimitri Fontaine http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] proposal - psql - show longest tables
On Mon, Jul 22, 2013 at 02:44:59PM -0700, Dimitri Fontaine wrote: Pavel Stehule pavel.steh...@gmail.com writes: SELECT * from top5(); $ TABLE top5; -- add a view on top of the SRF you cannot use parameters - then I have to have prepared files like top10, top20, ... what is not too friendly The SRF could be using custom GUCs so that you can parametrize it, or just even classic parameters… $ TABLE top(5); -- needs a patch to accept SRF here… Andrew Gierth will probably be posting a design patch for something similar soon :) Cheers, David. -- David Fetter da...@fetter.org 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-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] proposal - psql - show longest tables
On Mon, Jul 22, 2013 at 03:55:33PM -0700, David Fetter wrote: On Mon, Jul 22, 2013 at 02:44:59PM -0700, Dimitri Fontaine wrote: Pavel Stehule pavel.steh...@gmail.com writes: SELECT * from top5(); $ TABLE top5; -- add a view on top of the SRF you cannot use parameters - then I have to have prepared files like top10, top20, ... what is not too friendly The SRF could be using custom GUCs so that you can parametrize it, or just even classic parameters… $ TABLE top(5); -- needs a patch to accept SRF here… Andrew Gierth will probably be posting a design patch for something similar soon :) Probably not :P Andrew will be posting a design and patch if and when he decides it's appropriate to do so. Cheers, David. -- David Fetter da...@fetter.org 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-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] proposal - psql - show longest tables
Jeff Janes jeff.ja...@gmail.com writes: Is looking for the biggest tables a common enough thing that it should be available to everyone, without needing custom customization? I don't really think so. It's surely not much harder than select relname, pg_relation_size(oid) from pg_class order by 2 desc; Moreover, the people who need this likely don't need it as a psql command, but rather as something available to monitoring tools. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] proposal - psql - show longest tables
On 07/22/2013 04:26 PM, Jeff Janes wrote: On Mon, Jul 22, 2013 at 12:40 PM, Andrew Dunstan and...@dunslane.net wrote: On 07/22/2013 03:11 PM, Pavel Stehule wrote: 2013/7/22 Robert Haas robertmh...@gmail.com: Rather than just continuing to add more imposible-to-remember syntax, we really need a better design here. do you have any tip? I agree with Robert. My tip is this: when you're in a hole, the first thing to do is to stop digging. I don't think that Pavel believes himself to be in a hole. I'm not suggesting he's in a hole - I'm suggesting we are. cheers andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] proposal - psql - show longest tables
I agree with Robert. My tip is this: when you're in a hole, the first thing to do is to stop digging. I don't think that Pavel believes himself to be in a hole. After setting up my .psqlrc file as I normally do, I could do this: :rtsize limit 10; But it doesn't have the 'MB' feature, and if I want to help someone else I first have to explain to them how to set their .psqlrc file the same as mine, which is rather a bummer. Is looking for the biggest tables a common enough thing that it should be available to everyone, without needing custom customization? I am thinking so our psql interface is not complete without this feature in this are. But, sure, it is my opinion only. Everybody know command \dt and then should to learn only one char more s or r. In this time, we use (in GoodData) patch, that change order for \dt+ from alphabet to size ordered. But it is too limited. Regards Pavel Cheers, Jeff -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] proposal - psql - show longest tables
2013/7/23 Tom Lane t...@sss.pgh.pa.us: Jeff Janes jeff.ja...@gmail.com writes: Is looking for the biggest tables a common enough thing that it should be available to everyone, without needing custom customization? I don't really think so. It's surely not much harder than select relname, pg_relation_size(oid) from pg_class order by 2 desc; Moreover, the people who need this likely don't need it as a psql command, but rather as something available to monitoring tools. I can do it - but it is not consistent with other psql commands - so why we have \dt statement ? and this was is not simple for people that are on basic level. My motivations for this proposal are: * comfortable usage of psql * consistency with current psql design (I don't expect major reimplementation and redesign in next 5 years) and its completation * very simply implementation we use a splunk, but when you have opened psql console, is not comfortable to swith to splunk. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] proposal - psql - show longest tables
2013/7/22 Dimitri Fontaine dimi...@2ndquadrant.fr: Pavel Stehule pavel.steh...@gmail.com writes: SELECT * from top5(); $ TABLE top5; -- add a view on top of the SRF you cannot use parameters - then I have to have prepared files like top10, top20, ... what is not too friendly The SRF could be using custom GUCs so that you can parametrize it, or just even classic parameters… $ TABLE top(5); -- needs a patch to accept SRF here… $ TABLE top LIMIT 5; $ SET top.limit = 5; $ TABLE top; It just not comfortable and it is not consistent with current psql commands design. In my proposal, you should to learn just char s. I expect so every body knows \dt so \dts 10 compare with: $ TABLE top(5); -- needs a patch to accept SRF here… $ TABLE top LIMIT 5; Regards Pavel Regards, -- Dimitri Fontaine http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] proposal - psql - show longest tables
2013/7/23 Pavel Stehule pavel.steh...@gmail.com: 2013/7/22 Dimitri Fontaine dimi...@2ndquadrant.fr: Pavel Stehule pavel.steh...@gmail.com writes: SELECT * from top5(); $ TABLE top5; -- add a view on top of the SRF you cannot use parameters - then I have to have prepared files like top10, top20, ... what is not too friendly The SRF could be using custom GUCs so that you can parametrize it, or just even classic parameters… $ TABLE top(5); -- needs a patch to accept SRF here… $ TABLE top LIMIT 5; $ SET top.limit = 5; $ TABLE top; It just not comfortable and it is not consistent with current psql commands design. In my proposal, you should to learn just char s. I expect so every body knows \dt so \dts 10 compare with: $ TABLE top(5); -- needs a patch to accept SRF here… $ TABLE top LIMIT 5; I know so psql commands looks strange, but after few years when I work with other db, I am thinking so this (ingres design) was very practical - we have almost all important informations after press two or three keys. This has no any other database, and a few database copy this interface from us - Vertica, Monetdb. Regards Pavel Regards Pavel Regards, -- Dimitri Fontaine http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] proposal - psql - show longest tables
Robert Haas robertmh...@gmail.com writes: On Sun, Jul 21, 2013 at 12:47 AM, Pavel Stehule pavel.steh...@gmail.com wrote: I propose a few new commands \dts [N|size] ... show N largest tables | show tables larger than size ordered by size \dis [N|size] ... show N largest indexes | show indexes larger than size ordered by size \dtst [N|size] ... show N largest total size | show tables where total size is larger than size ordered by total size \dtr [N] ... show N largest tables (ordered by rows) I think our \d commands are in inscrutable morass of indecipherable gobbledygook as it is, and this is only one more step down the road to complete insanity. :-( Indeed. At least in this particular design, there is no sane way to tell the difference between this family of commands and the \dtisv family --- which has completely different behavior, starting with what it thinks the argument means. Even if you can come up with some arguably logical rule for the code to use, users will never remember which is which. In fact, the first three of those already have defined meanings, and while the fourth does not AFAIR, the current psql code nonetheless takes it, ignoring the r. Even if we thought the functionality was worth the trouble, which I continue to doubt, this particular syntax proposal is a disaster. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] proposal - psql - show longest tables
2013/7/23 Tom Lane t...@sss.pgh.pa.us: Robert Haas robertmh...@gmail.com writes: On Sun, Jul 21, 2013 at 12:47 AM, Pavel Stehule pavel.steh...@gmail.com wrote: I propose a few new commands \dts [N|size] ... show N largest tables | show tables larger than size ordered by size \dis [N|size] ... show N largest indexes | show indexes larger than size ordered by size \dtst [N|size] ... show N largest total size | show tables where total size is larger than size ordered by total size \dtr [N] ... show N largest tables (ordered by rows) I think our \d commands are in inscrutable morass of indecipherable gobbledygook as it is, and this is only one more step down the road to complete insanity. :-( Indeed. At least in this particular design, there is no sane way to tell the difference between this family of commands and the \dtisv family --- which has completely different behavior, starting with what it thinks the argument means. Even if you can come up with some arguably logical rule for the code to use, users will never remember which is which. In fact, the first three of those already have defined meanings, and while the fourth does not AFAIR, the current psql code nonetheless takes it, ignoring the r. Even if we thought the functionality was worth the trouble, which I continue to doubt, this particular syntax proposal is a disaster. I disagree - if it works well for vim editor, then it should to work in psql too. There is not too much other possibilities, how to implement TUI interface :( Regards Pavel regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
