- Proposal for repreparing prepared statements

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 considered a
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-grained control 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.
Finer grained control over which kind of reloading is performed.
Cons: Require more use of the scarce list of available signals.