CODEWITHSUNDEEP
postgresql
Hendrik
Hendrik

Reputation: 498

Get parameters for currently running queries in PostgreSQL

We wrote a small tool which displays all currently running queries. We get the currently running queries from pg_stat_activity.

The problem is: We dont know the parameters which were given to the query. We can only see the placeholders $1, $2, etc.

Is there any way to get the parameters for a currently running query?

The only workaround could be to enable the query log and parse the parameters from the query log, but this would be a very dirty and slow solution.

Upvotes: 43

Views: 10552

Answers (3)

Mark Tomandl
Mark Tomandl

Reputation: 31

How I solved this for numerical (BIG)INT values:

  1. Take a short (1 minute) packet capture on a Linux-based system: timeout 60s tcpdump -w db-$(date +%Y%m%d-%H%M).pcap
  2. Optionally compress the file: gzip db-$(date +%Y%m%d-%H%M).pcap
  3. Copy the file to your workstation and open in wireshark, it can understand gzipped files without you needing to expand them. I used the following filter: (pgsql.query || pgsql.type == "Row description" || pgsql.type == "Data row")
  4. Find a particular query in the bottom left pane in the Parse Message portion of the capture
    • Copy the PARSE fully parameterized query into fileA.txt (right-click on the "Query", select Copy Value)
    • Copy the BIND Parameter values into fileB.txt (right-click on the "Parameter values:" section, select Copy All Visible Selected Tree Items)
    • Run this fun little thing on a Linux-based system to convert the values from base16 as they're stored in the capture file back to base10 and display them properly in the query: for h in $(seq 1 $(grep Parameter fileB.txt | cut -d ':' -f 2 | tr -d ' ')); do Q="$(echo $((16#$(grep -m${h} Data fileB.txt | tail -1 | cut -d ':' -f 2 | tr -d ' '))))" sed -i "s/\$$h/$Q/" fileA.txt done; cat fileA.txt

Showing where the parameters display in wireshark 4.2.2

According to the docs, parameter-ized queries use the Extended Query protocol and has multiple parts:

  1. Parse Message. This includes the query string with parameters, some metadata about the parameter data types defined by OID (Object IDentifier, a context-sensitive type identification). This is what I was seeing in PGAdmin.
  2. Bind Message: This contains the values for the parameter placeholders and specifies the format for the data returned. All the values are there... but in hexadecimal form! Refer to step 4 above on how to convert them.
  3. Describe/Execute/Sync Messages, since we don't use prepared statements or stored procedures for my query, these are all kind of combined and run the query and close the unprepared statement and query plan.
  4. Parse Completion/Bind Completion/Row description/Data Row: This is the results of the query.

Coming from MySQL-land, it seemed very strange to me that I can't tell what values are being passed to the database! After puzzling over it a while, I thought the values had to be communicated somehow and that would happen over the network, so I tried a packet capture and there they are! In my case, we aren't using stored procedures or prepared statements and we're on PosgreSQL version 12 and we don't use SSL (this makes it much easier to read the capture).

Upvotes: 2

DavidEG
DavidEG

Reputation: 5947

I use to run all the queries through stored procedures/functions. This way you can add code to make a log at the start of every function.

Upvotes: 1

Peter Eisentraut
Peter Eisentraut

Reputation: 36719

I don't think it's possible. I have faced the same issue.

Upvotes: 12

Related Questions