Thread: Loading the PL/pgSQL debugger (and other plugins)

Loading the PL/pgSQL debugger (and other plugins)

From
korry
Date:
I'm working on a patch that implements the PL/pgSQL instrumentation 
stuff (i.e. the PL/pgSQL debugger)  that I discussed at the Anniversary 
Summit and I need some opinions (this seems like a good place to look 
for opinions :-)

A quick review:  the PL/pgSQL debugger is designed as an optional 
"plugin" that loads into the PL/pgSQL interpreter on-demand.  You can 
use the plugin idea to implement other kinds of instrumentation (I 
demo'ed a tracer and a profiler at the conference, along with a 
debugger).  A plugin architecture greatly reduces the (source code) 
footprint that would normally be required to implement a full-featured 
debugger.

A plugin is basically a structure that contains a few function 
pointers.  If those function pointers are NULL, the PL/pgSQL interpreter 
works exactly the way it does today.  If any of those function pointers 
are non-NULL, the PL/pgSQL interpreter calls the target function (which 
points to a chunk of code inside of the plugin) and the plugin does 
whatever it needs to do.

Right now, the plugin structure looks like this:

typedef struct
{   void (*init)( estate,  func, error_callback, assign_expr, expr );   void (*func_beg)( PLpgSQL_execstate * estate,
PLpgSQL_function* func );   void (*func_end)( PLpgSQL_execstate * estate, PLpgSQL_function * func );   void
(*stmt_beg)(PLpgSQL_execstate * estate, PLpgSQL_stmt * stmt );   void (*stmt_end)( PLpgSQL_execstate * estate,
PLpgSQL_stmt* stmt );
 
} PLpgSQL_plugin;

I've truncated the argument list (in this e-mail) for the (*init)() 
function since it's rather long (error_callback and assign_expr are both 
function pointers).

When the PL/pgSQL intrepreter loads the plugin, it calls the 
plugin->init() function.
When the PL/pgSQL intrepreter starts running a new function, it calls 
the plugin->func_beg() function.
When the PL/pgSQL intrepreter completes a function, it calls the 
plugin->func_end() function.
When the PL/pgSQL interpreter is about to execute a line of PL/pgSQL 
code, it calls plugin->stmt_beg()
When the PL/pgSQL interpreter has finished executing a line of PL/pgSQL 
code, it calls plugin->stmt_end()

So here is where I need a few opinions:

1) I think the most straightforward way to load an instrumentation 
plugin is to define a new custom GUC variable (using the 
custom_variable_classes mechanism).  When the PL/pgSQL call-handler 
loads, it can check that config. variable (something like plpgsql.plugin 
= '$libdir/plugin_profiler' or plpgsql.plugin = 
'$libdir/plugin_debugger') and load the plugin if non-NULL.  That seems 
a little obtuse to me since custom variables don't appear in the 
prototype postgresql.conf file.  Would it be better to add a real GUC 
variable instead of a custom variable?

2) Given that plpgsql.plugin points to the name of a shared-object file 
(or DLL or whatever you prefer to call it), we need to find *something* 
inside of the file.  The most obvious choice would be to look for a 
variable (a structure or structure pointer) with a fixed name. That 
would mean, for example, that a plugin would define an externally 
visible PLpgSQL_plugin structure named "plugin_hooks" and the PL/pgSQL 
interpreter would look for that symbol inside of the plugin.  
Alternatively, we could look for a function inside of the plugin 
(something like 'plugin_loader') and then call that function with a 
pointer to a PLpgSQL_plugin structure.  I prefer the function-pointer 
approach since we already have a reliable mechanism in place for finding 
a function inside of a shared-object (the same mechanism works for 
finding a variable instead of a function pointer, but I doubt that that 
has been tested in all platforms).

3) Any comments on the PLpgSQL_plugin structure?  Should it include (as 
it's first member) a structure version number so we can add to/change 
the structure as needed?

4) Do we need to support multiple active plugins?  Would you ever need 
to load the debugger at the same time you've loaded the profiler (no)?  
Would you ever need to load the tracer at the same time you need the 
debugger (probably not)?  If we need to support multiple plugins, should 
be just introduce a meta-plugin that knows how to handle a list of other 
plugins? (Messy, but certainly gets the job done without worrying about 
it right now).

5) I'll also be adding a void pointer to the PLpgSQL_execstate structure 
(think of a PLpgSQL_execstate as a stack frame).  The new pointer is 
reserved for use by the plugin.  It may be handy to add a void pointer 
to each PLpgSQL_stmt as well - is that acceptable? (That would mean an 
extra 4-bytes per-line of compiled PL/pgSQL code, even if you don't have 
a plugin loaded).

Any other comments?  Obviously, you'll have a chance to critique the 
patch when I get it sent in.

Thanks for your help.
         -- Korry




Re: Loading the PL/pgSQL debugger (and other plugins)

From
Tom Lane
Date:
korry <korryd@enterprisedb.com> writes:
> I'm working on a patch that implements the PL/pgSQL instrumentation 
> stuff (i.e. the PL/pgSQL debugger)  that I discussed at the Anniversary 
> Summit and I need some opinions (this seems like a good place to look 
> for opinions :-)

Opinions R US ;-)

> 1) I think the most straightforward way to load an instrumentation 
> plugin is to define a new custom GUC variable (using the 
> custom_variable_classes mechanism).

This seems a bit messy and special-purpose.  I see no good reason to tie
it to plpgsql; we'll just need another one for every other language.
IMHO what we want is something with similar properties to preload_libraries,
but processed on a per-backend basis instead of once at postmaster start.
(You could almost just tell people to select the plugin they want by
LOADing it, but that is hard to use if you're trying to debug a
non-interactive application.  A GUC variable can be set for an app
without much cooperation from the app.)

When the plugin's shared library gets loaded, one way or the other,
it should construct the function-pointer struct and then pass it to a
function defined by plpgsql (this lets us hide/postpone the decision
about whether there can be more than one active plugin).

One issue that needs to be thought about with either this proposal or
your original is what permissions are needed to set the GUC variable.
I don't think we dare allow non-superusers to specify LOADing of
arbitrary shared libraries, so there has to be some filter function.

Perhaps a better way is that the GUC variable specifies a (list of)
initialization functions to call at backend start, and then the
superuserness is involved with installing the init functions into
pg_proc, and the GUC variable itself needs no special permissions.
Again, a plugin's init function would just register its function-pointer
struct with plpgsql.

We should also think about a deregistration function.  This would allow
you to turn debugging on and off within an interactive session.  The
GUC variable is really only for coercing non-interactive applications
into being debuggable --- I don't see it as being important for
interactive debugging, as compared to just "select plugin_init();" ...

> 3) Any comments on the PLpgSQL_plugin structure?  Should it include (as 
> it's first member) a structure version number so we can add to/change 
> the structure as needed?

Given our current plans for enforcing recompiles at major version
changes (via magic-block checking), I'm not sure I see a need for this.

> 4) Do we need to support multiple active plugins?

Probably, but let's fix the API to hide this, so we don't have to commit
now.
        regards, tom lane


Re: Loading the PL/pgSQL debugger (and other plugins)

From
korry
Date:
Thanks for the quick feedback.<br /><blockquote cite="mid14284.1153330516@sss.pgh.pa.us" type="cite"><blockquote
type="cite"><prewrap="">1) I think the most straightforward way to load an instrumentation 
 
plugin is to define a new custom GUC variable (using the 
custom_variable_classes mechanism).   </pre></blockquote><pre wrap="">
This seems a bit messy and special-purpose.  </pre></blockquote> Agreed, I'm not crazy about using a
custom_variable_classvariable either.<br /><blockquote cite="mid14284.1153330516@sss.pgh.pa.us" type="cite"><pre
wrap="">Isee no good reason to tie
 
it to plpgsql; we'll just need another one for every other language. </pre></blockquote> Hmmm... but the plugins
themselveswould be language-specific.  I can't imagine that a plugin (say a profiler) for PL/python would work for
PL/pgSQL. It seems to me that, even if we come up with a common mechanism, we'll still need a separate GUC variable
*name*for each PL.  Or am I not understanding something?  Can you post an example of what you are thinking (what would
sucha GUC variable look like)?<br /><br /><blockquote cite="mid14284.1153330516@sss.pgh.pa.us" type="cite"><pre
wrap="">IMHOwhat we want is something with similar properties to preload_libraries,
 
but processed on a per-backend basis instead of once at postmaster start.
(You could almost just tell people to select the plugin they want by
LOADing it, but that is hard to use if you're trying to debug a
non-interactive application.  A GUC variable can be set for an app
without much cooperation from the app.) </pre></blockquote> Agreed. <br /><blockquote
cite="mid14284.1153330516@sss.pgh.pa.us"type="cite"><pre wrap="">When the plugin's shared library gets loaded, one way
orthe other,
 
it should construct the function-pointer struct and then pass it to a
function defined by plpgsql (this lets us hide/postpone the decision
about whether there can be more than one active plugin). </pre></blockquote> But there's a timing issue there.  If you
askthe plugin to call a call-handler function, then you can't load the plugin at backend startup because the PL/pgSQL
call-handlerisn't loaded until it's required.  Since both the plugin and the call-handler are dynamically loaded, I
thinkone of them has to load the other.  We already have a mechanism for loading call-handlers on demand - it seems
kindof messy to introduce another mechanism for loading plugins (that in turn load the call-handlers).<br /><br /> The
PL/pgSQLcall-handler has a convenient initialization function that could read the GUC variable and load the referenced
plugin(that's what I'm doing right now).<br /><br /> What I'm thinking is that the plpgsql_init() function would look
somethinglike this (my changes in red);<br /><br /><tt><font color="#ff0000">PLpgSQL_plugin   pluginHooks;<br />
typedefvoid (*plugin_loader_func)(PLpgSQL_plugin *hooks);<br /></font><br /> void<br /> plpgsql_init(void)<br /> {<br
/><fontcolor="#ff0000">    static char * pluginName;<br />     plugin_load_func   plugin_loader();<br /></font><br />
   /* Do initialization only once */<br />     if (!plpgsql_firstcall)<br />         return;<br /><br />    
plpgsql_HashTableInit();<br/>     RegisterXactCallback(plpgsql_xact_cb, NULL);<br />     plpgsql_firstcall = false;<br
/><br/><font color="#ff0000">    /* Load any instrumentation plugins */<br />     DefineCustomStringVariable(
"plpgsql.plugin",<br />                                 "Name of instrumentation plugin to use when PL/pgSQL function
isinvoked",<br />                                 NULL,<br />                                 &pluginName,<br />
                               PGC_USERSET,<br />                                 NULL,<br />                        
       NULL );<br /><br />     EmitWarningsOnPlaceholders("plpgsql");<br /><br />     if (pluginName )<br />     {<br
/>        plugin_loader = (plugin_loader_func *)load_external_function(pluginName, "plugin_loader", false, NULL );<br
/><br/>         if (plugin_loader)<br />             (*plugin_loader)(&pluginHooks);<br />     }<br
/></font>}  </tt> <br /><br /> (Ignore the custom variable stuff for now)<br /><br /> Each plugin would export a
plugin_loader()function - that function, given a pointer to a PLpgSQL_plugin structure, would fill in that structure
withthe required function pointers. <br /><blockquote cite="mid14284.1153330516@sss.pgh.pa.us" type="cite"><pre
wrap="">
One issue that needs to be thought about with either this proposal or
your original is what permissions are needed to set the GUC variable.
I don't think we dare allow non-superusers to specify LOADing of
arbitrary shared libraries, so there has to be some filter function.

Perhaps a better way is that the GUC variable specifies a (list of)
initialization functions to call at backend start, and then the
superuserness is involved with installing the init functions into
pg_proc, and the GUC variable itself needs no special permissions.
Again, a plugin's init function would just register its function-pointer
struct with plpgsql. </pre></blockquote> You're right, privileges are an issue.  Is it safe enough if we force all
pluginsto reside in $libdir?  Each plugin could enforce additional security as needed that way, but you'd have to hold
enoughprivileges to get your plugin into $libdir to begin with so you can't write your own nasty plugin to gain more
privilegesthan you ought to have.<br /><blockquote cite="mid14284.1153330516@sss.pgh.pa.us" type="cite"><pre wrap="">
 
We should also think about a deregistration function.  This would allow
you to turn debugging on and off within an interactive session.  The
GUC variable is really only for coercing non-interactive applications
into being debuggable --- I don't see it as being important for
interactive debugging, as compared to just "select plugin_init();" ... </pre></blockquote> Ok.<br /><blockquote
cite="mid14284.1153330516@sss.pgh.pa.us"type="cite"><blockquote type="cite"><pre wrap="">3) Any comments on the
PLpgSQL_pluginstructure?  Should it include (as 
 
it's first member) a structure version number so we can add to/change 
the structure as needed?   </pre></blockquote><pre wrap="">
Given our current plans for enforcing recompiles at major version
changes (via magic-block checking), I'm not sure I see a need for this. </pre></blockquote> That makes a lot more sense
-I don't like the idea of each plugin managing its own version information.  We can always add more function pointers
tothe end of the plugin structure - if the pointers are non-NULL, you gain more functionality.<br /><blockquote
cite="mid14284.1153330516@sss.pgh.pa.us"type="cite"><pre wrap=""></pre><blockquote type="cite"><pre wrap="">4) Do we
needto support multiple active plugins?   </pre></blockquote><pre wrap="">
 
Probably, but let's fix the API to hide this, so we don't have to commit
now. </pre></blockquote> Cool.<br /><br />           -- Korry<br /><br />

Re: Loading the PL/pgSQL debugger (and other plugins)

From
korry
Date:
Sorry to poke - but I'd like to get a patch submitted next week.  Any more comments?  Thanks.<br /><br />           --
Korry<br/><br /><blockquote cite="mid44BE7D8E.7080103@enterprisedb.com" type="cite"> Thanks for the quick feedback.<br
/><blockquotecite="mid14284.1153330516@sss.pgh.pa.us" type="cite"><blockquote type="cite"><pre wrap="">1) I think the
moststraightforward way to load an instrumentation 
 
plugin is to define a new custom GUC variable (using the 
custom_variable_classes mechanism).   </pre></blockquote><pre wrap="">
This seems a bit messy and special-purpose.  </pre></blockquote> Agreed, I'm not crazy about using a
custom_variable_classvariable either.<br /><blockquote cite="mid14284.1153330516@sss.pgh.pa.us" type="cite"><pre
wrap="">Isee no good reason to tie
 
it to plpgsql; we'll just need another one for every other language. </pre></blockquote> Hmmm... but the plugins
themselveswould be language-specific.  I can't imagine that a plugin (say a profiler) for PL/python would work for
PL/pgSQL. It seems to me that, even if we come up with a common mechanism, we'll still need a separate GUC variable
*name*for each PL.  Or am I not understanding something?  Can you post an example of what you are thinking (what would
sucha GUC variable look like)?<br /><br /><blockquote cite="mid14284.1153330516@sss.pgh.pa.us" type="cite"><pre
wrap="">IMHOwhat we want is something with similar properties to preload_libraries,
 
but processed on a per-backend basis instead of once at postmaster start.
(You could almost just tell people to select the plugin they want by
LOADing it, but that is hard to use if you're trying to debug a
non-interactive application.  A GUC variable can be set for an app
without much cooperation from the app.) </pre></blockquote> Agreed. <br /><blockquote
cite="mid14284.1153330516@sss.pgh.pa.us"type="cite"><pre wrap="">When the plugin's shared library gets loaded, one way
orthe other,
 
it should construct the function-pointer struct and then pass it to a
function defined by plpgsql (this lets us hide/postpone the decision
about whether there can be more than one active plugin). </pre></blockquote> But there's a timing issue there.  If you
askthe plugin to call a call-handler function, then you can't load the plugin at backend startup because the PL/pgSQL
call-handlerisn't loaded until it's required.  Since both the plugin and the call-handler are dynamically loaded, I
thinkone of them has to load the other.  We already have a mechanism for loading call-handlers on demand - it seems
kindof messy to introduce another mechanism for loading plugins (that in turn load the call-handlers).<br /><br /> The
PL/pgSQLcall-handler has a convenient initialization function that could read the GUC variable and load the referenced
plugin(that's what I'm doing right now).<br /><br /> What I'm thinking is that the plpgsql_init() function would look
somethinglike this (my changes in red);<br /><br /><tt><font color="#ff0000">PLpgSQL_plugin   pluginHooks;<br />
typedefvoid (*plugin_loader_func)(PLpgSQL_plugin *hooks);<br /></font><br /> void<br /> plpgsql_init(void)<br /> {<br
/><fontcolor="#ff0000">    static char * pluginName;<br />     plugin_load_func   plugin_loader();<br /></font><br />
   /* Do initialization only once */<br />     if (!plpgsql_firstcall)<br />         return;<br /><br />    
plpgsql_HashTableInit();<br/>     RegisterXactCallback(plpgsql_xact_cb, NULL);<br />     plpgsql_firstcall = false;<br
/><br/><font color="#ff0000">    /* Load any instrumentation plugins */<br />     DefineCustomStringVariable(
"plpgsql.plugin",<br />                                 "Name of instrumentation plugin to use when PL/pgSQL function
isinvoked",<br />                                 NULL,<br />                                 &pluginName,<br />
                               PGC_USERSET,<br />                                 NULL,<br />                        
       NULL );<br /><br />     EmitWarningsOnPlaceholders("plpgsql");<br /><br />     if (pluginName )<br />     {<br
/>        plugin_loader = (plugin_loader_func *)load_external_function(pluginName, "plugin_loader", false, NULL );<br
/><br/>         if (plugin_loader)<br />             (*plugin_loader)(&pluginHooks);<br />     }<br
/></font>}  </tt> <br /><br /> (Ignore the custom variable stuff for now)<br /><br /> Each plugin would export a
plugin_loader()function - that function, given a pointer to a PLpgSQL_plugin structure, would fill in that structure
withthe required function pointers. <br /><blockquote cite="mid14284.1153330516@sss.pgh.pa.us" type="cite"><pre
wrap="">Oneissue that needs to be thought about with either this proposal or
 
your original is what permissions are needed to set the GUC variable.
I don't think we dare allow non-superusers to specify LOADing of
arbitrary shared libraries, so there has to be some filter function.

Perhaps a better way is that the GUC variable specifies a (list of)
initialization functions to call at backend start, and then the
superuserness is involved with installing the init functions into
pg_proc, and the GUC variable itself needs no special permissions.
Again, a plugin's init function would just register its function-pointer
struct with plpgsql. </pre></blockquote> You're right, privileges are an issue.  Is it safe enough if we force all
pluginsto reside in $libdir?  Each plugin could enforce additional security as needed that way, but you'd have to hold
enoughprivileges to get your plugin into $libdir to begin with so you can't write your own nasty plugin to gain more
privilegesthan you ought to have.<br /><blockquote cite="mid14284.1153330516@sss.pgh.pa.us" type="cite"><pre wrap="">We
shouldalso think about a deregistration function.  This would allow
 
you to turn debugging on and off within an interactive session.  The
GUC variable is really only for coercing non-interactive applications
into being debuggable --- I don't see it as being important for
interactive debugging, as compared to just "select plugin_init();" ... </pre></blockquote> Ok.<br /><blockquote
cite="mid14284.1153330516@sss.pgh.pa.us"type="cite"><blockquote type="cite"><pre wrap="">3) Any comments on the
PLpgSQL_pluginstructure?  Should it include (as 
 
it's first member) a structure version number so we can add to/change 
the structure as needed?   </pre></blockquote><pre wrap="">
Given our current plans for enforcing recompiles at major version
changes (via magic-block checking), I'm not sure I see a need for this. </pre></blockquote> That makes a lot more sense
-I don't like the idea of each plugin managing its own version information.  We can always add more function pointers
tothe end of the plugin structure - if the pointers are non-NULL, you gain more functionality.<br /><blockquote
cite="mid14284.1153330516@sss.pgh.pa.us"type="cite"><blockquote type="cite"><pre wrap="">4) Do we need to support
multipleactive plugins?   </pre></blockquote><pre wrap="">
 
Probably, but let's fix the API to hide this, so we don't have to commit
now. </pre></blockquote> Cool.<br /><br />           -- Korry<br /><br /></blockquote><br />

Re: Loading the PL/pgSQL debugger (and other plugins)

From
John DeSoi
Date:
Hi Korry,

On Jul 21, 2006, at 12:51 PM, korry wrote:

> Sorry to poke - but I'd like to get a patch submitted next week.   
> Any more comments?  Thanks.


I'm unqualified to comment on the server side design, but I was  
wondering if there was consensus on how the client interface to the  
debugger would work. From previous threads I saw DBGP mentioned  
(http://xdebug.org/docs-dbgp.php), but I don't recall seeing any  
final commitment to it.

Thanks,

John




John DeSoi, Ph.D.
http://pgedit.com/
Power Tools for PostgreSQL



Re: Loading the PL/pgSQL debugger (and other plugins)

From
korry
Date:
> I'm unqualified to comment on the server side design, but I was 
> wondering if there was consensus on how the client interface to the 
> debugger would work. From previous threads I saw DBGP mentioned 
> (http://xdebug.org/docs-dbgp.php), but I don't recall seeing any final 
> commitment to it.
The patch that I'll be submitting for 8.2 will implement a way to 
instrument PL/pgSQL (and that idea can be extended to other PL 
languages).  'Instrumentation' can mean different things - it may be a 
debugger, a profiler, a coverage analyzer, a tracer, ... 

EnterpriseDB has developed a few plugins that we'll be contributing soon 
(a debugger, a profiler, and a tracer).  The debugger is by far the 
largest plugin that we've developed and we implemented it before we had 
the idea to use a modular architecture (we're still in the process of 
converting the debugger to modular form, at the moment it's pretty 
heavily integrated into the PL/pgSQL interpreter).  As soon as we get a 
patch in for the plugin architecture, we'll open-source at least one or 
two of the plugins so others can use them and/or write more (the 
debugger will take a little longer). 

That means that we (i.e. the community) haven't made a firm commitment 
to the debugger client protocol.  I can tell you a little about the 
protocol that we are currently using, but it may change by the time 
we're ready to open-source the debugger.  I gave a presentation at the 
anniversary summit that described the overall architecture and also 
showed the client/server protocol - the slides and audio should be 
available at the conference web site "real soon now". 

The most important part, from your perspective (assuming that you might 
want to add a debugger to pgEdit), is the method that a debugger client 
application uses to interact with the debugger server.  That's done 
through a collection of server-side functions that you can call from any 
libpq application.  For example, to set a breakpoint, you would:
   SELECT * FROM pldbg_set_breakpoint( sessionHandle, functionOID, 
lineNumber, processID );

to step/over:
   SELECT * FROM pldbg_step_over( sessionHandle );

to step/into:
   SELECT * FROM pldbg_step_into( sessionHandle );

to get a copy of all local variables:
   SELECT * FROM pldbg_get_variables( sessionHandle, stackFrame );

and so on.  There are a few functions that you can call to attach your 
debugger client to a target server and to set global breakpoints.

I'll be posting more information as we get closer to releasing this stuff. 
            -- Korry


Re: Loading the PL/pgSQL debugger (and other plugins)

From
Tom Lane
Date:
korry <korryd@enterprisedb.com> writes:
>> I see no good reason to tie
>> it to plpgsql; we'll just need another one for every other language.
>> 
> Hmmm... but the plugins themselves would be language-specific.

You miss my point.  The plugins will be language-specific but the
mechanism for selecting/loading them shouldn't be.

>> When the plugin's shared library gets loaded, one way or the other,
>> it should construct the function-pointer struct and then pass it to a
>> function defined by plpgsql (this lets us hide/postpone the decision
>> about whether there can be more than one active plugin).
>> 
> But there's a timing issue there.  If you ask the plugin to call a 
> call-handler function, then you can't load the plugin at backend startup 
> because the PL/pgSQL call-handler isn't loaded until it's required.  
> Since both the plugin and the call-handler are dynamically loaded, I 
> think one of them has to load the other.

Right, but if you set up the mechanism such that each individual PL is
responsible for loading plugins, then we'll have to duplicate all that
code each time we instrument another PL.  I want to do as much as
possible of the work in the core code so that we don't end up with
duplicate code to maintain.

That being the case, I don't see anything wrong with having the
selection mechanism pull in the selected plugin(s) and then those
force loading of the language handlers so that they can call the plugin
installation function.  Sure, sometimes this would result in loading
a plugin and handler that don't get used in the current session, but
given that people would only load plugins they intend to use, I don't
see that as a significant objection.

I'm thinking that the cleanest way to handle this would be to add
another column to pg_language containing the OID of the plugin receptor
function for each PL.  Then the plugin just calls that function passing
its constructed function-pointer struct.  This eliminates the need for
hard-wired assumptions about function names and so forth, and also lets
you use the existing fmgr functionality to pull in the PL's handler
library.  OTOH this requires extending the syntax of CREATE LANGUAGE
and so on.  That is all doable (it's basically the same kind of work
that got done when we added validator functions for PLs) but it might
be more work than we think the plugin idea is worth.

To do it without a pg_language column, we'd need code in each plugin to
identify the language shared library (by looking in pg_language), force
loading of same (using existing fmgr code), and look up and call a
plugin receptor function given an expected C-code name for it (again,
most of this already exists in fmgr).  It's not a huge amount of code,
probably, but again duplicating it in each plugin seems unappealing.
I suppose we could make fmgr export a general function to find a plugin
receptor function given the PL name and the expected C symbol.

Comments anyone?
        regards, tom lane


Re: Loading the PL/pgSQL debugger (and other plugins)

From
korry
Date:
<br /><blockquote cite="mid700.1153506529@sss.pgh.pa.us" type="cite"><blockquote type="cite"><blockquote
type="cite"><prewrap="">When the plugin's shared library gets loaded, one way or the other,
 
it should construct the function-pointer struct and then pass it to a
function defined by plpgsql (this lets us hide/postpone the decision
about whether there can be more than one active plugin).
     </pre></blockquote><pre wrap="">But there's a timing issue there.  If you ask the plugin to call a 
call-handler function, then you can't load the plugin at backend startup 
because the PL/pgSQL call-handler isn't loaded until it's required.  
Since both the plugin and the call-handler are dynamically loaded, I 
think one of them has to load the other.   </pre></blockquote><pre wrap="">
Right, but if you set up the mechanism such that each individual PL is
responsible for loading plugins, then we'll have to duplicate all that
code each time we instrument another PL.  I want to do as much as
possible of the work in the core code so that we don't end up with
duplicate code to maintain. </pre></blockquote> I think I'm missing something important here.  <br /><br /> At minimum,
youneed a way to identify a plugin (or a list of plugins), and, if we generalize the mechanism, a way to identify the
languagethat that plugin is associated with (like, this profiler works with PL/tcl, this debugger works with PL/Java,
...).<br/><br /> Once you have that, you've got two choices: <br /><br /> 1) The plugin loads the language<br />    
   or<br /> 2) The language loads the plugin<br /><br /> You are suggesting option 1.  That means that we must:<br
/><br/><blockquote>a) come up with a way to identify the set of plugins desired (probably some GUC variables?)<br /> b)
Extendthe pg_language structure<br /> c) Extend the CREATE LANGUAGE statement<br /> d) come up with a way for the
backendto load the plugins (the backend already knows how to load a language-handler)<br /> e) add loader code to each
plugin(there should be more plugins than languages eventually)<br /> f) add loader code to each language (at least each
languagethat wants to support a plugin)<br /></blockquote><br /> On the other hand, if the language loads the plugin,
wemust:<br /><br /><blockquote>a) come up with a way to identify the set of plugins desired (probably some GUC
variables?)<br/> b) add loader code to each plugin<br /> c) add loader code to each language (that wants to support a
plugin)<br/></blockquote><br /> In either case, the loader code in the language-handlers and the loader code in the
pluginscould be simple calls to common functions that are defined in the core, avoiding a lot of duplicate code. For
example,each language handler (in it's initialization code) could include a call such as:<br /><br />    
pl_load_plugins("pl/pgsql", &functionPointers );<br /><br /> or<br /><br />     pl_load_plugins( "pl/java",
&functionPointers);<br /><br /> pl_load_plugins() would reside in the core, it would find the list of plugins, load
eachone, find the plugin's initialization function, and call that function with &functionPointers (the initializer
wouldfill in the functionPointers structure).<br /><br /> So what am I missing?  What's the advantage to having the
pluginload the language?<br /><br /><blockquote cite="mid700.1153506529@sss.pgh.pa.us" type="cite"><pre wrap="">
 
To do it without a pg_language column, we'd need code in each plugin to
identify the language shared library (by looking in pg_language), force
loading of same (using existing fmgr code), and look up and call a
plugin receptor function given an expected C-code name for it (again,
most of this already exists in fmgr).  It's not a huge amount of code,
probably, but again duplicating it in each plugin seems unappealing.
I suppose we could make fmgr export a general function to find a plugin
receptor function given the PL name and the expected C symbol. </pre></blockquote> That's what I was thinking too.  But
wecould avoid hard-coded names using a syntax similar to preload_libraries (each entry in preload_libraries can contain
thename of an optional initialization function).  If you specify libraryName:functionName, we would assume that
functionNamewas the loader function, if you just specify libraryName, we could look for a hard-coded default.<br /><br
/><br/> (Oh, and any more comments on security?  Is it enough to require that all plugins live in $libdir?)<br /><br />
         -- Korry<br /> 

Re: Loading the PL/pgSQL debugger (and other plugins)

From
"Hiroshi Saito"
Date:
Hi korry-san.

From: "korry" 


> 
> > I'm unqualified to comment on the server side design, but I was 
> > wondering if there was consensus on how the client interface to the 
> > debugger would work. From previous threads I saw DBGP mentioned 
> > (http://xdebug.org/docs-dbgp.php), but I don't recall seeing any final 
> > commitment to it.
> The patch that I'll be submitting for 8.2 will implement a way to 
> instrument PL/pgSQL (and that idea can be extended to other PL 
> languages).  'Instrumentation' can mean different things - it may be a 
> debugger, a profiler, a coverage analyzer, a tracer, ... 

I can regard it as very great. probably, It is expected that workstation 
(edb-debugger) is realizable with an addition of some language parser.:-)

> 
> EnterpriseDB has developed a few plugins that we'll be contributing soon 
> (a debugger, a profiler, and a tracer).  The debugger is by far the 
> largest plugin that we've developed and we implemented it before we had 
> the idea to use a modular architecture (we're still in the process of 
> converting the debugger to modular form, at the moment it's pretty 
> heavily integrated into the PL/pgSQL interpreter).  As soon as we get a 
> patch in for the plugin architecture, we'll open-source at least one or 
> two of the plugins so others can use them and/or write more (the 
> debugger will take a little longer). 
> 
> That means that we (i.e. the community) haven't made a firm commitment 
> to the debugger client protocol.  I can tell you a little about the 
> protocol that we are currently using, but it may change by the time 
> we're ready to open-source the debugger.  I gave a presentation at the 
> anniversary summit that described the overall architecture and also 
> showed the client/server protocol - the slides and audio should be 
> available at the conference web site "real soon now". 

Great.! 
Your session was very wonderful. People who were not able to hear it will be seen.

> 
> The most important part, from your perspective (assuming that you might 
> want to add a debugger to pgEdit), is the method that a debugger client 
> application uses to interact with the debugger server.  That's done 
> through a collection of server-side functions that you can call from any 
> libpq application.  For example, to set a breakpoint, you would:
> 
>     SELECT * FROM pldbg_set_breakpoint( sessionHandle, functionOID, 
> lineNumber, processID );
> 
> to step/over:
> 
>     SELECT * FROM pldbg_step_over( sessionHandle );
> 
> to step/into:
> 
>     SELECT * FROM pldbg_step_into( sessionHandle );
> 
> to get a copy of all local variables:
> 
>     SELECT * FROM pldbg_get_variables( sessionHandle, stackFrame );
> 
> and so on.  There are a few functions that you can call to attach your 
> debugger client to a target server and to set global breakpoints.
> 
> I'll be posting more information as we get closer to releasing this stuff. 

This regards me as a very great contribution.! 
As for me, the feeling of workstation (edb-debugger) was pleased very much.
I consider it so that often to pgAdmin. Then, I am looking forward to the evolution.:-)

Thanks!!

Regards,
Hiroshi Saito





Re: Loading the PL/pgSQL debugger (and other plugins)

From
"Jim C. Nasby"
Date:
On Wed, Jul 19, 2006 at 01:35:16PM -0400, Tom Lane wrote:
> > 1) I think the most straightforward way to load an instrumentation 
> > plugin is to define a new custom GUC variable (using the 
> > custom_variable_classes mechanism).
> 
> This seems a bit messy and special-purpose.  I see no good reason to tie
> it to plpgsql; we'll just need another one for every other language.
> IMHO what we want is something with similar properties to preload_libraries,
> but processed on a per-backend basis instead of once at postmaster start.
> (You could almost just tell people to select the plugin they want by
> LOADing it, but that is hard to use if you're trying to debug a
> non-interactive application.  A GUC variable can be set for an app
> without much cooperation from the app.)
<snip> 
> We should also think about a deregistration function.  This would allow
> you to turn debugging on and off within an interactive session.  The
> GUC variable is really only for coercing non-interactive applications
> into being debuggable --- I don't see it as being important for
> interactive debugging, as compared to just "select plugin_init();" ...

This isn't the only example of where it would be handy to be able to
tell a certain backend or group of backends to do something, so you
could gain more insight into what some application is doing. Turning on
query logging is another example that comes to mind.

Is there some way we could allow one backend to tell another backend to
change certain aspects of its behavior? One idea is to have a function
that can send commands to another backend via some form of IPC. That
backend would then execute the commands the next time it would normally
accept commands from it's client connection. Of course this creates a
pretty big foot-gun, so we might want to greatly restrict what kind of
commands could be executed this way.

Another possibility would be allowing users to specify certain GUC
settings for backends that match certain criteria when they're spawned,
such as what IP the client is connecting from, or what user it's
authenticating as.
-- 
Jim C. Nasby, Sr. Engineering Consultant      jnasby@pervasive.com
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461


Re: Loading the PL/pgSQL debugger (and other plugins)

From
Tom Lane
Date:
"Jim C. Nasby" <jnasby@pervasive.com> writes:
> Another possibility would be allowing users to specify certain GUC
> settings for backends that match certain criteria when they're spawned,
> such as what IP the client is connecting from, or what user it's
> authenticating as.

ALTER USER SET ...
        regards, tom lane