Debian Codegraph @

doc/src/sgml/extend.sgml

raw
1 <!-- doc/src/sgml/extend.sgml -->
2
3 <chapter id="extend">
4 <title>Extending <acronym>SQL</acronym></title>
5
6 <indexterm zone="extend">
7 <primary>extending SQL</primary>
8 </indexterm>
9
10 <para>
11 In the sections that follow, we will discuss how you
12 can extend the <productname>PostgreSQL</productname>
13 <acronym>SQL</acronym> query language by adding:
14
15 <itemizedlist spacing="compact" mark="bullet">
16 <listitem>
17 <para>
18 functions (starting in <xref linkend="xfunc"/>)
19 </para>
20 </listitem>
21 <listitem>
22 <para>
23 aggregates (starting in <xref linkend="xaggr"/>)
24 </para>
25 </listitem>
26 <listitem>
27 <para>
28 data types (starting in <xref linkend="xtypes"/>)
29 </para>
30 </listitem>
31 <listitem>
32 <para>
33 operators (starting in <xref linkend="xoper"/>)
34 </para>
35 </listitem>
36 <listitem>
37 <para>
38 operator classes for indexes (starting in <xref linkend="xindex"/>)
39 </para>
40 </listitem>
41 <listitem>
42 <para>
43 packages of related objects (starting in <xref linkend="extend-extensions"/>)
44 </para>
45 </listitem>
46 </itemizedlist>
47 </para>
48
49 <sect1 id="extend-how">
50 <title>How Extensibility Works</title>
51
52 <para>
53 <productname>PostgreSQL</productname> is extensible because its operation is
54 catalog-driven. If you are familiar with standard
55 relational database systems, you know that they store information
56 about databases, tables, columns, etc., in what are
57 commonly known as system catalogs. (Some systems call
58 this the data dictionary.) The catalogs appear to the
59 user as tables like any other, but the <acronym>DBMS</acronym> stores
60 its internal bookkeeping in them. One key difference
61 between <productname>PostgreSQL</productname> and standard relational database systems is
62 that <productname>PostgreSQL</productname> stores much more information in its
63 catalogs: not only information about tables and columns,
64 but also information about data types, functions, access
65 methods, and so on. These tables can be modified by
66 the user, and since <productname>PostgreSQL</productname> bases its operation
67 on these tables, this means that <productname>PostgreSQL</productname> can be
68 extended by users. By comparison, conventional
69 database systems can only be extended by changing hardcoded
70 procedures in the source code or by loading modules
71 specially written by the <acronym>DBMS</acronym> vendor.
72 </para>
73
74 <para>
75 The <productname>PostgreSQL</productname> server can moreover
76 incorporate user-written code into itself through dynamic loading.
77 That is, the user can specify an object code file (e.g., a shared
78 library) that implements a new type or function, and
79 <productname>PostgreSQL</productname> will load it as required.
80 Code written in <acronym>SQL</acronym> is even more trivial to add
81 to the server. This ability to modify its operation <quote>on the
82 fly</quote> makes <productname>PostgreSQL</productname> uniquely
83 suited for rapid prototyping of new applications and storage
84 structures.
85 </para>
86 </sect1>
87
88 <sect1 id="extend-type-system">
89 <title>The <productname>PostgreSQL</productname> Type System</title>
90
91 <indexterm zone="extend-type-system">
92 <primary>base type</primary>
93 </indexterm>
94
95 <indexterm zone="extend-type-system">
96 <primary>data type</primary>
97 <secondary>base</secondary>
98 </indexterm>
99
100 <indexterm zone="extend-type-system">
101 <primary>composite type</primary>
102 </indexterm>
103
104 <indexterm zone="extend-type-system">
105 <primary>data type</primary>
106 <secondary>composite</secondary>
107 </indexterm>
108
109 <indexterm zone="extend-type-system">
110 <primary>container type</primary>
111 </indexterm>
112
113 <indexterm zone="extend-type-system">
114 <primary>data type</primary>
115 <secondary>container</secondary>
116 </indexterm>
117
118 <para>
119 <productname>PostgreSQL</productname> data types can be divided into base
120 types, container types, domains, and pseudo-types.
121 </para>
122
123 <sect2 id="extend-type-system-base">
124 <title>Base Types</title>
125
126 <para>
127 Base types are those, like <type>integer</type>, that are
128 implemented below the level of the <acronym>SQL</acronym> language
129 (typically in a low-level language such as C). They generally
130 correspond to what are often known as abstract data types.
131 <productname>PostgreSQL</productname> can only operate on such
132 types through functions provided by the user and only understands
133 the behavior of such types to the extent that the user describes
134 them.
135 The built-in base types are described in <xref linkend="datatype"/>.
136 </para>
137
138 <para>
139 Enumerated (enum) types can be considered as a subcategory of base
140 types. The main difference is that they can be created using
141 just <acronym>SQL</acronym> commands, without any low-level programming.
142 Refer to <xref linkend="datatype-enum"/> for more information.
143 </para>
144 </sect2>
145
146 <sect2 id="extend-type-system-container">
147 <title>Container Types</title>
148
149 <para>
150 <productname>PostgreSQL</productname> has three kinds
151 of <quote>container</quote> types, which are types that contain multiple
152 values of other types. These are arrays, composites, and ranges.
153 </para>
154
155 <para>
156 Arrays can hold multiple values that are all of the same type. An array
157 type is automatically created for each base type, composite type, range
158 type, and domain type. But there are no arrays of arrays. So far as
159 the type system is concerned, multi-dimensional arrays are the same as
160 one-dimensional arrays. Refer to <xref linkend="arrays"/> for more
161 information.
162 </para>
163
164 <para>
165 Composite types, or row types, are created whenever the user
166 creates a table. It is also possible to use <xref
167 linkend="sql-createtype"/> to
168 define a <quote>stand-alone</quote> composite type with no associated
169 table. A composite type is simply a list of types with
170 associated field names. A value of a composite type is a row or
171 record of field values. Refer to <xref linkend="rowtypes"/>
172 for more information.
173 </para>
174
175 <para>
176 A range type can hold two values of the same type, which are the lower
177 and upper bounds of the range. Range types are user-created, although
178 a few built-in ones exist. Refer to <xref linkend="rangetypes"/>
179 for more information.
180 </para>
181 </sect2>
182
183 <sect2 id="extend-type-system-domains">
184 <title>Domains</title>
185
186 <para>
187 A domain is based on a particular underlying type and for many purposes
188 is interchangeable with its underlying type. However, a domain can have
189 constraints that restrict its valid values to a subset of what the
190 underlying type would allow. Domains are created using
191 the <acronym>SQL</acronym> command <xref linkend="sql-createdomain"/>.
192 Refer to <xref linkend="domains"/> for more information.
193 </para>
194 </sect2>
195
196 <sect2 id="extend-type-system-pseudo">
197 <title>Pseudo-Types</title>
198
199 <para>
200 There are a few <quote>pseudo-types</quote> for special purposes.
201 Pseudo-types cannot appear as columns of tables or components of
202 container types, but they can be used to declare the argument and
203 result types of functions. This provides a mechanism within the
204 type system to identify special classes of functions. <xref
205 linkend="datatype-pseudotypes-table"/> lists the existing
206 pseudo-types.
207 </para>
208 </sect2>
209
210 <sect2 id="extend-types-polymorphic">
211 <title>Polymorphic Types</title>
212
213 <indexterm zone="extend-types-polymorphic">
214 <primary>polymorphic type</primary>
215 </indexterm>
216
217 <indexterm zone="extend-types-polymorphic">
218 <primary>polymorphic function</primary>
219 </indexterm>
220
221 <indexterm zone="extend-types-polymorphic">
222 <primary>data type</primary>
223 <secondary>polymorphic</secondary>
224 </indexterm>
225
226 <indexterm zone="extend-types-polymorphic">
227 <primary>function</primary>
228 <secondary>polymorphic</secondary>
229 </indexterm>
230
231 <para>
232 Some pseudo-types of special interest are the <firstterm>polymorphic
233 types</firstterm>, which are used to declare <firstterm>polymorphic
234 functions</firstterm>. This powerful feature allows a single function
235 definition to operate on many different data types, with the specific
236 data type(s) being determined by the data types actually passed to it
237 in a particular call. The polymorphic types are shown in
238 <xref linkend="extend-types-polymorphic-table"/>. Some examples of
239 their use appear in <xref linkend="xfunc-sql-polymorphic-functions"/>.
240 </para>
241
242 <table id="extend-types-polymorphic-table">
243 <title>Polymorphic Types</title>
244 <tgroup cols="3">
245 <colspec colname="col1" colwidth="2*"/>
246 <colspec colname="col2" colwidth="1*"/>
247 <colspec colname="col3" colwidth="2*"/>
248 <thead>
249 <row>
250 <entry>Name</entry>
251 <entry>Family</entry>
252 <entry>Description</entry>
253 </row>
254 </thead>
255
256 <tbody>
257 <row>
258 <entry><type>anyelement</type></entry>
259 <entry>Simple</entry>
260 <entry>Indicates that a function accepts any data type</entry>
261 </row>
262
263 <row>
264 <entry><type>anyarray</type></entry>
265 <entry>Simple</entry>
266 <entry>Indicates that a function accepts any array data type</entry>
267 </row>
268
269 <row>
270 <entry><type>anynonarray</type></entry>
271 <entry>Simple</entry>
272 <entry>Indicates that a function accepts any non-array data type</entry>
273 </row>
274
275 <row>
276 <entry><type>anyenum</type></entry>
277 <entry>Simple</entry>
278 <entry>Indicates that a function accepts any enum data type
279 (see <xref linkend="datatype-enum"/>)
280 </entry>
281 </row>
282
283 <row>
284 <entry><type>anyrange</type></entry>
285 <entry>Simple</entry>
286 <entry>Indicates that a function accepts any range data type
287 (see <xref linkend="rangetypes"/>)
288 </entry>
289 </row>
290
291 <row>
292 <entry><type>anymultirange</type></entry>
293 <entry>Simple</entry>
294 <entry>Indicates that a function accepts any multirange data type
295 (see <xref linkend="rangetypes"/>)
296 </entry>
297 </row>
298
299 <row>
300 <entry><type>anycompatible</type></entry>
301 <entry>Common</entry>
302 <entry>Indicates that a function accepts any data type,
303 with automatic promotion of multiple arguments to a common data type
304 </entry>
305 </row>
306
307 <row>
308 <entry><type>anycompatiblearray</type></entry>
309 <entry>Common</entry>
310 <entry>Indicates that a function accepts any array data type,
311 with automatic promotion of multiple arguments to a common data type
312 </entry>
313 </row>
314
315 <row>
316 <entry><type>anycompatiblenonarray</type></entry>
317 <entry>Common</entry>
318 <entry>Indicates that a function accepts any non-array data type,
319 with automatic promotion of multiple arguments to a common data type
320 </entry>
321 </row>
322
323 <row>
324 <entry><type>anycompatiblerange</type></entry>
325 <entry>Common</entry>
326 <entry>Indicates that a function accepts any range data type,
327 with automatic promotion of multiple arguments to a common data type
328 </entry>
329 </row>
330
331 <row>
332 <entry><type>anycompatiblemultirange</type></entry>
333 <entry>Common</entry>
334 <entry>Indicates that a function accepts any multirange data type,
335 with automatic promotion of multiple arguments to a common data type
336 </entry>
337 </row>
338 </tbody>
339 </tgroup>
340 </table>
341
342 <para>
343 Polymorphic arguments and results are tied to each other and are resolved
344 to specific data types when a query calling a polymorphic function is
345 parsed. When there is more than one polymorphic argument, the actual
346 data types of the input values must match up as described below. If the
347 function's result type is polymorphic, or it has output parameters of
348 polymorphic types, the types of those results are deduced from the
349 actual types of the polymorphic inputs as described below.
350 </para>
351
352 <para>
353 For the <quote>simple</quote> family of polymorphic types, the
354 matching and deduction rules work like this:
355 </para>
356
357 <para>
358 Each position (either argument or return value) declared as
359 <type>anyelement</type> is allowed to have any specific actual
360 data type, but in any given call they must all be the
361 <emphasis>same</emphasis> actual type. Each
362 position declared as <type>anyarray</type> can have any array data type,
363 but similarly they must all be the same type. And similarly,
364 positions declared as <type>anyrange</type> must all be the same range
365 type. Likewise for <type>anymultirange</type>.
366 </para>
367
368 <para>
369 Furthermore, if there are
370 positions declared <type>anyarray</type> and others declared
371 <type>anyelement</type>, the actual array type in the
372 <type>anyarray</type> positions must be an array whose elements are
373 the same type appearing in the <type>anyelement</type> positions.
374 <type>anynonarray</type> is treated exactly the same as <type>anyelement</type>,
375 but adds the additional constraint that the actual type must not be
376 an array type.
377 <type>anyenum</type> is treated exactly the same as <type>anyelement</type>,
378 but adds the additional constraint that the actual type must
379 be an enum type.
380 </para>
381
382 <para>
383 Similarly, if there are positions declared <type>anyrange</type>
384 and others declared <type>anyelement</type> or <type>anyarray</type>,
385 the actual range type in the <type>anyrange</type> positions must be a
386 range whose subtype is the same type appearing in
387 the <type>anyelement</type> positions and the same as the element type
388 of the <type>anyarray</type> positions.
389 If there are positions declared <type>anymultirange</type>,
390 their actual multirange type must contain ranges matching parameters declared
391 <type>anyrange</type> and base elements matching parameters declared
392 <type>anyelement</type> and <type>anyarray</type>.
393 </para>
394
395 <para>
396 Thus, when more than one argument position is declared with a polymorphic
397 type, the net effect is that only certain combinations of actual argument
398 types are allowed. For example, a function declared as
399 <literal>equal(anyelement, anyelement)</literal> will take any two input values,
400 so long as they are of the same data type.
401 </para>
402
403 <para>
404 When the return value of a function is declared as a polymorphic type,
405 there must be at least one argument position that is also polymorphic,
406 and the actual data type(s) supplied for the polymorphic arguments
407 determine the actual
408 result type for that call. For example, if there were not already
409 an array subscripting mechanism, one could define a function that
410 implements subscripting as <literal>subscript(anyarray, integer)
411 returns anyelement</literal>. This declaration constrains the actual first
412 argument to be an array type, and allows the parser to infer the correct
413 result type from the actual first argument's type. Another example
414 is that a function declared as <literal>f(anyarray) returns anyenum</literal>
415 will only accept arrays of enum types.
416 </para>
417
418 <para>
419 In most cases, the parser can infer the actual data type for a
420 polymorphic result type from arguments that are of a different
421 polymorphic type in the same family; for example <type>anyarray</type>
422 can be deduced from <type>anyelement</type> or vice versa.
423 An exception is that a
424 polymorphic result of type <type>anyrange</type> requires an argument
425 of type <type>anyrange</type>; it cannot be deduced
426 from <type>anyarray</type> or <type>anyelement</type> arguments. This
427 is because there could be multiple range types with the same subtype.
428 </para>
429
430 <para>
431 Note that <type>anynonarray</type> and <type>anyenum</type> do not represent
432 separate type variables; they are the same type as
433 <type>anyelement</type>, just with an additional constraint. For
434 example, declaring a function as <literal>f(anyelement, anyenum)</literal>
435 is equivalent to declaring it as <literal>f(anyenum, anyenum)</literal>:
436 both actual arguments have to be the same enum type.
437 </para>
438
439 <para>
440 For the <quote>common</quote> family of polymorphic types, the
441 matching and deduction rules work approximately the same as for
442 the <quote>simple</quote> family, with one major difference: the
443 actual types of the arguments need not be identical, so long as they
444 can be implicitly cast to a single common type. The common type is
445 selected following the same rules as for <literal>UNION</literal> and
446 related constructs (see <xref linkend="typeconv-union-case"/>).
447 Selection of the common type considers the actual types
448 of <type>anycompatible</type> and <type>anycompatiblenonarray</type>
449 inputs, the array element types of <type>anycompatiblearray</type>
450 inputs, the range subtypes of <type>anycompatiblerange</type> inputs,
451 and the multirange subtypes of <type>anycompatiblemultirange</type>
452 inputs. If <type>anycompatiblenonarray</type> is present then the
453 common type is required to be a non-array type. Once a common type is
454 identified, arguments in <type>anycompatible</type>
455 and <type>anycompatiblenonarray</type> positions are automatically
456 cast to that type, and arguments in <type>anycompatiblearray</type>
457 positions are automatically cast to the array type for that type.
458 </para>
459
460 <para>
461 Since there is no way to select a range type knowing only its subtype,
462 use of <type>anycompatiblerange</type> and/or
463 <type>anycompatiblemultirange</type> requires that all arguments declared
464 with that type have the same actual range and/or multirange type, and that
465 that type's subtype agree with the selected common type, so that no casting
466 of the range values is required. As with <type>anyrange</type> and
467 <type>anymultirange</type>, use of <type>anycompatiblerange</type> and
468 <type>anymultirange</type> as a function result type requires that there be
469 an <type>anycompatiblerange</type> or <type>anycompatiblemultirange</type>
470 argument.
471 </para>
472
473 <para>
474 Notice that there is no <type>anycompatibleenum</type> type. Such a
475 type would not be very useful, since there normally are not any
476 implicit casts to enum types, meaning that there would be no way to
477 resolve a common type for dissimilar enum inputs.
478 </para>
479
480 <para>
481 The <quote>simple</quote> and <quote>common</quote> polymorphic
482 families represent two independent sets of type variables. Consider
483 for example
484 <programlisting>
485 CREATE FUNCTION myfunc(a anyelement, b anyelement,
486 c anycompatible, d anycompatible)
487 RETURNS anycompatible AS ...
488 </programlisting>
489 In an actual call of this function, the first two inputs must have
490 exactly the same type. The last two inputs must be promotable to a
491 common type, but this type need not have anything to do with the type
492 of the first two inputs. The result will have the common type of the
493 last two inputs.
494 </para>
495
496 <para>
497 A variadic function (one taking a variable number of arguments, as in
498 <xref linkend="xfunc-sql-variadic-functions"/>) can be
499 polymorphic: this is accomplished by declaring its last parameter as
500 <literal>VARIADIC</literal> <type>anyarray</type> or
501 <literal>VARIADIC</literal> <type>anycompatiblearray</type>.
502 For purposes of argument
503 matching and determining the actual result type, such a function behaves
504 the same as if you had written the appropriate number of
505 <type>anynonarray</type> or <type>anycompatiblenonarray</type>
506 parameters.
507 </para>
508 </sect2>
509 </sect1>
510
511 &xfunc;
512 &xaggr;
513 &xtypes;
514 &xoper;
515 &xindex;
516
517
518 <sect1 id="extend-extensions">
519 <title>Packaging Related Objects into an Extension</title>
520
521 <indexterm zone="extend-extensions">
522 <primary>extension</primary>
523 </indexterm>
524
525 <para>
526 A useful extension to <productname>PostgreSQL</productname> typically includes
527 multiple SQL objects; for example, a new data type will require new
528 functions, new operators, and probably new index operator classes.
529 It is helpful to collect all these objects into a single package
530 to simplify database management. <productname>PostgreSQL</productname> calls
531 such a package an <firstterm>extension</firstterm>. To define an extension,
532 you need at least a <firstterm>script file</firstterm> that contains the
533 <acronym>SQL</acronym> commands to create the extension's objects, and a
534 <firstterm>control file</firstterm> that specifies a few basic properties
535 of the extension itself. If the extension includes C code, there
536 will typically also be a shared library file into which the C code
537 has been built. Once you have these files, a simple
538 <link linkend="sql-createextension"><command>CREATE EXTENSION</command></link> command loads the objects into
539 your database.
540 </para>
541
542 <para>
543 The main advantage of using an extension, rather than just running the
544 <acronym>SQL</acronym> script to load a bunch of <quote>loose</quote> objects
545 into your database, is that <productname>PostgreSQL</productname> will then
546 understand that the objects of the extension go together. You can
547 drop all the objects with a single <link linkend="sql-dropextension"><command>DROP EXTENSION</command></link>
548 command (no need to maintain a separate <quote>uninstall</quote> script).
549 Even more useful, <application>pg_dump</application> knows that it should not
550 dump the individual member objects of the extension &mdash; it will
551 just include a <command>CREATE EXTENSION</command> command in dumps, instead.
552 This vastly simplifies migration to a new version of the extension
553 that might contain more or different objects than the old version.
554 Note however that you must have the extension's control, script, and
555 other files available when loading such a dump into a new database.
556 </para>
557
558 <para>
559 <productname>PostgreSQL</productname> will not let you drop an individual object
560 contained in an extension, except by dropping the whole extension.
561 Also, while you can change the definition of an extension member object
562 (for example, via <command>CREATE OR REPLACE FUNCTION</command> for a
563 function), bear in mind that the modified definition will not be dumped
564 by <application>pg_dump</application>. Such a change is usually only sensible if
565 you concurrently make the same change in the extension's script file.
566 (But there are special provisions for tables containing configuration
567 data; see <xref linkend="extend-extensions-config-tables"/>.)
568 In production situations, it's generally better to create an extension
569 update script to perform changes to extension member objects.
570 </para>
571
572 <para>
573 The extension script may set privileges on objects that are part of the
574 extension, using <command>GRANT</command> and <command>REVOKE</command>
575 statements. The final set of privileges for each object (if any are set)
576 will be stored in the
577 <link linkend="catalog-pg-init-privs"><structname>pg_init_privs</structname></link>
578 system catalog. When <application>pg_dump</application> is used, the
579 <command>CREATE EXTENSION</command> command will be included in the dump, followed
580 by the set of <command>GRANT</command> and <command>REVOKE</command>
581 statements necessary to set the privileges on the objects to what they were
582 at the time the dump was taken.
583 </para>
584
585 <para>
586 <productname>PostgreSQL</productname> does not currently support extension scripts
587 issuing <command>CREATE POLICY</command> or <command>SECURITY LABEL</command>
588 statements. These are expected to be set after the extension has been
589 created. All RLS policies and security labels on extension objects will be
590 included in dumps created by <application>pg_dump</application>.
591 </para>
592
593 <para>
594 The extension mechanism also has provisions for packaging modification
595 scripts that adjust the definitions of the SQL objects contained in an
596 extension. For example, if version 1.1 of an extension adds one function
597 and changes the body of another function compared to 1.0, the extension
598 author can provide an <firstterm>update script</firstterm> that makes just those
599 two changes. The <command>ALTER EXTENSION UPDATE</command> command can then
600 be used to apply these changes and track which version of the extension
601 is actually installed in a given database.
602 </para>
603
604 <para>
605 The kinds of SQL objects that can be members of an extension are shown in
606 the description of <link linkend="sql-alterextension"><command>ALTER EXTENSION</command></link>. Notably, objects
607 that are database-cluster-wide, such as databases, roles, and tablespaces,
608 cannot be extension members since an extension is only known within one
609 database. (Although an extension script is not prohibited from creating
610 such objects, if it does so they will not be tracked as part of the
611 extension.) Also notice that while a table can be a member of an
612 extension, its subsidiary objects such as indexes are not directly
613 considered members of the extension.
614 Another important point is that schemas can belong to extensions, but not
615 vice versa: an extension as such has an unqualified name and does not
616 exist <quote>within</quote> any schema. The extension's member objects,
617 however, will belong to schemas whenever appropriate for their object
618 types. It may or may not be appropriate for an extension to own the
619 schema(s) its member objects are within.
620 </para>
621
622 <para>
623 If an extension's script creates any temporary objects (such as temp
624 tables), those objects are treated as extension members for the
625 remainder of the current session, but are automatically dropped at
626 session end, as any temporary object would be. This is an exception
627 to the rule that extension member objects cannot be dropped without
628 dropping the whole extension.
629 </para>
630
631 <sect2 id="extend-extensions-files">
632 <title>Extension Files</title>
633
634 <indexterm>
635 <primary>control file</primary>
636 </indexterm>
637
638 <para>
639 The <command>CREATE EXTENSION</command> command relies on a control
640 file for each extension, which must be named the same as the extension
641 with a suffix of <literal>.control</literal>, and must be placed in the
642 installation's <literal>SHAREDIR/extension</literal> directory. There
643 must also be at least one <acronym>SQL</acronym> script file, which follows the
644 naming pattern
645 <literal><replaceable>extension</replaceable>--<replaceable>version</replaceable>.sql</literal>
646 (for example, <literal>foo--1.0.sql</literal> for version <literal>1.0</literal> of
647 extension <literal>foo</literal>). By default, the script file(s) are also
648 placed in the <literal>SHAREDIR/extension</literal> directory; but the
649 control file can specify a different directory for the script file(s).
650 </para>
651
652 <para>
653 Additional locations for extension control files can be configured using
654 the parameter <xref linkend="guc-extension-control-path"/>.
655 </para>
656
657 <para>
658 The file format for an extension control file is the same as for the
659 <filename>postgresql.conf</filename> file, namely a list of
660 <replaceable>parameter_name</replaceable> <literal>=</literal> <replaceable>value</replaceable>
661 assignments, one per line. Blank lines and comments introduced by
662 <literal>#</literal> are allowed. Be sure to quote any value that is not
663 a single word or number.
664 </para>
665
666 <para>
667 A control file can set the following parameters:
668 </para>
669
670 <variablelist>
671 <varlistentry id="extend-extensions-files-directory">
672 <term><varname>directory</varname> (<type>string</type>)</term>
673 <listitem>
674 <para>
675 The directory containing the extension's <acronym>SQL</acronym> script
676 file(s). Unless an absolute path is given, the name is relative to
677 the directory where the control file was found. By default,
678 the script files are looked for in the same directory where the
679 control file was found.
680 </para>
681 </listitem>
682 </varlistentry>
683
684 <varlistentry id="extend-extensions-files-default-version">
685 <term><varname>default_version</varname> (<type>string</type>)</term>
686 <listitem>
687 <para>
688 The default version of the extension (the one that will be installed
689 if no version is specified in <command>CREATE EXTENSION</command>). Although
690 this can be omitted, that will result in <command>CREATE EXTENSION</command>
691 failing if no <literal>VERSION</literal> option appears, so you generally
692 don't want to do that.
693 </para>
694 </listitem>
695 </varlistentry>
696
697 <varlistentry id="extend-extensions-files-comment">
698 <term><varname>comment</varname> (<type>string</type>)</term>
699 <listitem>
700 <para>
701 A comment (any string) about the extension. The comment is applied
702 when initially creating an extension, but not during extension updates
703 (since that might override user-added comments). Alternatively,
704 the extension's comment can be set by writing
705 a <xref linkend="sql-comment"/> command in the script file.
706 </para>
707 </listitem>
708 </varlistentry>
709
710 <varlistentry id="extend-extensions-files-encoding">
711 <term><varname>encoding</varname> (<type>string</type>)</term>
712 <listitem>
713 <para>
714 The character set encoding used by the script file(s). This should
715 be specified if the script files contain any non-ASCII characters.
716 Otherwise the files will be assumed to be in the database encoding.
717 </para>
718 </listitem>
719 </varlistentry>
720
721 <varlistentry id="extend-extensions-files-module-pathname">
722 <term><varname>module_pathname</varname> (<type>string</type>)</term>
723 <listitem>
724 <para>
725 The value of this parameter will be substituted for each occurrence
726 of <literal>MODULE_PATHNAME</literal> in the script file(s). If it is not
727 set, no substitution is made. Typically, this is set to just
728 <literal><replaceable>shared_library_name</replaceable></literal> and
729 then <literal>MODULE_PATHNAME</literal> is used in <command>CREATE
730 FUNCTION</command> commands for C-language functions, so that the script
731 files do not need to hard-wire the name of the shared library.
732 </para>
733 </listitem>
734 </varlistentry>
735
736 <varlistentry id="extend-extensions-files-requires">
737 <term><varname>requires</varname> (<type>string</type>)</term>
738 <listitem>
739 <para>
740 A list of names of extensions that this extension depends on,
741 for example <literal>requires = 'foo, bar'</literal>. Those
742 extensions must be installed before this one can be installed.
743 </para>
744 </listitem>
745 </varlistentry>
746
747 <varlistentry id="extend-extensions-files-no-relocate">
748 <term><varname>no_relocate</varname> (<type>string</type>)</term>
749 <listitem>
750 <para>
751 A list of names of extensions that this extension depends on that
752 should be barred from changing their schemas via <command>ALTER
753 EXTENSION ... SET SCHEMA</command>.
754 This is needed if this extension's script references the name
755 of a required extension's schema (using
756 the <literal>@extschema:<replaceable>name</replaceable>@</literal>
757 syntax) in a way that cannot track renames.
758 </para>
759 </listitem>
760 </varlistentry>
761
762 <varlistentry id="extend-extensions-files-superuser">
763 <term><varname>superuser</varname> (<type>boolean</type>)</term>
764 <listitem>
765 <para>
766 If this parameter is <literal>true</literal> (which is the default),
767 only superusers can create the extension or update it to a new
768 version (but see also <varname>trusted</varname>, below).
769 If it is set to <literal>false</literal>, just the privileges
770 required to execute the commands in the installation or update script
771 are required.
772 This should normally be set to <literal>true</literal> if any of the
773 script commands require superuser privileges. (Such commands would
774 fail anyway, but it's more user-friendly to give the error up front.)
775 </para>
776 </listitem>
777 </varlistentry>
778
779 <varlistentry id="extend-extensions-files-trusted">
780 <term><varname>trusted</varname> (<type>boolean</type>)</term>
781 <listitem>
782 <para>
783 This parameter, if set to <literal>true</literal> (which is not the
784 default), allows some non-superusers to install an extension that
785 has <varname>superuser</varname> set to <literal>true</literal>.
786 Specifically, installation will be permitted for anyone who has
787 <literal>CREATE</literal> privilege on the current database.
788 When the user executing <command>CREATE EXTENSION</command> is not
789 a superuser but is allowed to install by virtue of this parameter,
790 then the installation or update script is run as the bootstrap
791 superuser, not as the calling user.
792 This parameter is irrelevant if <varname>superuser</varname> is
793 <literal>false</literal>.
794 Generally, this should not be set true for extensions that could
795 allow access to otherwise-superuser-only abilities, such as
796 file system access.
797 Also, marking an extension trusted requires significant extra effort
798 to write the extension's installation and update script(s) securely;
799 see <xref linkend="extend-extensions-security"/>.
800 </para>
801 </listitem>
802 </varlistentry>
803
804 <varlistentry id="extend-extensions-files-relocatable">
805 <term><varname>relocatable</varname> (<type>boolean</type>)</term>
806 <listitem>
807 <para>
808 An extension is <firstterm>relocatable</firstterm> if it is possible to move
809 its contained objects into a different schema after initial creation
810 of the extension. The default is <literal>false</literal>, i.e., the
811 extension is not relocatable.
812 See <xref linkend="extend-extensions-relocation"/> for more information.
813 </para>
814 </listitem>
815 </varlistentry>
816
817 <varlistentry id="extend-extensions-files-schema">
818 <term><varname>schema</varname> (<type>string</type>)</term>
819 <listitem>
820 <para>
821 This parameter can only be set for non-relocatable extensions.
822 It forces the extension to be loaded into exactly the named schema
823 and not any other.
824 The <varname>schema</varname> parameter is consulted only when
825 initially creating an extension, not during extension updates.
826 See <xref linkend="extend-extensions-relocation"/> for more information.
827 </para>
828 </listitem>
829 </varlistentry>
830 </variablelist>
831
832 <para>
833 In addition to the primary control file
834 <literal><replaceable>extension</replaceable>.control</literal>,
835 an extension can have secondary control files named in the style
836 <literal><replaceable>extension</replaceable>--<replaceable>version</replaceable>.control</literal>.
837 If supplied, these must be located in the script file directory.
838 Secondary control files follow the same format as the primary control
839 file. Any parameters set in a secondary control file override the
840 primary control file when installing or updating to that version of
841 the extension. However, the parameters <varname>directory</varname> and
842 <varname>default_version</varname> cannot be set in a secondary control file.
843 </para>
844
845 <para>
846 An extension's <acronym>SQL</acronym> script files can contain any SQL commands,
847 except for transaction control commands (<command>BEGIN</command>,
848 <command>COMMIT</command>, etc.) and commands that cannot be executed inside a
849 transaction block (such as <command>VACUUM</command>). This is because the
850 script files are implicitly executed within a transaction block.
851 </para>
852
853 <para>
854 An extension's <acronym>SQL</acronym> script files can also contain lines
855 beginning with <literal>\echo</literal>, which will be ignored (treated as
856 comments) by the extension mechanism. This provision is commonly used
857 to throw an error if the script file is fed to <application>psql</application>
858 rather than being loaded via <command>CREATE EXTENSION</command> (see example
859 script in <xref linkend="extend-extensions-example"/>).
860 Without that, users might accidentally load the
861 extension's contents as <quote>loose</quote> objects rather than as an
862 extension, a state of affairs that's a bit tedious to recover from.
863 </para>
864
865 <para>
866 If the extension script contains the
867 string <literal>@extowner@</literal>, that string is replaced with the
868 (suitably quoted) name of the user calling <command>CREATE
869 EXTENSION</command> or <command>ALTER EXTENSION</command>. Typically
870 this feature is used by extensions that are marked trusted to assign
871 ownership of selected objects to the calling user rather than the
872 bootstrap superuser. (One should be careful about doing so, however.
873 For example, assigning ownership of a C-language function to a
874 non-superuser would create a privilege escalation path for that user.)
875 </para>
876
877 <para>
878 While the script files can contain any characters allowed by the specified
879 encoding, control files should contain only plain ASCII, because there
880 is no way for <productname>PostgreSQL</productname> to know what encoding a
881 control file is in. In practice this is only an issue if you want to
882 use non-ASCII characters in the extension's comment. Recommended
883 practice in that case is to not use the control file <varname>comment</varname>
884 parameter, but instead use <command>COMMENT ON EXTENSION</command>
885 within a script file to set the comment.
886 </para>
887
888 </sect2>
889
890 <sect2 id="extend-extensions-relocation">
891 <title>Extension Relocatability</title>
892
893 <para>
894 Users often wish to load the objects contained in an extension into a
895 different schema than the extension's author had in mind. There are
896 three supported levels of relocatability:
897 </para>
898
899 <itemizedlist>
900 <listitem>
901 <para>
902 A fully relocatable extension can be moved into another schema
903 at any time, even after it's been loaded into a database.
904 This is done with the <command>ALTER EXTENSION SET SCHEMA</command>
905 command, which automatically renames all the member objects into
906 the new schema. Normally, this is only possible if the extension
907 contains no internal assumptions about what schema any of its
908 objects are in. Also, the extension's objects must all be in one
909 schema to begin with (ignoring objects that do not belong to any
910 schema, such as procedural languages). Mark a fully relocatable
911 extension by setting <literal>relocatable = true</literal> in its control
912 file.
913 </para>
914 </listitem>
915
916 <listitem>
917 <para>
918 An extension might be relocatable during installation but not
919 afterwards. This is typically the case if the extension's script
920 file needs to reference the target schema explicitly, for example
921 in setting <literal>search_path</literal> properties for SQL functions.
922 For such an extension, set <literal>relocatable = false</literal> in its
923 control file, and use <literal>@extschema@</literal> to refer to the target
924 schema in the script file. All occurrences of this string will be
925 replaced by the actual target schema's name (double-quoted if
926 necessary) before the script is executed. The user can set the
927 target schema using the
928 <literal>SCHEMA</literal> option of <command>CREATE EXTENSION</command>.
929 </para>
930 </listitem>
931
932 <listitem>
933 <para>
934 If the extension does not support relocation at all, set
935 <literal>relocatable = false</literal> in its control file, and also set
936 <literal>schema</literal> to the name of the intended target schema. This
937 will prevent use of the <literal>SCHEMA</literal> option of <command>CREATE
938 EXTENSION</command>, unless it specifies the same schema named in the control
939 file. This choice is typically necessary if the extension contains
940 internal assumptions about its schema name that can't be replaced by
941 uses of <literal>@extschema@</literal>. The <literal>@extschema@</literal>
942 substitution mechanism is available in this case too, although it is
943 of limited use since the schema name is determined by the control file.
944 </para>
945 </listitem>
946 </itemizedlist>
947
948 <para>
949 In all cases, the script file will be executed with
950 <xref linkend="guc-search-path"/> initially set to point to the target
951 schema; that is, <command>CREATE EXTENSION</command> does the equivalent of
952 this:
953 <programlisting>
954 SET LOCAL search_path TO @extschema@, pg_temp;
955 </programlisting>
956 This allows the objects created by the script file to go into the target
957 schema. The script file can change <varname>search_path</varname> if it wishes,
958 but that is generally undesirable. <varname>search_path</varname> is restored
959 to its previous setting upon completion of <command>CREATE EXTENSION</command>.
960 </para>
961
962 <para>
963 The target schema is determined by the <varname>schema</varname> parameter in
964 the control file if that is given, otherwise by the <literal>SCHEMA</literal>
965 option of <command>CREATE EXTENSION</command> if that is given, otherwise the
966 current default object creation schema (the first one in the caller's
967 <varname>search_path</varname>). When the control file <varname>schema</varname>
968 parameter is used, the target schema will be created if it doesn't
969 already exist, but in the other two cases it must already exist.
970 </para>
971
972 <para>
973 If any prerequisite extensions are listed in <varname>requires</varname>
974 in the control file, their target schemas are added to the initial
975 setting of <varname>search_path</varname>, following the new
976 extension's target schema. This allows their objects to be visible to
977 the new extension's script file.
978 </para>
979
980 <para>
981 For security, <literal>pg_temp</literal> is automatically appended to
982 the end of <varname>search_path</varname> in all cases.
983 </para>
984
985 <para>
986 Although a non-relocatable extension can contain objects spread across
987 multiple schemas, it is usually desirable to place all the objects meant
988 for external use into a single schema, which is considered the extension's
989 target schema. Such an arrangement works conveniently with the default
990 setting of <varname>search_path</varname> during creation of dependent
991 extensions.
992 </para>
993
994 <para>
995 If an extension references objects belonging to another extension,
996 it is recommended to schema-qualify those references. To do that,
997 write <literal>@extschema:<replaceable>name</replaceable>@</literal>
998 in the extension's script file, where <replaceable>name</replaceable>
999 is the name of the other extension (which must be listed in this
1000 extension's <literal>requires</literal> list). This string will be
1001 replaced by the name (double-quoted if necessary) of that extension's
1002 target schema.
1003 Although this notation avoids the need to make hard-wired assumptions
1004 about schema names in the extension's script file, its use may embed
1005 the other extension's schema name into the installed objects of this
1006 extension. (Typically, that happens
1007 when <literal>@extschema:<replaceable>name</replaceable>@</literal> is
1008 used inside a string literal, such as a function body or
1009 a <varname>search_path</varname> setting. In other cases, the object
1010 reference is reduced to an OID during parsing and does not require
1011 subsequent lookups.) If the other extension's schema name is so
1012 embedded, you should prevent the other extension from being relocated
1013 after yours is installed, by adding the name of the other extension to
1014 this one's <literal>no_relocate</literal> list.
1015 </para>
1016 </sect2>
1017
1018 <sect2 id="extend-extensions-config-tables">
1019 <title>Extension Configuration Tables</title>
1020
1021 <para>
1022 Some extensions include configuration tables, which contain data that
1023 might be added or changed by the user after installation of the
1024 extension. Ordinarily, if a table is part of an extension, neither
1025 the table's definition nor its content will be dumped by
1026 <application>pg_dump</application>. But that behavior is undesirable for a
1027 configuration table; any data changes made by the user need to be
1028 included in dumps, or the extension will behave differently after a dump
1029 and restore.
1030 </para>
1031
1032 <indexterm>
1033 <primary>pg_extension_config_dump</primary>
1034 </indexterm>
1035
1036 <para>
1037 To solve this problem, an extension's script file can mark a table
1038 or a sequence it has created as a configuration relation, which will
1039 cause <application>pg_dump</application> to include the table's or the sequence's
1040 contents (not its definition) in dumps. To do that, call the function
1041 <function>pg_extension_config_dump(regclass, text)</function> after creating the
1042 table or the sequence, for example
1043 <programlisting>
1044 CREATE TABLE my_config (key text, value text);
1045 CREATE SEQUENCE my_config_seq;
1046
1047 SELECT pg_catalog.pg_extension_config_dump('my_config', '');
1048 SELECT pg_catalog.pg_extension_config_dump('my_config_seq', '');
1049 </programlisting>
1050 Any number of tables or sequences can be marked this way. Sequences
1051 associated with <type>serial</type> or <type>bigserial</type> columns can
1052 be marked as well.
1053 </para>
1054
1055 <para>
1056 When the second argument of <function>pg_extension_config_dump</function> is
1057 an empty string, the entire contents of the table are dumped by
1058 <application>pg_dump</application>. This is usually only correct if the table
1059 is initially empty as created by the extension script. If there is
1060 a mixture of initial data and user-provided data in the table,
1061 the second argument of <function>pg_extension_config_dump</function> provides
1062 a <literal>WHERE</literal> condition that selects the data to be dumped.
1063 For example, you might do
1064 <programlisting>
1065 CREATE TABLE my_config (key text, value text, standard_entry boolean);
1066
1067 SELECT pg_catalog.pg_extension_config_dump('my_config', 'WHERE NOT standard_entry');
1068 </programlisting>
1069 and then make sure that <structfield>standard_entry</structfield> is true only
1070 in the rows created by the extension's script.
1071 </para>
1072
1073 <para>
1074 For sequences, the second argument of <function>pg_extension_config_dump</function>
1075 has no effect.
1076 </para>
1077
1078 <para>
1079 More complicated situations, such as initially-provided rows that might
1080 be modified by users, can be handled by creating triggers on the
1081 configuration table to ensure that modified rows are marked correctly.
1082 </para>
1083
1084 <para>
1085 You can alter the filter condition associated with a configuration table
1086 by calling <function>pg_extension_config_dump</function> again. (This would
1087 typically be useful in an extension update script.) The only way to mark
1088 a table as no longer a configuration table is to dissociate it from the
1089 extension with <command>ALTER EXTENSION ... DROP TABLE</command>.
1090 </para>
1091
1092 <para>
1093 Note that foreign key relationships between these tables will dictate the
1094 order in which the tables are dumped out by pg_dump. Specifically, pg_dump
1095 will attempt to dump the referenced-by table before the referencing table.
1096 As the foreign key relationships are set up at CREATE EXTENSION time (prior
1097 to data being loaded into the tables) circular dependencies are not
1098 supported. When circular dependencies exist, the data will still be dumped
1099 out but the dump will not be able to be restored directly and user
1100 intervention will be required.
1101 </para>
1102
1103 <para>
1104 Sequences associated with <type>serial</type> or <type>bigserial</type> columns
1105 need to be directly marked to dump their state. Marking their parent
1106 relation is not enough for this purpose.
1107 </para>
1108 </sect2>
1109
1110 <sect2 id="extend-extensions-updates">
1111 <title>Extension Updates</title>
1112
1113 <para>
1114 One advantage of the extension mechanism is that it provides convenient
1115 ways to manage updates to the SQL commands that define an extension's
1116 objects. This is done by associating a version name or number with
1117 each released version of the extension's installation script.
1118 In addition, if you want users to be able to update their databases
1119 dynamically from one version to the next, you should provide
1120 <firstterm>update scripts</firstterm> that make the necessary changes to go from
1121 one version to the next. Update scripts have names following the pattern
1122 <literal><replaceable>extension</replaceable>--<replaceable>old_version</replaceable>--<replaceable>target_version</replaceable>.sql</literal>
1123 (for example, <literal>foo--1.0--1.1.sql</literal> contains the commands to modify
1124 version <literal>1.0</literal> of extension <literal>foo</literal> into version
1125 <literal>1.1</literal>).
1126 </para>
1127
1128 <para>
1129 Given that a suitable update script is available, the command
1130 <command>ALTER EXTENSION UPDATE</command> will update an installed extension
1131 to the specified new version. The update script is run in the same
1132 environment that <command>CREATE EXTENSION</command> provides for installation
1133 scripts: in particular, <varname>search_path</varname> is set up in the same
1134 way, and any new objects created by the script are automatically added
1135 to the extension. Also, if the script chooses to drop extension member
1136 objects, they are automatically dissociated from the extension.
1137 </para>
1138
1139 <para>
1140 If an extension has secondary control files, the control parameters
1141 that are used for an update script are those associated with the script's
1142 target (new) version.
1143 </para>
1144
1145 <para>
1146 <command>ALTER EXTENSION</command> is able to execute sequences of update
1147 script files to achieve a requested update. For example, if only
1148 <literal>foo--1.0--1.1.sql</literal> and <literal>foo--1.1--2.0.sql</literal> are
1149 available, <command>ALTER EXTENSION</command> will apply them in sequence if an
1150 update to version <literal>2.0</literal> is requested when <literal>1.0</literal> is
1151 currently installed.
1152 </para>
1153
1154 <para>
1155 <productname>PostgreSQL</productname> doesn't assume anything about the properties
1156 of version names: for example, it does not know whether <literal>1.1</literal>
1157 follows <literal>1.0</literal>. It just matches up the available version names
1158 and follows the path that requires applying the fewest update scripts.
1159 (A version name can actually be any string that doesn't contain
1160 <literal>--</literal> or leading or trailing <literal>-</literal>.)
1161 </para>
1162
1163 <para>
1164 Sometimes it is useful to provide <quote>downgrade</quote> scripts, for
1165 example <literal>foo--1.1--1.0.sql</literal> to allow reverting the changes
1166 associated with version <literal>1.1</literal>. If you do that, be careful
1167 of the possibility that a downgrade script might unexpectedly
1168 get applied because it yields a shorter path. The risky case is where
1169 there is a <quote>fast path</quote> update script that jumps ahead several
1170 versions as well as a downgrade script to the fast path's start point.
1171 It might take fewer steps to apply the downgrade and then the fast
1172 path than to move ahead one version at a time. If the downgrade script
1173 drops any irreplaceable objects, this will yield undesirable results.
1174 </para>
1175
1176 <para>
1177 To check for unexpected update paths, use this command:
1178 <programlisting>
1179 SELECT * FROM pg_extension_update_paths('<replaceable>extension_name</replaceable>');
1180 </programlisting>
1181 This shows each pair of distinct known version names for the specified
1182 extension, together with the update path sequence that would be taken to
1183 get from the source version to the target version, or <literal>NULL</literal> if
1184 there is no available update path. The path is shown in textual form
1185 with <literal>--</literal> separators. You can use
1186 <literal>regexp_split_to_array(path,'--')</literal> if you prefer an array
1187 format.
1188 </para>
1189 </sect2>
1190
1191 <sect2 id="extend-extensions-update-scripts">
1192 <title>Installing Extensions Using Update Scripts</title>
1193
1194 <para>
1195 An extension that has been around for awhile will probably exist in
1196 several versions, for which the author will need to write update scripts.
1197 For example, if you have released a <literal>foo</literal> extension in
1198 versions <literal>1.0</literal>, <literal>1.1</literal>, and <literal>1.2</literal>, there
1199 should be update scripts <filename>foo--1.0--1.1.sql</filename>
1200 and <filename>foo--1.1--1.2.sql</filename>.
1201 Before <productname>PostgreSQL</productname> 10, it was necessary to also create
1202 new script files <filename>foo--1.1.sql</filename> and <filename>foo--1.2.sql</filename>
1203 that directly build the newer extension versions, or else the newer
1204 versions could not be installed directly, only by
1205 installing <literal>1.0</literal> and then updating. That was tedious and
1206 duplicative, but now it's unnecessary, because <command>CREATE
1207 EXTENSION</command> can follow update chains automatically.
1208 For example, if only the script
1209 files <filename>foo--1.0.sql</filename>, <filename>foo--1.0--1.1.sql</filename>,
1210 and <filename>foo--1.1--1.2.sql</filename> are available then a request to
1211 install version <literal>1.2</literal> is honored by running those three
1212 scripts in sequence. The processing is the same as if you'd first
1213 installed <literal>1.0</literal> and then updated to <literal>1.2</literal>.
1214 (As with <command>ALTER EXTENSION UPDATE</command>, if multiple pathways are
1215 available then the shortest is preferred.) Arranging an extension's
1216 script files in this style can reduce the amount of maintenance effort
1217 needed to produce small updates.
1218 </para>
1219
1220 <para>
1221 If you use secondary (version-specific) control files with an extension
1222 maintained in this style, keep in mind that each version needs a control
1223 file even if it has no stand-alone installation script, as that control
1224 file will determine how the implicit update to that version is performed.
1225 For example, if <filename>foo--1.0.control</filename> specifies <literal>requires
1226 = 'bar'</literal> but <literal>foo</literal>'s other control files do not, the
1227 extension's dependency on <literal>bar</literal> will be dropped when updating
1228 from <literal>1.0</literal> to another version.
1229 </para>
1230 </sect2>
1231
1232 <sect2 id="extend-extensions-security">
1233 <title>Security Considerations for Extensions</title>
1234
1235 <para>
1236 Widely-distributed extensions should assume little about the database
1237 they occupy. Therefore, it's appropriate to write functions provided
1238 by an extension in a secure style that cannot be compromised by
1239 search-path-based attacks.
1240 </para>
1241
1242 <para>
1243 An extension that has the <varname>superuser</varname> property set to
1244 true must also consider security hazards for the actions taken within
1245 its installation and update scripts. It is not terribly difficult for
1246 a malicious user to create trojan-horse objects that will compromise
1247 later execution of a carelessly-written extension script, allowing that
1248 user to acquire superuser privileges.
1249 </para>
1250
1251 <para>
1252 If an extension is marked <varname>trusted</varname>, then its
1253 installation schema can be selected by the installing user, who might
1254 intentionally use an insecure schema in hopes of gaining superuser
1255 privileges. Therefore, a trusted extension is extremely exposed from a
1256 security standpoint, and all its script commands must be carefully
1257 examined to ensure that no compromise is possible.
1258 </para>
1259
1260 <para>
1261 Advice about writing functions securely is provided in
1262 <xref linkend="extend-extensions-security-funcs"/> below, and advice
1263 about writing installation scripts securely is provided in
1264 <xref linkend="extend-extensions-security-scripts"/>.
1265 </para>
1266
1267 <sect3 id="extend-extensions-security-funcs">
1268 <title>Security Considerations for Extension Functions</title>
1269
1270 <para>
1271 SQL-language and PL-language functions provided by extensions are at
1272 risk of search-path-based attacks when they are executed, since
1273 parsing of these functions occurs at execution time not creation time.
1274 </para>
1275
1276 <para>
1277 The <link linkend="sql-createfunction-security"><command>CREATE
1278 FUNCTION</command></link> reference page contains advice about
1279 writing <literal>SECURITY DEFINER</literal> functions safely. It's
1280 good practice to apply those techniques for any function provided by
1281 an extension, since the function might be called by a high-privilege
1282 user.
1283 </para>
1284
1285 <!-- XXX It's not enough to use qualified names, because one might write a
1286 qualified name to an object that itself uses unqualified names. Many
1287 information_schema functions have that defect, for example. However,
1288 that's a defect in the referenced object, and relatively few queries
1289 will be affected. Also, we direct applications to secure search_path
1290 when connecting to an untrusted database; if applications do that,
1291 they are immune to known attacks even if some extension refers to a
1292 defective object. Therefore, guide extension authors as though core
1293 PostgreSQL contained no such defect. -->
1294 <para>
1295 If you cannot set the <varname>search_path</varname> to contain only
1296 secure schemas, assume that each unqualified name could resolve to an
1297 object that a malicious user has defined. Beware of constructs that
1298 depend on <varname>search_path</varname> implicitly; for
1299 example, <token>IN</token>
1300 and <literal>CASE <replaceable>expression</replaceable> WHEN</literal>
1301 always select an operator using the search path. In their place, use
1302 <literal>OPERATOR(<replaceable>schema</replaceable>.=) ANY</literal>
1303 and <literal>CASE WHEN <replaceable>expression</replaceable></literal>.
1304 </para>
1305
1306 <para>
1307 A general-purpose extension usually should not assume that it's been
1308 installed into a secure schema, which means that even schema-qualified
1309 references to its own objects are not entirely risk-free. For
1310 example, if the extension has defined a
1311 function <literal>myschema.myfunc(bigint)</literal> then a call such
1312 as <literal>myschema.myfunc(42)</literal> could be captured by a
1313 hostile function <literal>myschema.myfunc(integer)</literal>. Be
1314 careful that the data types of function and operator parameters exactly
1315 match the declared argument types, using explicit casts where necessary.
1316 </para>
1317 </sect3>
1318
1319 <sect3 id="extend-extensions-security-scripts">
1320 <title>Security Considerations for Extension Scripts</title>
1321
1322 <para>
1323 An extension installation or update script should be written to guard
1324 against search-path-based attacks occurring when the script executes.
1325 If an object reference in the script can be made to resolve to some
1326 other object than the script author intended, then a compromise might
1327 occur immediately, or later when the mis-defined extension object is
1328 used.
1329 </para>
1330
1331 <para>
1332 DDL commands such as <command>CREATE FUNCTION</command>
1333 and <command>CREATE OPERATOR CLASS</command> are generally secure,
1334 but beware of any command having a general-purpose expression as a
1335 component. For example, <command>CREATE VIEW</command> needs to be
1336 vetted, as does a <literal>DEFAULT</literal> expression
1337 in <command>CREATE FUNCTION</command>.
1338 </para>
1339
1340 <para>
1341 Sometimes an extension script might need to execute general-purpose
1342 SQL, for example to make catalog adjustments that aren't possible via
1343 DDL. Be careful to execute such commands with a
1344 secure <varname>search_path</varname>; do <emphasis>not</emphasis>
1345 trust the path provided by <command>CREATE/ALTER EXTENSION</command>
1346 to be secure. Best practice is to temporarily
1347 set <varname>search_path</varname> to <literal>pg_catalog,
1348 pg_temp</literal> and insert references to the extension's
1349 installation schema explicitly where needed. (This practice might
1350 also be helpful for creating views.) Examples can be found in
1351 the <filename>contrib</filename> modules in
1352 the <productname>PostgreSQL</productname> source code distribution.
1353 </para>
1354
1355 <para>
1356 Secure cross-extension references typically require schema-qualification
1357 of the names of the other extension's objects, using the
1358 <literal>@extschema:<replaceable>name</replaceable>@</literal>
1359 syntax, in addition to careful matching of argument types for functions
1360 and operators.
1361 </para>
1362 </sect3>
1363 </sect2>
1364
1365 <sect2 id="extend-extensions-example">
1366 <title>Extension Example</title>
1367
1368 <para>
1369 Here is a complete example of an <acronym>SQL</acronym>-only
1370 extension, a two-element composite type that can store any type of value
1371 in its slots, which are named <quote>k</quote> and <quote>v</quote>. Non-text
1372 values are automatically coerced to text for storage.
1373 </para>
1374
1375 <para>
1376 The script file <filename>pair--1.0.sql</filename> looks like this:
1377
1378 <programlisting><![CDATA[
1379 -- complain if script is sourced in psql, rather than via CREATE EXTENSION
1380 \echo Use "CREATE EXTENSION pair" to load this file. \quit
1381
1382 CREATE TYPE pair AS ( k text, v text );
1383
1384 CREATE FUNCTION pair(text, text)
1385 RETURNS pair LANGUAGE SQL AS 'SELECT ROW($1, $2)::@[email protected];';
1386
1387 CREATE OPERATOR ~> (LEFTARG = text, RIGHTARG = text, FUNCTION = pair);
1388
1389 -- "SET search_path" is easy to get right, but qualified names perform better.
1390 CREATE FUNCTION lower(pair)
1391 RETURNS pair LANGUAGE SQL
1392 AS 'SELECT ROW(lower($1.k), lower($1.v))::@[email protected];'
1393 SET search_path = pg_temp;
1394
1395 CREATE FUNCTION pair_concat(pair, pair)
1396 RETURNS pair LANGUAGE SQL
1397 AS 'SELECT ROW($1.k OPERATOR(pg_catalog.||) $2.k,
1398 $1.v OPERATOR(pg_catalog.||) $2.v)::@[email protected];';
1399 ]]>
1400 </programlisting>
1401 </para>
1402
1403 <para>
1404 The control file <filename>pair.control</filename> looks like this:
1405
1406 <programlisting>
1407 # pair extension
1408 comment = 'A key/value pair data type'
1409 default_version = '1.0'
1410 # cannot be relocatable because of use of @extschema@
1411 relocatable = false
1412 </programlisting>
1413 </para>
1414
1415 <para>
1416 While you hardly need a makefile to install these two files into the
1417 correct directory, you could use a <filename>Makefile</filename> containing this:
1418
1419 <programlisting>
1420 EXTENSION = pair
1421 DATA = pair--1.0.sql
1422
1423 PG_CONFIG = pg_config
1424 PGXS := $(shell $(PG_CONFIG) --pgxs)
1425 include $(PGXS)
1426 </programlisting>
1427
1428 This makefile relies on <acronym>PGXS</acronym>, which is described
1429 in <xref linkend="extend-pgxs"/>. The command <literal>make install</literal>
1430 will install the control and script files into the correct
1431 directory as reported by <application>pg_config</application>.
1432 </para>
1433
1434 <para>
1435 Once the files are installed, use the
1436 <command>CREATE EXTENSION</command> command to load the objects into
1437 any particular database.
1438 </para>
1439 </sect2>
1440 </sect1>
1441
1442 <sect1 id="extend-pgxs">
1443 <title>Extension Building Infrastructure</title>
1444
1445 <indexterm zone="extend-pgxs">
1446 <primary>pgxs</primary>
1447 </indexterm>
1448
1449 <para>
1450 If you are thinking about distributing your
1451 <productname>PostgreSQL</productname> extension modules, setting up a
1452 portable build system for them can be fairly difficult. Therefore
1453 the <productname>PostgreSQL</productname> installation provides a build
1454 infrastructure for extensions, called <acronym>PGXS</acronym>, so
1455 that simple extension modules can be built simply against an
1456 already installed server. <acronym>PGXS</acronym> is mainly intended
1457 for extensions that include C code, although it can be used for
1458 pure-SQL extensions too. Note that <acronym>PGXS</acronym> is not
1459 intended to be a universal build system framework that can be used
1460 to build any software interfacing to <productname>PostgreSQL</productname>;
1461 it simply automates common build rules for simple server extension
1462 modules. For more complicated packages, you might need to write your
1463 own build system.
1464 </para>
1465
1466 <para>
1467 To use the <acronym>PGXS</acronym> infrastructure for your extension,
1468 you must write a simple makefile.
1469 In the makefile, you need to set some variables
1470 and include the global <acronym>PGXS</acronym> makefile.
1471 Here is an example that builds an extension module named
1472 <literal>isbn_issn</literal>, consisting of a shared library containing
1473 some C code, an extension control file, an SQL script, an include file
1474 (only needed if other modules might need to access the extension functions
1475 without going via SQL), and a documentation text file:
1476 <programlisting>
1477 MODULES = isbn_issn
1478 EXTENSION = isbn_issn
1479 DATA = isbn_issn--1.0.sql
1480 DOCS = README.isbn_issn
1481 HEADERS_isbn_issn = isbn_issn.h
1482
1483 PG_CONFIG = pg_config
1484 PGXS := $(shell $(PG_CONFIG) --pgxs)
1485 include $(PGXS)
1486 </programlisting>
1487 The last three lines should always be the same. Earlier in the
1488 file, you assign variables or add custom
1489 <application>make</application> rules.
1490 </para>
1491
1492 <para>
1493 Set one of these three variables to specify what is built:
1494
1495 <variablelist>
1496 <varlistentry id="extend-pgxs-modules">
1497 <term><varname>MODULES</varname></term>
1498 <listitem>
1499 <para>
1500 list of shared-library objects to be built from source files with same
1501 stem (do not include library suffixes in this list)
1502 </para>
1503 </listitem>
1504 </varlistentry>
1505
1506 <varlistentry id="extend-pgxs-module-big">
1507 <term><varname>MODULE_big</varname></term>
1508 <listitem>
1509 <para>
1510 a shared library to build from multiple source files
1511 (list object files in <varname>OBJS</varname>)
1512 </para>
1513 </listitem>
1514 </varlistentry>
1515
1516 <varlistentry id="extend-pgxs-program">
1517 <term><varname>PROGRAM</varname></term>
1518 <listitem>
1519 <para>
1520 an executable program to build
1521 (list object files in <varname>OBJS</varname>)
1522 </para>
1523 </listitem>
1524 </varlistentry>
1525 </variablelist>
1526
1527 The following variables can also be set:
1528
1529 <variablelist>
1530 <varlistentry id="extend-pgxs-extension">
1531 <term><varname>EXTENSION</varname></term>
1532 <listitem>
1533 <para>
1534 extension name(s); for each name you must provide an
1535 <literal><replaceable>extension</replaceable>.control</literal> file,
1536 which will be installed into
1537 <literal><replaceable>prefix</replaceable>/share/extension</literal>
1538 </para>
1539 </listitem>
1540 </varlistentry>
1541
1542 <varlistentry id="extend-pgxs-moduledir">
1543 <term><varname>MODULEDIR</varname></term>
1544 <listitem>
1545 <para>
1546 subdirectory of <literal><replaceable>prefix</replaceable>/share</literal>
1547 into which DATA and DOCS files should be installed
1548 (if not set, default is <literal>extension</literal> if
1549 <varname>EXTENSION</varname> is set,
1550 or <literal>contrib</literal> if not)
1551 </para>
1552 </listitem>
1553 </varlistentry>
1554
1555 <varlistentry id="extend-pgxs-data">
1556 <term><varname>DATA</varname></term>
1557 <listitem>
1558 <para>
1559 random files to install into <literal><replaceable>prefix</replaceable>/share/$MODULEDIR</literal>
1560 </para>
1561 </listitem>
1562 </varlistentry>
1563
1564 <varlistentry id="extend-pgxs-data-built">
1565 <term><varname>DATA_built</varname></term>
1566 <listitem>
1567 <para>
1568 random files to install into
1569 <literal><replaceable>prefix</replaceable>/share/$MODULEDIR</literal>,
1570 which need to be built first
1571 </para>
1572 </listitem>
1573 </varlistentry>
1574
1575 <varlistentry id="extend-pgxs-data-tsearch">
1576 <term><varname>DATA_TSEARCH</varname></term>
1577 <listitem>
1578 <para>
1579 random files to install under
1580 <literal><replaceable>prefix</replaceable>/share/tsearch_data</literal>
1581 </para>
1582 </listitem>
1583 </varlistentry>
1584
1585 <varlistentry id="extend-pgxs-docs">
1586 <term><varname>DOCS</varname></term>
1587 <listitem>
1588 <para>
1589 random files to install under
1590 <literal><replaceable>prefix</replaceable>/doc/$MODULEDIR</literal>
1591 </para>
1592 </listitem>
1593 </varlistentry>
1594
1595 <varlistentry id="extend-pgxs-headers">
1596 <term><varname>HEADERS</varname></term>
1597 <term><varname>HEADERS_built</varname></term>
1598 <listitem>
1599 <para>
1600 Files to (optionally build and) install under
1601 <literal><replaceable>prefix</replaceable>/include/server/$MODULEDIR/$MODULE_big</literal>.
1602 </para>
1603 <para>
1604 Unlike <literal>DATA_built</literal>, files in <literal>HEADERS_built</literal>
1605 are not removed by the <literal>clean</literal> target; if you want them removed,
1606 also add them to <literal>EXTRA_CLEAN</literal> or add your own rules to do it.
1607 </para>
1608 </listitem>
1609 </varlistentry>
1610
1611 <varlistentry id="extend-pgxs-headers-module">
1612 <term><varname>HEADERS_$MODULE</varname></term>
1613 <term><varname>HEADERS_built_$MODULE</varname></term>
1614 <listitem>
1615 <para>
1616 Files to install (after building if specified) under
1617 <literal><replaceable>prefix</replaceable>/include/server/$MODULEDIR/$MODULE</literal>,
1618 where <literal>$MODULE</literal> must be a module name used
1619 in <literal>MODULES</literal> or <literal>MODULE_big</literal>.
1620 </para>
1621 <para>
1622 Unlike <literal>DATA_built</literal>, files in <literal>HEADERS_built_$MODULE</literal>
1623 are not removed by the <literal>clean</literal> target; if you want them removed,
1624 also add them to <literal>EXTRA_CLEAN</literal> or add your own rules to do it.
1625 </para>
1626 <para>
1627 It is legal to use both variables for the same module, or any
1628 combination, unless you have two module names in the
1629 <literal>MODULES</literal> list that differ only by the presence of a
1630 prefix <literal>built_</literal>, which would cause ambiguity. In
1631 that (hopefully unlikely) case, you should use only the
1632 <literal>HEADERS_built_$MODULE</literal> variables.
1633 </para>
1634 </listitem>
1635 </varlistentry>
1636
1637 <varlistentry id="extend-pgxs-scripts">
1638 <term><varname>SCRIPTS</varname></term>
1639 <listitem>
1640 <para>
1641 script files (not binaries) to install into
1642 <literal><replaceable>prefix</replaceable>/bin</literal>
1643 </para>
1644 </listitem>
1645 </varlistentry>
1646
1647 <varlistentry id="extend-pgxs-scripts-built">
1648 <term><varname>SCRIPTS_built</varname></term>
1649 <listitem>
1650 <para>
1651 script files (not binaries) to install into
1652 <literal><replaceable>prefix</replaceable>/bin</literal>,
1653 which need to be built first
1654 </para>
1655 </listitem>
1656 </varlistentry>
1657
1658 <varlistentry id="extend-pgxs-regress">
1659 <term><varname>REGRESS</varname></term>
1660 <listitem>
1661 <para>
1662 list of regression test cases (without suffix), see below
1663 </para>
1664 </listitem>
1665 </varlistentry>
1666
1667 <varlistentry id="extend-pgxs-regress-opts">
1668 <term><varname>REGRESS_OPTS</varname></term>
1669 <listitem>
1670 <para>
1671 additional switches to pass to <application>pg_regress</application>
1672 </para>
1673 </listitem>
1674 </varlistentry>
1675
1676 <varlistentry id="extend-pgxs-isolation">
1677 <term><varname>ISOLATION</varname></term>
1678 <listitem>
1679 <para>
1680 list of isolation test cases, see below for more details
1681 </para>
1682 </listitem>
1683 </varlistentry>
1684
1685 <varlistentry id="extend-pgxs-isolation-opts">
1686 <term><varname>ISOLATION_OPTS</varname></term>
1687 <listitem>
1688 <para>
1689 additional switches to pass to
1690 <application>pg_isolation_regress</application>
1691 </para>
1692 </listitem>
1693 </varlistentry>
1694
1695 <varlistentry id="extend-pgxs-tap-tests">
1696 <term><varname>TAP_TESTS</varname></term>
1697 <listitem>
1698 <para>
1699 switch defining if TAP tests need to be run, see below
1700 </para>
1701 </listitem>
1702 </varlistentry>
1703
1704 <varlistentry id="extend-pgxs-no-install">
1705 <term><varname>NO_INSTALL</varname></term>
1706 <listitem>
1707 <para>
1708 don't define an <literal>install</literal> target, useful for test
1709 modules that don't need their build products to be installed
1710 </para>
1711 </listitem>
1712 </varlistentry>
1713
1714 <varlistentry id="extend-pgxs-no-installcheck">
1715 <term><varname>NO_INSTALLCHECK</varname></term>
1716 <listitem>
1717 <para>
1718 don't define an <literal>installcheck</literal> target, useful e.g., if tests require special configuration, or don't use <application>pg_regress</application>
1719 </para>
1720 </listitem>
1721 </varlistentry>
1722
1723 <varlistentry id="extend-pgxs-extra-clean">
1724 <term><varname>EXTRA_CLEAN</varname></term>
1725 <listitem>
1726 <para>
1727 extra files to remove in <literal>make clean</literal>
1728 </para>
1729 </listitem>
1730 </varlistentry>
1731
1732 <varlistentry id="extend-pgxs-pg-cppflags">
1733 <term><varname>PG_CPPFLAGS</varname></term>
1734 <listitem>
1735 <para>
1736 will be prepended to <varname>CPPFLAGS</varname>
1737 </para>
1738 </listitem>
1739 </varlistentry>
1740
1741 <varlistentry id="extend-pgxs-pg-cflags">
1742 <term><varname>PG_CFLAGS</varname></term>
1743 <listitem>
1744 <para>
1745 will be appended to <varname>CFLAGS</varname>
1746 </para>
1747 </listitem>
1748 </varlistentry>
1749
1750 <varlistentry id="extend-pgxs-pg-cxxflags">
1751 <term><varname>PG_CXXFLAGS</varname></term>
1752 <listitem>
1753 <para>
1754 will be appended to <varname>CXXFLAGS</varname>
1755 </para>
1756 </listitem>
1757 </varlistentry>
1758
1759 <varlistentry id="extend-pgxs-pg-ldflags">
1760 <term><varname>PG_LDFLAGS</varname></term>
1761 <listitem>
1762 <para>
1763 will be prepended to <varname>LDFLAGS</varname>
1764 </para>
1765 </listitem>
1766 </varlistentry>
1767
1768 <varlistentry id="extend-pgxs-pg-libs">
1769 <term><varname>PG_LIBS</varname></term>
1770 <listitem>
1771 <para>
1772 will be added to <varname>PROGRAM</varname> link line
1773 </para>
1774 </listitem>
1775 </varlistentry>
1776
1777 <varlistentry id="extend-pgxs-shlib-link">
1778 <term><varname>SHLIB_LINK</varname></term>
1779 <listitem>
1780 <para>
1781 will be added to <varname>MODULE_big</varname> link line
1782 </para>
1783 </listitem>
1784 </varlistentry>
1785
1786 <varlistentry id="extend-pgxs-pg-config">
1787 <term><varname>PG_CONFIG</varname></term>
1788 <listitem>
1789 <para>
1790 path to <application>pg_config</application> program for the
1791 <productname>PostgreSQL</productname> installation to build against
1792 (typically just <literal>pg_config</literal> to use the first one in your
1793 <varname>PATH</varname>)
1794 </para>
1795 </listitem>
1796 </varlistentry>
1797 </variablelist>
1798 </para>
1799
1800 <para>
1801 Put this makefile as <literal>Makefile</literal> in the directory
1802 which holds your extension. Then you can do
1803 <literal>make</literal> to compile, and then <literal>make
1804 install</literal> to install your module. By default, the extension is
1805 compiled and installed for the
1806 <productname>PostgreSQL</productname> installation that
1807 corresponds to the first <command>pg_config</command> program
1808 found in your <varname>PATH</varname>. You can use a different installation by
1809 setting <varname>PG_CONFIG</varname> to point to its
1810 <command>pg_config</command> program, either within the makefile
1811 or on the <literal>make</literal> command line.
1812 </para>
1813
1814 <para>
1815 You can select a separate directory prefix in which to install your
1816 extension's files, by setting the <command>make</command> variable
1817 <varname>prefix</varname> when executing <literal>make install</literal>
1818 like so:
1819 <programlisting>
1820 make install prefix=/usr/local/postgresql
1821 </programlisting>
1822 This will install the extension control and SQL files into
1823 <filename>/usr/local/postgresql/share</filename> and the shared modules into
1824 <filename>/usr/local/postgresql/lib</filename>. If the prefix does not
1825 include the strings <literal>postgres</literal> or
1826 <literal>pgsql</literal>, such as
1827 <programlisting>
1828 make install prefix=/usr/local/extras
1829 </programlisting>
1830 then <literal>postgresql</literal> will be appended to the directory
1831 names, installing the control and SQL files into
1832 <filename>/usr/local/extras/share/postgresql/extension</filename> and the
1833 shared modules into <filename>/usr/local/extras/lib/postgresql</filename>.
1834 Either way, you'll need to set <xref
1835 linkend="guc-extension-control-path"/> and <xref
1836 linkend="guc-dynamic-library-path"/> to enable the
1837 <productname>PostgreSQL</productname> server to find the files:
1838 <programlisting>
1839 extension_control_path = '/usr/local/extras/share/postgresql:$system'
1840 dynamic_library_path = '/usr/local/extras/lib/postgresql:$libdir'
1841 </programlisting>
1842 </para>
1843
1844 <para>
1845 You can also run <literal>make</literal> in a directory outside the source
1846 tree of your extension, if you want to keep the build directory separate.
1847 This procedure is also called a
1848 <indexterm><primary>VPATH</primary></indexterm><firstterm>VPATH</firstterm>
1849 build. Here's how:
1850 <programlisting>
1851 mkdir build_dir
1852 cd build_dir
1853 make -f /path/to/extension/source/tree/Makefile
1854 make -f /path/to/extension/source/tree/Makefile install
1855 </programlisting>
1856 </para>
1857
1858 <para>
1859 Alternatively, you can set up a directory for a VPATH build in a similar
1860 way to how it is done for the core code. One way to do this is using the
1861 core script <filename>config/prep_buildtree</filename>. Once this has been done
1862 you can build by setting the <literal>make</literal> variable
1863 <varname>VPATH</varname> like this:
1864 <programlisting>
1865 make VPATH=/path/to/extension/source/tree
1866 make VPATH=/path/to/extension/source/tree install
1867 </programlisting>
1868 This procedure can work with a greater variety of directory layouts.
1869 </para>
1870
1871 <para>
1872 The scripts listed in the <varname>REGRESS</varname> variable are used for
1873 regression testing of your module, which can be invoked by <literal>make
1874 installcheck</literal> after doing <literal>make install</literal>. For this to
1875 work you must have a running <productname>PostgreSQL</productname> server.
1876 The script files listed in <varname>REGRESS</varname> must appear in a
1877 subdirectory named <literal>sql/</literal> in your extension's directory.
1878 These files must have extension <literal>.sql</literal>, which must not be
1879 included in the <varname>REGRESS</varname> list in the makefile. For each
1880 test there should also be a file containing the expected output in a
1881 subdirectory named <literal>expected/</literal>, with the same stem and
1882 extension <literal>.out</literal>. <literal>make installcheck</literal>
1883 executes each test script with <application>psql</application>, and compares the
1884 resulting output to the matching expected file. Any differences will be
1885 written to the file <literal>regression.diffs</literal> in <command>diff
1886 -c</command> format. Note that trying to run a test that is missing its
1887 expected file will be reported as <quote>trouble</quote>, so make sure you
1888 have all expected files.
1889 </para>
1890
1891 <para>
1892 The scripts listed in the <varname>ISOLATION</varname> variable are used
1893 for tests stressing behavior of concurrent session with your module, which
1894 can be invoked by <literal>make installcheck</literal> after doing
1895 <literal>make install</literal>. For this to work you must have a
1896 running <productname>PostgreSQL</productname> server. The script files
1897 listed in <varname>ISOLATION</varname> must appear in a subdirectory
1898 named <literal>specs/</literal> in your extension's directory. These files
1899 must have extension <literal>.spec</literal>, which must not be included
1900 in the <varname>ISOLATION</varname> list in the makefile. For each test
1901 there should also be a file containing the expected output in a
1902 subdirectory named <literal>expected/</literal>, with the same stem and
1903 extension <literal>.out</literal>. <literal>make installcheck</literal>
1904 executes each test script, and compares the resulting output to the
1905 matching expected file. Any differences will be written to the file
1906 <literal>output_iso/regression.diffs</literal> in
1907 <command>diff -c</command> format. Note that trying to run a test that is
1908 missing its expected file will be reported as <quote>trouble</quote>, so
1909 make sure you have all expected files.
1910 </para>
1911
1912 <para>
1913 <literal>TAP_TESTS</literal> enables the use of TAP tests. Data from each
1914 run is present in a subdirectory named <literal>tmp_check/</literal>.
1915 See also <xref linkend="regress-tap"/> for more details.
1916 </para>
1917
1918 <tip>
1919 <para>
1920 The easiest way to create the expected files is to create empty files,
1921 then do a test run (which will of course report differences). Inspect
1922 the actual result files found in the <literal>results/</literal>
1923 directory (for tests in <literal>REGRESS</literal>), or
1924 <literal>output_iso/results/</literal> directory (for tests in
1925 <literal>ISOLATION</literal>), then copy them to
1926 <literal>expected/</literal> if they match what you expect from the test.
1927 </para>
1928
1929 </tip>
1930 </sect1>
1931
1932 </chapter>