Lists: | pgsql-hackers |
---|
From: | Andrew Gilligan <andy(at)tcpd(dot)net> |
---|---|
To: | pgsql-hackers(at)postgresql(dot)org |
Subject: | [PATCH] Add size/acl information when listing databases |
Date: | 2008-01-20 01:12:41 |
Message-ID: | 488C2FE3-A6C7-4CFA-BD3C-F0588DA81F2E@tcpd.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-hackers |
Greetings,
Attached is a rather small change to src/bin/psql/describe.c to
show database size and permissions when using the psql \l command.
Typical output would be:
List of databases
Name | Owner | Encoding | Size | Access privileges
-----------+-------+----------+---------
+-------------------------------------------
andy | andy | UTF8 | 734 MB | {andy=CTc/andy}
postgres | pgsql | UTF8 | 3914 kB |
template0 | pgsql | UTF8 | 3656 kB | {=c/pgsql,pgsql=CTc/pgsql}
template1 | pgsql | UTF8 | 4034 kB | {=c/pgsql,pgsql=CTc/pgsql}
Hope someone finds this useful.
Best regards,
-Andy
Attachment | Content-Type | Size |
---|---|---|
patch-psql-describe.c | application/octet-stream | 474 bytes |
From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | Andrew Gilligan <andy(at)tcpd(dot)net> |
Cc: | pgsql-hackers(at)postgresql(dot)org |
Subject: | Re: [PATCH] Add size/acl information when listing databases |
Date: | 2008-01-20 02:06:59 |
Message-ID: | 2015.1200794819@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-hackers |
Andrew Gilligan <andy(at)tcpd(dot)net> writes:
> Attached is a rather small change to src/bin/psql/describe.c to
> show database size and permissions when using the psql \l command.
Doesn't this slow down \l by several orders of magnitude? There's
also the small problem that the query will fail entirely if there are
any databases the current user cannot connect to.
The ACL part is fine, the database size not so much. I could see
relegating the size to a \l+ option, but you still have to deal with the
permissions problem.
regards, tom lane
From: | Andrew Gilligan <andy(at)tcpd(dot)net> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | pgsql-hackers(at)postgresql(dot)org |
Subject: | Re: [PATCH] Add size/acl information when listing databases |
Date: | 2008-01-20 04:18:17 |
Message-ID: | 2A6F34B3-0964-4DF5-8AF8-477673AA78FC@tcpd.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-hackers |
On 20 Jan 2008, at 02:06, Tom Lane wrote:
> Andrew Gilligan <andy(at)tcpd(dot)net> writes:
>> Attached is a rather small change to src/bin/psql/describe.c to
>> show database size and permissions when using the psql \l command.
>
> Doesn't this slow down \l by several orders of magnitude? There's
> also the small problem that the query will fail entirely if there are
> any databases the current user cannot connect to.
I didn't run into the permissions issue while testing on 8.2.6, but
you're quite correct, after trying 8.3.x it fails entirely. Sorry.
On databases containing around 50 tables it seems to return in under
10ms, but I haven't been able to test with larger than that.
> The ACL part is fine, the database size not so much. I could see
> relegating the size to a \l+ option, but you still have to deal with
> the
> permissions problem.
Showing the size on \l+ probably makes more sense, but I imagine that
would require a very different approach due to the permissions changes?
Best regards,
-Andy
From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | Andrew Gilligan <andy(at)tcpd(dot)net> |
Cc: | pgsql-hackers(at)postgresql(dot)org |
Subject: | Re: [PATCH] Add size/acl information when listing databases |
Date: | 2008-01-20 04:34:17 |
Message-ID: | 3235.1200803657@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-hackers |
Andrew Gilligan <andy(at)tcpd(dot)net> writes:
> Showing the size on \l+ probably makes more sense, but I imagine that
> would require a very different approach due to the permissions changes?
I haven't experimented, but perhaps something along the lines of
case when has_database_privilege(current_user, db.oid, 'connect')
then pg_database_size...
else null
end
would fix it.
regards, tom lane
From: | Andrew Gilligan <andy(at)tcpd(dot)net> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | pgsql-hackers(at)postgresql(dot)org |
Subject: | Re: [PATCH] Add size/acl information when listing databases |
Date: | 2008-01-20 05:27:05 |
Message-ID: | 7269E86B-552C-4E5F-B993-4CAAA7255644@tcpd.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-hackers |
On 20 Jan 2008, at 04:34, Tom Lane wrote:
> Andrew Gilligan <andy(at)tcpd(dot)net> writes:
>> Showing the size on \l+ probably makes more sense, but I imagine that
>> would require a very different approach due to the permissions
>> changes?
>
> I haven't experimented, but perhaps something along the lines of
>
> case when has_database_privilege(current_user, db.oid, 'connect')
> then pg_database_size...
> else null
> end
>
> would fix it.
Yep, that seems to do the trick.
I've attached an updated patch (based on 8.3RC2) that adds the ACL
information to \l and the size to \l+ if available.
Best regards,
-Andy
Attachment | Content-Type | Size |
---|---|---|
patch-83rc2-src-bin-psql-describe.c.diff | application/octet-stream | 1.0 KB |
From: | Bruce Momjian <bruce(at)momjian(dot)us> |
---|---|
To: | Andrew Gilligan <andy(at)tcpd(dot)net> |
Cc: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, pgsql-hackers(at)postgresql(dot)org |
Subject: | Re: [PATCH] Add size/acl information when listing databases |
Date: | 2008-01-28 17:47:42 |
Message-ID: | 200801281747.m0SHlgU24497@momjian.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-hackers |
This has been saved for the 8.4 release:
http://momjian.postgresql.org/cgi-bin/pgpatches_hold
---------------------------------------------------------------------------
Andrew Gilligan wrote:
>
> On 20 Jan 2008, at 04:34, Tom Lane wrote:
> > Andrew Gilligan <andy(at)tcpd(dot)net> writes:
> >> Showing the size on \l+ probably makes more sense, but I imagine that
> >> would require a very different approach due to the permissions
> >> changes?
> >
> > I haven't experimented, but perhaps something along the lines of
> >
> > case when has_database_privilege(current_user, db.oid, 'connect')
> > then pg_database_size...
> > else null
> > end
> >
> > would fix it.
>
>
> Yep, that seems to do the trick.
>
> I've attached an updated patch (based on 8.3RC2) that adds the ACL
> information to \l and the size to \l+ if available.
>
> Best regards,
> -Andy
>
>
[ Attachment, skipping... ]
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 7: You can help support the PostgreSQL project by donating at
>
> http://www.postgresql.org/about/donate
--
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. +
From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | Andrew Gilligan <andy(at)tcpd(dot)net> |
Cc: | pgsql-hackers(at)postgresql(dot)org |
Subject: | Re: [PATCH] Add size/acl information when listing databases |
Date: | 2008-03-30 18:11:16 |
Message-ID: | 26114.1206900676@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-hackers |
Andrew Gilligan <andy(at)tcpd(dot)net> writes:
> I've attached an updated patch (based on 8.3RC2) that adds the ACL
> information to \l and the size to \l+ if available.
Applied, thanks.
regards, tom lane