Extensions User Design

Hi,

= PostgreSQL Extensions

Here's the first round of User Design about PostgreSQL Extensions. I tried
to put together the ideas expressed by a lot of different people. The aim
here is to first agree on the naming and the goals, then talk about what
user design we propose.

== name

The contenders are extension, module, bundle and package. My vote is
extension.

The module is something else in the SQL standard, a bundle is an ok choice,
a package would certainly make people think we're Oracle compatible (and we
don't want to have Ada like skeleton and bodies), and extension is what PGXS
is make for and what we -you-name-it- authors made.

== v1.0 goals

We're not trying to be feature complete on first round.

* must have

- dump & restore support (when upgrading a cluster or just restoring)

- easy install and uninstall

- support for home grown SQL/PLpgSQL only extensions in order to make life
easier for in-house PG based development (you don't have to code in C to
benefit from extensions)

- support for "basic" modules, providing a type and its operators and
indexing support, such as ip4r, hstore, temporal, prefix and many others,
you name it, of even simpler things like preprepare or
backports/min_update.

- support for procedural languages (a priori easily covered within basic
modules but I'm not sure) like plproxy, pllolcode, pllua, plscheme, plsh
et al.

- support for all what you find in contrib/ for 8.4 (covered already?)

* would be great (target later commit fest)

- versioning support with upgrade in place facility (hooks?)

- supporting more than one version of the same module installed in the same
time, possibly (I suppose always but...) in different schemas

- custom variables?

- PostGIS complete support, with user data dependancy, even if an
extensible typmod system would certainly solve this problem in a better
place. Maybe someone will come up with another existing extension sharing
the problem and not the typmod solution?

- a core team approved list of extensions (replacing contribs, maybe adding
to it), where approved means code has been reviewed and the only reason
why it's not in the core itself is that core team feels that it's not
part of a RDBMS per-se, or feel like the code should be maintained and
released separately until it gets some more field exposure... (think
plproxy).

* later please

- CPAN or ports like infrastructure for auto downloading a more or less
prepared "bundle", place it at the right place on the filesystem and
install it in the database(s) of choice

- complex support for ad-hoc bootstrap of uncommon modules such as pljava

- dependancy graph solving and automatic installation, with depends,
recommends and suggest sections and with rules/setup to choose what to
pull in by default...

== dump & restore

We want pg_dump to issue only one line per extension, the one installing the
extension in the database, see syntax.

== syntax

Extensions will need metadata, and after reading several proposals, what I
propose here is to have a first explicit step to register the extension name
and metadata, then have "basic" tools to play with it.

=== installing and removing an extension

begin;
install extension foo with search_path = foo;
commit;

Extensions authors are asked not to bother about search_path in their sql
scripts so that it's easy for DBAs to decide where to install them. The with
strange syntax is there to allow for the "install extension" command to
default to, e.g., pg_extension, which won't typically be the first schema in
the search_path.

begin;
drop extension foo [cascade];
commit;

The "cascade" option is there to care about reverse depends.

=== creating extensions (authoring)

The 'foo' extension author is meant to provide a +foo.sql+ file containing
this:

create extension foo
with version 1.0
install [script] 'foo.install.sql'
uninstall [script] 'foo.uninstall.sql'
upgrade function upgrade_foo(old version, new version)
[ custom_variable_classes 'a,b'
configuration file 'foo.conf' ]
depends on bar version 0.3
and on baz version >= 1.2;

Here we suppose we have also a new datatype "version" to host the
versionning information, with the associated operators. See
http://packages.debian.org/sid/postgresql-8.3-debversion

Doing it this way, we skip the need to provide a way of telling "next
comands are meant for creating SQL objects which belongs to such extension",
at the expense of forcing authors to manage upgrades to add objects.

The upgrade function is mandatory, and has to return the installed version
or null, meaning "please run the install script again, that's how I
upgrade". The error management is to be made by means of RAISE EXCEPTION.

If a specific function is to get called at install or uninstall time, it's
easy enough to SELECT install_function(); from within the install script,
after having defined it. To support this, internal GUCs (not exposed in
postgresql.conf) will be provided and set by PG when running those scripts,
named current_extension and current_extension_version.

== ACLs

The "bulk" ACL management of an extension's objects is pushed to the globing
support project for GRANT/REVOKE, so we don't have to speak about what it'll
look like here :)

== OS Filesystem Interaction

PostgreSQL already provides standard paths where to install extensions by
means of PGXS, and distribution packagers have been able to adapt those. We
should just stick with this, meaning the problem is solved.

--
dim

PS: using the asciidoc syntax, which allowed me have a nice HTML
browsable document. Hope you don't mind, dear reader.

On Jun 23, 2009, at 10:44 AM, Dimitri Fontaine wrote:

> The contenders are extension, module, bundle and package. My vote is
> extension.

+1

> == v1.0 goals
>
> We're not trying to be feature complete on first round.
>
> * must have
>
> - dump & restore support (when upgrading a cluster or just restoring)
>
> - easy install and uninstall
>
> - support for home grown SQL/PLpgSQL only extensions in order to
> make life
> easier for in-house PG based development (you don't have to code
> in C to
> benefit from extensions)

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).

> - support for "basic" modules, providing a type and its operators and
> indexing support, such as ip4r, hstore, temporal, prefix and many
> others,
> you name it, of even simpler things like preprepare or
> backports/min_update.
>
> - support for procedural languages (a priori easily covered within
> basic
> modules but I'm not sure) like plproxy, pllolcode, pllua,
> plscheme, plsh
> et al.

Oh, here it is. So this goes with the point above, and can be
simplified to "support all procedural languages," yes?

> - support for all what you find in contrib/ for 8.4 (covered already?)
>
> * would be great (target later commit fest)
>
> - versioning support with upgrade in place facility (hooks?)

Yeah, we should standardize versioning somehow to make upgrading
easier. It should be a simple as possible, IMHO. If it tries to do too
much, you get stuck with great complexity.

> - supporting more than one version of the same module installed in
> the same
> time, possibly (I suppose always but...) in different schemas

Eh. This could be in 2.0 I think.

> - custom variables?

You mean GUC variables? That'd certainly be useful, but again,
probably not necessary for 1.0.

> - PostGIS complete support, with user data dependancy, even if an
> extensible typmod system would certainly solve this problem in a
> better
> place. Maybe someone will come up with another existing extension
> sharing
> the problem and not the typmod solution?

Well, PostGIS is itself an extension, no? What we need, then, is
dependency tracking.

> - a core team approved list of extensions (replacing contribs, maybe
> adding
> to it), where approved means code has been reviewed and the only
> reason
> why it's not in the core itself is that core team feels that it's
> not
> part of a RDBMS per-se, or feel like the code should be maintained
> and
> released separately until it gets some more field exposure... (think
> plproxy).

I hate the idea of "approved" extensions, but would love to see a kind
of "standard library" as a separate distribution that contains a bunch
of stuff that's commonly used. I'd want to steer clear of blessing by
the core team other than that, though, because then you start to get
into politics.

> * later please

Yah.

> - CPAN or ports like infrastructure for auto downloading a more or
> less
> prepared "bundle", place it at the right place on the filesystem and
> install it in the database(s) of choice

Yes, this would be nice. Also, integrated testing as with CPAN. I
happen to know of a really nice test framework we could use…

> - complex support for ad-hoc bootstrap of uncommon modules such as
> pljava

Not sure what this means; can you provide more detail?

> - dependancy graph solving and automatic installation, with depends,
> recommends and suggest sections and with rules/setup to choose
> what to
> pull in by default...

We'd likely have to store this information in some sort of system
table, too, yes?

> == dump & restore
>
> We want pg_dump to issue only one line per extension, the one
> installing the
> extension in the database, see syntax.
>
> == syntax
>
> Extensions will need metadata, and after reading several proposals,
> what I
> propose here is to have a first explicit step to register the
> extension name
> and metadata, then have "basic" tools to play with it.

Register with whom? I have to say that, although there is namespace
registration for CPAN, it's not required, and this is, in fact, a big
part of the reason for CPAN's success. There is no approval process
barrier to entry.

> === installing and removing an extension
>
> begin;
> install extension foo with search_path = foo;
> commit;

It would need something to ensure an appropriate version, too, no?

> Extensions authors are asked not to bother about search_path in
> their sql
> scripts so that it's easy for DBAs to decide where to install them.
> The with
> strange syntax is there to allow for the "install extension" command
> to
> default to, e.g., pg_extension, which won't typically be the first
> schema in
> the search_path.

And how will functions that call other functions within an extension
know that they're calling those functions in the appropriate schema? I
get this all the time with pgTAP: You can install it in its own
schema, but you have to include that schema in the search_path in
order for it to work, as some pgTAP functions call other pgTAP
functions with no schema-qualification.

> begin;
> drop extension foo [cascade];
> commit;
>
> The "cascade" option is there to care about reverse depends.

Would it fail if there were dependencies on the module in the
database, such as functions that use its functions, or tables that
depend on a custom data type?

> === creating extensions (authoring)
>
> The 'foo' extension author is meant to provide a +foo.sql+ file
> containing
> this:
>
> create extension foo
> with version 1.0
> install [script] 'foo.install.sql'
> uninstall [script] 'foo.uninstall.sql'
> upgrade function upgrade_foo(old version, new version)
> [ custom_variable_classes 'a,b'
> configuration file 'foo.conf' ]
> depends on bar version 0.3
> and on baz version >= 1.2;
>
> Here we suppose we have also a new datatype "version" to host the
> versionning information, with the associated operators. See
> http://packages.debian.org/sid/postgresql-8.3-debversion

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.

> Doing it this way, we skip the need to provide a way of telling "next
> comands are meant for creating SQL objects which belongs to such
> extension",
> at the expense of forcing authors to manage upgrades to add objects.

The install and uninstall script attributes should also allow either
full paths or, if just a simple file name, paths to the extensions
installation directory (currently $PGSQL/share/contrib).

> The upgrade function is mandatory, and has to return the installed
> version
> or null, meaning "please run the install script again, that's how I
> upgrade". The error management is to be made by means of RAISE
> EXCEPTION.

I'm not following you here. If I have a bunch of releases with a
number of changes to them, this function could get quite complex, I
should think. Also, in what language could it be written?

> If a specific function is to get called at install or uninstall
> time, it's
> easy enough to SELECT install_function(); from within the install
> script,
> after having defined it. To support this, internal GUCs (not exposed
> in
> postgresql.conf) will be provided and set by PG when running those
> scripts,
> named current_extension and current_extension_version.

Nice.

> == ACLs
>
> The "bulk" ACL management of an extension's objects is pushed to the
> globing
> support project for GRANT/REVOKE, so we don't have to speak about
> what it'll
> look like here :)
>
> == OS Filesystem Interaction
>
> PostgreSQL already provides standard paths where to install
> extensions by
> means of PGXS, and distribution packagers have been able to adapt
> those. We
> should just stick with this, meaning the problem is solved.

Sounds fine to me.

Best,

David

Le 23 juin 09 à 20:30, David E. Wheeler a écrit :

> On Jun 23, 2009, at 10:44 AM, Dimitri Fontaine wrote:
>> - support for home grown SQL/PLpgSQL only extensions in order to
>> make life
>> easier for in-house PG based development (you don't have to code
>> in C to
>> benefit from extensions)
>
> 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.

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.

>> - support for procedural languages (a priori easily covered within
>> basic
>> modules but I'm not sure) like plproxy, pllolcode, pllua,
>> plscheme, plsh
>> et al.
>
> Oh, here it is. So this goes with the point above, and can be
> simplified to "support all procedural languages," yes?

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.

>> - supporting more than one version of the same module installed in
>> the same
>> time, possibly (I suppose always but...) in different schemas
>
> Eh. This could be in 2.0 I think.

Yeah, my point exactly.

>> - custom variables?
>
> You mean GUC variables? That'd certainly be useful, but again,
> probably not necessary for 1.0.

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

> Well, PostGIS is itself an extension, no? What we need, then, is
> dependency tracking.

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.

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?

>> - a core team approved list of extensions (replacing contribs,
>> maybe adding
>> to it), where approved means code has been reviewed and the only
>> reason
>> why it's not in the core itself is that core team feels that it's
>> not
>> part of a RDBMS per-se, or feel like the code should be maintained
>> and
>> released separately until it gets some more field exposure... (think
>> plproxy).
>
> I hate the idea of "approved" extensions, but would love to see a
> kind of "standard library" as a separate distribution that contains
> a bunch of stuff that's commonly used. I'd want to steer clear of
> blessing by the core team other than that, though, because then you
> start to get into politics.

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.

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.

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).

>> * later please
> Yes, this would be nice. Also, integrated testing as with CPAN. I
> happen to know of a really nice test framework we could use…

hehe

>> - complex support for ad-hoc bootstrap of uncommon modules such as
>> pljava
>
> Not sure what this means; can you provide more detail?

See above.

>> - dependancy graph solving and automatic installation, with depends,
>> recommends and suggest sections and with rules/setup to choose
>> what to
>> pull in by default...
>
> We'd likely have to store this information in some sort of system
> table, too, yes?

Yes, that'd be part of the extension "meta data".

>> Extensions will need metadata, and after reading several proposals,
>> what I
>> propose here is to have a first explicit step to register the
>> extension name
>> and metadata, then have "basic" tools to play with it.
>
> Register with whom? I have to say that, although there is namespace
> registration for CPAN, it's not required, and this is, in fact, a
> big part of the reason for CPAN's success. There is no approval
> process barrier to entry.

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?).

>> === installing and removing an extension
>>
>> begin;
>> install extension foo with search_path = foo;
>> commit;
>
> 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.

>> Extensions authors are asked not to bother about search_path in
>> their sql
>> scripts so that it's easy for DBAs to decide where to install them.
>> The with
>> strange syntax is there to allow for the "install extension"
>> command to
>> default to, e.g., pg_extension, which won't typically be the first
>> schema in
>> the search_path.
>
> And how will functions that call other functions within an extension
> know that they're calling those functions in the appropriate schema?
> I get this all the time with pgTAP: You can install it in its own
> schema, but you have to include that schema in the search_path in
> order for it to work, as some pgTAP functions call other pgTAP
> functions with no schema-qualification.

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.

>> begin;
>> drop extension foo [cascade];
>> commit;
>>
>> The "cascade" option is there to care about reverse depends.
>
> Would it fail if there were dependencies on the module in the
> database, such as functions that use its functions, or tables that
> depend on a custom data type?

Yes, when you don't use the CASCADE keyword.

>> === creating extensions (authoring)
>>
>> The 'foo' extension author is meant to provide a +foo.sql+ file
>> containing
>> this:
>>
>> create extension foo
>> with version 1.0
>> install [script] 'foo.install.sql'
>> uninstall [script] 'foo.uninstall.sql'
>> upgrade function upgrade_foo(old version, new version)
>> [ custom_variable_classes 'a,b'
>> configuration file 'foo.conf' ]
>> depends on bar version 0.3
>> and on baz version >= 1.2;
>>
>> Here we suppose we have also a new datatype "version" to host the
>> versionning information, with the associated operators. See
>> http://packages.debian.org/sid/postgresql-8.3-debversion
>
> 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.

>> Doing it this way, we skip the need to provide a way of telling "next
>> comands are meant for creating SQL objects which belongs to such
>> extension",
>> at the expense of forcing authors to manage upgrades to add objects.
>
> The install and uninstall script attributes should also allow either
> full paths or, if just a simple file name, paths to the extensions
> installation directory (currently $PGSQL/share/contrib).

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

>> The upgrade function is mandatory, and has to return the installed
>> version
>> or null, meaning "please run the install script again, that's how I
>> upgrade". The error management is to be made by means of RAISE
>> EXCEPTION.
>
> I'm not following you here. If I have a bunch of releases with a
> number of changes to them, this function could get quite complex, I
> should think. Also, in what language could it be written?

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).

Regards,
--
dim

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

[Skipping most of it as I'd like to read what other people think about
it before going in lengthy thread already] :)

Le 23 juin 09 à 23:41, David E. Wheeler a écrit :
> 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;

If we happen to accept the debian policy versioning scheme, then the
hard work is already done for us, it seems:
http://packages.debian.org/fr/sid/postgresql-8.3-debversion

>> 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?

It's "just" PostgreSQL reading an SQL file (foo.install.sql) and
parsing each statement etc, so we obviously have the machinery to
recognize SQL objects names and schema qualification. Replacing the
schema on-the-fly should be a SMOP? (*cough*)

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

Oh, you want EAV already? Or maybe a supplementary hstore column into
the pg_extension catalog... but I guess we can't have this dependancy :)

> 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?

You provide a function upgrade(old, new) where parameters are version
numbers. The body of the (typically plpgsql) function should implement
the ALTER TABLE or CREATE OR REPLACE FUNCTION stuff you need to do,
with some conditions on the version numbers.

I expect people would write a upgrade_10_to_11() function then call it
from upgrade() when old = 1.0 and new = 1.1, for example.

Maybe we should also provide some support functions to run the install
and uninstall script, and some more facilities, so that you could
implement as follow:
BEGIN
-- loop over columns storing data from our type
FOR s, t, c IN SELECT nspname, relname, attname
FROM pg_find_columns('mytype'::regclass)
LOOP
EXECUTE 'ALTER TABLE $1.$2 ALTER COLUMN $3 TYPE text USING
mycast($3)'
USING s, t, c;
END LOOP;

PERFORM pg_extension_uninstall('foo', old);
PERFORM pg_extension_install('foo', new);

-- ALTER TYPE the other way round
END;

Some other stuff could be needed to check about indexes to, storing a
list of them in a temp table then recreating them, but it seems to me
you can already hand craft the catalog queries now. But as it becomes
common practise, we might want to offer them in a more ready for
public consumption way.

Regards,
--
dim

On Jun 23, 2009, at 3:02 PM, Dimitri Fontaine wrote:

> If we happen to accept the debian policy versioning scheme, then the
> hard work is already done for us, it seems:
> http://packages.debian.org/fr/sid/postgresql-8.3-debversion

As long as we don't need to implement a new data type, fine.

>> Replace what? How would pg_extension or INSTALL EXTENSION know to
>> magically schema-qualify the function calls internal to an extension?
>
> It's "just" PostgreSQL reading an SQL file (foo.install.sql) and
> parsing each statement etc, so we obviously have the machinery to
> recognize SQL objects names and schema qualification. Replacing the
> schema on-the-fly should be a SMOP? (*cough*)

Well, no. I might have written a function in PL/Perl. Is PostgreSQL
going to parse my Perl function for unqualified function calls?
Really? Hell, I don't think that PL/pgSQL is parsed until functions
are loaded, either, though I may be wrong about that.

Better is to have some magic so that functions in an extension
magically have their schema put onto the front of search_path when
they're called. Or when they're compiled. Or something.

> Oh, you want EAV already? Or maybe a supplementary hstore column
> into the pg_extension catalog... but I guess we can't have this
> dependancy :)

No, but a simple key/value table with an FK constraint should be
sufficient for non-core metadata.

>> 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?
>
> You provide a function upgrade(old, new) where parameters are
> version numbers. The body of the (typically plpgsql) function should
> implement the ALTER TABLE or CREATE OR REPLACE FUNCTION stuff you
> need to do, with some conditions on the version numbers.

Well, CREATE OR REPLACE FUNCTION is probably already in my install.sql
file. But I could see dropping deprecated functions and, of course,
altering tables.

> I expect people would write a upgrade_10_to_11() function then call
> it from upgrade() when old = 1.0 and new = 1.1, for example.

Okay, that makes sense.

> Maybe we should also provide some support functions to run the
> install and uninstall script, and some more facilities, so that you
> could implement as follow:
> BEGIN
> -- loop over columns storing data from our type
> FOR s, t, c IN SELECT nspname, relname, attname
> FROM pg_find_columns('mytype'::regclass)
> LOOP
> EXECUTE 'ALTER TABLE $1.$2 ALTER COLUMN $3 TYPE text USING
> mycast($3)'
> USING s, t, c;
> END LOOP;
>
> PERFORM pg_extension_uninstall('foo', old);
> PERFORM pg_extension_install('foo', new);
>
> -- ALTER TYPE the other way round
> END;
>
> Some other stuff could be needed to check about indexes to, storing
> a list of them in a temp table then recreating them, but it seems to
> me you can already hand craft the catalog queries now. But as it
> becomes common practise, we might want to offer them in a more ready
> for public consumption way.

Yes, whatever tools we can provide to make things easier for extension
authors/maintainers, the better. But I recognize that we might have to
wait and see what cow paths develop.

Best,

David

David E. Wheeler wrote:
> On Jun 23, 2009, at 3:02 PM, Dimitri Fontaine wrote:
>> It's "just" PostgreSQL reading an SQL file (foo.install.sql) and
>> parsing each statement etc, so we obviously have the machinery to
>> recognize SQL objects names and schema qualification. Replacing the
>> schema on-the-fly should be a SMOP? (*cough*)
>
> Well, no. I might have written a function in PL/Perl. Is PostgreSQL
> going to parse my Perl function for unqualified function calls? Really?
> Hell, I don't think that PL/pgSQL is parsed until functions are loaded,
> either, though I may be wrong about that.
>
> Better is to have some magic so that functions in an extension magically
> have their schema put onto the front of search_path when they're called.
> Or when they're compiled. Or something.

With the given example of extension "foo" depending on "bar" and "baz",
I'd suggest:
- Default search_path = ext:self, pg_catalog
- ext:self = <wherever foo installs>
- ext:bar = <wherever bar installs>
- ext:baz = <wherever baz installs>
You *can't* have anything other than the current package in the
search-path in case bar/baz have conflicting objects.

I've no idea if ext:<name> makes sense from a parser point of view, but
the idea is to map extension name to a schema. If possible, this should
work anywhere in PG that a schema can be specified.

So - If extension foo is installed in schema1 then ext:foo.fn1() is the
same as schema1.fn1()

--
Richard Huxton
Archonet Ltd

On Tue, Jun 23, 2009 at 12:44 PM, Dimitri
Fontaine<dfontaine(at)hi-media(dot)com> wrote:
>
>  - a core team approved list of extensions (replacing contribs,

are you aware of the enormous job that will imply for core team?
maybe a community approved list of extensions or maybe we can have
some kind of jury (just like patch reviewers) that could test and mark
as tested... i remember a conversation about this very point

--
Atentamente,
Jaime Casanova
Soporte y capacitación de PostgreSQL
Asesoría y desarrollo de sistemas
Guayaquil - Ecuador
Cel. +59387171157

Jaime Casanova <jcasanov(at)systemguards(dot)com(dot)ec> writes:

> On Tue, Jun 23, 2009 at 12:44 PM, Dimitri
> Fontaine<dfontaine(at)hi-media(dot)com> wrote:
>>
>>  - a core team approved list of extensions (replacing contribs,
>
> are you aware of the enormous job that will imply for core team?

I'm not saying I want core to take care of all pgfoundry projects that
will make them available as extensions, but to have contrib/ made
extensions and have core if possible continue doing what they have been
doing... forever?

> maybe a community approved list of extensions or maybe we can have
> some kind of jury (just like patch reviewers) that could test and mark
> as tested... i remember a conversation about this very point

Having other sources of extensions apart from contrib seems to me a very
good idea.

--
Dimitri Fontaine
PostgreSQL DBA, Architecte

On Jun 24, 2009, at 12:59 AM, Dimitri Fontaine wrote:

>> are you aware of the enormous job that will imply for core team?
>
> I'm not saying I want core to take care of all pgfoundry projects that
> will make them available as extensions, but to have contrib/ made
> extensions and have core if possible continue doing what they have
> been
> doing... forever?

Right, in an independent distribution.

Best,

David

Dim,

> The contenders are extension, module, bundle and package. My vote is
> extension.

+1 on "extension".

> We're not trying to be feature complete on first round.
>
> * must have
>
>
> - support for all what you find in contrib/ for 8.4 (covered already?)

... most of. Some of the things in contrib are largely examples or
hacker tools; if we don't cover those it's OK.

> * would be great (target later commit fest)
>
> - versioning support with upgrade in place facility (hooks?)

We need versioning support right now, separate from any UIP support.
Otherwise the dump/reload won't work.

> - supporting more than one version of the same module installed in the same
> time, possibly (I suppose always but...) in different schemas

We can put this off until we have a use-case for it. I can't imagine one.

> - custom variables?

Don't we have these already?

> - PostGIS complete support, with user data dependancy, even if an
> extensible typmod system would certainly solve this problem in a better
> place. Maybe someone will come up with another existing extension sharing
> the problem and not the typmod solution?

Or we just fix that issue for 8.5.

> - a core team approved list of extensions (replacing contribs, maybe adding
> to it), where approved means code has been reviewed and the only reason
> why it's not in the core itself is that core team feels that it's not
> part of a RDBMS per-se, or feel like the code should be maintained and
> released separately until it gets some more field exposure... (think
> plproxy).

The core team isn't appropriate for this. We'd start a new
committee/list somewhere instead, and it would be part of the same
effort which produces a "recommended" list of extensions and drivers for
packagers.

> - CPAN or ports like infrastructure for auto downloading a more or less
> prepared "bundle", place it at the right place on the filesystem and
> install it in the database(s) of choice

This may not be necessary if simple download-unzip-and-install is simple
enough.

> - complex support for ad-hoc bootstrap of uncommon modules such as pljava
>
> - dependancy graph solving and automatic installation, with depends,
> recommends and suggest sections and with rules/setup to choose what to
> pull in by default...

Uh-huh. That'll be the day ...

> === installing and removing an extension
>
> begin;
> install extension foo with search_path = foo;

Needs install file location:

INSTALL EXTENSION foo FROM '~/downloads/foo' WITH search_path = 'foo';

> == OS Filesystem Interaction
>
> PostgreSQL already provides standard paths where to install extensions by
> means of PGXS, and distribution packagers have been able to adapt those. We
> should just stick with this, meaning the problem is solved.

I think that the user should be able to put the extension file download
anywhere in their filesystem, and on install PostgreSQL should copy the
files to the appropriate place. That is, they shouldn't have to first
copy the files to /pg_source_dir/contrib/. Maybe you had that covered,
but I didn't see it explicitly.

Also, this means that we'll want to make sure that PGXS is included in
all existing packages of PostgresQL. Is it?

--
Josh Berkus
PostgreSQL Experts Inc.
www.pgexperts.com

Josh Berkus wrote:
>
>> - a core team approved list of extensions (replacing contribs,
>> maybe adding
>> to it), where approved means code has been reviewed and the only
>> reason
>> why it's not in the core itself is that core team feels that it's
>> not
>> part of a RDBMS per-se, or feel like the code should be
>> maintained and
>> released separately until it gets some more field exposure... (think
>> plproxy).
>
> The core team isn't appropriate for this. We'd start a new
> committee/list somewhere instead, and it would be part of the same
> effort which produces a "recommended" list of extensions and drivers
> for packagers.
>
>

Actually, I think we should be like Perl here. There is a list of
standard modules that comes with the base Perl distro, and then there
are addons, such as you find on CPAN. File::Find is an example of a
standard module, DBD::Pg is an example of an addon.

Quite apart from anything else, having some extensions maintained by
core will help in validating the extension mechanism.

Good candidates for core-supported extensions would include
PL{Perl,Python,Tcl}, pgcrypto and hstore, IMNSHO. Between them they
illustrate a number of the major extension paradigms.

Beyond standard extensions, I'm not sure we need a committee to
"approve" extensions. Does Perl have such an animal? I'm fairly wary of
creating new decision-making bureaucracies.

cheers

andrew

Le 24 juin 09 à 22:43, Josh Berkus a écrit :
> ... most of. Some of the things in contrib are largely examples or
> hacker tools; if we don't cover those it's OK.

Good to know.

> We need versioning support right now, separate from any UIP support.
> Otherwise the dump/reload won't work.

You want pg_dump to issue an INSTALL EXTENSION command with specific
version needed, right?

>> - supporting more than one version of the same module installed in
>> the same
>> time, possibly (I suppose always but...) in different schemas
>
> We can put this off until we have a use-case for it. I can't
> imagine one.

Good for me :)

>> - custom variables?
>
> Don't we have these already?

It's a matter of exposing a way to attach them to a specific
extension. Are GUCs a possible element of pg_depend?

>> - PostGIS complete support, with user data dependancy, even if an
>> extensible typmod system would certainly solve this problem in a
>> better
>> place. Maybe someone will come up with another existing
>> extension sharing
>> the problem and not the typmod solution?
>
> Or we just fix that issue for 8.5.

That'd make my day.

>> - a core team approved list of extensions (replacing contribs,
>> maybe adding
>> to it), where approved means code has been reviewed and the only
>> reason
>> why it's not in the core itself is that core team feels that
>> it's not
>> part of a RDBMS per-se, or feel like the code should be
>> maintained and
>> released separately until it gets some more field exposure...
>> (think
>> plproxy).
>
> The core team isn't appropriate for this. We'd start a new
> committee/list somewhere instead, and it would be part of the same
> effort which produces a "recommended" list of extensions and drivers
> for packagers.

It'd still deprecate contrib/, which could maybe become examples/?

>> - CPAN or ports like infrastructure for auto downloading a more or
>> less
>> prepared "bundle", place it at the right place on the filesystem
>> and
>> install it in the database(s) of choice
>
> This may not be necessary if simple download-unzip-and-install is
> simple enough.

I hope it'll get simple enough, yes, as simple as current PGXS modules
from source are:
- cvs up or wget
- tar xzf ... && cd ...
- make install
- psql -f ... mydb

>> begin;
>> install extension foo with search_path = foo;
>
> Needs install file location:

No, extensions meta-data are in foo.sql and already loaded into the
database by the time you get to INSTALL EXTENSION. That's a part I
like because it makes it simple to handle meta-data and to declare
that SQL objects from the script are part of the extension.
I also dislike the CREATE EXTENSION which is not INSTALLing it...
maybe a WITH INSTALL syntax option could do?

>> PostgreSQL already provides standard paths where to install
>> extensions by
>> means of PGXS, and distribution packagers have been able to adapt
>> those. We
>> should just stick with this, meaning the problem is solved.
>
> I think that the user should be able to put the extension file
> download anywhere in their filesystem, and on install PostgreSQL
> should copy the files to the appropriate place. That is, they
> shouldn't have to first copy the files to /pg_source_dir/contrib/.
> Maybe you had that covered, but I didn't see it explicitly.

PGXS has it covered, and we're not yet there, but I'm thinking PGXS
should be a pre requisite of the extension facility as far as
extensions authors are concerned. Then packagers will make it so that
users won't typically face those details.

> Also, this means that we'll want to make sure that PGXS is included
> in all existing packages of PostgresQL. Is it?

Only those packages you want to have extension support from source ;)
--
dim

Le 24 juin 09 à 23:07, Andrew Dunstan a écrit :
> Actually, I think we should be like Perl here. There is a list of
> standard modules that comes with the base Perl distro, and then
> there are addons, such as you find on CPAN. File::Find is an example
> of a standard module, DBD::Pg is an example of an addon.

Agreed.

> Quite apart from anything else, having some extensions maintained by
> core will help in validating the extension mechanism.
>
> Good candidates for core-supported extensions would include
> PL{Perl,Python,Tcl}, pgcrypto and hstore, IMNSHO. Between them they
> illustrate a number of the major extension paradigms.

That read as a good start, even if I'd maybe like to add ltree and
plproxy, maybe more for convenience than anything else.

> Beyond standard extensions, I'm not sure we need a committee to
> "approve" extensions. Does Perl have such an animal? I'm fairly wary
> of creating new decision-making bureaucracies.

I think what Josh is referring too is to have the standard core
extensions whose aim is to show how extensions work, provided
maintained examples etc, *and* a community list of useful extensions
(temporal, prefix, oracfe, pgtap, you name it) that users will
probably want to find.
This list will have to provide some more information, ones that are
implicit within the first group: is the software maintained, by whom,
is it production ready, feature complete, is it a community endorsed
product, etc.

While I'm all for avoiding bureaucracy, I'd like us to be able to show
how rich and trustworthy the PostgreSQL overall solution and community
is. Core-supported extensions won't allow that on their own.

Regards,
--
dim

Andrew,

> Actually, I think we should be like Perl here. There is a list of
> standard modules that comes with the base Perl distro, and then there
> are addons, such as you find on CPAN. File::Find is an example of a
> standard module, DBD::Pg is an example of an addon.

Actually, chromatic, Allison, etc. regard the Standard Modules as a
mistake and are talking about moving away from having any for Perl 6.

On the other hand, their main reason for doing this (the issues with
maintaining the included version and the CPAN version separately)
wouldn't apply to us.

On the third hand, having "all modules equal, just some recommended"
approach woudl make it far easier to drop a module which went
unmaintained, e.g. CUBE. But some people may regard this as a misfeature.

--
Josh Berkus
PostgreSQL Experts Inc.
www.pgexperts.com

On Jun 24, 2009, at 2:07 PM, Andrew Dunstan wrote:

> Actually, I think we should be like Perl here. There is a list of
> standard modules that comes with the base Perl distro, and then
> there are addons, such as you find on CPAN.

Actually, the lesson slowly emerging in the Perl community is that
there should be very few extensions distributed with the core, as
keeping them in sync is a giant PITA and in part responsible for the
duration of time between releases.

A separate distribution of "recommended extensions" would fill the
same need, but not bind core to the schedule of extension updates.

> Beyond standard extensions, I'm not sure we need a committee to
> "approve" extensions. Does Perl have such an animal? I'm fairly wary
> of creating new decision-making bureaucracies.

Agreed. Perl does not have such a thing. PHP does, and it's no doubt
part of the reason that PEAR has so few modules.

Best,

David

On Jun 24, 2009, at 2:12 PM, Dimitri Fontaine wrote:

>> The core team isn't appropriate for this. We'd start a new
>> committee/list somewhere instead, and it would be part of the same
>> effort which produces a "recommended" list of extensions and
>> drivers for packagers.
>
> It'd still deprecate contrib/, which could maybe become examples/?

No, it would not be distributed with core at all. They could all be
packaged up together in a single distribution of recommended modules,
however.

>> This may not be necessary if simple download-unzip-and-install is
>> simple enough.
>
> I hope it'll get simple enough, yes, as simple as current PGXS
> modules from source are:
> - cvs up or wget
> - tar xzf ... && cd ...
> - make install
> - psql -f ... mydb

Then it could also be easily scripted, too.

> PGXS has it covered, and we're not yet there, but I'm thinking PGXS
> should be a pre requisite of the extension facility as far as
> extensions authors are concerned. Then packagers will make it so
> that users won't typically face those details.

+1.

Best,

David

Josh Berkus wrote:
> Andrew,
>
>> Actually, I think we should be like Perl here. There is a list of
>> standard modules that comes with the base Perl distro, and then there
>> are addons, such as you find on CPAN. File::Find is an example of a
>> standard module, DBD::Pg is an example of an addon.
>
> Actually, chromatic, Allison, etc. regard the Standard Modules as a
> mistake and are talking about moving away from having any for Perl 6.
>
> On the other hand, their main reason for doing this (the issues with
> maintaining the included version and the CPAN version separately)
> wouldn't apply to us.

I agree they have too many. I think moving to none would be a mistake,
though. Would they even drop things like Dynaloader or
ExtUtils::MakeMaker? That would be crazy, IMNSHO. I think there's a
sweet spot here and we are not very far away from it in the number of
things we currently ship.

>
> On the third hand, having "all modules equal, just some recommended"
> approach woudl make it far easier to drop a module which went
> unmaintained, e.g. CUBE. But some people may regard this as a
> misfeature.

I would happily push cube out of the nest now :-)

cheers

andrew

On Jun 24, 2009, at 2:41 PM, Andrew Dunstan wrote:

> I agree they have too many. I think moving to none would be a
> mistake, though. Would they even drop things like Dynaloader or
> ExtUtils::MakeMaker? That would be crazy, IMNSHO. I think there's a
> sweet spot here and we are not very far away from it in the number
> of things we currently ship.

They want to drop everything except for tools to download, build,
test, and install other modules. That's the limitation.

Best,

David

David E. Wheeler wrote:
> On Jun 24, 2009, at 2:41 PM, Andrew Dunstan wrote:
>
>> I agree they have too many. I think moving to none would be a
>> mistake, though. Would they even drop things like Dynaloader or
>> ExtUtils::MakeMaker? That would be crazy, IMNSHO. I think there's a
>> sweet spot here and we are not very far away from it in the number of
>> things we currently ship.
>
> They want to drop everything except for tools to download, build,
> test, and install other modules. That's the limitation.
>
>

Well, I think in our case that would be going too far. I think there is
a very good case for keeping a few key extensions in core both as
exemplars and to make it easy to validate the extension mechanism
itself. There have been suggestions in the past about throwing a bunch
of things overboard, sometimes out of a passion for neatness more than
anything else ISTM, but there have been good arguments against as well,
particularly in the case of the PLs, which are tied so closely to the
backend.

cheers

andrew

On Wed, Jun 24, 2009 at 4:07 PM, Andrew Dunstan<andrew(at)dunslane(dot)net> wrote:
>
> Beyond standard extensions, I'm not sure we need a committee to "approve"
> extensions. Does Perl have such an animal? I'm fairly wary of creating new
> decision-making bureaucracies.
>

not "approve", just mark it as something like: "tested with pg vX.XX",
"not ready for production", etc...

--
Atentamente,
Jaime Casanova
Soporte y capacitación de PostgreSQL
Asesoría y desarrollo de sistemas
Guayaquil - Ecuador
Cel. +59387171157

On Jun 24, 2009, at 3:09 PM, Andrew Dunstan wrote:

> Well, I think in our case that would be going too far. I think there
> is a very good case for keeping a few key extensions in core both as
> exemplars and to make it easy to validate the extension mechanism
> itself. There have been suggestions in the past about throwing a
> bunch of things overboard, sometimes out of a passion for neatness
> more than anything else ISTM, but there have been good arguments
> against as well, particularly in the case of the PLs, which are tied
> so closely to the backend.

Exemplars are good if they behave in the same way as non-core
extensions. So it might be good for the core to maintain contrib
extensions, although I would urge them to keep the size down quite
low, and to be very conservative about adding new extensions. Part of
the issue Perl ran into is that it was too liberal about adding new
stuff to core, especially modules with large dependency trees.
Anything in core should be kept very simple, both to avoid bloat and
to minimize the maintenance overhead for the core team.

Best,

David

David E. Wheeler wrote:
> On Jun 24, 2009, at 3:09 PM, Andrew Dunstan wrote:
>
>> Well, I think in our case that would be going too far. I think there
>> is a very good case for keeping a few key extensions in core both as
>> exemplars and to make it easy to validate the extension mechanism
>> itself. There have been suggestions in the past about throwing a
>> bunch of things overboard, sometimes out of a passion for neatness
>> more than anything else ISTM, but there have been good arguments
>> against as well, particularly in the case of the PLs, which are tied
>> so closely to the backend.
>
> Exemplars are good if they behave in the same way as non-core
> extensions. So it might be good for the core to maintain contrib
> extensions, although I would urge them to keep the size down quite
> low, and to be very conservative about adding new extensions. Part of
> the issue Perl ran into is that it was too liberal about adding new
> stuff to core, especially modules with large dependency trees.
> Anything in core should be kept very simple, both to avoid bloat and
> to minimize the maintenance overhead for the core team.
>
>

We have been conservative about this in the past and there is no reason
to expect we will not be in the future. If anything, we are likely to
become more so.

cheers

andrew

On Jun 24, 2009, at 3:41 PM, Andrew Dunstan wrote:

> We have been conservative about this in the past and there is no
> reason to expect we will not be in the future. If anything, we are
> likely to become more so.

Good, perfect.

Best,

David

Re-reading in the morning rather that late at night...

Josh Berkus <josh(at)agliodbs(dot)com> writes:
>> === installing and removing an extension
>>
>> begin;
>> install extension foo with search_path = foo;
>
> Needs install file location:
>
> INSTALL EXTENSION foo FROM '~/downloads/foo' WITH search_path = 'foo';

This would run the foo.sql file containing the CREATE EXTENSION call,
then run the install procedure itself which will run the author's
foo.install.sql script, right? I'm all for it.

Now, I'm not sure which form would pg_dump issue, maybe it should dumps
the CREATE EXTENSION and the INSTALL EXTENSION commands separately?

--
Dimitri Fontaine
PostgreSQL DBA, Architecte

On Wed, Jun 24, 2009 at 9:43 PM, Josh Berkus<josh(at)agliodbs(dot)com> wrote:

>> == OS Filesystem Interaction
>>
>> PostgreSQL already provides standard paths where to install extensions by
>> means of PGXS, and distribution packagers have been able to adapt those.
>> We
>> should just stick with this, meaning the problem is solved.
>
> I think that the user should be able to put the extension file download
> anywhere in their filesystem, and on install PostgreSQL should copy the
> files to the appropriate place.  That is, they shouldn't have to first copy
> the files to /pg_source_dir/contrib/.  Maybe you had that covered, but I
> didn't see it explicitly.
>
> Also, this means that we'll want to make sure that PGXS is included in all
> existing packages of PostgresQL.  Is it?

Apologies if I missed further discussion on this - I'm somewhat
distracted with release preparations at the moment...

PGXS is essentially useless on Windows unless you're compiling your
own code using Mingw/msys, which will be a miniscule percentage of
users. Our installers for 8.3 and above are all built using VC++.

--
Dave Page
EnterpriseDB UK: http://www.enterprisedb.com

Dave Page <dpage(at)pgadmin(dot)org> writes:

> On Wed, Jun 24, 2009 at 9:43 PM, Josh Berkus<josh(at)agliodbs(dot)com> wrote:
>> Also, this means that we'll want to make sure that PGXS is included in all
>> existing packages of PostgresQL.  Is it?
>
> Apologies if I missed further discussion on this - I'm somewhat
> distracted with release preparations at the moment...

Yeah, wasn't the best timing for me to open the thread, but I've been
sitting on it what seemed far too much time...

> PGXS is essentially useless on Windows unless you're compiling your
> own code using Mingw/msys, which will be a miniscule percentage of
> users. Our installers for 8.3 and above are all built using VC++.

I guess we'll have to see how the windows world installers are
comparable / compatible with PGXS here, I confess I know nothing about
them...

Is it possible to design this part of the extension system with only
PGXS in mind and later adapt the windows toolsuite?

Regards,
--
dim

On Thu, Jun 25, 2009 at 9:58 AM, Dimitri Fontaine<dfontaine(at)hi-media(dot)com> wrote:
> Dave Page <dpage(at)pgadmin(dot)org> writes:
>> PGXS is essentially useless on Windows unless you're compiling your
>> own code using Mingw/msys, which will be a miniscule percentage of
>> users. Our installers for 8.3 and above are all built using VC++.
>
> I guess we'll have to see how the windows world installers are
> comparable / compatible with PGXS here, I confess I know nothing about
> them...
>
> Is it possible to design this part of the extension system with only
> PGXS in mind and later adapt the windows toolsuite?

Anything is possible :-). Better to ask someone with more perl
expertise than me how much effort it might take to have the VC++ build
system be able to create a project from an arbitrary PGXS makefile.
Andrew or Magnus would seem the obvious people.

--
Dave Page
EnterpriseDB UK: http://www.enterprisedb.com

Dave Page wrote:
> On Thu, Jun 25, 2009 at 9:58 AM, Dimitri Fontaine<dfontaine(at)hi-media(dot)com> wrote:
>> Dave Page <dpage(at)pgadmin(dot)org> writes:
>>> PGXS is essentially useless on Windows unless you're compiling your
>>> own code using Mingw/msys, which will be a miniscule percentage of
>>> users. Our installers for 8.3 and above are all built using VC++.
>> I guess we'll have to see how the windows world installers are
>> comparable / compatible with PGXS here, I confess I know nothing about
>> them...
>>
>> Is it possible to design this part of the extension system with only
>> PGXS in mind and later adapt the windows toolsuite?
>
> Anything is possible :-). Better to ask someone with more perl
> expertise than me how much effort it might take to have the VC++ build
> system be able to create a project from an arbitrary PGXS makefile.
> Andrew or Magnus would seem the obvious people.

We do it for Makefiles in contrib, so in theory it should be doable.

The problem is, I think, that the Makefile format is way too flexible.
You can write anything as shell commands in there, and there is no way
we can ever parse that and make it work in the msvc build system. I
haven't read the pgxs docs in a while so I don't know if it makes
restrictions on this, but AFAIK there are no technical reasons
preventing people from doing this.

--
Magnus Hagander
Self: http://www.hagander.net/
Work: http://www.redpill-linpro.com/

Magnus Hagander <magnus(at)hagander(dot)net> writes:
> We do it for Makefiles in contrib, so in theory it should be doable.

Excellent!

> The problem is, I think, that the Makefile format is way too flexible.
> You can write anything as shell commands in there, and there is no way
> we can ever parse that and make it work in the msvc build system. I
> haven't read the pgxs docs in a while so I don't know if it makes
> restrictions on this, but AFAIK there are no technical reasons
> preventing people from doing this.

Well if the consequence of using random unix invocations in the Makefile
(which is otherwise quite short for simple extensions) is that the
extension won't work in windows, that means the burden is on the
extension author. We can't force them to write windows compatible code
in the first place, I presume.

It sounds like PGXS dependancy is the way to go, knowing that some perl
magic will have to get from the Makefile to the .project. Right?
--
dim

Magnus Hagander <magnus(at)hagander(dot)net> writes:
>> On Thu, Jun 25, 2009 at 9:58 AM, Dimitri Fontaine<dfontaine(at)hi-media(dot)com> wrote:
>>> Is it possible to design this part of the extension system with only
>>> PGXS in mind and later adapt the windows toolsuite?

> We do it for Makefiles in contrib, so in theory it should be doable.

> The problem is, I think, that the Makefile format is way too flexible.

I think the contrib makefiles are considered to be our standard test
suite for PGXS. If a 3rd-party makefile is doing anything not
represented in contrib, it's not guaranteed to work anyway. So I'd
be plenty satisfied if we just made the existing contrib infrastructure
work for 3rd-party modules.

regards, tom lane

On Jun 25, 2009, at 2:21 AM, Dave Page wrote:

>> Is it possible to design this part of the extension system with only
>> PGXS in mind and later adapt the windows toolsuite?
>
> Anything is possible :-). Better to ask someone with more perl
> expertise than me how much effort it might take to have the VC++ build
> system be able to create a project from an arbitrary PGXS makefile.
> Andrew or Magnus would seem the obvious people.

I think my head just exploded.

Best,

David

On Jun 25, 2009, at 7:16 AM, Tom Lane wrote:

>> The problem is, I think, that the Makefile format is way too
>> flexible.
>
> I think the contrib makefiles are considered to be our standard test
> suite for PGXS. If a 3rd-party makefile is doing anything not
> represented in contrib, it's not guaranteed to work anyway. So I'd
> be plenty satisfied if we just made the existing contrib
> infrastructure
> work for 3rd-party modules.

Is there no platform-independent build system we could take advantage
of?

One reason the Perl community is (very gradually) moving away from
ExtUtils::MakMaker towards Module::Build (pure Perl installer) is to
minimize such issues. I realize that we don't depend on Perl on Unix
platforms, so it wouldn't make sense to use its build system for our
extensions, but perhaps there's something else we could do?

Best,

David

On 6/25/09, David E. Wheeler <david(at)kineticode(dot)com> wrote:
> On Jun 25, 2009, at 2:21 AM, Dave Page wrote:
>
>>> Is it possible to design this part of the extension system with only
>>> PGXS in mind and later adapt the windows toolsuite?
>>
>> Anything is possible :-). Better to ask someone with more perl
>> expertise than me how much effort it might take to have the VC++ build
>> system be able to create a project from an arbitrary PGXS makefile.
>> Andrew or Magnus would seem the obvious people.
>
> I think my head just exploded.

Sounds messy...

--
Dave Page
EnterpriseDB UK: http://www.enterprisedb.com

"David E. Wheeler" <david(at)kineticode(dot)com> writes:
> Is there no platform-independent build system we could take advantage
> of?

There's been some talk of using cmake, but the conversion effort would
be massive, and I'm not sure the benefits would warrant it.

regards, tom lane

On Jun 25, 2009, at 10:10 AM, Tom Lane wrote:

>> Is there no platform-independent build system we could take advantage
>> of?
>
> There's been some talk of using cmake, but the conversion effort would
> be massive, and I'm not sure the benefits would warrant it.

Might it be worthwhile just for the extensions stuff?

Best,

David

On Thursday 25 June 2009 01:09:17 Andrew Dunstan wrote:
> Well, I think in our case that would be going too far. I think there is
> a very good case for keeping a few key extensions in core both as
> exemplars and to make it easy to validate the extension mechanism
> itself. There have been suggestions in the past about throwing a bunch
> of things overboard, sometimes out of a passion for neatness more than
> anything else ISTM, but there have been good arguments against as well,
> particularly in the case of the PLs, which are tied so closely to the
> backend.

Another thing we might want to consider once we have a robust extension
mechanism is to move some things out of the backend into extensions.
Candidates could be uuid, legacy geometry types, inet/cidr, for example.
These extensions would still be available and probably installed by default,
but they need not be hardcoded into the backend. But a policy of shipping
zero extensions with the postgresql tarball obviously leaves very little
flexibility to do any sort of thing like this.

Peter Eisentraut wrote:
>
> Another thing we might want to consider once we have a robust extension
> mechanism is to move some things out of the backend into extensions.
> Candidates could be uuid, legacy geometry types, inet/cidr, for example.
> These extensions would still be available and probably installed by default,
> but they need not be hardcoded into the backend.

Presumably would help the prospective upgrader too. Upgrade tool can't
cope with the change to inet types? No problem, I *know* they're not in
use, since they're not loaded.

--
Richard Huxton
Archonet Ltd

Hi,

While backporting UUID stuff to 8.2 where I'll need it (I wish I could
have more impact on PostgreSQL upgrade schedules... who doesn't), I
faced a problem I didn't foresee, and that we maybe should think about.

http://cvs.pgfoundry.org/cgi-bin/cvsweb.cgi/backports/

The idea in this backport is to have UUID as an extension to 8.2, which
was plain easy to do once you remember about adding the
PG_FUNCTION_INFO_V1() macro calls where they fit. Then there's the
uuid-ossp contrib stuff, already packaged as an extension, but with code
dependancy to the UUID provided functions (uuid_in, uuid_out).

What I ended up doing was duplicating code in order not to have to tweak
local_preload_libraries, so that uuid-ossp.so is self-contained.

Any advice or missing knowledge about loading modules which depends on
code from another module not already loaded in the backend is welcome :)

Josh Berkus <josh(at)agliodbs(dot)com> writes:
>> - dependancy graph solving and automatic installation, with depends,
>> recommends and suggest sections and with rules/setup to choose what to
>> pull in by default...
>
> Uh-huh. That'll be the day ...

So it seems we will have to teach the extension facility about loading
dependant extensions first when calling a function, which I guess we can
do as soon as we have the dependancies information in there?

(calling a function from 'MODULE_PATHNAME' will have the .so loaded, so
I guess than knowing it depends on another 'MODULE_PATHNAME' will
enable us to load those first)

Regards,
--
dim

Dimitri Fontaine <dfontaine(at)hi-media(dot)com> writes:
> Any advice or missing knowledge about loading modules which depends on
> code from another module not already loaded in the backend is welcome :)

You should be able to configure the dynamic loader to do that, although
in the case of uuid I strongly doubt it's worth the trouble.
Duplicated code would be a lot simpler to manage ...

regards, tom lane

Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> writes:
> You should be able to configure the dynamic loader to do that, although
> in the case of uuid I strongly doubt it's worth the trouble.

In the context of the extensions facility, will we be able to do this
configuration automatically from the backend, or to "manually" load any
dependant .so?

> Duplicated code would be a lot simpler to manage ...

Ok, I'll keep it this way then.

Regards,
--
dim

Dimitri Fontaine <dfontaine(at)hi-media(dot)com> writes:
> Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> writes:
>> You should be able to configure the dynamic loader to do that, although
>> in the case of uuid I strongly doubt it's worth the trouble.

> In the context of the extensions facility, will we be able to do this
> configuration automatically from the backend, or to "manually" load any
> dependant .so?

I have zero interest in trying to support either. I doubt it's even
possible --- the backend code has no way to inform the dynamic loader
how to resolve cross-library references. So if the DL doesn't already
understand the dependency it's never going to work.

regards, tom lane

Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> writes:
> I have zero interest in trying to support either. I doubt it's even
> possible --- the backend code has no way to inform the dynamic loader
> how to resolve cross-library references. So if the DL doesn't already
> understand the dependency it's never going to work.

Ok, that means less work for the extension facility (it was not
targetted for it's first incarnation anyway) stuff.

FWIW, I had in mind to use the dependancy information in the extension
meta-data to issue more than one "dlopen()" when a plugin function is
called. I'm being told that in my case linking uuid-ossp.so against
uuid.so should do the trick, though.

I'll drop the idea off the scope of the extension facility.

Regards,
--
dim

On Tuesday 23 June 2009 20:44:39 Dimitri Fontaine wrote:
> === installing and removing an extension
>
> begin;
> install extension foo with search_path = foo;
> commit;
>
> Extensions authors are asked not to bother about search_path in their sql
> scripts so that it's easy for DBAs to decide where to install them. The
> with strange syntax is there to allow for the "install extension" command
> to default to, e.g., pg_extension, which won't typically be the first
> schema in the search_path.
>
> begin;
> drop extension foo [cascade];
> commit;
>
> The "cascade" option is there to care about reverse depends.

I have been thinking about a different use case for this, and I wonder whether
that can fit into your proposal.

Instead of installing an "extension", that is, say, a collection of types and
functions provided by a third-party source, I would like to have a mechanism
to deploy my own actual database application code.

That is, after all, how I work with non-database deployments: I build a
package (deb, rpm) from the code, and install it on the target machine. The
package system here functions as a deployment aid both for "extensions" of the
operating system and for local custom code.

Applying this method to database code, with regard to your proposal, means
first of all that naming this thing "extension" is questionable, and that
installing everything by default into some schema like pg_extensions is
inappropriate.

If you look at how a dpkg or rpm package is structured, it's basically an
archive (ar or cpio) of the files to install plus some control information
such as name, version, dependencies, and various pre/post scripts. We already
have the first part of this: pg_dump/pg_restore are basically tools to create
an archive file out of a database and extract an archive file into a database.
I have been toying with the idea lately to create a thin wrapper around
pg_restore that would contain a bit of metainformation of the kind listed
above. That would actually solve a number of problems already. And then, if
pg_restore could be taught to do upgrades instead of just overwriting (e.g.,
ALTER TABLE ADD COLUMN instead of DROP TABLE + CREATE TABLE), this would all
fall into place nicely.

What this needs below the surface is basically librpm: an interface to
describe and query which objects belong to which "package" and to associate
pre/post scripts with packages. And I think that that interface is quite like
the CREATE/DROP EXTENSION stuff that you are describing. (Pre/post scripts
could be functions, actually, instead of scripts.)

On the matter of schemas, I suggest that we consider two ideas that have
helped RPM in its early days, when everyone had their own very specific ideas
about what should be installed where:

- file system hierarchy standard
- relocations

This means, we'd write up standard of where we think you *should* install
things. And we expect that quality packages/bundles/extensions created for
wider distribution install themselves in the right place without additional
user intervention. But the packaging tool would provide a way to override
this. Then, something that is a true extension could in fact be set up to
install itself by default into pg_extensions, but a bundle containing local
custom code would be set up so that it installs into a different schema or
schemas by default.

What do you think?

Peter Eisentraut wrote:

> Instead of installing an "extension", that is, say, a collection
> of types and functions provided by a third-party source, I would
> like to have a mechanism to deploy my own actual database
> application code.

> On the matter of schemas, I suggest that we consider two ideas that have
> helped RPM in its early days, when everyone had their own very specific ideas
> about what should be installed where:
>
> - file system hierarchy standard
> - relocations

Of course if you have IMPORT from an extension, it's down to the DBA:

INSTALL chinese_calendar;
IMPORT FROM chinese_calendar SECTION (default) INTO SCHEMA pg_extension;
IMPORT FROM chinese_calendar SECTION (year_names) INTO SCHEMA lookups;

INSTALL peter_e_app;
IMPORT FROM peter_e_app SECTION (all) INTO SCHEMA public;

Of course this means two things:
1. Every "extension" has to have its own schema mappings.
2. The application view of the database is a sort of "default extension"

Pros:
- Namespace collisions begone!
- Anything to help extension upgrades could be re-used for applications
(and vice-versa)
- Some stuff isn't visible outside the extension *at all*
- You can separate extension installation from usage (good for
multi-user setups).

Cons:
- Extra layer of indirection (find my namespace => namespace lookup =>
object)
- Extensions need to list what they export in what sections
- More code required

--
Richard Huxton
Archonet Ltd

Peter Eisentraut <peter_e(at)gmx(dot)net> writes:
> On Tuesday 23 June 2009 20:44:39 Dimitri Fontaine wrote:
> Instead of installing an "extension", that is, say, a collection of types and
> functions provided by a third-party source, I would like to have a mechanism
> to deploy my own actual database application code.

I'd like for the extension facility to cover application code in the
database too, yes. Short of install time choice of schema I think we're
there, but please refer to the infamous "search_path vs extensions"
debate we had, that I wanted to consider as a pre-requisite for User
Extension Design:

http://archives.postgresql.org/pgsql-hackers/2009-05/msg00912.php

After this, I'm considering that if we want to have anything, we'll have
to begin implementing extensions and find a schema relocation facility
later on. Unless you have one now? :)

> That is, after all, how I work with non-database deployments: I build a
> package (deb, rpm) from the code, and install it on the target machine. The
> package system here functions as a deployment aid both for "extensions" of the
> operating system and for local custom code.
>
> Applying this method to database code, with regard to your proposal, means
> first of all that naming this thing "extension" is questionable, and that
> installing everything by default into some schema like pg_extensions is
> inappropriate.

I'll be happy to be provided a better name if we manage to implement
both ideas into the same facility, or see a way to get there in a near
future :)

> And then, if
> pg_restore could be taught to do upgrades instead of just overwriting (e.g.,
> ALTER TABLE ADD COLUMN instead of DROP TABLE + CREATE TABLE), this would all
> fall into place nicely.

I'm not sure about this. What we want when using pg_restore is typically
an upgrade, of PostgreSQL itself but of the extensions too... and I
don't think we can manage from the metadata what the extension upgrading
needs are.

> What this needs below the surface is basically librpm: an interface to
> describe and query which objects belong to which "package" and to associate
> pre/post scripts with packages. And I think that that interface is quite like
> the CREATE/DROP EXTENSION stuff that you are describing. (Pre/post scripts
> could be functions, actually, instead of scripts.)

Yes, and we're having both an entry into pg_catalog.pg_extension
containing the metadata and pg_catalog.pg_depend entries to cook up a
query acting as either `dpkg -L` or `rpm -ql`.

Now, pre and post script if needed could also be pre_install.sql and
post_install.sql with some support at the CREATE EXTENSION level.

I didn't want to add them on the first round to avoid being pointed at
doing over engineering, but now that it is you asking for it, let's do
that :)

> On the matter of schemas, I suggest that we consider two ideas that have
> helped RPM in its early days, when everyone had their own very specific ideas
> about what should be installed where:
>
> - file system hierarchy standard
> - relocations
>
> This means, we'd write up standard of where we think you *should* install
> things. And we expect that quality packages/bundles/extensions created for
> wider distribution install themselves in the right place without additional
> user intervention.

The aim is for users to \i extension.sql which only contains the CREATE
EXTENSION command, then INSTALL EXTENSION extension, and be done with it.

> But the packaging tool would provide a way to override
> this. Then, something that is a true extension could in fact be set up to
> install itself by default into pg_extensions, but a bundle containing local
> custom code would be set up so that it installs into a different schema or
> schemas by default.
>
> What do you think?

How do you implement relocate in a way to guarantee there's no security
disaster waiting to happen? Namely that a function foo() calling another
function foo_support_fn() from within the extension won't be calling a
(malicious?) user defined foo_support_fn() from another schema,
depending on run time search_path?

Having both extension function calls schema qualified and relocations is
the biggest problem we're facing, and it seems we're still short of a
solution for it... or did I just miss it?
--
dim

Richard Huxton <dev(at)archonet(dot)com> writes:
> INSTALL chinese_calendar;
> IMPORT FROM chinese_calendar SECTION (default) INTO SCHEMA pg_extension;
> IMPORT FROM chinese_calendar SECTION (year_names) INTO SCHEMA lookups;

Please see Andrew Dunstan mail about using some notion of ALIAS (is that
a standard compliant SYNONYM?) for handling this:

http://archives.postgresql.org/pgsql-hackers/2009-05/msg01391.php
http://archives.postgresql.org/pgsql-hackers/2009-05/msg01401.php

Regards,
--
dim

Dimitri Fontaine wrote:
> Richard Huxton <dev(at)archonet(dot)com> writes:
>
>> INSTALL chinese_calendar;
>> IMPORT FROM chinese_calendar SECTION (default) INTO SCHEMA pg_extension;
>> IMPORT FROM chinese_calendar SECTION (year_names) INTO SCHEMA lookups;
>>
>
> Please see Andrew Dunstan mail about using some notion of ALIAS (is that
> a standard compliant SYNONYM?) for handling this:
>
> http://archives.postgresql.org/pgsql-hackers/2009-05/msg01391.php
> http://archives.postgresql.org/pgsql-hackers/2009-05/msg01401.php
>
>

Please particularly see this sentence: "But unless someone wants to
tackle that I think we should leave schema management entirely alone,
and leave it up to the extension author / DBA between them."

I think we are in some danger of massively overdesigning this feature
(and of repeating past discussions with little extra content). Please
don't keep adding bells and whistles. The best development is almost
always incremental. Let's start simple and then add features.

cheers

andrew

On Jul 23, 2009, at 9:09 AM, Andrew Dunstan wrote:

> Please particularly see this sentence: "But unless someone wants to
> tackle that I think we should leave schema management entirely
> alone, and leave it up to the extension author / DBA between them."
>
> I think we are in some danger of massively overdesigning this
> feature (and of repeating past discussions with little extra
> content). Please don't keep adding bells and whistles. The best
> development is almost always incremental. Let's start simple and
> then add features.

This is what I was trying to get at in my last post in the other
thread. While throwing some ideas out on how to handle some of these
issues, where there is no clear agreement on what to do, I think we
should punt in favor of implementing those parts for which there *is*
general agreement.

Best,

David