Thread: 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
--- 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
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
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
<p><font size="2">Sorry for top-posting -- challenged reader.<br /><br /> Perhaps a future addition as \L ?<br /><br /> Thiscommand doesn't seem to be used and could be documented as being subject to permissions and slower.<br /><br /> I actuallywould find this useful, but there are other ways of getting it. But having the option would be nice sometimes IMHO.<br/><br /> [I've been testing 8.3beta1 with no issues and have just installed the beta2 release, hence I've been lurkingon this list. No errors other than self-inflicted ones.]<br /><br /> Greg Williamson<br /> Senior DBA<br /> GlobeXplorerLLC, a DigitalGlobe company<br /><br /> 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 andmust 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 theoriginal message.<br /><br /> (My corporate masters made me say this.)<br /><br /><br /><br /> -----Original Message-----<br/> From: pgsql-hackers-owner@postgresql.org on behalf of Tom Lane<br /> Sent: Wed 10/31/2007 5:44 PM<br />To: Andrew Dunstan<br /> Cc: andy; PostgreSQL-development<br /> Subject: Re: [HACKERS] psql show dbsize?<br /><br /> AndrewDunstan <andrew@dunslane.net> writes:<br /> > Perhaps both these considerations dictate providing anothercommand or a<br /> > special flavor of \l instead of just modifying it?<br /><br /> I've seen no argument madewhy \l should print this info at all.<br /><br /> regards, tom lane<br /><br /> ---------------------------(endof broadcast)---------------------------<br /> TIP 2: Don't 'kill -9' the postmaster<br /><br/></font>
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
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
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?
>
> http://www.postgresql.org/docs/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. +