File: README.asciidoc

package info (click to toggle)
preprepare 0.9-5
  • links: PTS, VCS
  • area: main
  • in suites: bookworm, bullseye
  • size: 168 kB
  • sloc: ansic: 216; makefile: 40; sql: 37; sh: 10
file content (120 lines) | stat: -rw-r--r-- 4,120 bytes parent folder | download | duplicates (3)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
= pre_prepare

== Ideas

The +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
just call +EXECUTE+.

== Setup

=== 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.

=== postgresql.conf

Add a custom class then the following settings:

  preprepare.relation = 'preprepare.statements'

The +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 running PostgreSQL 9.1 or earlier, an additional setting is required:

  custom_variable_classes = 'preprepare' # only for 9.1 and earlier

=== pgbouncer

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 +DISCARD ALL+
as your +reset_query+...

=== local_preload_libraries

==== If you have +8.3+

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 +pre_prepare+
module, you still have to explicitely call +SELECT prepare_all()+.

==== If running 8.4 or later

To call +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.

Update your +postgresql.conf+ to have the following:

  custom_variable_classes = 'preprepare' # only for 9.1 and earlier
  preprepare.at_init  = on
  preprepare.relation = 'preprepare.statements'

Then +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
forget about +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 +off+
again then +reload+, or remove +pre_prepare+ from +local_preload_libraries+
then +restart+.

== Usage

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+
part.

  insert into pre_prepare.statements values ('test', 'prepare test as select 1');

=== prepare_all()

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 +pgbouncer+ nor +preprepare.at_init = on+, you'll have to call
the function yourself to have your statements prepared:

  SELECT prepare_all();

=== prepare_all('schema.table')

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
statements table:

  SELECT prepare_all('public.expensive_planning');

This can be automated in a special +pgbouncer+ fake +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.

=== discard()

The module also offers a +discard()+ function which does the same as
+DISCARD ALL+ except that it won't call +DEALLOCATE ALL+.