Prev: [HACKERS] Order of pg_stat_activity timestamp columns
Next: Command to prune archive at restartpoints
From: Tom Lane on 17 Mar 2010 17:30
"Kevin Grittner" <Kevin.Grittner(a)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 info
3. 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

--
Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

From: "Kevin Grittner" on 17 Mar 2010 17:36
Bruce Momjian <bruce(a)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

--
Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

From: "Kevin Grittner" on 17 Mar 2010 17:39
Tom Lane <tgl(a)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

--
Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

From: Tom Lane on 17 Mar 2010 17:47
"Kevin Grittner" <Kevin.Grittner(a)wicourts.gov> writes:
> Tom Lane <tgl(a)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?

regards, tom lane

--
Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

From: David Fetter on 17 Mar 2010 19:03
On Wed, Mar 17, 2010 at 05:47:49PM -0400, Tom Lane wrote:
> "Kevin Grittner" <Kevin.Grittner(a)wicourts.gov> writes:
> > Tom Lane <tgl(a)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(a)fetter.org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david.fetter(a)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

--
Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

First  |  Prev  |  Next  |  Last
Pages: 1 2 3
Prev: [HACKERS] Order of pg_stat_activity timestamp columns
Next: Command to prune archive at restartpoints