Thread: Getting previous statements executed a backend currenly in an in transaction state.

Dear Friends,

Due to some bug(s) in our apps , in a mod_perl environment
many backend remain in <idle> in transaction state. This
backends sometimes block other statements in batch
processes which in turn blocks access to some production
tables.

This issue has been posted before also but this time i wanted
to know, if it is possible to know what statements have been
executed by the backend in the same transaction which is currently
idle. this can probably help us in identifying the faulty application
code.

in past we had set a logging prefix in guc and used grep to digout
the statements but that was logging all statements which was
probably not a good idea in production environment. Can some
one suggest if anything else is readily available.


regds
mallah.

On 1/22/07, Umesh Shastry <ushastry@techbooks.com> wrote:
>
>
> This may help you...
thanks for reply.
but thats for slow queries. Blocked queries are different
regds
mallah.
>
> http://archives.postgresql.org/pgsql-announce/2006-01/msg00007.php
>
>

----- Original Message -----

From: Rajesh Kumar Mallah

To: Lista Postgres

Sent: Monday, January 22, 2007 9:55 AM

Subject: [ADMIN] Getting previous statements executed a backend currenly in an <idle> in transaction state.

Dear Friends,

Due to some bug(s) in our apps , in a mod_perl environment
many backend remain in <idle> in transaction state. This
backends sometimes block other statements in batch
processes which in turn blocks access to some production
tables.

This issue has been posted before also but this time i wanted
to know, if it is possible to know what statements have been
executed by the backend in the same transaction which is currently
idle. this can probably help us in identifying the faulty application
code.

in past we had set a logging prefix in guc and used grep to digout
the statements but that was logging all statements which was
probably not a good idea in production environment. Can some
one suggest if anything else is readily available.


regds
mallah.

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

               http://archives.postgresql.org

"Rajesh Kumar Mallah" <mallah.rajesh@gmail.com> writes:
> This issue has been posted before also but this time i wanted
> to know, if it is possible to know what statements have been
> executed by the backend in the same transaction which is currently
> idle. this can probably help us in identifying the faulty application
> code.

> in past we had set a logging prefix in guc and used grep to digout
> the statements but that was logging all statements which was
> probably not a good idea in production environment. Can some
> one suggest if anything else is readily available.

AFAICS, log_statement and friends are much cheaper than any other
alternative could be.

            regards, tom lane

> AFAICS, log_statement and friends are much cheaper than any other
> alternative could be.

Dear Sir,
thanks for the reply and clarifying.
regds
mallah.
>
>                         regards, tom lane
>