Re: ALTER EXTENSION ... UPGRADE;

On Fri, Dec 10, 2010 at 8:14 PM, Josh Berkus <josh(at)agliodbs(dot)com> wrote:
>> I don't believe that extension SQL scripts should rely on DO blocks.
>> There is no requirement that plpgsql be installed, and we're not going
>> to create one as part of this feature.  What this means is that the
>> design you offer above doesn't work at all, since it fundamentally
>> assumes that the SQL script can do conditional logic.  What's more,
>> it fundamentally assumes that the script WILL do conditional logic
>> and support (in one lump) every possible combination of versions.
>> That's going to turn into buggy spaghetti-code very quickly.
>
> I just noticed this response, and don't think it can stand as-is.
>
> While I agree that it's not reasonable to have a single script which
> supports every combination of versions, I also assert that it's
> completely unreasonable to expect extension authors to write upgrade
> scripts with no conditional logic.  Your view would essentially be
> requiring authors to write a completely seperate SQL script for every
> single possible combination of two versions.
>
> For an extension which has had 10 releases with SQL modifications, this
> would be 45 separate upgrade files.  That's a ridiculous thing to expect
> of any contributor.
>
> I, for one, have no problem whatsoever with requiring that users have
> plpgsql installed in order to use extensions. It's installed by default.
> If they need to uninstall plpgsql for some security reason, then fine;
> they can write their own upgrade scripts.  You are pushing making things
> easy for 0.5% of our users at the expense of everyone else.

Yea, verily. I share Tom's concern about depending on a procedural
language that isn't absolutely guaranteed to be there, but crippling
the extension mechanism is a bad solution. Conditional logic is
important, and we need to have it. If we're really bent on making
this watertight, we can either somehow nail down PL/pgsql so that it's
always present, or add conditional logic to straight SQL, or some
other magic I'm not thinking of. Or we can just suck up the fact that
people who uninstall PL/pgsql are not going to be able to install
extensions that depend on PL/pgsql, which isn't great, but I think it
beats the alternative.

In my not-inconsiderable experience writing upgrade scripts, most of
the time, you just add new objects. So if CREATE OR REPLACE or CREATE
IF NOT EXISTS is available, you only need one upgrade script to
upgrade from ANY prior version. And most of what people create with
these scripts are functions, which have CREATE OR REPLACE. However,
every once in a while you want to change the definition of an existing
object, at which point you enter what I like to call dependency hell.
If the object has no dependencies, you can just drop and recreate it,
but if it does, go directly to unspeakable agony. A further problem
with extensions is that we haven't got either COR or CINE for things
like types, operator classes, operator class members, etc. If we
decline to add that, then people are going to have to work around it
by writing the logic in PL/pgsql.... or else go with Tom's suggestion
of having a separate script for every to/from combination.

But I don't think that's really the right way to go. I think what
will quickly happen is that the conditional logic will move out of the
SQL script itself and into complicated Makefile hackery which will
generate a whole bunch of similar but not quite identical upgrade
scripts. Blech.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company