PL Code Archive Proposal

Hi,

In my efforts to allow PostgreSQL users to be able to fully use the
server even when not granted file system level access to it, came the
question of PL "lib" code management.

Where do you manage the "library" code you need, those parts of your
code that are not exposed at the SQL level?

For example when doing plpython you still need to install files on the
server's file system each time you want to be able to "import package"
from your Stored Procedures.

The Python community seems to have finally solved that problem and now
offers a facility (called wheel) comparable to Java .jar files, see
details at https://pypi.python.org/pypi/wheel. I don't know about the
Perl and TCL communities.

When thinking about a way to benefit from those facilities in our PL
infrastructure, we would need to be able to upload an "archive" file in
a suitable format and I guess register per-PL handlers for those
archives: storage and loading has to be considered.

CREATE ARCHIVE schema.name
LANGUAGE plpythonu
AS $$
binary blob here, maybe base64 encoded, PL dependent
$$;

The standard saith that in the case of PL/Java a spefific function's
classpath is composed of all those JAR archives that you've been
registering against the same schema as where you put the function in.

If we choose to follow that model then any function created in the same
schema and language as any given archive is going to be able to "import"
things from it: the archive will be LOADed (whatever that means in your
PL of choice) when the function is "compiled" and "used".

Now, uploading a binary file and storing it in $PGDATA looks a lot like
what we're still talking about for the DSO modules bits. So here's
another way to think about it, where we don't need any language feature:

CREATE ARCHIVE schema.name
LANGUAGE plpythonu
WITH 'path/to/component.py' AS $$ … $$,
'path/to/__init__.py' AS $$ … $$,
…;

That would just upload given text/plain contents on the file system and
arrange for the language runtime to be able to use it. For python that
means tweaking PYTHONPATH.

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