- Proposal for repreparing prepared statements - Mailing list pgsql-hackers

The following is a proposal for work I'd like to do to force 
long-running backend processes to reprepare their prepared statements.  
It would be used in cases where the user knows they have made a database 
change that will invalidate an existing prepared statement. 

I look forward to comments from the community.
------------
I propose creating a new system administration function to force 
repreparation of prepared statements in all backends.  The functionality 
could be extended to include re-initialization of other kinds of 
"per-backend" data.

This proposal addresses, to some degree, the prepare-alter-exec issue 
discussed in various mailing list postings, and the following wish-list 
item:

# Invalidate prepared queries, like INSERT, when the table definition is 
altered

However, the solution would only be partial, as it would be the 
responsibility of database clients to call the system administration 
function when needed.  Alternately, additional integration work could be 
done to invoke this logic automatically whenever the columns of any 
table are altered.

------
Here is what I propose:

We define a new system administration function called 
pg_reload_per_backend_data.  This function would work much like 
pg_reload_conf, i.e. it would require superuser privileges and would 
work by sending a signal to the postmaster that would then be propagated 
to all the child backends (but not the special ones, like the 
bgwriter).  The signal handling logic for the backends would be modified 
to respond to the signal by reinitializing any data cached in the 
backend's memory space, such as prepared statements.  Each kind of data 
that would be reinitialized would require special logic, as they would 
all be reinitialized in their own particular way.

Choosing an appropriate signal to send might be difficult, as the list 
of available signals is somewhat restricted.  The "user-defined" signals 
would be a natural choice, but it appears SIGUSR1 is used for "sinval" 
or catchup events, while SIGUSR2 is used for asynchronous notification.  
Use of the "real time" signals (signal numbers >= 32) might be possible, 
but could have portability problems.  Another alternative would be to 
overload SIGHUP, so that it causes both configuration reloads and 
reloading of per-backend data.  This makes some sense, since most 
configuration parameters are basically a special form of per-backend 
data.  However, changing the behavior of an existing signal might have 
undesirable side effects.  Overall, I'm very open to suggestions 
regarding the appropriate signal to use.

To implement the repreparation logic, a new function called 
RepreparePreparedStatements() could be added to source files 
backend/commands/prepare.[ch].  This function would be called by a 
signal handler installed the backends within backend/tcop/postgres.c.  
RepreparePreparedStatements would do the equivalent of iterating over 
the prepared_queries hash table and executing DropPreparedStatement() 
and PrepareQuery on each.  However, it is possible that some refactoring 
of the logic would be needed to improve performance and make the code 
more robust.

The scope of pg_reload_per_backend_data could also be expanded to 
include reinitialization of other data that resides in the memory space 
of individual backend processes.  An example of such cached entities are 
reusable modules associated with a particular procedural language, e.g. 
the TCL modules found in the table pltcl_modules.  Once a such a module 
is used in a particular backend, it remains held in backend memory and 
changes to the disk version are not noticed.  There is also no way to 
undefine any global variables associated with such modules.

I have not given much consideration to the implementation for reloading 
modules, but doing the equivalent of the SQL command "LOAD '<libname>' 
for all dynamically loaded libraries should have the desired effect (at 
least it does for the library that implements the PL/TCL language, 
pltcl.so).  Perhaps the the general response should be to reload any 
libraries that have been dynamically-loaded by the particular backend.

------
Here are few permutations of this plan that could be considered:

1. Bundle pg_reload_per_backend_data functionality with pg_reload_conf.

Pros: Avoids having to find an appropriate unused signal     Logical consistancy with reloading config, which could be
considereda     special case of reloading per-backend data.
 
Cons: Changes behavior of an existing functionality, which has the risk of     unintended side-effects.     Gives less
fine-grainedcontrol over when per-backend data is 
 
reloaded.

2. Break pg_reload_per_backend_data functional into multiple functions.

Pros: Can assign more descriptive names to the functionality, e.g.     pg_reload_ddl, pg_reprepare_statements, etc.
Finergrained control over which kind of reloading is performed.
 
Cons: Require more use of the scarce list of available signals.