Re: How Do You Associate a Query With its Invoking Procedure?

On Fri, Sep 14, 2018 at 12:34 PM David G. Johnston <
david(dot)g(dot)johnston(at)gmail(dot)com> wrote:

> On Thu, Sep 13, 2018 at 12:49 PM, Fd Habash <fmhabash(at)gmail(dot)com> wrote:
>
>> In API function may invoke 10 queries. Ideally, I would like to know what
>> queries are invoked by it and how long each took.
>>
>>
>>
>> I’m using pg_stat_statement. I can see the API function statement, but
>> how do I deterministically identify all queries invoked by it?
>>
>
> pg_stat_statement is a global tracker that throws away execution context,
> in this case the process id, needed to track the level of detail you
> desire. I think the best you can do is log all statements and durations to
> the log file and parse that.
>
>
If you have big queries you almost certainly will want to bump your
"track_activity_query_size" value bigger to be able to capture the whole
query.

You are going to have to find the queries in the api source code. If they
are not distinct enough to easily figure out which was which you can do
things to make them distinct. One of the easiest things is to add a
"literal" column to the query:

select
'query_1',
first_name,
...

Then when you look in the query statements in the database you can see that
literal column and tell which query it was that invoked it.

You can also make them unique by renaming columns:

select
first_name as 'query1_first_name'
...

Depending on your ORM or whether your api calls queries directly, you could
add comments to the query as well:
select
-- this one is query 1
first_name,
...

Unfortunately there is no out of the box "github hook" that can
automatically connect a query from your postgresql logs to the lines of
code in your api.