4

I have a heavy loaded application that holds connections persistently (it creates them on start and never releases).

During the lifetime it creates and (re)uses prepared statements, which are stored in an LRU structure (so the least used are eventually released).

And the question is: is it possible to obtain the number and other statistics of prepared statements for a given connection?

There is nothing relevant on http://www.postgresql.org/docs/9.3/static/monitoring-stats.html so not sure if there are other interfaces to access postgresql stats.

UPD: I probably was not clear enough: I need to obtain information using the normal DBA connection, since I cannot run arbitrary queries from the production software. For that very reason the pg_prepared_statements solution would not work.

2 Answers 2

2

You can view prepared statements with the pg_prepared_statements view. Query this view to get a count.

2
  • 1
    "The pg_prepared_statements view displays all the prepared statements that are available in the current session" --- and that's the problem: I cannot run arbitrary queries in the real production connections.
    – zerkms
    Nov 29, 2015 at 20:55
  • Seems like your other answer on stackoverflow would satisfy me :-) stackoverflow.com/a/12160161/251311
    – zerkms
    Nov 29, 2015 at 20:58
0

You can use pg_prepared_statements view to show all the prepared statements in the current session. *Prepared statements are deleted after logout:

SELECT * FROM pg_prepared_statements;

And, you can use count() to show the number of all the prepared statements in the current session:

SELECT count(*) FROM pg_prepared_statements;
1
  • You have not read the question entirely have you?
    – zerkms
    Jan 28 at 5:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.