pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedHow Do You Associate a Query With its Invoking Procedure?
7+ messages / 6 participants
[nested] [flat]
* How Do You Associate a Query With its Invoking Procedure?
@ 2018-09-13 19:49 Fd Habash <fmhabash@gmail.com>
0 siblings, 3 replies; 7+ messages in thread
From: Fd Habash @ 2018-09-13 19:49 UTC (permalink / raw)
To: pgsql-performance@lists.postgresql.org <pgsql-performance@lists.postgresql.org>
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?
----------------
Thank you
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: How Do You Associate a Query With its Invoking Procedure?
@ 2018-09-14 15:38 Fred Habash <fmhabash@gmail.com>
parent: Fd Habash <fmhabash@gmail.com>
2 siblings, 1 reply; 7+ messages in thread
From: Fred Habash @ 2018-09-14 15:38 UTC (permalink / raw)
To: pgsql-performance@lists.postgresql.org
Any ideas, please?
On Thu, Sep 13, 2018, 3:49 PM Fd Habash <fmhabash@gmail.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?
>
>
>
>
>
> ----------------
> Thank you
>
>
>
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: How Do You Associate a Query With its Invoking Procedure?
@ 2018-09-14 16:33 David G. Johnston <david.g.johnston@gmail.com>
parent: Fd Habash <fmhabash@gmail.com>
2 siblings, 1 reply; 7+ messages in thread
From: David G. Johnston @ 2018-09-14 16:33 UTC (permalink / raw)
To: Fd Habash <fmhabash@gmail.com>; +Cc: pgsql-performance@lists.postgresql.org <pgsql-performance@lists.postgresql.org>
On Thu, Sep 13, 2018 at 12:49 PM, Fd Habash <fmhabash@gmail.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.
For the "what queries are invoked by it" you can just read the source
code...
As there is no canned solution to provide the answer you seek the final
solution you come up with will be influenced by your access patterns,
specific needs, and (in)ability to write C code (though maybe there is an
extension out there you could leverage...).
David J.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: How Do You Associate a Query With its Invoking Procedure?
@ 2018-09-14 17:14 Rick Otten <rottenwindfish@gmail.com>
parent: David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 0 replies; 7+ messages in thread
From: Rick Otten @ 2018-09-14 17:14 UTC (permalink / raw)
To: David G. Johnston <david.g.johnston@gmail.com>; +Cc: fmhabash@gmail.com; pgsql-performance@lists.postgresql.org
On Fri, Sep 14, 2018 at 12:34 PM David G. Johnston <
david.g.johnston@gmail.com> wrote:
> On Thu, Sep 13, 2018 at 12:49 PM, Fd Habash <fmhabash@gmail.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.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: How Do You Associate a Query With its Invoking Procedure?
@ 2018-09-14 18:18 Roman Konoval <rkonoval@gmail.com>
parent: Fd Habash <fmhabash@gmail.com>
2 siblings, 0 replies; 7+ messages in thread
From: Roman Konoval @ 2018-09-14 18:18 UTC (permalink / raw)
To: Fd Habash <fmhabash@gmail.com>; +Cc: pgsql-performance@lists.postgresql.org <pgsql-performance@lists.postgresql.org>
If you can change the application then one option is to set application_name so that it contains API function name. This should happen before the first call in API function hits the database. After the API function finishes it should reset application_name.
Then you can enable logging of all queries and set the format to include application_name parameter. This way every query is logged and each log entry has an application name.
Several things to keep in mind:
1. logging everything may affect performance
2. application_name is 64 chars by default
Regards,
Roman Konoval
rkonoval@gmail.com
> On Sep 13, 2018, at 21:49, Fd Habash <fmhabash@gmail.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?
>
>
> ----------------
> Thank you
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: How Do You Associate a Query With its Invoking Procedure?
@ 2018-09-15 09:24 Patrick Molgaard <draaglom@gmail.com>
parent: Fred Habash <fmhabash@gmail.com>
0 siblings, 1 reply; 7+ messages in thread
From: Patrick Molgaard @ 2018-09-15 09:24 UTC (permalink / raw)
To: Fred Habash <fmhabash@gmail.com>; +Cc: pgsql-performance@lists.postgresql.org
You might find application-level tracing a more practical answer - e.g.
check out Datadog APM for a (commercial) plug and play approach or Jaeger
for a self-hostable option.
Patrick
On Fri, Sep 14, 2018 at 4:38 PM Fred Habash <fmhabash@gmail.com> wrote:
> Any ideas, please?
>
> On Thu, Sep 13, 2018, 3:49 PM Fd Habash <fmhabash@gmail.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?
>>
>>
>>
>>
>>
>> ----------------
>> Thank you
>>
>>
>>
>
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: How Do You Associate a Query With its Invoking Procedure?
@ 2018-09-16 21:53 Fred Habash <fmhabash@gmail.com>
parent: Patrick Molgaard <draaglom@gmail.com>
0 siblings, 0 replies; 7+ messages in thread
From: Fred Habash @ 2018-09-16 21:53 UTC (permalink / raw)
To: Patrick Molgaard <draaglom@gmail.com>; +Cc: pgsql-performance@lists.postgresql.org
All great ideas.
I was thinking something similar to some other RDBMS engines where SQL is automatically tied to the invoking PROGRAM_ID with zero setup on the client side. I thought there could be something similar in PG somewhere in the catalog.
As always, great support. This level of support helps a lot in our migration to Postgres.
————-
Thank you.
> On Sep 15, 2018, at 5:24 AM, Patrick Molgaard <draaglom@gmail.com> wrote:
>
> You might find application-level tracing a more practical answer - e.g. check out Datadog APM for a (commercial) plug and play approach or Jaeger for a self-hostable option.
>
> Patrick
>> On Fri, Sep 14, 2018 at 4:38 PM Fred Habash <fmhabash@gmail.com> wrote:
>> Any ideas, please?
>>
>>> On Thu, Sep 13, 2018, 3:49 PM Fd Habash <fmhabash@gmail.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?
>>>
>>>
>>>
>>>
>>>
>>> ----------------
>>> Thank you
>>>
>>>
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2018-09-16 21:53 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2018-09-13 19:49 How Do You Associate a Query With its Invoking Procedure? Fd Habash <fmhabash@gmail.com>
2018-09-14 15:38 ` Fred Habash <fmhabash@gmail.com>
2018-09-15 09:24 ` Patrick Molgaard <draaglom@gmail.com>
2018-09-16 21:53 ` Fred Habash <fmhabash@gmail.com>
2018-09-14 16:33 ` David G. Johnston <david.g.johnston@gmail.com>
2018-09-14 17:14 ` Rick Otten <rottenwindfish@gmail.com>
2018-09-14 18:18 ` Roman Konoval <rkonoval@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox