Re: Inline Extension - Mailing list pgsql-hackers

On Fri, Jan 20, 2012 at 8:52 AM, Dimitri Fontaine
<dimitri@2ndquadrant.fr> wrote:
> Tom Lane <tgl@sss.pgh.pa.us> writes:
>> Robert Haas <robertmhaas@gmail.com> writes:
>>> I guess the question is: for what purpose?
>>
>> Indeed, it seems like such a thing is not an extension at all anymore,
>> or at least it gives up many of the useful properties of extensions.
>
> I'm thinking that a common name and version number tracked in the
> database for a set of related functions (that usually form an API) is
> useful enough a property to be wanting to have extension support more
> use cases than contrib-like “module centric” extensions (meaning, C
> coded and shipped with a .so).

I see that there is some usefulness there, but I'm not sure that this
is the best way to get our hands around it.  For one thing, people can
and do schema versioning and schema upgrade scripts entirely in
userland.  My last implementation worked by keeping a schema_versions
table on the server with one column, a UUID.  The deployment tarball
contained a file with a list of UUIDs in it, each one associated to an
SQL script.  At install time, the install script ran through that file
in order and ran any scripts whose UUID didn't yet appear in the
table, and then added the UUIDs of the run scripts to the table.  This
might not be what any given person wants, but there's a lot of
flexibility to do things like that without any particular support from
the DBMS.  (Incidentally, this experience is what convinced me that
CREATE TABLE IF EXISTS and ALTER TABLE IF EXISTS are good things to
have; my system could have been a lot simpler if I'd had those.)

One random design idea I had related to providing this functionality
in the DB core is to have a command that creates an empty extension,
maybe just "CREATE EXTENSION foo EMPTY", and an ALTER command that
forcibly changes the DB's notion of what version is installed, like
"ALTER EXTENSION foo FORCE VERSION TO '1.1'".  That would allow the
same sort of thing you're after here by using those two features plus
ALTER EXTENSION ADD/DROP, and could also be used to do other things.
For example, suppose you have DB servers A and B.  A is running an old
version of some extension and is in a shared hosting environment where
you can't get access to the box.  B, on the other hand, is your
brand-new, dedicated server.  You could upgrade the extension on the
old machine "manually", by issuing SQL commands to forcibly change its
state, and then do a dump and reload onto B.  This might be useful if,
for example, B is also running a newer DB server version that won't
support the very old version of the extension running on A.   This is
probably an unusual situation, but maybe there's some value in
allowing users who want to do such things a cleaner way to do it than
direct catalog hackery.

Anyway, I'm just thinking out loud here - I don't actually have a very
strong feeling that I know what all of the solutions are in this area,
or even all the problems.  I'm interested in hearing about your
experiences with the system, and other people's, because I certainly
do agree that there's room for improvement.  One of my personal pet
peeves is that the system doesn't know how to do an install of v1.1 by
running the v1.0 script followed by the 1.0-1.1 upgrade script, which
I fear is going to lead to a rapid uptick in the number of copies of
almost-identical scripts in our git repository.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company