psql show dbsize?
I know its way too late in the game, sorry, but it's a very small patch...
I was wondering if this could be added to 8.3: it adds the dbsize to \l
in psql.
It looks like this:
List of databases
Name | Owner | Encoding | Dbsize
-----------+----------+----------+---------
andy | andy | LATIN1 | 4255 kB
cramd | andy | LATIN1 | 526 MB
postgres | postgres | LATIN1 | 4263 kB
template0 | postgres | LATIN1 | 4136 kB
template1 | postgres | LATIN1 | 4255 kB
(5 rows)
pretty nice, huh?
-Andy
Attachments:
psqldbsize.patchtext/plain; name=psqldbsize.patchDownload
--- src/bin/psql/describe.orig 2007-10-31 13:37:04.130331740 -0500
+++ src/bin/psql/describe.c 2007-10-31 13:37:07.130371448 -0500
@@ -400,6 +400,9 @@
appendPQExpBuffer(&buf,
",\n pg_catalog.pg_encoding_to_char(d.encoding) as \"%s\"",
_("Encoding"));
+ appendPQExpBuffer(&buf,
+ ",\n pg_size_pretty(pg_database_size(d.oid)) as \"%s\"",
+ _("Dbsize"));
if (verbose)
{
appendPQExpBuffer(&buf,
andy wrote:
I know its way too late in the game, sorry, but it's a very small patch...
I was wondering if this could be added to 8.3: it adds the dbsize to \l
in psql.
8.3 is many months beyond feature-freeze, so no, that's not likely to
happen.
It looks like this:
List of databases
Name | Owner | Encoding | Dbsize
-----------+----------+----------+---------
andy | andy | LATIN1 | 4255 kB
cramd | andy | LATIN1 | 526 MB
postgres | postgres | LATIN1 | 4263 kB
template0 | postgres | LATIN1 | 4136 kB
template1 | postgres | LATIN1 | 4255 kB
(5 rows)pretty nice, huh?
Not sure I like it at all. You've just turned \l from something that's
essentially free (a lookup in pg_database, which is very likely to be
either cached or at least very small) to something that can carry a
significant I/O cost if you have a lot of/large databases.
//Magnus
andy <andy@squeakycode.net> writes:
I know its way too late in the game, sorry, but it's a very small patch...
(1) What's the performance impact? I should think that this makes \l orders
of magnitude slower.
(2) Doesn't this render \l entirely nonfunctional for users who don't
have CONNECT privilege to all DBs in the installation?
regards, tom lane
Tom Lane wrote:
andy <andy@squeakycode.net> writes:
I know its way too late in the game, sorry, but it's a very small patch...
(1) What's the performance impact? I should think that this makes \l orders
of magnitude slower.(2) Doesn't this render \l entirely nonfunctional for users who don't
have CONNECT privilege to all DBs in the installation?
Perhaps both these considerations dictate providing another command or a
special flavor of \l instead of just modifying it?
cheers
andrew
Andrew Dunstan <andrew@dunslane.net> writes:
Perhaps both these considerations dictate providing another command or a
special flavor of \l instead of just modifying it?
I've seen no argument made why \l should print this info at all.
regards, tom lane
Sorry for top-posting -- challenged reader.
Perhaps a future addition as \L ?
This command doesn't seem to be used and could be documented as being subject to permissions and slower.
I actually would find this useful, but there are other ways of getting it. But having the option would be nice sometimes IMHO.
[I've been testing 8.3beta1 with no issues and have just installed the beta2 release, hence I've been lurking on this list. No errors other than self-inflicted ones.]
Greg Williamson
Senior DBA
GlobeXplorer LLC, a DigitalGlobe company
Confidentiality Notice: This e-mail message, including any attachments, is for the sole use of the intended recipient(s) and may contain confidential and privileged information and must be protected in accordance with those provisions. Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient, please contact the sender by reply e-mail and destroy all copies of the original message.
(My corporate masters made me say this.)
-----Original Message-----
From: pgsql-hackers-owner@postgresql.org on behalf of Tom Lane
Sent: Wed 10/31/2007 5:44 PM
To: Andrew Dunstan
Cc: andy; PostgreSQL-development
Subject: Re: [HACKERS] psql show dbsize?
Andrew Dunstan <andrew@dunslane.net> writes:
Perhaps both these considerations dictate providing another command or a
special flavor of \l instead of just modifying it?
I've seen no argument made why \l should print this info at all.
regards, tom lane
---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster
Tom Lane wrote:
andy <andy@squeakycode.net> writes:
I know its way too late in the game, sorry, but it's a very small patch...
(1) What's the performance impact? I should think that this makes \l orders
of magnitude slower.(2) Doesn't this render \l entirely nonfunctional for users who don't
have CONNECT privilege to all DBs in the installation?regards, tom lane
Yeah... I guess lesson learned: a small patch does not mean small affect.
Ok, never mind... move along, nothing to see here :-)
-Andy
tgl@sss.pgh.pa.us (Tom Lane) writes:
Andrew Dunstan <andrew@dunslane.net> writes:
Perhaps both these considerations dictate providing another command or a
special flavor of \l instead of just modifying it?I've seen no argument made why \l should print this info at all.
Its interesting information, but I agree that there are BIG
disadvantages to adding it to \l directly. If there's an "\lv" or
such, where it's more certain that people want extended information,
and perhaps that they have appropriate permissions.
--
(format nil "~S@~S" "cbbrowne" "cbbrowne.com")
http://linuxdatabases.info/info/sgml.html
Eagles may soar, but weasels don't get sucked into jet engines.
Chris Browne wrote:
tgl@sss.pgh.pa.us (Tom Lane) writes:
Andrew Dunstan <andrew@dunslane.net> writes:
Perhaps both these considerations dictate providing another command or a
special flavor of \l instead of just modifying it?I've seen no argument made why \l should print this info at all.
Its interesting information, but I agree that there are BIG
disadvantages to adding it to \l directly. If there's an "\lv" or
such, where it's more certain that people want extended information,
and perhaps that they have appropriate permissions.
Humm... I wonder, instead of putting it in \l, what if we had a \stats
that print stuff just about the db your connected to, and it could
probably find a bunch of other info to print besides just the dbsize.
-Andy
On 11/1/07, Chris Browne <cbbrowne@acm.org> wrote:
tgl@sss.pgh.pa.us (Tom Lane) writes:
Andrew Dunstan <andrew@dunslane.net> writes:
Perhaps both these considerations dictate providing another command or a
special flavor of \l instead of just modifying it?I've seen no argument made why \l should print this info at all.
Its interesting information, but I agree that there are BIG
disadvantages to adding it to \l directly. If there's an "\lv" or
such, where it's more certain that people want extended information,
and perhaps that they have appropriate permissions.
I'd find this convenient too. Although \l+ would be more consistent
with the \d series of commands.
Cheers
BJ
"Brendan Jurd" <direvus@gmail.com> writes:
I'd find this convenient too. Although \l+ would be more consistent
with the \d series of commands.
Putting it into \l+ would address my gripe about increased execution
time. The permissions angle still bothers me though. AFAIR there are
no psql catalog-inquiry backslash commands that require any special
permissions, so making \l+ into something that's quite likely to fail
in a locked-down installation seems out of place.
Is there a way to get it to just not print anything, instead of failing,
for DBs you don't have privileges for?
regards, tom lane
On Thursday 01 November 2007 00:44:16 Tom Lane wrote:
Andrew Dunstan <andrew@dunslane.net> writes:
Perhaps both these considerations dictate providing another command or a
special flavor of \l instead of just modifying it?I've seen no argument made why \l should print this info at all.
regards, tom lane
What about \l+ ?
The '+' is already in \d, so it's a known "feature", and then people wanting
more info from \l can use \l+.
--
Andreas Joseph Krogh <andreak@officenet.no>
Senior Software Developer / Manager
------------------------+---------------------------------------------+
OfficeNet AS | The most difficult thing in the world is to |
Karenslyst Allé 11 | know how to do a thing and to watch |
PO. Box 529 Skøyen | somebody else doing it wrong, without |
0214 Oslo | comment. |
NORWAY | |
Tlf: +47 24 15 38 90 | |
Fax: +47 24 15 38 91 | |
Mobile: +47 909 56 963 | |
------------------------+---------------------------------------------+
Added to TODO:
o Have \l+ show database size, if permissions allow
Ideally it will not generate an error for invalid permissions
---------------------------------------------------------------------------
Tom Lane wrote:
"Brendan Jurd" <direvus@gmail.com> writes:
I'd find this convenient too. Although \l+ would be more consistent
with the \d series of commands.Putting it into \l+ would address my gripe about increased execution
time. The permissions angle still bothers me though. AFAIR there are
no psql catalog-inquiry backslash commands that require any special
permissions, so making \l+ into something that's quite likely to fail
in a locked-down installation seems out of place.Is there a way to get it to just not print anything, instead of failing,
for DBs you don't have privileges for?regards, tom lane
---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?
--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://postgres.enterprisedb.com
+ If your life is a hard drive, Christ can be your backup. +