Re: RFC: PostgreSQL Add-On Network - Mailing list pgsql-hackers

Dave Page <dpage@pgadmin.org> writes:
> We have discussed this sort of facility at previous developer
> meetings, and as I recall came to the conclusion that we need to have
> the ability to distribute pre-built binaries, not just source code as
> virtually no Windows users are ever going to have a build environment
> setup. Similarly, neither are Mac users, if they didn't install XCode.

And I think that's exactly why David and you are both right. The way I
see it, we need *both* a source level distribution infrastructure, which
David is proposing to implement under the PGAN name, and a binary one
too, which is implemented by distributions like debian, Fedora and the
like, and by Devrim when it comes to PGDG RPMs repos and… by you when we
talk about MacOSX and Windows binary distribution.

I think the aim of the PGAN should be to make it damn easy for binary
distributions to include whatever PGXS extension they whish, with about
no effort.

> We also discussed extension management at the DBMS level, which I
> believe Dimitri was working on in his spare time. You should look at
> what he's been doing.

We've been talking about that together on IRC, and my only reserve here
ain't technical, as we already have a somewhat advanced notion of an
extension. What we miss is support for dump and restore, so my reserve
is that pushing the current system to point-and-clic users when it's not
ready to allow you to just pg_restore a dump when you installed any PGXS
extension (contribs included) is going to have us *lose* users, not gain
any.

Unfortunately I was overly optimistic last May when I though I could
reach a patch submission in the 8.5 cycle. I'd put it on too much work
on the day and no more free time with the required energy when my son is
so young (yes, that's a good problem to have). But still.

What I have been able to do is to determine what features to include in
a first extensions implementation, and proposing a syntax that no one
refused (after less successful attempts). Also the work remaining to be
done would represent easily a month of my evening and week-end time, and
maybe a Tom's morning. But he wouldn't have the fun of learning anything
new, and to paraphrase him, source contributors don't grow on trees :)

Anyway I've tried to summary it all (with some code perspective) in the
following wiki page:
 http://wiki.postgresql.org/wiki/ExtensionPackaging

Essentially, it's all about coding a new backend function to execute
commands from a file on the server, in the spirit of \i for psql, say
e.g. pg_execute_commands_from_file('path/ to/file.sql'). It would not
send any result back to the client because you can only describe the
portal once for any given single query.

Then you need to add a catalog for holding the extensions metadata, like
the path of the install file, uninstall file, current version, upgrade
function, custom class if any, path of the setup file (GUCs).

And dependancies between extensions, and between a given extension and
what core provides too (plpgsql e.g., or major postgresql version too).

Now you can hack a CREATE EXTENSION command to fill-in the catalog, and
the commands INSTALL EXTENSION and DROP EXTENSION to execute the given
files. You have to invent a trick to be able to assign any SQL object
created while executing the install file a dependency entry targeting
the extension's catalog oid. As this is all done in a single given
backend, my guess is that a static variable holding the oid would be
enough, but you still have to touch all object creation code path.

You do *not* have to care about schema in any way *BUT* to prepend the
search_path with pg_extension just before executing the install
file. It's a new schema to provide in template databases, so that
extensions default to creating their objects there. This new schema
should be installed just before pg_catalog in the search_path in places
that search for objects from their non qualified name. Full stop on this
topic.

Oh and special bonus if CREATE EXTENSION, given a newer version of an
already installed extension, will register things so as to run the
upgrade function from the newer version at INSTALL EXTENSION time. Or
invent yet another syntax for upgrading.

And. Wait. That's it.

Oh, you could also try to turn plpgsql, plperl, plpython and their
unstrusted variants into built-in extensions too, and contribs too. It
would be nice if contribs where all CREATEd into template0 and template1
and you only had to INSTALL EXTENSION hstore; for enjoying it. That'd be
a Tom's easy evening I guess :)

Anyone having the hours and skill required to do it? It's great fun,
rewarding, everybody and his neighbour *want* it to be done, and I'll be
as available as possible to answer mails, be there on IRC, and do the
necessary political work if there's still some more to be done.

Consider it as a gift I'm giving you: I'm refusing easily earned
reputation by allowing you to code that for me ;)

Regards,
--
dim

PS: that ought to do it.