Hi,
On Tue, Jan 11, 2022 at 03:04:14PM +0000, Dirschel, Steve wrote:
>
> I'm not sure if this is the correct distribution list for this type of
> question but I'll try anyways. We have an app that uses multiple schemas.
> It will do a set schema 'schema_name' and execute queries. The queries
> executed are the same regardless of the schema the connection set.
>
> In pg_stat_statements the exact same query will get a different queryid for
> each schema that executes the query.
>
> I'm unable to determine which queryid comes from which schema the query was
> executed under. Is anyone aware of a way to determine this?
>
Unfortunately this is a known limitation.
There were some previous discussions (e.g. [1] and [2] more recently), but I
don't think there was a real consensus on how to solve that problem.
Storing a query text with fully qualified names (either the current query or a
new field) is not practical for performance purpose, but there were no
objections to storing additional information, like the active search_path when
the entry was created. But as noted, while it technically gives the
information you need it's far from being convenient to use, which is probably
why it was never implemented.
[1] https://www.postgresql.org/message-id/flat/8f54c609-17c6-945b-fe13-8b07c0866420%40dalibo.com
[2] https://www.postgresql.org/message-id/flat/9baf5c06-d6ab-c688-010c-843348e3d98c%40gmail.com