Re: IDLE in transaction introspection

On Tue, Nov 1, 2011 at 10:40 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Robert Haas <robertmhaas(at)gmail(dot)com> writes:
> > On Tue, Nov 1, 2011 at 9:52 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> >> That would cost twice as much shared memory for query strings, and twice
> >> as much time to update the strings, for what seems pretty marginal
> >> value. I'm for just redefining the query field as "current or last
> >> query".
>
> > Not really. You could just store it once in shared memory, and put
> > the complexity in the view definition.
>
> I understood the proposal to be "store the previous query in addition
> to the current-query-if-any". If that's not what was meant, then my
> objection was incorrect. However, like you, I'm pretty dubious of
> having two mostly-redundant fields in the view definition, just because
> of window width issues.
>

The biggest reason I dislike the multi-field approach is because it limits
us to only the [single] previous_query in the system with all the overhead
we talked about (memory, window width and messing with system catalogs in
general). That's actually why I implemented it the way I did, just by
appending the last query on the end of the string when it's <IDLE> in
transaction.

Marti wrote:

I'd very much like to see a more generic solution: a runtime query log
> facility that can be queried in any way you want. pg_stat_statements
> comes close, but is limited too due to its (arbitrary, I find)
> deduplication -- you can't query for "10 last statements from process
> N" since it has no notion of processes, just users and databases.

This is what I'd really like to see (just haven't had time as it is a much
bigger project). The next question my devs ask is "what were the last five
queries that ran"... "can you show me an overview of an entire transaction"
etc...

That being said, having the previous_query available feels like it fixes
about 80% of the *problem*; transaction profiling, or looking back 10 / 15
/ 20 queries is [immensely] useful, but I find that the bigger need is the
ability to short-circuit dba / dev back-n-forth by just saying "Your app
refused to commit/rollback after running query XYZ".

Robert Wrote:
> Yeah. Otherwise, people who are parsing the hard-coded strings <idle>
> and <idle in transaction> are likely to get confused.

I would be interested ( and frankly very surprised ) to find out if many
monitoring tools are actually parsing that field. Most that I see just
dump whatever is in current_query to the user. I would imaging that, so
long as the server obeyed pgstat_track_activity_size most tools would
behave nicely.

Now... all that being said, I've implemented the 'previous_query' column
and (maybe just for my own benefit), is the PostgreSQL community interested
in the patch?

--
Scott Mead
OpenSCG http://www.openscg.com

>
> regards, tom lane
>