Re: Patch: Auto-generate search_path statement for selected schema in query editor

Hi

On Fri, Feb 8, 2013 at 3:51 PM, Florian Klaar <flo(dot)klaar(at)gmx(dot)de> wrote:

> Hi all,
>
> Recently I wrote a small patch for frmQuery.cpp in order to
> auto-generate a "SET search_path TO ..." statement when opening the
> query editor with a schema-related object selected in the object
> browser, e.g. a table, function or sequence (or their respective
> collections).
> The reason being that in our company, after migrating from MSSQL Server
> to pg, we use a single database for many of our customers, with one
> schema per customer and identically named objects within those schemas.
> E.g. cust_schema_01.table_x, cust_schema_02.table_x,
> cust_schema_03.table_x.
> For maintenance and debugging, we connect to the database using the
> postgres superuser account in pgAdmin3. Now in order to access table_x
> within a certain customer's schema in the query editor, we always have
> to prepend the schema name to the table name or issue a "SET search_path
> TO cust_schema_nn,public" statement.
> This is rather tedious, so I came up with a patch for pgAdmin3 that
> tries to intelligently generate a search_path depending on the currently
> selected object in the object browser as well as depending on the
> existing search_path configured for the current database connection.
> That way, we can easily open query editors under different schemas
> without bothering about the search_path ourselves.
>

OK.

> This is what my code does when opening a new query editor window:
> - Check whether the currently selected object in the object browser is
> of type pgSchema, pgSchemaObject or pgSchemaObjCollection or one of
> their descendants which (if I'm not mistaken) means it does have a
> schema associated with it.
>

Why not just do something like:

if (obj->GetSchema())
sql = wxT("SET search_path TO ") + obj->GetSchema()->GetName();

> - If so, it checks whether the schema belonging to this object is
> already contained in the user's search_path (case-sensitively and
> considering the $user placeholder).
> - If the schema isn't already in the user's search_path, the code
> generates a "SET search_path TO
> <selected_schema>,<existing_search_path>" statement and has it written
> into the newly opened query editor window.
>

Shouldn't you just check that it's not at the front of the search path?
Otherwise, if it's further back then queries might still be directed to a
different check.

> - After that, it places the cursor to the end of the sql text so the
> user can begin typing right away.
>

OK.

> Tested on Windows XP Pro SP3 and Windows7 Pro only - I didn't bother to
> create a build environment on my Linux box yet.
> In case you deem this patch useful, find the diff output based on the
> 1.16.1 release source code attached below. There may occur usability
> problems in combination with the existing "sticky SQL" option though. We
> don't use the "sticky SQL" feature in our environment, so for now I
> didn't spend too much thought on it.
>

It's essential the patch works with that, if it's to have any hope of being
committed.

> Beware also that though being a developer, I'm really inexperienced in
> C/C++ and completely new to wxWidgets and to the inner workings of
> pgAdmin, so there may well be room for improvement in my code. Having
> said that, I'd be willing to dig further into the pgAdmin3 code in order
> to make this a configurable option, integrate it better into the
> existing code etc. if need be.

Cool - thanks. I definitely think it needs to be a configurable option -
though, I wonder how it would work alongside Sticky SQL. That just copies
the SQL from the SQL pane into the query tool - but, that may have schemas
in it. If the search path is set, we almost certainly wouldn't want that
(related to that, are the various "XXXX Script" options on each object,
which have a similar issue)

--
Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake

EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company