jo <jose(dot)soares(at)sferacarta(dot)com> wrote:
> 1) there are some queries running for 14 days, I tried to delete
> them without success.why they are there for 14 days? what are
> they doing? is there a way to remove them safely?
Those are idle connections which have not started a query in 14 days.
The safest way to close them is for the client which opened them to do
so, although I'm not sure why you feel it is important.
> 2) some times the current_query column show: "<IDLE>", sometimes
> show: "<IDLE> in transaction" and sometimes it show the query
> string. what those values mean?
If a query is currently running, you will see the start of it. If no
query is running on a connection, the connection will show '<IDLE>' or
'<IDLE> in transaction' depending on whether the client has started a
database transaction on that connection. While a transaction remains
open it can interfere with the work of other connections, including
some important maintenance activities (particularly VACUUM), so if
connections linger in '<IDLE> in transaction' state for a long time,
you should investigate. It is normally a client-side programming bug.
> 3) Sometimes the xact_start column show a timestamp and sometimes
> no. what this mean?
If a transaction isn't active on a connection, transaction start time
is not applicable.
> 4) whay the waiting column is all the time False even when the
> query seems in transaction?
This flag indicates whether the connection is waiting on a lock held
by some other PostgreSQL process.
> 5) what's the difference between query_start and backend_start?
Each connection has its own back end process, so the former indicates
the last time a query started on the connection, which the latter
indicates when the connection was made.
-Kevin