Re: 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

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(at)squeakycode(dot)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(at)squeakycode(dot)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(at)dunslane(dot)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(at)postgresql(dot)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(at)dunslane(dot)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(at)squeakycode(dot)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(at)sss(dot)pgh(dot)pa(dot)us (Tom Lane) writes:
> Andrew Dunstan <andrew(at)dunslane(dot)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(at)~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(at)sss(dot)pgh(dot)pa(dot)us (Tom Lane) writes:
>> Andrew Dunstan <andrew(at)dunslane(dot)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(at)acm(dot)org> wrote:
> tgl(at)sss(dot)pgh(dot)pa(dot)us (Tom Lane) writes:
> > Andrew Dunstan <andrew(at)dunslane(dot)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(at)gmail(dot)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(at)dunslane(dot)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(at)officenet(dot)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(at)gmail(dot)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(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://postgres.enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +