1

I was debugging my PostgreSQL database and found 7 queries for "DEALLOCATE pdo_stmt" that have been running for 15 days.

Reading from the PostgreSQL docs, I see that these queries are supposed to deallocate prepared statements after a session ends, but i can't believe that it can take so much time.

Also, other projects in the company have found that the same queries have been running for an unusual amounts of time.

The DB is hosted on an AWS RDS, and is being accessed using Laravel 5.2.

The query I ran was:

SELECT
 pid,
 now() - query_start as duration,
 query,
 state
FROM pg_stat_activity
 where now() - query_start  > interval '2 minutes'
 order by now() - query_start desc

An the result was:

10169   15 days 23:52:04.697072 DEALLOCATE pdo_stmt_00000003    idle
10170   15 days 23:52:04.64288  DEALLOCATE pdo_stmt_00000003    idle
10171   15 days 23:52:04.616907 DEALLOCATE pdo_stmt_00000003    idle
10172   15 days 23:52:04.60968  DEALLOCATE pdo_stmt_00000003    idle
10174   15 days 23:52:04.604393 DEALLOCATE pdo_stmt_00000003    idle
10173   15 days 23:52:04.596357 DEALLOCATE pdo_stmt_00000003    idle
10175   15 days 23:52:04.591835 DEALLOCATE pdo_stmt_00000003    idle
BernalCarlos
  • 936
  • 3
  • 12
  • 25
  • Are you using `PgBouncer`? – Hackerman Dec 21 '17 at 18:30
  • @Hackerman No, we're not using `PgBouncer`. The DB is hosted on an AWS RDS. – BernalCarlos Dec 21 '17 at 18:39
  • https://www.postgresql.org/docs/current/static/monitoring-stats.html#PG-STAT-ACTIVITY-VIEW "`idle`: The backend is waiting for a new client command." – Abelisto Dec 21 '17 at 18:51
  • @Abelisto I don't get you're point. What are you trying to say? – BernalCarlos Dec 21 '17 at 19:02
  • `idle` state means that the PostgreSQL (AWS) is idle and waiting for the next command (SQL statement) from the client. So it seems that the problem is not in the `deallocate` statement but in some "dead" sessions in the middle/frontend. – Abelisto Dec 21 '17 at 19:07
  • @Abelisto Mmm... Is a "dead" session a db connection that was never closed?. Also, doesn't postgres kill a connection that has been opened for 15 days? – BernalCarlos Dec 21 '17 at 19:27
  • Maybe you can take advantage of this answer: https://stackoverflow.com/questions/12391174/how-to-close-idle-connections-in-postgresql-automatically – Hackerman Dec 21 '17 at 19:35

1 Answers1

2

There is nothing to worry about. You are not seeing statements that are currently running, only the latest statement that has been executed in that database session (and probably, as you correctly assume, only took milliseconds).

See the documentation for the possible values of the state column.

Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263