pre_prepare module aims to prepare all your statements as soon as
possible and in a way that allows client queries not to bother at all and
compile and install
The module is using the PG_XS build infrastructure, so just:
make make install psql -f `pg_config --sharedir`/contrib/pre_prepare.sql -U user dbname
Depending on the way you got PostgreSQL installed in the first place, you
may need to use sudo for the
make install step.
Add a custom class then the following settings:
custom_variable_classes = 'preprepare' preprepare.relation = 'preprepare.statements'
pre_prepare.relation is the name of the table where you’ll put all the
statements you want to module to prepare. The following SQL query against
the given relation must return the names and SQL statements to be prepared.
SELECT name, statement FROM <pre_prepare.relation>;
The statements won’t be edited, so must be the all PREPARE stuff.
When using pgbouncer, which is a good idea, consider setting up
connect_query to prepare all the statements at server connection time.
[databases] foo = port=5432 connect_query='SELECT prepare_all();'
Of course, if using
pre_prepare, you’ll want to avoid using
If you have
It’s unfortunately not possible to call
SPI_connect() from the module
initialization routine (
_PG_init()) when called via
local_preload_libraries, it’s too much early. So fully transparent
preparing of user given statement is not possible with the
module, you still have to explicitely call
If running 8.4 or later
SPI_connect() in fact what’s needed is an opened transaction and a
current active Snapshot. Both are possible to acquire from within a
dynamically loaded module, so
preprepare is supporting the case.
postgresql.conf to have the following:
custom_variable_classes = 'preprepare' preprepare.at_init = on preprepare.relation = 'preprepare.statements'
reload PostgreSQL and enjoy: any new connection will have already
prepared your statements by the time you’re able to send queries, so you can
PREPARE and directly
EXECUTE. No need for extra software.
Please note that if your statements contain any error, PostgreSQL will
handle it as a
FATAL error and this will effectively prevent you from
connecting to your server. You’ll have to turn preprepare.at_init
reload, or remove
First create a table where to store your statements, e.g.:
create table pre_prepare.statements(name text primary key, statement text);
The statement stored is the complete statement including the
PREPARE name AS
insert into pre_prepare.statements values ('test', 'prepare test as select 1');
Then connect to PostgreSQL (via
pgbouncer if not using
local_preload_libraries) and run the
EXECUTE command matching with your
PREPARE statements, they all are already prepared.
If not using
preprepare.at_init = on, you’ll have to call
the function yourself to have your statements prepared:
If you’re not in a position to always prepare the same set of queries, you
can use the second form of
prepare_all calling, which accepts a specific
This can be automated in a special
database where the
connect_query will prepare specific queries when you know you need them in
some occasion, but they’re way to expensive to always prepare ahead of time.
The module also offers a
discard() function which does the same as
DISCARD ALL except that it won’t call