Extensions, this time with a patch

Hi,

Please find attached a WIP patch for extension's pg_dump support in
PostgreSQL, following design decisions that we've outlined earlier at
this year's and last year's PGCon developer meetings.

What's in the patch?

An extension is a new SQL object with a catalog and two commands to
manage them (reserved to superuser):

CREATE EXTENSION <extension> ;
DROP EXTENSION [IF EXISTS] <extension> [ RESTRICT | CASCADE ];

The first command (create) will parse the "control" file from a fixed
place (`pg_control --sharedir`/contrib/<extension>.control) and insert
an entry in the pg_extension catalog. That gives us an Oid which we can
use in pg_depend. Once we have it, the command will execute the SQL
script at same/place/<extension>.sql and recordDependencyOn lots of
objects created here (not all of them, because it does not appear
necessary to).

The drop command will happily remove the extension's object and all its
"direct" dependencies. That's what's been installed by the script. If
there exists some object not created by the script and that depends on
the extension, CASCADE is needed (e.g. create table foo(kv hstore);).

With that tracking in place, pg_dump is now able to issue a single
command per extension, the CREATE command. All dependent objects are
filtered out of the dump in the pg_dump queries. There's a nasty corner
case here with schema, see pg_dump support.

Rough support for a new \dx command in psql is implemented, too.

PGXS has been adapted so that it produces automatically the control file
should it be missing, using $(MAJORVERSION) as the contrib's
version. That means that right after installing contrib, it's possible
to 'create extension <any of them>' automatically (thanks to a 2 lines
file).

Open Items :

- cfparser

To parse the control/registry file, I've piggybacked on the recovery
configuration file parsing. The function to parse each line was not
exported from xlog, I've made a new src/backend/utils/misc/cfparser.c
file and placed parseRecoveryCommandFileLine() in there.

Please find attached a separate patch implementing just that, in case
you want to review/apply that on its own. The main patch do contains
the change too.

- User Documentation. Where in the manual do I write it?

- Naming of the "control" file, <extension>.{control,registry,install}

Currently, inspired by debian/control, the file is called .control,
but maybe we want to choose something that the user can guess about
the purpose before reading the fine manual. I don't like .install
because that's not what it is. .registry?

- Handling of custom_variable_classes

The attached patch has a column to store the information but makes no
use whatsoever of it, the goal would be to append classes from the
extension's registry file and possibly setup the default values
there.

As I don't think this part has been agreed before, I send the patch
without the code for that, even if I suspect it would be a rather
short addition, and very worthwile too.

- User data tables

An extension can install plain relations, and that's fine. The
problem is when the data in there are changed after installing.
Because the name of the game here is to exclude the table from the
dumps, of course the data will not be in there.

The solution would be to offer extension's author a way to 'flag'
some tables as worthy of dumping, I think marking the dependency as
DEPENDENCY_NORMAL rather then DEPENDENCY_INTERNAL will do the trick:

SELECT pg_extension_flag_dump(oid);

- Extension Upgrading

Should this be done by means of 'create extension' or some other
command, like 'alter extension foo upgrade'? The command would run
the SQL script again, which would be responsible for any steps the
extension author might find necessary to run.

- Bugs to fix

There's at least one where drop extension leaves things behind,
although it uses performDeletion(). The dependencies are fine enough
so that the leftover objects are not part of the dump done before to
drop, though. I didn't investigate it at all, this mail is in the
"discuss early" tradition of the project.

Regards,
--
Dimitri Fontaine
http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support