Re: Extension Templates S03E11

Stephen Frost <sfrost(at)snowman(dot)net> writes:
>> Stephen had some legitimate concerns. I don't entirely agree, but they
>> are legitimate concerns, and we don't want to just override them.

The main disturbing concern for me is to do with pg_restore and major
upgrades, where the blob we have in the catalogs might not parse
correctly into the new version.

Of course, it's easy enough to fix either the source database or the
pg_restore text itself, as it's just plain SQL in there.

>> At the same time, I'm skeptical of the alternatives Stephen offered
>> (though I don't think he intended them as a full proposal).

I began working out a full proposal out of them, and here's the most
important conclusions I can offer from that work:

- The main constraint we have to work against is that no matter what,
extension objects are not going to be selected for pg_dump.

That basically means that the only model of extensions we accept is
ever going to be the contrib model, whereas my current attemps are
meant to evolve the extension model way beyond contrib.

The tension we have there is extremely hard to resolve, which
explains the strange idea of storing SQL blobs in the catalogs.

- While it's possible to work out some new versioned container
objects, I see mainly 3 consequences of doing so:

1. We're going to kill extensions, which design would be limited to
only care about contribs: no data, code in C, any single version
of the extension is intended to work against only one major
version of PostgreSQL.

Guess what, I know of no extension maintained by those rules
outside of contribs.

The third rule is the easy one to work around, of course, except
if you consider the pg_restore behaviour, framed as a bug by
Stephen.

2. The new thing would seamlessly allow for data only extensions,
such as census or geolocation, etc, because we would actually
backup that data.

3. The separation of concerns in between the extension author who
maintains the install and upgrade scripts and the DBA who
applies them is going to be pushed entirely to the client
software, and that sounds way more fragile that what we have
now.

So I see 3 solutions ahead of us: we allow extensions to manage more
than the contrib model, a third-party software is going to work around
it to make extensions usable for non-contrib things, or we're providing
a new kind of container in core and kill extensions.

Of course, none of them are exclusive, and I think realistically we're
going to have to live with at least 2 of those alternatives in a near
future.

> It was more thoughts on how I'd expect these things to work. I've also
> been talking to David about what he'd like to see done with PGXN and his
> thinking was a way to automate creating RPMs and DEBs based on PGXN spec
> files, but he points out that the challenge there is dealing with
> external dependencies.

With all due respect, we don't live in a world where its customary to
have root privileges on your production service anymore.

>> So right now I'm discouraged about the whole idea of installing
>> extensions using SQL. I don't see a lot of great options. On top of
>> that, the inability to handle native code limits the number of
>> extensions that could make use of such a facility, which dampens my
>> enthusiasm.

Rejoice! Have a look at the documentation for dynamic_library_path. Any
distribution or packaging software would trivially be able to make it so
that the modules (.so) are loaded from a non-system place.

Only missing is another path GUC to allow PostgreSQL to search for the
extension control files in non-system places too, meanwhile it's already
possible to edit the file system privileges.

> Yeah, having looked at PGXN, it turns out that some 80+% of the
> extensions there have .c code included, something well beyond what I was

I call that a chicken and eggs problem.

Every serious PostgreSQL user will have stored procedure code to
maintain, where “serious” means that PostgreSQL is considered a part of
the application platform.

The only reason why this code is not organized as an extension today is
because extensions are restricted to the contrib model. There's
absolutely no surprise in discovering that current extensions in the
wild are about all fitting the only currently supported model.

> expecting, but most of those cases also look to have external
> dependencies (eg: FDWs), which really makes me doubt this notion that
> they could be distributed independently and outside of the OS packaging
> system (or that it would be a particularly good idea to even try...).

It seems to me that if dynamic_library_path and the system dynamic
loader are both looking at the same places, then storing modules and
their dependencies there is going to be all you need to make it work.

The main packaging system (debian and red hat) have automatic dependency
tracking using `ldd` or the like, so it should be possible to implement
the same thing in the software packaging and distribution layer that we
keep talking about to complement that feature.

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