Re: Extensions User Design

On Jun 23, 2009, at 2:06 PM, Dimitri Fontaine wrote:

>> It'd be nice if it supported other core languages like PL/Perl, but
>> it's okay if it doesn't on the first round (I'd likely want to use
>> some CPAN modules in a PL/Perl extension, anyway).
>
> At first sight I though you were talking about a non-issue, as I
> meant that an extension should not have to be a .so (or dll) +
> a .sql exposing it, but any SQL code PostgreSQL is able to
> understand, plperl included.

Well, C code with /[.](?:so|dll|dylib)/ should be allowed as well.

> But plpgsql and plperl are not available by default on databases, so
> it makes sense to rise the question, and the right answer might be
> to expose some (optional?) core components as extensions (version is
> PG major version), in order for out-of-core extensions to be able to
> depend on them being there.

Yes, and it could also be that a particular extension requires an
unsafe version of a PL. That would need to be included in the metadata
for the PL extension.

PL/pgSQL should be in core and enabled by default, IMHO. The other PLs
should be extensions.

> I've been told pljava is complex in that it requires a part of
> pljave to be there in order to be installable (like pseudo DDL coded
> in pljava and needed in the installation procedure). So I'd prefer
> not to go this far, just in case.

Well, if each PL aside from SQL, C, and PL/pgSQL is an extension, then
it's just a dependency, right?

>> In fact supporting custom classes GUCs seems to be part of what Tom
>> Dunstan did, so it should be ok to plan to have it?
> http://archives.postgresql.org/message-id/ca33c0a30804061349s41b4d8fcsa9c579454b27ecd2@mail.gmail.com

Ah, cool.

> In fact PostGIS is a complex beast, in that it's registering typmod
> like information about user columns into its own private tables
> (think extension's catalog). Now that means circular dependancy of
> some sort as restoring user data requires to have the PostGIS
> private tables filled already, but as they refer user data (not sure
> if it's targeting user tables ---DDL--- only), you have to already
> have restored them.

Ouch. Must be a nightmare today, too.

> Even if it's only targeting schema level stuff, you'd need to
> restore the extension's data after the schema but before the data,
> but the extension's itself (data types, indexes opclass, etc) BEFORE
> the data.
>
> I'm not sure you should target to support this level of complexity
> (it has to be generic) in the first incantation of it, but if some
> hacker tells me it's damn easy to get right with pg_depend, why not?

Yeah, we should KISS to start with.

> Maybe it's just a (non native) misuse of vocabulary, I see contrib
> as the current incarnation of the standard extension library and
> would like to see it evolve into a list of reviewed and maintained
> extensions, which in a later step you'll be able to remotely fetch
> and install easily from source from postgresql.org services, or in
> binary from your distribution package.

Oh, yeah, I'm on board with that.

> But I think we'll still need a contrib/ like suite that core hackers
> keep an eye on and maintain in minor branches and adapt in major
> releases.

This says the same thing as the last paragraph, no? I don't think I'd
call such a distribution "contrib," though. Maybe standard extensions.

> Now if we ever get to a point where we can setup an http repository
> of easily installable extensions that you can point a built-in core
> tool to, that means there will be the standard official one and a
> myriad of others (pgfoundry, and self hosting).

Yes.

> None of this, stay aboard :)
> Register within the database where you'll want to install it. The
> install step as shown below will then use the meta-data to do the
> sanity checking (dependancies) and the installation (what script to
> read?).

Oh, *that* kind of registration. Fine, of course!

>> It would need something to ensure an appropriate version, too, no?
>
> So it's:
> create schema foo;
> install extension foo with version = 1.2, search_path = foo;
>
> That's fine by me, but I'm not sure whether first extension's
> implementation will support installing several versions of the same
> extension in parallel, so I'm unsure what we get here... one more
> sanity check? I buy it.

Yes, although as I said before, version numbers are hard to get right.
We should keep them very simple, with a strict requirement as to the
simple format (perhaps /\d+[.]\d{2}/) or perhaps NUMERIC or some other
core data type, and then we'd be able to use simple operators:

install extension foo with version = 1.2 OR version >= 1.4,
search_path = foo;

> I don't think we want to cancel user ability to choose schema where
> to install, so an idea could be to ask extensions author to
> systematically use pg_extension (or non-qualify), and PostgreSQL
> could replace this with the INSTALL EXTENSION command schema.

Replace what? How would pg_extension or INSTALL EXTENSION know to
magically schema-qualify the function calls internal to an extension?

>> I like this. Then the build file contains, essentially, just a SQL
>> command. That will make it easy for extension authors. However,
>> they might wish to include quite a lot of other metadata for the
>> extension, such as URLs for VC and bug tracking.
>
> I guess it'll get easy to add those once we agree on the way to go
> here.

I think that people will want to be able to associate arbitrary
metadata. It'd be useful for configuration, too.

> Sold, with current privileges and location restrictions about file
> system access from within the database... does this boils down to
> $PGDATA subdirectory only?

Yes, probably, since the database system user will need to have
permission to access them.

> It'll get as complex as you need it to be, and it's only required
> that it's a PostgreSQL function. I guess writing the plphp upgrade
> function in plphp would be quite challenging, unless we're able to
> guarantee that the newer extension's code won't get loaded before
> until the fonction returned (and didn't RAISE EXCEPTION).

The upgrade function stuff is what I understand least about this
proposal. Can you provide a real-world type example of how it will be
used?

Thanks,

David