Re: IDLE in transaction introspection

On Tue, Nov 1, 2011 at 1:20 PM, Magnus Hagander <magnus(at)hagander(dot)net> wrote:
> On Tue, Nov 1, 2011 at 18:11, Scott Mead <scottm(at)openscg(dot)com> wrote:
>>
>> 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.
>
> Well, by appending it in that field, you require the end
> user/monitoring app to parse out the string anyway, so you're not
> exactly making life easier on the consumer of the information..
>

+1

>
>>> 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".
>
> This would be great, but as you say, it's a different project.
>

+1 (but I'd like to see that different project)

> Perhaps something could be made along the line of each backend keeping
> it's *own* set of old queries, and then making it available to a
> specific function ("pg_get_last_queries_for_backend(nnn)") - since
> this is not the type of information you'd ask for often, it would be
> ok if getting this data took longer.. And you seldom want "give me the
> last 5 queries for each backend at once".
>
>
>>> 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.
>
> Really? I'd assume every single monitoring tool that graphs how many
> active connections you have (vs idle vs idle in transaction) would do
> just this.
>

Having written and/or patched dozens of these types of things, your
spot on; all of the ones with anything other than the most brain dead
of monitoring parse for IDLE and <IDLE> in transaction. That said, I'm
happy to see the {active|idle|idle in txn} status field and
"query_string" fields show up and break backwards compatibility.
Updating the tools will be simple for those who need it, and make a
view to work around it will be simple for those who don't. Happy to
add an example view definition to the docs if it will help.

Robert Treat
conjecture: xzilla.net
consulting: omniti.com