Re: pg_stat_statements and non default search_path

From: Lukas Fittl <lukas(at)fittl(dot)com>
To: Julien Rouhaud <julien(dot)rouhaud(at)dalibo(dot)com>, PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org>
Cc: Jim Nasby <Jim(dot)Nasby(at)bluetreble(dot)com>
Subject: Re: pg_stat_statements and non default search_path
Date: 2016-10-16 08:47:21
Message-ID: CAP53Pkx0jm+7bKvC=bhybZTgrcxiPLr62a-x29WBc0tmi=NKxA@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

On Sat, Oct 15, 2016 at 11:29 PM, Julien Rouhaud <julien(dot)rouhaud(at)dalibo(dot)com>
wrote:
>
> > BTW, after thinking about it some more, I don't see how storing the
> > search_path would help at all... it's not like you can do anything with
> > it unless you have a huge chunk of the parser available.
> >
>
> My use case is not really to know the fully qualified name of each
> identifier, but being able to optimize a problematic query found with
> pg_stat_statements. I can already "unjumble" it automatically, but the
> search_path is needed to be able to get an explain or just execute the
> query.
>

I'd also find having the search_path available to be a helpful benefit -
I've run into the same problem as Julien where two identical queries
couldn't be identified correctly because of the missing search_path.

In particular this situation might happen if you shard different tenants
using schemas (one schema for each tenant), and use the search_path to set
the current tenant.

In my own setup I combine pg_stat_statements with a slightly hacked up copy
of the Postgres parser, so having the correct search_path + query text
available is enough to find the objects a query references (most of the
time, there are a few other edge cases).

My assumption thus far has been that adding another field like this might
not be considered because of performance considerations.

Can somebody chime in if it would be feasible to store this in the
out-of-band query text file, and whether a patch for this would be
considered acceptable?

Best,
Lukas

--
Lukas Fittl

Skype: lfittl
Phone: +1 415 321 0630

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Julien Rouhaud 2016-10-16 09:17:07 Re: pg_stat_statements and non default search_path
Previous Message Julien Rouhaud 2016-10-16 06:29:58 Re: pg_stat_statements and non default search_path