Re: IDLE in transaction introspection

On Tue, Nov 1, 2011 at 18:40, Scott Mead <scottm@openscg.com> wrote:
>
>
> On Tue, Nov 1, 2011 at 1:20 PM, Magnus Hagander <magnus@hagander.net> wrote:
>>
>> On Tue, Nov 1, 2011 at 18:11, Scott Mead <scottm@openscg.com> wrote:
>> >
>> > On Tue, Nov 1, 2011 at 10:40 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> >>
>> >> Robert Haas <robertmhaas@gmail.com> writes:
>> >> > On Tue, Nov 1, 2011 at 9:52 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.
>>
>> 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..
>>
>>
>> >> 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.
>>
>> 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)")
>
> Yeah, I was kind of thinking this too, I just feel dirty adding a (n
> * track_activity_query_size) overhead to shared memory for tracking that if
> we're already concerned about adding just a 'previous_query' string.  It's
> easy enough to either hard-code or set a limit on 'n', but, if I were to do
> that, is it something that would be accepted? (my ability to code
> not-withstanding :-)

No, I meant storing it in backend local memory, and then transfer it
upon request. That would remove locking requirements etc.

--
 Magnus Hagander
 Me: http://www.hagander.net/
 Work: http://www.redpill-linpro.com/