On Tue, Nov 1, 2011 at 10:40 AM, Tom Lane
<tgl@sss.pgh.pa.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
regards, tom lane