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

Apologies for the delay in replying...

On Mon, Feb 11, 2013 at 4:38 PM, Florian Klaar <flo(dot)klaar(at)gmx(dot)de> wrote:

> I wonder if we're actually looking at it the wrong way, and what we really
> should consider is allowing the user to define a "template" block of SQL
> that's always added to any new SQL Query windows. That block could include
> placeholders that are replaced with context-specific values, or GUC
> variable values, e.g, the user could specify a template of:
>
> SET search_path TO '%%SCHEMA%%, %%GUC:search_path%%'
>
> Which would replace %%SCHEMA%% with the context-specific schema, and
> %%GUC:search_path%% with the current value of the search_path GUC.
>
> The nice thing about doing it this way is that it can be used for a lot
> of different purposes - you can solve your problem, another user might have
> a default of "BEGIN;" to ensure they always run in an explicit transaction
> block etc, but perhaps more importantly, it saves us having to worry about
> what Sticky SQL or XXX Script features do, as it becomes an issue for the
> user to ensure their templates will work correctly in their environment.
>
> That'd be a nice enhancement indeed. But using your example with the
> search_path, we'd still have the inconvenience of a redundant
> [public,myschema,public] path resulting from the template. Which OTOH is
> just a matter of taste (or is it?).
>

Yes, you would. I think that's a price you'd pay for flexibility.

On further thought, I'm fairly convinced that this is probably the best way
to implement this. It's infinitely more flexible and could be useful in so
many different cases, and it avoids the need for many special cases in the
code for handling scenarios like those we've discussed, but shifting that
responsibility onto the user (bwhahahaha :-) ).

>
> BTW, after adapting my code for re-ordering the search_path like we
> discussed and making it a configurable option (which was really easy,
> actually), I tested another unrelated idea: executing a query in the query
> editor repeatedly in user-definable intervals. I intended this primarily
> for monitoring tasks (pgAdmin's "server status" feature is great, but
> sometimes you just have more specific demands) or for watching logging
> tables being populated by an external application. This comes with a few
> UI-related problems though... all input fields should be made read-only
> while the timer is running, and the query editor for some reason is always
> brought to the foreground everytime the query has completed, which can be
> annoying. Also, certain menu items in the query editor should be disabled
> when the timer is running but the statement is not currently executed.
> If this feature might be of broader interest, I'd be willing to look into
> some of these issues.
>

Wouldn't it be easiest to do this by adding a new procedure (WAIT) to the
pgScript language, and then just running the query in a WHILE TRUE style
loop, with a WAIT call in it?

> Oh, and when testing this, I found a small bug: open query editor, click
> into the SQL notepad, then click on the GQB tab... pgAdmin will crash. I
> haven't traced this down yet, just noticed it's reproduceable.
>

I can't reproduce that here on Mac. Can you get a backtrace?

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

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