pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedBUG #18790: Pg_stat_statements doesn't track schema.
6+ messages / 4 participants
[nested] [flat]
* BUG #18790: Pg_stat_statements doesn't track schema.
@ 2025-01-29 19:10 PG Bug reporting form <noreply@postgresql.org>
2025-01-30 06:40 ` Re: BUG #18790: Pg_stat_statements doesn't track schema. Michael Paquier <michael@paquier.xyz>
0 siblings, 1 reply; 6+ messages in thread
From: PG Bug reporting form @ 2025-01-29 19:10 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org; +Cc: raghshr1351@gmail.com
The following bug has been logged on the website:
Bug reference: 18790
Logged by: Raghvendra Mishra
Email address: raghshr1351@gmail.com
PostgreSQL version: 17.0
Operating system: Linux
Description:
Currently, pg_stat_statment doesn't track the schema to which the query
belongs. In the case of a multitenant database, it becomes hard to find a
query belonging to which customer is the culprit. It could be solely an
enhancement, so my question is, Is it useful to expose the schema name also?
If yes can I contribute to add this support?
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: BUG #18790: Pg_stat_statements doesn't track schema.
2025-01-29 19:10 BUG #18790: Pg_stat_statements doesn't track schema. PG Bug reporting form <noreply@postgresql.org>
@ 2025-01-30 06:40 ` Michael Paquier <michael@paquier.xyz>
2025-01-30 16:29 ` Re: BUG #18790: Pg_stat_statements doesn't track schema. Raghvendra Mishra <raghshr1351@gmail.com>
0 siblings, 1 reply; 6+ messages in thread
From: Michael Paquier @ 2025-01-30 06:40 UTC (permalink / raw)
To: raghshr1351@gmail.com; pgsql-bugs@lists.postgresql.org
On Wed, Jan 29, 2025 at 07:10:33PM +0000, PG Bug reporting form wrote:
> Currently, pg_stat_statment doesn't track the schema to which the query
> belongs. In the case of a multitenant database, it becomes hard to find a
> query belonging to which customer is the culprit. It could be solely an
> enhancement, so my question is, Is it useful to expose the schema name also?
> If yes can I contribute to add this support?
Objects from multiple schemas could be used in a single query. Even
if multiple schemas are tracked, I doubt that the cost of tracking
them is going to be really useful at this level for monitoring. Or
perhaps you have some specific use case in mind?
--
Michael
Attachments:
signature.asc (application/pgp-signature, 832B) download
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: BUG #18790: Pg_stat_statements doesn't track schema.
2025-01-29 19:10 BUG #18790: Pg_stat_statements doesn't track schema. PG Bug reporting form <noreply@postgresql.org>
2025-01-30 06:40 ` Re: BUG #18790: Pg_stat_statements doesn't track schema. Michael Paquier <michael@paquier.xyz>
@ 2025-01-30 16:29 ` Raghvendra Mishra <raghshr1351@gmail.com>
2025-01-30 18:01 ` Re: BUG #18790: Pg_stat_statements doesn't track schema. Greg Sabino Mullane <htamfids@gmail.com>
0 siblings, 1 reply; 6+ messages in thread
From: Raghvendra Mishra @ 2025-01-30 16:29 UTC (permalink / raw)
To: Michael Paquier <michael@paquier.xyz>; +Cc: pgsql-bugs@lists.postgresql.org
If multiple schema is used in a query then this information can be
extracted by parsing the query.
But when the schema is being accessed by setting the search path then it
becomes hard to find with which schema
query belongs to in pg_stat_statements.
Thanks for your attention,
Ragh
On Thu, 30 Jan 2025 at 12:10, Michael Paquier <michael@paquier.xyz> wrote:
> On Wed, Jan 29, 2025 at 07:10:33PM +0000, PG Bug reporting form wrote:
> > Currently, pg_stat_statment doesn't track the schema to which the query
> > belongs. In the case of a multitenant database, it becomes hard to find a
> > query belonging to which customer is the culprit. It could be solely an
> > enhancement, so my question is, Is it useful to expose the schema name
> also?
> > If yes can I contribute to add this support?
>
> Objects from multiple schemas could be used in a single query. Even
> if multiple schemas are tracked, I doubt that the cost of tracking
> them is going to be really useful at this level for monitoring. Or
> perhaps you have some specific use case in mind?
> --
> Michael
>
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: BUG #18790: Pg_stat_statements doesn't track schema.
2025-01-29 19:10 BUG #18790: Pg_stat_statements doesn't track schema. PG Bug reporting form <noreply@postgresql.org>
2025-01-30 06:40 ` Re: BUG #18790: Pg_stat_statements doesn't track schema. Michael Paquier <michael@paquier.xyz>
2025-01-30 16:29 ` Re: BUG #18790: Pg_stat_statements doesn't track schema. Raghvendra Mishra <raghshr1351@gmail.com>
@ 2025-01-30 18:01 ` Greg Sabino Mullane <htamfids@gmail.com>
2025-01-31 11:23 ` Re: BUG #18790: Pg_stat_statements doesn't track schema. Raghvendra Mishra <raghshr1351@gmail.com>
0 siblings, 1 reply; 6+ messages in thread
From: Greg Sabino Mullane @ 2025-01-30 18:01 UTC (permalink / raw)
To: Raghvendra Mishra <raghshr1351@gmail.com>; +Cc: Michael Paquier <michael@paquier.xyz>; pgsql-bugs@lists.postgresql.org
On Thu, Jan 30, 2025 at 11:30 AM Raghvendra Mishra <raghshr1351@gmail.com>
wrote:
> If multiple schema is used in a query then this information can be
> extracted by parsing the query.
>
But when the schema is being accessed by setting the search path then it
> becomes hard to find with which schema
> query belongs to in pg_stat_statements.
>
Even if you were to store the search_path as a separate field, there is no
promise that the items in it have not changed since the query was added to
pg_stat_statements. Your best bet is to schema-qualify your relations when
writing your queries. Then your pg_stat_statement output will contain the
information you want.
Note that you can use the queryid to figure out (with a little work) which
schemas were used for particular queries:
CREATE SCHEMA a; CREATE TABLE a.foo (id int);
CREATE SCHEMA b; CREATE TABLE b.foo (id int);
SET search_path = a; select * from foo;
SET search_path = b; select * from foo;
RESET search_path;
select query, queryid from pg_stat_statements where query ~ 'select \* from
foo';
query | queryid
-------------------+----------------------
select * from foo | 255924940643424438
select * from foo | -7783557204835816030
(2 rows)
greg=# explain verbose select * from a.foo;
QUERY PLAN
---------------------------------------------------------
Seq Scan on a.foo (cost=0.00..35.50 rows=2550 width=4)
Output: id
Query Identifier: 255924940643424438
(3 rows)
greg=# explain verbose select * from b.foo;
QUERY PLAN
---------------------------------------------------------
Seq Scan on b.foo (cost=0.00..35.50 rows=2550 width=4)
Output: id
Query Identifier: -7783557204835816030
Cheers,
Greg
--
Crunchy Data - https://www.crunchydata.com
Enterprise Postgres Software Products & Tech Support
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: BUG #18790: Pg_stat_statements doesn't track schema.
2025-01-29 19:10 BUG #18790: Pg_stat_statements doesn't track schema. PG Bug reporting form <noreply@postgresql.org>
2025-01-30 06:40 ` Re: BUG #18790: Pg_stat_statements doesn't track schema. Michael Paquier <michael@paquier.xyz>
2025-01-30 16:29 ` Re: BUG #18790: Pg_stat_statements doesn't track schema. Raghvendra Mishra <raghshr1351@gmail.com>
2025-01-30 18:01 ` Re: BUG #18790: Pg_stat_statements doesn't track schema. Greg Sabino Mullane <htamfids@gmail.com>
@ 2025-01-31 11:23 ` Raghvendra Mishra <raghshr1351@gmail.com>
2025-01-31 17:50 ` Re: BUG #18790: Pg_stat_statements doesn't track schema. Greg Sabino Mullane <htamfids@gmail.com>
0 siblings, 1 reply; 6+ messages in thread
From: Raghvendra Mishra @ 2025-01-31 11:23 UTC (permalink / raw)
To: Greg Sabino Mullane <htamfids@gmail.com>; +Cc: Michael Paquier <michael@paquier.xyz>; pgsql-bugs@lists.postgresql.org
I doubt if it generates the same queryid for the substituted query
parameter we have in pg_stat_statement.
Thanks,
Raghvendra
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: BUG #18790: Pg_stat_statements doesn't track schema.
2025-01-29 19:10 BUG #18790: Pg_stat_statements doesn't track schema. PG Bug reporting form <noreply@postgresql.org>
2025-01-30 06:40 ` Re: BUG #18790: Pg_stat_statements doesn't track schema. Michael Paquier <michael@paquier.xyz>
2025-01-30 16:29 ` Re: BUG #18790: Pg_stat_statements doesn't track schema. Raghvendra Mishra <raghshr1351@gmail.com>
2025-01-30 18:01 ` Re: BUG #18790: Pg_stat_statements doesn't track schema. Greg Sabino Mullane <htamfids@gmail.com>
2025-01-31 11:23 ` Re: BUG #18790: Pg_stat_statements doesn't track schema. Raghvendra Mishra <raghshr1351@gmail.com>
@ 2025-01-31 17:50 ` Greg Sabino Mullane <htamfids@gmail.com>
0 siblings, 0 replies; 6+ messages in thread
From: Greg Sabino Mullane @ 2025-01-31 17:50 UTC (permalink / raw)
To: Raghvendra Mishra <raghshr1351@gmail.com>; +Cc: Michael Paquier <michael@paquier.xyz>; pgsql-bugs@lists.postgresql.org
Yes, it will generate the same queryid regardless of the parameters: that's
the whole point of flattening (aka normalizing) the queries.
Cheers,
Greg
--
Crunchy Data - https://www.crunchydata.com
Enterprise Postgres Software Products & Tech Support
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2025-01-31 17:50 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2025-01-29 19:10 BUG #18790: Pg_stat_statements doesn't track schema. PG Bug reporting form <noreply@postgresql.org>
2025-01-30 06:40 ` Michael Paquier <michael@paquier.xyz>
2025-01-30 16:29 ` Raghvendra Mishra <raghshr1351@gmail.com>
2025-01-30 18:01 ` Greg Sabino Mullane <htamfids@gmail.com>
2025-01-31 11:23 ` Raghvendra Mishra <raghshr1351@gmail.com>
2025-01-31 17:50 ` Greg Sabino Mullane <htamfids@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