Horton

Thread

  1. BUG #18790: Pg_stat_statements doesn't track schema.

    PG Bug reporting form <noreply@postgresql.org> — 2025-01-29T19:10:33Z

    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?

  2. Re: BUG #18790: Pg_stat_statements doesn't track schema.

    Michael Paquier <michael@paquier.xyz> — 2025-01-30T06:40:34Z

    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

  3. Re: BUG #18790: Pg_stat_statements doesn't track schema.

    Raghvendra Mishra <raghshr1351@gmail.com> — 2025-01-30T16:29:41Z

    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
>

  4. Re: BUG #18790: Pg_stat_statements doesn't track schema.

    Greg Sabino Mullane <htamfids@gmail.com> — 2025-01-30T18:01:15Z

    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

  5. Re: BUG #18790: Pg_stat_statements doesn't track schema.

    Raghvendra Mishra <raghshr1351@gmail.com> — 2025-01-31T11:23:18Z

    I doubt if it generates the same queryid for the substituted query
parameter we have in pg_stat_statement.

Thanks,
Raghvendra

  6. Re: BUG #18790: Pg_stat_statements doesn't track schema.

    Greg Sabino Mullane <htamfids@gmail.com> — 2025-01-31T17:50:38Z

    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