-
Notifications
You must be signed in to change notification settings - Fork 1
Expand file tree
/
Copy pathadmin.html
More file actions
438 lines (423 loc) · 21.1 KB
/
Copy pathadmin.html
File metadata and controls
438 lines (423 loc) · 21.1 KB
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
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Requirements — pg-python v1.0.1 documentation</title>
<link rel="stylesheet" href="_static/default.css" type="text/css" />
<link rel="stylesheet" href="_static/pygments.css" type="text/css" />
<script type="text/javascript">
var DOCUMENTATION_OPTIONS = {
URL_ROOT: '',
VERSION: '1.0.1',
COLLAPSE_INDEX: false,
FILE_SUFFIX: '.html',
HAS_SOURCE: true
};
</script>
<script type="text/javascript" src="_static/jquery.js"></script>
<script type="text/javascript" src="_static/underscore.js"></script>
<script type="text/javascript" src="_static/doctools.js"></script>
<link rel="top" title="pg-python v1.0.1 documentation" href="index.html" />
<link rel="next" title="Programming" href="programming.html" />
<link rel="prev" title="pg-python" href="index.html" />
</head>
<body>
<div class="related">
<h3>Navigation</h3>
<ul>
<li class="right" style="margin-right: 10px">
<a href="genindex.html" title="General Index"
accesskey="I">index</a></li>
<li class="right" >
<a href="programming.html" title="Programming"
accesskey="N">next</a> |</li>
<li class="right" >
<a href="index.html" title="pg-python"
accesskey="P">previous</a> |</li>
<li><a href="index.html">pg-python v1.0.1 documentation</a> »</li>
</ul>
</div>
<div class="document">
<div class="documentwrapper">
<div class="bodywrapper">
<div class="body">
<div class="section" id="requirements">
<h1>Requirements<a class="headerlink" href="#requirements" title="Permalink to this headline">¶</a></h1>
<p>This version of pg-python is known to work with the following versions of
PostgreSQL:</p>
<blockquote>
<div><ul class="simple">
<li>PostgreSQL 8.3</li>
<li>PostgreSQL 8.4</li>
<li>PostgreSQL 9.0</li>
<li>PostgreSQL 9.1</li>
</ul>
</div></blockquote>
<p>And the following versions of Python:</p>
<blockquote>
<div><ul class="simple">
<li>Python 3.1</li>
<li>Python 3.2</li>
</ul>
</div></blockquote>
<p>Python’s C-APIs are fairly stable, so newer versions of Python are expected to
normally work. However, PostgreSQL’s C-APIs and header files can be adjusted
from minor release to minor release. If the target version of PostgreSQL is not
listed above, it should not be expected to work.</p>
</div>
<div class="section" id="administration">
<h1>Administration<a class="headerlink" href="#administration" title="Permalink to this headline">¶</a></h1>
<p>The administration of procedural language extensions can be tricky in some
situations. If you have any questions, be sure to contact the mailing list:</p>
<blockquote>
<div><a class="reference external" href="mailto:python-general%40pgfoundry.org">mailto:python-general<span>@</span>pgfoundry<span>.</span>org</a></div></blockquote>
<div class="admonition note">
<p class="first admonition-title">Note</p>
<p class="last">This chapter occasionally refers <tt class="docutils literal"><span class="pre">$PREFIX</span></tt>; this is referring to the
location in which PostgreSQL is installed. When referenced, it is assumed
that the installation did not modify the directory layout. In the event that
the distribution or packager did modify the layout, it is important to
reference the appropriate documentation in order to properly identify the
corresponding locations.</p>
</div>
<div class="section" id="compilation">
<h2>Compilation<a class="headerlink" href="#compilation" title="Permalink to this headline">¶</a></h2>
<p>Currently, the only supported compilation method uses PostgreSQL’s PGXS
makefile system. For compilation, the Python and PostgreSQL header files are
necessary, so for some platforms, <em>it is important to install the Python</em>
<em>and PostgreSQL developer packages</em>.</p>
<div class="section" id="pgxs-build">
<h3>PGXS Build<a class="headerlink" href="#pgxs-build" title="Permalink to this headline">¶</a></h3>
<p>The first step for PGXS compilation is running the <tt class="docutils literal"><span class="pre">configure</span></tt> script.
By default, the <tt class="docutils literal"><span class="pre">configure</span></tt> script identifies the PostgreSQL installation and
the Python installation to use by executing the <tt class="docutils literal"><span class="pre">python3</span></tt> command and
<tt class="docutils literal"><span class="pre">pg_config</span></tt> command. Whichever <tt class="docutils literal"><span class="pre">python3</span></tt> and <tt class="docutils literal"><span class="pre">pg_config</span></tt> executable
comes first in the system’s path will be the target installation chosen by
<tt class="docutils literal"><span class="pre">configure</span></tt>:</p>
<div class="highlight-python"><pre>./configure</pre>
</div>
<p>Once configured, the makefile includes have been built, and the
<tt class="docutils literal"><span class="pre">GNUmakefile</span></tt>‘s functionality will be ready for use:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="c"># Or "gmake" on some systems.</span>
<span class="n">make</span>
</pre></div>
</div>
<p>And finally, to install the built module:</p>
<div class="highlight-python"><pre># Prefix with "sudo", if need be.
make install</pre>
</div>
<p>There is only one file that needs to be installed, normally named <tt class="docutils literal"><span class="pre">python.so</span></tt>.
<tt class="docutils literal"><span class="pre">make</span> <span class="pre">install</span></tt> will place it in the <tt class="docutils literal"><span class="pre">$PREFIX/lib/postgresql</span></tt> directory.</p>
</div>
</div>
<div class="section" id="database-installation">
<h2>Database Installation<a class="headerlink" href="#database-installation" title="Permalink to this headline">¶</a></h2>
<p>In 9.1, database extensions were introduced. Since version 1.0.1 of pg-python,
an installation script and control file is provided for use with <tt class="docutils literal"><span class="pre">CREATE</span> <span class="pre">EXTENSION</span></tt>.</p>
<div class="admonition note">
<p class="first admonition-title">Note</p>
<p class="last">Each of the following sections on installation includes a step to create the
<tt class="docutils literal"><span class="pre">__python__</span></tt> schema. The <tt class="docutils literal"><span class="pre">__python__</span></tt> schema is not required, but recommended
to help manage potential future features, and compensate for ambiguous procedure
names employed by pg-python.</p>
</div>
<div class="section" id="extension-installation">
<h3>Extension Installation<a class="headerlink" href="#extension-installation" title="Permalink to this headline">¶</a></h3>
<p>In 9.1 and later, the following should be ran:</p>
<div class="highlight-python"><pre>CREATE SCHEMA __python__;
CREATE EXTENSION python SCHEMA __python__;</pre>
</div>
</div>
<div class="section" id="script-based-installation">
<h3>Script Based Installation<a class="headerlink" href="#script-based-installation" title="Permalink to this headline">¶</a></h3>
<p>For 9.0 and later the <tt class="docutils literal"><span class="pre">src/install_inline.sql</span></tt> file should be executed using
<tt class="docutils literal"><span class="pre">psql</span></tt> in order to include the inline handler:</p>
<div class="highlight-python"><pre>CREATE SCHEMA __python__;
SET search_path TO __python__;
\i src/install_inline.sql</pre>
</div>
<p>For 8.4 and earlier, the <tt class="docutils literal"><span class="pre">src/install_no_inline.sql</span></tt> file should be executed:</p>
<div class="highlight-python"><pre>CREATE SCHEMA __python__;
SET search_path TO __python__;
\i src/install_no_inline.sql</pre>
</div>
<p>Or, relative to the installation after <tt class="docutils literal"><span class="pre">make</span> <span class="pre">install</span></tt>:</p>
<div class="highlight-python"><pre>CREATE SCHEMA __python__;
SET search_path TO __python__;
\i $PREFIX/share/postgresql/extension/python--$VERSION.sql</pre>
</div>
</div>
<div class="section" id="manual-installation">
<h3>Manual Installation<a class="headerlink" href="#manual-installation" title="Permalink to this headline">¶</a></h3>
<p>Installation into a database is the last necessary step before a Python
<tt class="docutils literal"><span class="pre">FUNCTION</span></tt> can be created. This is normally done by executing the following
commands:</p>
<div class="highlight-python"><pre>BEGIN;
CREATE SCHEMA __python__;
SET search_path TO __python__;
CREATE FUNCTION handler() RETURNS LANGUAGE_HANDLER
LANGUAGE C AS 'python', 'pl_handler';
CREATE FUNCTION validator(oid) RETURNS VOID
LANGUAGE C AS 'python', 'pl_validator';
COMMIT;
-- For 9.0 and greater.
BEGIN;
SET search_path TO __python__;
CREATE FUNCTION inline(INTERNAL) RETURNS VOID
LANGUAGE C AS 'python', 'pl_inline';
CREATE LANGUAGE python
HANDLER handler INLINE inline VALIDATOR validator;
COMMIT;
-- For versions before 9.0.
BEGIN;
SET search_path TO __python__;
CREATE LANGUAGE python
HANDLER handler VALIDATOR validator;
COMMIT;</pre>
</div>
<p>The distinct schema is used hold the functions and for future expansion.</p>
<div class="admonition tip">
<p class="first admonition-title">Tip</p>
<p class="last">If a language is installed into <tt class="docutils literal"><span class="pre">template1</span></tt>, all subsequently
created databases will have the language installed automatically.</p>
</div>
<p>The above commands will only work if the extension module is installed into
PostgreSQL’s library directory. Normally, <tt class="docutils literal"><span class="pre">$PREFIX/lib/postgresql</span></tt>. In cases
where the extension does not exist in the default location, a full path to the
library will need to be given to the <tt class="docutils literal"><span class="pre">CREATE</span> <span class="pre">FUNCTION</span></tt> commands:</p>
<div class="highlight-python"><pre>CREATE FUNCTION handler() RETURNS LANGUAGE_HANDLER
LANGUAGE C AS '/full/path/to/python', 'pl_handler';
CREATE FUNCTION validator(oid) RETURNS VOID
LANGUAGE C AS '/full/path/to/python', 'pl_validator';
CREATE FUNCTION inline(INTERNAL) RETURNS VOID
LANGUAGE C AS '/full/path/to/python', 'pl_inline';</pre>
</div>
</div>
</div>
<div class="section" id="configuration">
<h2>Configuration<a class="headerlink" href="#configuration" title="Permalink to this headline">¶</a></h2>
<p>pg-python does not require any additional configuration once installed.
However, much initialization overhead can be eliminated by preloading
the extension using the <tt class="docutils literal"><span class="pre">shared_preload_libraries</span></tt> configuration parameter.</p>
<p>Specifically:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="n">shared_preload_libraries</span> <span class="o">=</span> <span class="s">'python'</span>
</pre></div>
</div>
<p>Using this will cause Python to be initialized when Postgres is started.</p>
<div class="section" id="local-initialization">
<h3>Local Initialization<a class="headerlink" href="#local-initialization" title="Permalink to this headline">¶</a></h3>
<p>When Python is loaded, the <tt class="docutils literal"><span class="pre">init.py</span></tt> file in the data directory will be executed if it
exists. The contents of this file will be executed in a new module named
<tt class="docutils literal"><span class="pre">__pg_init__</span></tt>. The module is stored in <tt class="docutils literal"><span class="pre">sys.modules</span></tt>, but primarily for
identifying that an <tt class="docutils literal"><span class="pre">init.py</span></tt> file was loaded.</p>
<p>The primary purpose of the initialization file is to allow modules to be
preloaded when the server is started. Writing functions that
depend on objects created by <tt class="docutils literal"><span class="pre">init.py</span></tt> should be avoided.</p>
<p>For <tt class="docutils literal"><span class="pre">init.py</span></tt> to provide any savings, it should be used
in conjunction with the <tt class="docutils literal"><span class="pre">shared_preload_libraries</span></tt> configuration parameter.</p>
<div class="admonition note">
<p class="first admonition-title">Note</p>
<p class="last">When <tt class="docutils literal"><span class="pre">init.py</span></tt> is loaded, database access is prohibited.</p>
</div>
</div>
</div>
<div class="section" id="managing-multiple-versions">
<h2>Managing Multiple Versions<a class="headerlink" href="#managing-multiple-versions" title="Permalink to this headline">¶</a></h2>
<div class="admonition warning">
<p class="first admonition-title">Warning</p>
<dl class="last docutils">
<dt>This section covers advanced material, and primarily applies to version 9.0</dt>
<dd>and earlier of PostgreSQL.</dd>
</dl>
</div>
<p>In some situations, it may be necessary to have multiple builds of pg-python
installed in the same PostgreSQL installation for upgrade purposes. When such
cases arise, using a distinct name for the language is necessary.</p>
<p>There are two routes that can be taken. The first is to rename the existing
language making room for the new one, and the second is to use a different name
for the new version. The best path depends on the situation.</p>
<div class="section" id="renaming-the-existing-language">
<h3>Renaming the Existing Language<a class="headerlink" href="#renaming-the-existing-language" title="Permalink to this headline">¶</a></h3>
<p>Renaming the existing language is the proper choice when there is an
incompatibility between the existing language and the new <em>target</em> language.
This situation demands that functions be incrementally ported to the new
version as time permits.</p>
<div class="admonition note">
<p class="first admonition-title">Note</p>
<p class="last">Renaming the existing language may require restart of the cluster.</p>
</div>
<p>The first step in renaming the existing language is to copy the
original <tt class="docutils literal"><span class="pre">python.so</span></tt> so that it’s name is distinct from the
one that is going to be installed. This can be done while the cluster is
running:</p>
<div class="highlight-python"><pre>cd $PREFIX/lib/postgresql/
cp python.so python31.so</pre>
</div>
<p>The second step is to create the new language object. It is recommended to name
it after the specific version of Python being used:</p>
<div class="highlight-python"><pre>BEGIN;
CREATE SCHEMA __python31__;
SET search_path = __python31__;
CREATE FUNCTION handler() RETURNS LANGUAGE_HANDLER
LANGUAGE C AS 'python31', 'pl_handler';
CREATE FUNCTION validator(oid) RETURNS VOID
LANGUAGE C AS 'python31', 'pl_validator';
COMMIT;
-- For 9.0 and greater.
BEGIN;
SET search_path = __python31__;
CREATE FUNCTION inline(INTERNAL) RETURNS VOID
LANGUAGE C AS 'python31', 'pl_inline';
CREATE LANGUAGE python31
HANDLER handler INLINE inline VALIDATOR validator;
COMMIT;
-- For versions before 9.0.
BEGIN;
SET search_path = __python31__;
CREATE LANGUAGE python31
HANDLER handler VALIDATOR validator;
COMMIT;</pre>
</div>
<p>Next, the existing functions need to have their language updated:</p>
<div class="highlight-python"><pre>UPDATE pg_catalog.pg_proc
SET prolang = (SELECT oid FROM pg_language WHERE lanname = 'python31')
WHERE prolang = (SELECT oid FROM pg_language WHERE lanname = 'python');</pre>
</div>
<div class="admonition warning">
<p class="first admonition-title">Warning</p>
<p class="last">It is best not to have other backends running while the above command is
running.</p>
</div>
<p>And finally, the old language should be removed in order to make room for the
new default:</p>
<div class="highlight-python"><pre>SET search_path = __python__;
DROP FUNCTION handler();
DROP FUNCTION validator(oid);
DROP FUNCTION inline(internal);
RESET search_path;
DROP SCHEMA __python__;</pre>
</div>
<p>With those steps complete, the language has been renamed, and the new language
may be installed using the steps documented in <a class="reference internal" href="#compilation">Compilation</a> and
<a class="reference internal" href="#database-installation">Database Installation</a>.</p>
</div>
<div class="section" id="using-a-custom-name-for-the-new-language">
<h3>Using a Custom Name for the New Language<a class="headerlink" href="#using-a-custom-name-for-the-new-language" title="Permalink to this headline">¶</a></h3>
<p>Using a custom name for the new language is the proper choice when necessary
functionality is exclusively available in a past version of Python. Such cases
are likely transient, but when they occur, they are best handled as an
exception.</p>
<p>Using a custom name for the language is significantly easier renaming an
existing language. All that is required is that the built <tt class="docutils literal"><span class="pre">python.so</span></tt>
file be manually into the <tt class="docutils literal"><span class="pre">$PREFIX/lib/postgresql</span></tt> directory under a new
name:</p>
<div class="highlight-python"><pre>cp python.so $PREFIX/lib/postgresql/python31.so</pre>
</div>
<p>Once complete, database installation can then be performed with a slight
modification:</p>
<div class="highlight-python"><pre>BEGIN;
CREATE SCHEMA __python31__;
SET search_path = __python31__;
CREATE FUNCTION handler() RETURNS LANGUAGE_HANDLER
LANGUAGE C AS 'python31', 'pl_handler';
CREATE FUNCTION validator(oid) RETURNS VOID
LANGUAGE C AS 'python31', 'pl_validator';
COMMIT;
-- For 9.0 and greater
BEGIN;
CREATE FUNCTION inline(INTERNAL) RETURNS VOID
LANGUAGE C AS 'python31', 'pl_inline';
CREATE LANGUAGE python31
HANDLER handler INLINE inline VALIDATOR validator;
COMMIT;
-- For versions before 9.0
BEGIN;
CREATE LANGUAGE python31
HANDLER handler VALIDATOR validator;
COMMIT;</pre>
</div>
<p>Complete. The <tt class="docutils literal"><span class="pre">python31</span></tt> language will be ready for use alongside any others.</p>
</div>
</div>
</div>
</div>
</div>
</div>
<div class="sphinxsidebar">
<div class="sphinxsidebarwrapper">
<h3><a href="index.html">Table Of Contents</a></h3>
<ul>
<li><a class="reference internal" href="#">Requirements</a></li>
<li><a class="reference internal" href="#administration">Administration</a><ul>
<li><a class="reference internal" href="#compilation">Compilation</a><ul>
<li><a class="reference internal" href="#pgxs-build">PGXS Build</a></li>
</ul>
</li>
<li><a class="reference internal" href="#database-installation">Database Installation</a><ul>
<li><a class="reference internal" href="#extension-installation">Extension Installation</a></li>
<li><a class="reference internal" href="#script-based-installation">Script Based Installation</a></li>
<li><a class="reference internal" href="#manual-installation">Manual Installation</a></li>
</ul>
</li>
<li><a class="reference internal" href="#configuration">Configuration</a><ul>
<li><a class="reference internal" href="#local-initialization">Local Initialization</a></li>
</ul>
</li>
<li><a class="reference internal" href="#managing-multiple-versions">Managing Multiple Versions</a><ul>
<li><a class="reference internal" href="#renaming-the-existing-language">Renaming the Existing Language</a></li>
<li><a class="reference internal" href="#using-a-custom-name-for-the-new-language">Using a Custom Name for the New Language</a></li>
</ul>
</li>
</ul>
</li>
</ul>
<h4>Previous topic</h4>
<p class="topless"><a href="index.html"
title="previous chapter">pg-python</a></p>
<h4>Next topic</h4>
<p class="topless"><a href="programming.html"
title="next chapter">Programming</a></p>
<h3>This Page</h3>
<ul class="this-page-menu">
<li><a href="_sources/admin.txt"
rel="nofollow">Show Source</a></li>
</ul>
<div id="searchbox" style="display: none">
<h3>Quick search</h3>
<form class="search" action="search.html" method="get">
<input type="text" name="q" size="18" />
<input type="submit" value="Go" />
<input type="hidden" name="check_keywords" value="yes" />
<input type="hidden" name="area" value="default" />
</form>
<p class="searchtip" style="font-size: 90%">
Enter search terms or a module, class or function name.
</p>
</div>
<script type="text/javascript">$('#searchbox').show(0);</script>
</div>
</div>
<div class="clearer"></div>
</div>
<div class="related">
<h3>Navigation</h3>
<ul>
<li class="right" style="margin-right: 10px">
<a href="genindex.html" title="General Index"
>index</a></li>
<li class="right" >
<a href="programming.html" title="Programming"
>next</a> |</li>
<li class="right" >
<a href="index.html" title="pg-python"
>previous</a> |</li>
<li><a href="index.html">pg-python v1.0.1 documentation</a> »</li>
</ul>
</div>
<div class="footer">
© Copyright 2010, James William Pye <x@jwp.name>.
Created using <a href="http://sphinx.pocoo.org/">Sphinx</a> 1.0.7.
</div>
</body>
</html>