Re: Extensions User Design - Mailing list pgsql-hackers

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