Postgresql中的extension

Packaging Related Objects into an Extension

将相关对象打包进Extension

A useful extension to PostgreSQL typically includes multiple SQL objects; for example, a new data type will require new functions, new operators, and probably new index operator classes. It is helpful to collect all these objects into a single package to simplify database management. PostgreSQL calls such a package an extension. To define an extension, you need at least a script file that contains the SQL commands to create the extension's objects, and a control file that specifies a few basic properties of the extension itself. If the extension includes C code, there will typically also be a shared library file into which the C code has been built. Once you have these files, a simple CREATE EXTENSION command loads the objects into your database.

一个典型的extension包含多个SQL对象。举个例子，如果我们创建一个新的数据类型(data type)，那可能同时还需要与之相关的新函数(function)、新的操作符(operator)以及新的索引类型(index operator class)等，为了简化数据库管理，我们可以把这个新的数据类型以及所有与之相关的对象都封装到一个包(package)里。在Postgresql里，这样的包(package)就是extension。Extension至少包含一个脚本文件和一个控制文件，脚本文件(包含SQL命令)用来创建相关对象，控制文件(control file)用来指定extension的基本属性。有些extension中还包含一些C语言实现的共享库。如果具备了这些文件，一条简单的CREATE EXTENSION命令就可以将所有属于同一个extensioin的相关对象加载到数据库中。

The main advantage of using an extension, rather than just running the SQL script to load a bunch of "loose" objects into your database, is that PostgreSQL will then understand that the objects of the extension go together. You can drop all the objects with a single DROP EXTENSION command (no need to maintain a separate "uninstall" script). Even more useful, pg_dump knows that it should not dump the individual member objects of the extension — it will just include a CREATE EXTENSION command in dumps, instead. This vastly simplifies migration to a new version of the extension that might contain more or different objects than the old version. Note however that you must have the extension's control, script, and other files available when loading such a dump into a new database.

用extension的好处是可以让原本处于“松散”状态但实际相关的SQL对象在数据库中可以以一个整体的形式存在。一条简单的DROP EXTENSION命令就可以删除所有的相关对象，而不需要一个个的去删。除此之外，pg_dump命令在执行时仅会备份CREATE EXTENSION命令，而不备份extension中的对象，这样极大的简化了不同版本之间的相互备份和恢复。注意，在使用extension时，必须具备相关的权限(创建SQL对象、相关文件)。

PostgreSQL will not let you drop an individual object contained in an extension, except by dropping the whole extension. Also, while you can change the definition of an extension member object (for example, via CREATE OR REPLACE FUNCTION for a function), bear in mind that the modified definition will not be dumped by pg_dump. Such a change is usually only sensible if you concurrently make the same change in the extension's script file. (But there are special provisions for tables containing configuration data; see below.)

不允许删除extension中的单个对象，要删除必须删除整个extension，而且对单个对象的修改(如CREATE OR REPLACE FUNCTION)也不会被pg_dump备份，除非将相同的修改命令写到extension中的脚本文件中。

The extension mechanism also has provisions for packaging modification scripts that adjust the definitions of the SQL objects contained in an extension. For example, if version 1.1 of an extension adds one function and changes the body of another function compared to 1.0, the extension author can provide an update script that makes just those two changes. The ALTER EXTENSION UPDATE command can then be used to apply these changes and track which version of the extension is actually installed in a given database.

extension机制也支持添加修改脚本来对某些SQL对象进行重新定义。举个例子，1.1版本的extension在1.0的基础上增加了一个函数，并修改了另一个函数的部分代码，那创建extension的用户就可以添加一个更新脚本来做这些修改。 ALTER EXTENSION UPDATE命令可以用来使这些修改生效，同时还能知道当前添加到数据库的extension到底是哪一个版本。

The kinds of SQL objects that can be members of an extension are shown in the description of ALTER EXTENSION. Notably, objects that are database-cluster-wide, such as databases, roles, and tablespaces, cannot be extension members since an extension is only known within one database. (Although an extension script is not prohibited from creating such objects, if it does so they will not be tracked as part of the extension.) Also notice that while a table can be a member of an extension, its subsidiary objects such as indexes are not directly considered members of the extension. Another important point is that schemas can belong to extensions, but not vice versa: an extension as such has an unqualified name and does not exist "within" any schema. The extension's member objects, however, will belong to schemas whenever appropriate for their object types. It may or may not be appropriate for an extension to own the schema(s) its member objects are within.

想知道extension所支持的SQL对象的类型，请看ALTER EXTENSION命令的相关文档。注意，由于extension是属于具体的某个数据库的，诸如数据库、角色以及表空间这些所有数据库共享的对象是不能作为extension的成员对象的，虽然extension中的脚本也可以创建这些对象，但即使创建了这些对象，它们也不会被当作当前extension的一部分。另外，虽然表可以作为extension的成员，但这个表的子对象并不是extension的成员，比如表的索引。除此之外还要注意，模式(schema)可以属于extension，extension并不属于一个schema。

原文：http://www.postgresql.org/docs/9.3/static/extend-extensions.html