Question 256564 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Finding the actual query which causing issue inside a function or procedure present in pg_stat_activity

I have run pg_stat_activity I found there are log of session running. One of session using a function. In that function there are several blocks or dml select operations are being performed. How can I find the select or dml operation causing issue. Because directly it is not available in the query section of pg_stat_activity. Please help. Thank you.

I have tried to find out solution but unable to get. As in Oracle it is readily available in v$ession even any procedure is running.

Tags:

2

Answers


  1. 0

    Enable query logging in your PostgreSQL setup by setting log_statement = 'all' to identify the specific SQL statement that is creating problems with a PostgreSQL function. Next, use the pg_stat_activity command to locate the session that is now running the function, note its backend PID, and look for items pertaining to that PID in the PostgreSQL log files. Consider the scenario where you are tracking a function called my_function() in a session with a PID of 12345. You may discover:

    2023-09-28 14:32:45.123 EDT [12345] [mydatabase] [myuser] [myapp] LOG:  statement: SELECT * FROM my_table WHERE condition = 'something';
2023-09-28 14:34:12.456 EDT [12345] [mydatabase] [myuser] [myapp] LOG:  statement: UPDATE my_table SET column = 'new_value' WHERE condition = 'something_else';

    You may identify and examine the issue SQL statements that were performed within the function by looking at the log entries connected to the PID of the session.

    Hope this helps, do let me know if this helps.

    Login or Signup to reply.
  2. 0

    You can see the slow statement with pg_stat_statements (if you enable the extension and set pg_stat_statements.track = all), or you can log the slow statement with auto_explain (which I recommend).

    In postgresql.conf, set

    shared_preload_libraries = 'auto_explain'

auto_explain.log_min_duration = 1000  # if you want to catch anything slower than a second
auto_explain.log_nested_statements = on

    Then restart PostgreSQL.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search