Thread: Order of pg_stat_activity timestamp columns
Has anyone ever noticed that the order of pg_stat_activity timestamp columns is illogical: xact_start | timestamp with time zone |query_start | timestamp with time zone |backend_start | timestamp withtime zone | query_start is always between the other two timestamps. Moving query_start before xact_start would make the most sense. I wouldn't bring this up except we just added application_name before these columns, so we are already going to have different column locations for these fields in 9.0. Should we move query_start? -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com PG East: http://www.enterprisedb.com/community/nav-pg-east-2010.do
On Wed, Mar 17, 2010 at 21:42, Bruce Momjian <bruce@momjian.us> wrote: > Has anyone ever noticed that the order of pg_stat_activity timestamp > columns is illogical: > > xact_start | timestamp with time zone | > query_start | timestamp with time zone | > backend_start | timestamp with time zone | Well, 7.4 had only "query start". 8.1 added backend. 8.3 added transaction. So I guess my original guess that things were just added on the end was wrong :-) > query_start is always between the other two timestamps. Moving > query_start before xact_start would make the most sense. I wouldn't > bring this up except we just added application_name before these > columns, so we are already going to have different column locations for > these fields in 9.0. > > Should we move query_start? Or perhaps we should consider moving application_name to the end so it *doesn't* break them? -- Magnus HaganderMe: http://www.hagander.net/Work: http://www.redpill-linpro.com/
Magnus Hagander wrote: > On Wed, Mar 17, 2010 at 21:42, Bruce Momjian <bruce@momjian.us> wrote: > > Has anyone ever noticed that the order of pg_stat_activity timestamp > > columns is illogical: > > > > ?xact_start ? ? ? | timestamp with time zone | > > ?query_start ? ? ?| timestamp with time zone | > > ?backend_start ? ?| timestamp with time zone | > > Well, 7.4 had only "query start". 8.1 added backend. 8.3 added > transaction. So I guess my original guess that things were just added > on the end was wrong :-) > > > > query_start is always between the other two timestamps. ?Moving > > query_start before xact_start would make the most sense. ?I wouldn't > > bring this up except we just added application_name before these > > columns, so we are already going to have different column locations for > > these fields in 9.0. > > > > Should we move query_start? > > Or perhaps we should consider moving application_name to the end so it > *doesn't* break them? That's a possibility, but we obviously have been adding columns out-of-order for several releases now and no one has complained. -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com PG East: http://www.enterprisedb.com/community/nav-pg-east-2010.do
Bruce Momjian <bruce@momjian.us> writes:
> That's a possibility, but we obviously have been adding columns
> out-of-order for several releases now and no one has complained.
On balance I'm for rationalizing this. The query_start time is
logically associated with current_query and waiting, so it ought
to be next to them. Without the historical fact that we've mucked
with the column ordering before, I might've voted differently.
regards, tom lane
Bruce Momjian <bruce@momjian.us> wrote: > xact_start | timestamp with time zone | > query_start | timestamp with time zone | > backend_start | timestamp with time zone | > Should we move query_start? It would scan better, to my mind, if we moved backend_start ahead of xact_start. And paint it red. -Kevin
Kevin Grittner wrote: > Bruce Momjian <bruce@momjian.us> wrote: > > > xact_start | timestamp with time zone | > > query_start | timestamp with time zone | > > backend_start | timestamp with time zone | > > > Should we move query_start? > > It would scan better, to my mind, if we moved backend_start ahead of > xact_start. Yes, that is another idea that would work, though Tom's idea that the query start should be near the query makes sense. -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com PG East: http://www.enterprisedb.com/community/nav-pg-east-2010.do
"Kevin Grittner" <Kevin.Grittner@wicourts.gov> writes:
> It would scan better, to my mind, if we moved backend_start ahead of
> xact_start.
The current column ordering can be rationalized to some extent as
1. identity info (user id, db id, application name)2. current query info3. session info (backend start time, client
addr/port)
Putting backend_start first doesn't fit at all with that view of the
grouping. xact_start is sort of a borderline case, although one
could imagine that it might someday grow some friends and become a
full-fledged "current transaction info" grouping. So I'd prefer to
see it in between the columns that are clearly #2 and those that are
clearly #3.
If you believe that argument, there is a case for moving procpid into
group #3. I'm more hesitant to mess with the columns that have "always
been there" than those that got added in more recent releases, though.
It's possible also that some people might consider procpid as an identity
(key) column, in which case it's okay where it is.
regards, tom lane
Bruce Momjian <bruce@momjian.us> wrote: > Kevin Grittner wrote: >> It would scan better, to my mind, if we moved backend_start ahead >> of xact_start. > > Yes, that is another idea that would work, though Tom's idea that > the query start should be near the query makes sense. Well, in an ideal world, I would put the current_query column at the end, so that long queries wouldn't make it hard to see the other values. I think I'd want to squeeze waiting in between the timestamps and the query. I would generally want items to be close together if related and farther down the field list if they were more volatile. For example, since application_name can be changed but client_* values can't, I'd put application_name later -- possibly right before the timestamps. If we're willing to re-order the existing columns, why not try to make the whole thing sane? -Kevin
Tom Lane <tgl@sss.pgh.pa.us> wrote: > The current column ordering can be rationalized to some extent as > > 1. identity info (user id, db id, application name) > 2. current query info > 3. session info (backend start time, client addr/port) OK. I guess that trumps my idea, although it would sure be nice if it were possible to swap 2 and 3 so that we could put the query text at the end. -Kevin
"Kevin Grittner" <Kevin.Grittner@wicourts.gov> writes:
> Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> The current column ordering can be rationalized to some extent as
>>
>> 1. identity info (user id, db id, application name)
>> 2. current query info
>> 3. session info (backend start time, client addr/port)
> OK. I guess that trumps my idea, although it would sure be nice if
> it were possible to swap 2 and 3 so that we could put the query text
> at the end.
Well, the current ordering is definitely historical rather than
designed, but I'm hesitant to do more than minor tweaking. Even if we
think/hope it won't break applications, people are probably used to
seeing a particular ordering.
I'm not necessarily dead set against it though. I guess if we were
to do what you suggest, we'd end up with
identity:datid | oid | datname | name | procpid |
integer | usesysid | oid | usename | name |
application_name| text |
session:client_addr | inet | client_port | integer | backend_start |
timestampwith time zone |
transaction:xact_start | timestamp with time zone |
query:query_start | timestamp with time zone | waiting | boolean | current_query |
text |
or possibly that plus relocate procpid somewhere else. Anyone think
this is sufficiently better to justify possible confusion?
regards, tom lane
On Wed, Mar 17, 2010 at 05:47:49PM -0400, Tom Lane wrote: > "Kevin Grittner" <Kevin.Grittner@wicourts.gov> writes: > > Tom Lane <tgl@sss.pgh.pa.us> wrote: > >> The current column ordering can be rationalized to some extent as > >> > >> 1. identity info (user id, db id, application name) > >> 2. current query info > >> 3. session info (backend start time, client addr/port) > > > OK. I guess that trumps my idea, although it would sure be nice if > > it were possible to swap 2 and 3 so that we could put the query text > > at the end. > > Well, the current ordering is definitely historical rather than > designed, but I'm hesitant to do more than minor tweaking. Even if we > think/hope it won't break applications, people are probably used to > seeing a particular ordering. > > I'm not necessarily dead set against it though. I guess if we were > to do what you suggest, we'd end up with > > identity: > datid | oid | > datname | name | > procpid | integer | > usesysid | oid | > usename | name | > application_name | text | > session: > client_addr | inet | > client_port | integer | > backend_start | timestamp with time zone | > transaction: > xact_start | timestamp with time zone | > query: > query_start | timestamp with time zone | > waiting | boolean | > current_query | text | > > or possibly that plus relocate procpid somewhere else. Anyone think > this is sufficiently better to justify possible confusion? Grouping these this way will help a lot more people, namely the future ones, than it can possibly confuse :) Cheers, David. -- David Fetter <david@fetter.org> http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fetter@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
Tom Lane wrote: > Well, the current ordering is definitely historical rather than > designed, but I'm hesitant to do more than minor tweaking. Even if we > think/hope it won't break applications, people are probably used to > seeing a particular ordering. > > I'm not necessarily dead set against it though. I guess if we were > to do what you suggest, we'd end up with > > identity: > datid | oid | > datname | name | > procpid | integer | > usesysid | oid | > usename | name | > application_name | text | > session: > client_addr | inet | > client_port | integer | > backend_start | timestamp with time zone | > transaction: > xact_start | timestamp with time zone | > query: > query_start | timestamp with time zone | > waiting | boolean | > current_query | text | > > or possibly that plus relocate procpid somewhere else. Anyone think > this is sufficiently better to justify possible confusion? I think most reports have the stable information first, and the more dynamic information at the end, so reordering it this way does make sense. -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com PG East: http://www.enterprisedb.com/community/nav-pg-east-2010.do
Tom Lane wrote:
> "Kevin Grittner" <Kevin.Grittner@wicourts.gov> writes:
> > Tom Lane <tgl@sss.pgh.pa.us> wrote:
> >> The current column ordering can be rationalized to some extent as
> >>
> >> 1. identity info (user id, db id, application name)
> >> 2. current query info
> >> 3. session info (backend start time, client addr/port)
>
> > OK. I guess that trumps my idea, although it would sure be nice if
> > it were possible to swap 2 and 3 so that we could put the query text
> > at the end.
>
> Well, the current ordering is definitely historical rather than
> designed, but I'm hesitant to do more than minor tweaking. Even if we
> think/hope it won't break applications, people are probably used to
> seeing a particular ordering.
>
> I'm not necessarily dead set against it though. I guess if we were
> to do what you suggest, we'd end up with
>
> identity:
> datid | oid |
> datname | name |
> procpid | integer |
> usesysid | oid |
> usename | name |
> application_name | text |
> session:
> client_addr | inet |
> client_port | integer |
> backend_start | timestamp with time zone |
> transaction:
> xact_start | timestamp with time zone |
> query:
> query_start | timestamp with time zone |
> waiting | boolean |
> current_query | text |
>
> or possibly that plus relocate procpid somewhere else. Anyone think
> this is sufficiently better to justify possible confusion?
I implemented Tom's suggested ordering above:
test=> SELECT * FROM pg_stat_activity;
-[ RECORD 1 ]----+--------------------------------
datid | 16384
datname | test
procpid | 22216
usesysid | 10
usename | postgres
application_name | psql
client_addr |
client_port | -1
backend_start | 2010-04-24 22:35:21.683308-04
xact_start | 2010-04-24 22:47:19.53821-04
query_start | 2010-04-24 22:47:19.53821-04
waiting | f
current_query | SELECT * FROM pg_stat_activity;
Patch attached. It will require a catversion bump too.
--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com
Index: src/backend/catalog/system_views.sql
===================================================================
RCS file: /cvsroot/pgsql/src/backend/catalog/system_views.sql,v
retrieving revision 1.65
diff -c -c -r1.65 system_views.sql
*** src/backend/catalog/system_views.sql 2 Jan 2010 16:57:36 -0000 1.65
--- src/backend/catalog/system_views.sql 25 Apr 2010 02:47:39 -0000
***************
*** 335,347 ****
S.usesysid,
U.rolname AS usename,
S.application_name,
! S.current_query,
! S.waiting,
S.xact_start,
S.query_start,
! S.backend_start,
! S.client_addr,
! S.client_port
FROM pg_database D, pg_stat_get_activity(NULL) AS S, pg_authid U
WHERE S.datid = D.oid AND
S.usesysid = U.oid;
--- 335,347 ----
S.usesysid,
U.rolname AS usename,
S.application_name,
! S.client_addr,
! S.client_port,
! S.backend_start,
S.xact_start,
S.query_start,
! S.waiting,
! S.current_query
FROM pg_database D, pg_stat_get_activity(NULL) AS S, pg_authid U
WHERE S.datid = D.oid AND
S.usesysid = U.oid;
Index: src/test/regress/expected/rules.out
===================================================================
RCS file: /cvsroot/pgsql/src/test/regress/expected/rules.out,v
retrieving revision 1.154
diff -c -c -r1.154 rules.out
*** src/test/regress/expected/rules.out 29 Dec 2009 20:11:45 -0000 1.154
--- src/test/regress/expected/rules.out 25 Apr 2010 02:47:40 -0000
***************
*** 1289,1295 ****
pg_rules | SELECT n.nspname AS schemaname, c.relname AS tablename, r.rulename, pg_get_ruledef(r.oid)
ASdefinition FROM ((pg_rewrite r JOIN pg_class c ON ((c.oid = r.ev_class))) LEFT JOIN pg_namespace n ON ((n.oid =
c.relnamespace)))WHERE (r.rulename <> '_RETURN'::name);
pg_settings | SELECT a.name, a.setting, a.unit, a.category, a.short_desc, a.extra_desc, a.context,
a.vartype,a.source, a.min_val, a.max_val, a.enumvals, a.boot_val, a.reset_val, a.sourcefile, a.sourceline FROM
pg_show_all_settings()a(name, setting, unit, category, short_desc, extra_desc, context, vartype, source, min_val,
max_val,enumvals, boot_val, reset_val, sourcefile, sourceline);
pg_shadow | SELECT pg_authid.rolname AS usename, pg_authid.oid AS usesysid, pg_authid.rolcreatedb AS
usecreatedb,pg_authid.rolsuper AS usesuper, pg_authid.rolcatupdate AS usecatupd, pg_authid.rolpassword AS passwd,
(pg_authid.rolvaliduntil)::abstimeAS valuntil, s.setconfig AS useconfig FROM (pg_authid LEFT JOIN pg_db_role_setting s
ON(((pg_authid.oid = s.setrole) AND (s.setdatabase = (0)::oid)))) WHERE pg_authid.rolcanlogin;
! pg_stat_activity | SELECT s.datid, d.datname, s.procpid, s.usesysid, u.rolname AS usename,
s.application_name,s.current_query, s.waiting, s.xact_start, s.query_start, s.backend_start, s.client_addr,
s.client_portFROM pg_database d, pg_stat_get_activity(NULL::integer) s(datid, procpid, usesysid, application_name,
current_query,waiting, xact_start, query_start, backend_start, client_addr, client_port), pg_authid u WHERE ((s.datid =
d.oid)AND (s.usesysid = u.oid));
pg_stat_all_indexes | SELECT c.oid AS relid, i.oid AS indexrelid, n.nspname AS schemaname, c.relname, i.relname
ASindexrelname, pg_stat_get_numscans(i.oid) AS idx_scan, pg_stat_get_tuples_returned(i.oid) AS idx_tup_read,
pg_stat_get_tuples_fetched(i.oid)AS idx_tup_fetch FROM (((pg_class c JOIN pg_index x ON ((c.oid = x.indrelid))) JOIN
pg_classi ON ((i.oid = x.indexrelid))) LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))) WHERE (c.relkind = ANY
(ARRAY['r'::"char",'t'::"char"]));
pg_stat_all_tables | SELECT c.oid AS relid, n.nspname AS schemaname, c.relname, pg_stat_get_numscans(c.oid) AS
seq_scan,pg_stat_get_tuples_returned(c.oid) AS seq_tup_read, (sum(pg_stat_get_numscans(i.indexrelid)))::bigint AS
idx_scan,((sum(pg_stat_get_tuples_fetched(i.indexrelid)))::bigint + pg_stat_get_tuples_fetched(c.oid)) AS
idx_tup_fetch,pg_stat_get_tuples_inserted(c.oid) AS n_tup_ins, pg_stat_get_tuples_updated(c.oid) AS n_tup_upd,
pg_stat_get_tuples_deleted(c.oid)AS n_tup_del, pg_stat_get_tuples_hot_updated(c.oid) AS n_tup_hot_upd,
pg_stat_get_live_tuples(c.oid)AS n_live_tup, pg_stat_get_dead_tuples(c.oid) AS n_dead_tup,
pg_stat_get_last_vacuum_time(c.oid)AS last_vacuum, pg_stat_get_last_autovacuum_time(c.oid) AS last_autovacuum,
pg_stat_get_last_analyze_time(c.oid)AS last_analyze, pg_stat_get_last_autoanalyze_time(c.oid) AS last_autoanalyze FROM
((pg_classc LEFT JOIN pg_index i ON ((c.oid = i.indrelid))) LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace)))
WHERE(c.relkind = ANY (ARRAY['r'::"char", 't'::"char"])) GROUP BY c.oid, n.nspname, c.relname;
pg_stat_bgwriter | SELECT pg_stat_get_bgwriter_timed_checkpoints() AS checkpoints_timed,
pg_stat_get_bgwriter_requested_checkpoints()AS checkpoints_req, pg_stat_get_bgwriter_buf_written_checkpoints() AS
buffers_checkpoint,pg_stat_get_bgwriter_buf_written_clean() AS buffers_clean, pg_stat_get_bgwriter_maxwritten_clean()
ASmaxwritten_clean, pg_stat_get_buf_written_backend() AS buffers_backend, pg_stat_get_buf_alloc() AS buffers_alloc;
--- 1289,1295 ----
pg_rules | SELECT n.nspname AS schemaname, c.relname AS tablename, r.rulename, pg_get_ruledef(r.oid)
ASdefinition FROM ((pg_rewrite r JOIN pg_class c ON ((c.oid = r.ev_class))) LEFT JOIN pg_namespace n ON ((n.oid =
c.relnamespace)))WHERE (r.rulename <> '_RETURN'::name);
pg_settings | SELECT a.name, a.setting, a.unit, a.category, a.short_desc, a.extra_desc, a.context,
a.vartype,a.source, a.min_val, a.max_val, a.enumvals, a.boot_val, a.reset_val, a.sourcefile, a.sourceline FROM
pg_show_all_settings()a(name, setting, unit, category, short_desc, extra_desc, context, vartype, source, min_val,
max_val,enumvals, boot_val, reset_val, sourcefile, sourceline);
pg_shadow | SELECT pg_authid.rolname AS usename, pg_authid.oid AS usesysid, pg_authid.rolcreatedb AS
usecreatedb,pg_authid.rolsuper AS usesuper, pg_authid.rolcatupdate AS usecatupd, pg_authid.rolpassword AS passwd,
(pg_authid.rolvaliduntil)::abstimeAS valuntil, s.setconfig AS useconfig FROM (pg_authid LEFT JOIN pg_db_role_setting s
ON(((pg_authid.oid = s.setrole) AND (s.setdatabase = (0)::oid)))) WHERE pg_authid.rolcanlogin;
! pg_stat_activity | SELECT s.datid, d.datname, s.procpid, s.usesysid, u.rolname AS usename,
s.application_name,s.client_addr, s.client_port, s.backend_start, s.xact_start, s.query_start, s.waiting,
s.current_queryFROM pg_database d, pg_stat_get_activity(NULL::integer) s(datid, procpid, usesysid, application_name,
current_query,waiting, xact_start, query_start, backend_start, client_addr, client_port), pg_authid u WHERE ((s.datid =
d.oid)AND (s.usesysid = u.oid));
pg_stat_all_indexes | SELECT c.oid AS relid, i.oid AS indexrelid, n.nspname AS schemaname, c.relname, i.relname
ASindexrelname, pg_stat_get_numscans(i.oid) AS idx_scan, pg_stat_get_tuples_returned(i.oid) AS idx_tup_read,
pg_stat_get_tuples_fetched(i.oid)AS idx_tup_fetch FROM (((pg_class c JOIN pg_index x ON ((c.oid = x.indrelid))) JOIN
pg_classi ON ((i.oid = x.indexrelid))) LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))) WHERE (c.relkind = ANY
(ARRAY['r'::"char",'t'::"char"]));
pg_stat_all_tables | SELECT c.oid AS relid, n.nspname AS schemaname, c.relname, pg_stat_get_numscans(c.oid) AS
seq_scan,pg_stat_get_tuples_returned(c.oid) AS seq_tup_read, (sum(pg_stat_get_numscans(i.indexrelid)))::bigint AS
idx_scan,((sum(pg_stat_get_tuples_fetched(i.indexrelid)))::bigint + pg_stat_get_tuples_fetched(c.oid)) AS
idx_tup_fetch,pg_stat_get_tuples_inserted(c.oid) AS n_tup_ins, pg_stat_get_tuples_updated(c.oid) AS n_tup_upd,
pg_stat_get_tuples_deleted(c.oid)AS n_tup_del, pg_stat_get_tuples_hot_updated(c.oid) AS n_tup_hot_upd,
pg_stat_get_live_tuples(c.oid)AS n_live_tup, pg_stat_get_dead_tuples(c.oid) AS n_dead_tup,
pg_stat_get_last_vacuum_time(c.oid)AS last_vacuum, pg_stat_get_last_autovacuum_time(c.oid) AS last_autovacuum,
pg_stat_get_last_analyze_time(c.oid)AS last_analyze, pg_stat_get_last_autoanalyze_time(c.oid) AS last_autoanalyze FROM
((pg_classc LEFT JOIN pg_index i ON ((c.oid = i.indrelid))) LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace)))
WHERE(c.relkind = ANY (ARRAY['r'::"char", 't'::"char"])) GROUP BY c.oid, n.nspname, c.relname;
pg_stat_bgwriter | SELECT pg_stat_get_bgwriter_timed_checkpoints() AS checkpoints_timed,
pg_stat_get_bgwriter_requested_checkpoints()AS checkpoints_req, pg_stat_get_bgwriter_buf_written_checkpoints() AS
buffers_checkpoint,pg_stat_get_bgwriter_buf_written_clean() AS buffers_clean, pg_stat_get_bgwriter_maxwritten_clean()
ASmaxwritten_clean, pg_stat_get_buf_written_backend() AS buffers_backend, pg_stat_get_buf_alloc() AS buffers_alloc;
Applied; catalog version bumped. --------------------------------------------------------------------------- Bruce Momjian wrote: > Tom Lane wrote: > > "Kevin Grittner" <Kevin.Grittner@wicourts.gov> writes: > > > Tom Lane <tgl@sss.pgh.pa.us> wrote: > > >> The current column ordering can be rationalized to some extent as > > >> > > >> 1. identity info (user id, db id, application name) > > >> 2. current query info > > >> 3. session info (backend start time, client addr/port) > > > > > OK. I guess that trumps my idea, although it would sure be nice if > > > it were possible to swap 2 and 3 so that we could put the query text > > > at the end. > > > > Well, the current ordering is definitely historical rather than > > designed, but I'm hesitant to do more than minor tweaking. Even if we > > think/hope it won't break applications, people are probably used to > > seeing a particular ordering. > > > > I'm not necessarily dead set against it though. I guess if we were > > to do what you suggest, we'd end up with > > > > identity: > > datid | oid | > > datname | name | > > procpid | integer | > > usesysid | oid | > > usename | name | > > application_name | text | > > session: > > client_addr | inet | > > client_port | integer | > > backend_start | timestamp with time zone | > > transaction: > > xact_start | timestamp with time zone | > > query: > > query_start | timestamp with time zone | > > waiting | boolean | > > current_query | text | > > > > or possibly that plus relocate procpid somewhere else. Anyone think > > this is sufficiently better to justify possible confusion? > > I implemented Tom's suggested ordering above: > > test=> SELECT * FROM pg_stat_activity; > -[ RECORD 1 ]----+-------------------------------- > datid | 16384 > datname | test > procpid | 22216 > usesysid | 10 > usename | postgres > application_name | psql > client_addr | > client_port | -1 > backend_start | 2010-04-24 22:35:21.683308-04 > xact_start | 2010-04-24 22:47:19.53821-04 > query_start | 2010-04-24 22:47:19.53821-04 > waiting | f > current_query | SELECT * FROM pg_stat_activity; > > Patch attached. It will require a catversion bump too. > > -- > Bruce Momjian <bruce@momjian.us> http://momjian.us > EnterpriseDB http://enterprisedb.com > Index: src/backend/catalog/system_views.sql > =================================================================== > RCS file: /cvsroot/pgsql/src/backend/catalog/system_views.sql,v > retrieving revision 1.65 > diff -c -c -r1.65 system_views.sql > *** src/backend/catalog/system_views.sql 2 Jan 2010 16:57:36 -0000 1.65 > --- src/backend/catalog/system_views.sql 25 Apr 2010 02:47:39 -0000 > *************** > *** 335,347 **** > S.usesysid, > U.rolname AS usename, > S.application_name, > ! S.current_query, > ! S.waiting, > S.xact_start, > S.query_start, > ! S.backend_start, > ! S.client_addr, > ! S.client_port > FROM pg_database D, pg_stat_get_activity(NULL) AS S, pg_authid U > WHERE S.datid = D.oid AND > S.usesysid = U.oid; > --- 335,347 ---- > S.usesysid, > U.rolname AS usename, > S.application_name, > ! S.client_addr, > ! S.client_port, > ! S.backend_start, > S.xact_start, > S.query_start, > ! S.waiting, > ! S.current_query > FROM pg_database D, pg_stat_get_activity(NULL) AS S, pg_authid U > WHERE S.datid = D.oid AND > S.usesysid = U.oid; > Index: src/test/regress/expected/rules.out > =================================================================== > RCS file: /cvsroot/pgsql/src/test/regress/expected/rules.out,v > retrieving revision 1.154 > diff -c -c -r1.154 rules.out > *** src/test/regress/expected/rules.out 29 Dec 2009 20:11:45 -0000 1.154 > --- src/test/regress/expected/rules.out 25 Apr 2010 02:47:40 -0000 > *************** > *** 1289,1295 **** > pg_rules | SELECT n.nspname AS schemaname, c.relname AS tablename, r.rulename, pg_get_ruledef(r.oid)AS definition FROM ((pg_rewrite r JOIN pg_class c ON ((c.oid = r.ev_class))) LEFT JOIN pg_namespacen ON ((n.oid = c.relnamespace))) WHERE (r.rulename <> '_RETURN'::name); > pg_settings | SELECT a.name, a.setting, a.unit, a.category, a.short_desc, a.extra_desc, a.context, a.vartype,a.source, a.min_val, a.max_val, a.enumvals, a.boot_val, a.reset_val, a.sourcefile, a.sourceline FROM pg_show_all_settings()a(name, setting, unit, category, short_desc, extra_desc, context, vartype, source, min_val, max_val,enumvals, boot_val, reset_val, sourcefile, sourceline); > pg_shadow | SELECT pg_authid.rolname AS usename, pg_authid.oid AS usesysid, pg_authid.rolcreatedb ASusecreatedb, pg_authid.rolsuper AS usesuper, pg_authid.rolcatupdate AS usecatupd, pg_authid.rolpassword AS passwd, (pg_authid.rolvaliduntil)::abstimeAS valuntil, s.setconfig AS useconfig FROM (pg_authid LEFT JOIN pg_db_role_setting s ON(((pg_authid.oid = s.setrole) AND (s.setdatabase = (0)::oid)))) WHERE pg_authid.rolcanlogin; > ! pg_stat_activity | SELECT s.datid, d.datname, s.procpid, s.usesysid, u.rolname AS usename, s.application_name,s.current_query, s.waiting, s.xact_start, s.query_start, s.backend_start, s.client_addr, s.client_portFROM pg_database d, pg_stat_get_activity(NULL::integer) s(datid, procpid, usesysid, application_name, current_query,waiting, xact_start, query_start, backend_start, client_addr, client_port), pg_authid u WHERE ((s.datid = d.oid)AND (s.usesysid = u.oid)); > pg_stat_all_indexes | SELECT c.oid AS relid, i.oid AS indexrelid, n.nspname AS schemaname, c.relname, i.relnameAS indexrelname, pg_stat_get_numscans(i.oid) AS idx_scan, pg_stat_get_tuples_returned(i.oid) AS idx_tup_read, pg_stat_get_tuples_fetched(i.oid)AS idx_tup_fetch FROM (((pg_class c JOIN pg_index x ON ((c.oid = x.indrelid))) JOIN pg_classi ON ((i.oid = x.indexrelid))) LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))) WHERE (c.relkind = ANY (ARRAY['r'::"char",'t'::"char"])); > pg_stat_all_tables | SELECT c.oid AS relid, n.nspname AS schemaname, c.relname, pg_stat_get_numscans(c.oid) ASseq_scan, pg_stat_get_tuples_returned(c.oid) AS seq_tup_read, (sum(pg_stat_get_numscans(i.indexrelid)))::bigint AS idx_scan,((sum(pg_stat_get_tuples_fetched(i.indexrelid)))::bigint + pg_stat_get_tuples_fetched(c.oid)) AS idx_tup_fetch,pg_stat_get_tuples_inserted(c.oid) AS n_tup_ins, pg_stat_get_tuples_updated(c.oid) AS n_tup_upd, pg_stat_get_tuples_deleted(c.oid)AS n_tup_del, pg_stat_get_tuples_hot_updated(c.oid) AS n_tup_hot_upd, pg_stat_get_live_tuples(c.oid)AS n_live_tup, pg_stat_get_dead_tuples(c.oid) AS n_dead_tup, pg_stat_get_last_vacuum_time(c.oid)AS last_vacuum, pg_stat_get_last_autovacuum_time(c.oid) AS last_autovacuum, pg_stat_get_last_analyze_time(c.oid)AS last_analyze, pg_stat_get_last_autoanalyze_time(c.oid) AS last_autoanalyze FROM ((pg_classc LEFT JOIN pg_index i ON ((c.oid = i.indrelid))) LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))) WHERE(c.relkind = ANY (ARRAY['r'::"char", 't'::"char"])) GROUP BY c.oid, n.nspname, c.relname; > pg_stat_bgwriter | SELECT pg_stat_get_bgwriter_timed_checkpoints() AS checkpoints_timed, pg_stat_get_bgwriter_requested_checkpoints()AS checkpoints_req, pg_stat_get_bgwriter_buf_written_checkpoints() AS buffers_checkpoint,pg_stat_get_bgwriter_buf_written_clean() AS buffers_clean, pg_stat_get_bgwriter_maxwritten_clean() ASmaxwritten_clean, pg_stat_get_buf_written_backend() AS buffers_backend, pg_stat_get_buf_alloc() AS buffers_alloc; > --- 1289,1295 ---- > pg_rules | SELECT n.nspname AS schemaname, c.relname AS tablename, r.rulename, pg_get_ruledef(r.oid)AS definition FROM ((pg_rewrite r JOIN pg_class c ON ((c.oid = r.ev_class))) LEFT JOIN pg_namespacen ON ((n.oid = c.relnamespace))) WHERE (r.rulename <> '_RETURN'::name); > pg_settings | SELECT a.name, a.setting, a.unit, a.category, a.short_desc, a.extra_desc, a.context, a.vartype,a.source, a.min_val, a.max_val, a.enumvals, a.boot_val, a.reset_val, a.sourcefile, a.sourceline FROM pg_show_all_settings()a(name, setting, unit, category, short_desc, extra_desc, context, vartype, source, min_val, max_val,enumvals, boot_val, reset_val, sourcefile, sourceline); > pg_shadow | SELECT pg_authid.rolname AS usename, pg_authid.oid AS usesysid, pg_authid.rolcreatedb ASusecreatedb, pg_authid.rolsuper AS usesuper, pg_authid.rolcatupdate AS usecatupd, pg_authid.rolpassword AS passwd, (pg_authid.rolvaliduntil)::abstimeAS valuntil, s.setconfig AS useconfig FROM (pg_authid LEFT JOIN pg_db_role_setting s ON(((pg_authid.oid = s.setrole) AND (s.setdatabase = (0)::oid)))) WHERE pg_authid.rolcanlogin; > ! pg_stat_activity | SELECT s.datid, d.datname, s.procpid, s.usesysid, u.rolname AS usename, s.application_name,s.client_addr, s.client_port, s.backend_start, s.xact_start, s.query_start, s.waiting, s.current_queryFROM pg_database d, pg_stat_get_activity(NULL::integer) s(datid, procpid, usesysid, application_name, current_query,waiting, xact_start, query_start, backend_start, client_addr, client_port), pg_authid u WHERE ((s.datid = d.oid)AND (s.usesysid = u.oid)); > pg_stat_all_indexes | SELECT c.oid AS relid, i.oid AS indexrelid, n.nspname AS schemaname, c.relname, i.relnameAS indexrelname, pg_stat_get_numscans(i.oid) AS idx_scan, pg_stat_get_tuples_returned(i.oid) AS idx_tup_read, pg_stat_get_tuples_fetched(i.oid)AS idx_tup_fetch FROM (((pg_class c JOIN pg_index x ON ((c.oid = x.indrelid))) JOIN pg_classi ON ((i.oid = x.indexrelid))) LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))) WHERE (c.relkind = ANY (ARRAY['r'::"char",'t'::"char"])); > pg_stat_all_tables | SELECT c.oid AS relid, n.nspname AS schemaname, c.relname, pg_stat_get_numscans(c.oid) ASseq_scan, pg_stat_get_tuples_returned(c.oid) AS seq_tup_read, (sum(pg_stat_get_numscans(i.indexrelid)))::bigint AS idx_scan,((sum(pg_stat_get_tuples_fetched(i.indexrelid)))::bigint + pg_stat_get_tuples_fetched(c.oid)) AS idx_tup_fetch,pg_stat_get_tuples_inserted(c.oid) AS n_tup_ins, pg_stat_get_tuples_updated(c.oid) AS n_tup_upd, pg_stat_get_tuples_deleted(c.oid)AS n_tup_del, pg_stat_get_tuples_hot_updated(c.oid) AS n_tup_hot_upd, pg_stat_get_live_tuples(c.oid)AS n_live_tup, pg_stat_get_dead_tuples(c.oid) AS n_dead_tup, pg_stat_get_last_vacuum_time(c.oid)AS last_vacuum, pg_stat_get_last_autovacuum_time(c.oid) AS last_autovacuum, pg_stat_get_last_analyze_time(c.oid)AS last_analyze, pg_stat_get_last_autoanalyze_time(c.oid) AS last_autoanalyze FROM ((pg_classc LEFT JOIN pg_index i ON ((c.oid = i.indrelid))) LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))) WHERE(c.relkind = ANY (ARRAY['r'::"char", 't'::"char"])) GROUP BY c.oid, n.nspname, c.relname; > pg_stat_bgwriter | SELECT pg_stat_get_bgwriter_timed_checkpoints() AS checkpoints_timed, pg_stat_get_bgwriter_requested_checkpoints()AS checkpoints_req, pg_stat_get_bgwriter_buf_written_checkpoints() AS buffers_checkpoint,pg_stat_get_bgwriter_buf_written_clean() AS buffers_clean, pg_stat_get_bgwriter_maxwritten_clean() ASmaxwritten_clean, pg_stat_get_buf_written_backend() AS buffers_backend, pg_stat_get_buf_alloc() AS buffers_alloc; > > > -- > Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-hackers -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com