Thread: Logging the query executed on the server
Hi, Is it possible to log the query that will be executed on the server? I'm writing an application that connects to the server through ODBC and libpq. For some reason ODBC interface is failing - it desn't return any rows So I'm thinking if I have a proof that the query I am actually executing is the same as the one I run through the psql - I will know where to look. I am actually binding some parameters and trying to execute the query. Thank you.
Hi Igor,
(from psql, but hopefully you get the idea)
b2bcreditonline=# set log_min_duration_statement to 0;
SET
b2bcreditonline=# set log_statement to 'all';
SET
SET
b2bcreditonline=# set log_statement to 'all';
SET
Ref: https://www.postgresql.org/docs/current/sql-set.html, https://www.postgresql.org/docs/current/config-setting.html
Then submit your query and it will be set to the server log. You can get the name of the current logfile with:
b2bcreditonline=# select pg_current_logfile();
pg_current_logfile
--------------------
/log/pg.csv
pg_current_logfile
--------------------
/log/pg.csv
HTH,
Steve
On Sun, Jul 24, 2022 at 3:26 PM Igor Korot <ikorot01@gmail.com> wrote:
Hi,
Is it possible to log the query that will be executed
on the server?
I'm writing an application that connects to the server
through ODBC and libpq.
For some reason ODBC interface is failing - it desn't
return any rows
So I'm thinking if I have a proof that the query I am
actually executing is the same as the one I run through
the psql - I will know where to look.
I am actually binding some parameters and trying to
execute the query.
Thank you.
Hi, Steve, On Sun, Jul 24, 2022 at 12:51 AM Steve Baldwin <steve.baldwin@gmail.com> wrote: > > Hi Igor, > > Before you issue your query, try something like this: > > (from psql, but hopefully you get the idea) > > b2bcreditonline=# set log_min_duration_statement to 0; > SET > b2bcreditonline=# set log_statement to 'all'; > SET > > Ref: https://www.postgresql.org/docs/current/sql-set.html, https://www.postgresql.org/docs/current/config-setting.html > > Then submit your query and it will be set to the server log. You can get the name of the current logfile with: > > b2bcreditonline=# select pg_current_logfile(); > pg_current_logfile > -------------------- > /log/pg.csv 2 things: 1. How do I turn this off? ;-) 2. The log does show the query but it shows it with the placeholders. Is there a way to see the actual query? Thank you. > > HTH, > > Steve > > On Sun, Jul 24, 2022 at 3:26 PM Igor Korot <ikorot01@gmail.com> wrote: >> >> Hi, >> Is it possible to log the query that will be executed >> on the server? >> >> I'm writing an application that connects to the server >> through ODBC and libpq. >> For some reason ODBC interface is failing - it desn't >> return any rows >> >> So I'm thinking if I have a proof that the query I am >> actually executing is the same as the one I run through >> the psql - I will know where to look. >> >> I am actually binding some parameters and trying to >> execute the query. >> >> Thank you. >> >>
On Sun, Jul 24, 2022 at 4:29 PM Igor Korot <ikorot01@gmail.com> wrote:
2 things:
1. How do I turn this off? ;-)
When you change the setting via 'set', that change is only for the current session. You can revert it with 'set {some param} to default;' or just terminate the session. If you want to make the change permanent, you need to set it in the config file.
2. The log does show the query but it shows it with the placeholders.
Is there a way to see the actual query?
Not sure what you mean here. The query shown in the log should be what is actually executed by the server. If you are using placeholders, there are probably 3 different log entries - one for the parse step, one for the bind step and one for the execute step. If you are asking what are the bind variable values, they are shown in the bind step. For example:
2022-07-24 07:00:00 UTC:10.122.33.196(33732):b2bc_api@b2bcreditonline:[7786]:LOG: duration: 0.072 ms bind <unnamed>:
select public.currency_on_mismatch() as on_mismatch,
set_config('search_path', $1, true),
set_config('application.user_id', $2, true),
set_config('application.app_client', $3, true),
set_config('application.api_client_id', $4 , true),
set_config('application.source', $5 , true),
set_config('application.request_id', $6 , true),
set_config('application.in_test_context', $7, true),
set_config('lock_timeout', $8, true),
txid_current()
2022-07-24 07:00:00 UTC:10.122.33.196(33732):b2bc_api@b2bcreditonline:[7786]:DETAIL: parameters: $1 = 'public', $2 = 'Admin/Support', $3 = 'Admin/Support', $4 = 'ce34e2bc-2c65-4fc1-9b95-878aef19a348', $5 = '?', $6 = '4432dbb2-ab1c-4bd8-a413-ff5c704209a6', $7 = 'f', $8 = '10s'
select public.currency_on_mismatch() as on_mismatch,
set_config('search_path', $1, true),
set_config('application.user_id', $2, true),
set_config('application.app_client', $3, true),
set_config('application.api_client_id', $4 , true),
set_config('application.source', $5 , true),
set_config('application.request_id', $6 , true),
set_config('application.in_test_context', $7, true),
set_config('lock_timeout', $8, true),
txid_current()
2022-07-24 07:00:00 UTC:10.122.33.196(33732):b2bc_api@b2bcreditonline:[7786]:DETAIL: parameters: $1 = 'public', $2 = 'Admin/Support', $3 = 'Admin/Support', $4 = 'ce34e2bc-2c65-4fc1-9b95-878aef19a348', $5 = '?', $6 = '4432dbb2-ab1c-4bd8-a413-ff5c704209a6', $7 = 'f', $8 = '10s'
If this doesn't help, maybe post what you're seeing in the log.
Cheers,
Steve