Re: Inline Extension

On Thu, Jan 19, 2012 at 8:15 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> Heikki Linnakangas <heikki.linnakangas@enterprisedb.com> writes:
>>> Frankly I don't see the point of this. If the extension is an independent
>>> piece of (SQL) code, developed separately from an application, with its own
>>> lifecycle, a .sql file seems like the best way to distribute it. If it's
>>> not, ie. if it's an integral part of the database schema, then why package
>>> it as an extension in the first place?
> I'm with Heikki on not believing that this is a good idea.  If you are
> trying to do careful versioning of a set of object definitions, you want
> to stick the things in a file, you don't want them just flying by in
> submitted SQL.  Also, a large part of the point of the extension
> facility is to be able to do uninstall/reinstall and version
> upgrades/downgrades, none of which are possible unless the extension
> scripts are stored somewhere.
>
> ISTM your distribution concern would be much better addressed by
> installing contrib/adminpack and then just using pg_file_write()
> to put the new extension script into the remote server's library.

I think this is somewhat rube-goldberg-esque, and denies non-superuser
roles the ability to get more version management of schema and
operators.  As-is many organizations are submitting "migrations" via
plain SQL that include committing to a version management table that
is maintained by convention, and as-is that is considered a modern-day
best-practice.

Unless someone comes up with something fundamentally new in how to
handle the problem being solved in user-land via 'migrations' and
version tables in Postgres, I think tying completely safe, sandboxed,
non-superuser operators to super-user-only (and awkward besides) file
management via FEBE on the server-side is not going to do anything but
redelegate the problem of soundness to every migration/change
management regime, and decrease reuse of useful operators that do not
require superuser.

The ship has sailed.  Encouraging use of files and .sql buy no
soundness, because everyone is moving towards is overlaying version
management via pure FEBE anyway.  At best, this means to me that
Postgres is completely neutral about what version management regime
you want to use for operators and schemas.  At worst, it means
Postgres frustratingly unhelpful in common use cases.  And somewhere
in the middle is "this may have value, but not enough to be worth
maintaining."  Given the lack of a fast and widely deployed trusted PL
integrations, I my interpretation of the truth falls somewhere between
the latter two interpretations.

--
fdr