Re: Dumping an Extension's Script

On 2012-12-05 14:10:34 -0500, Robert Haas wrote:
> On Wed, Dec 5, 2012 at 2:01 PM, Andres Freund <andres@2ndquadrant.com> wrote:
> > E.g. for years I had a set of (trigger) functions to counted the number
> > of rows in a table in a lockless manner. That's used in 10+ applications
> > of former clients of mine. All (plpg)sql.
> > Imagine I want to ship an updated version that 1. removes some
> > *internal* functions, 2. adds some internal function. 3. adds a new
> > *external* function.
> >
> > Now most of the clients use completely different development models and
> > completely different ways of manageing upgrades. I needed to integrate
> > my teensy module into all of them.
> >
> > If we had a way to package it nicely they could just upload the
> > extension inside their own workflows and I (or they) would be freed from
> > integrating foreign update scripts into their workflow.
>
> OK, but let me play devil's advocate here.   Under the status quo, if
> they used loose database objects, they would need to execute some
> database code that does this:
>
> DROP FUNCTION internalfunc1(int);
> CREATE FUNCTION internalfunc2(int);
> CREATE FUNCTION externalfunc3(int);

They would need to do exactly that if their database had version 1.1 and
they upgrade to 1.3 but not if they already had 1.2...

> IIUC, under this proposal, the client would instead need to execute
> some SQL code that looks something this (I'm faking the syntax here,
> forgive me, but the patch doesn't seem to contemplate ALTER):
>
> ALTER EXTENSION myextension UPDATE TO 1.1 USING SCRIPT $$
>    ALTER EXTENSION myextension DROP FUNCTION internalfunc1(int);
>    DROP FUNCTION internalfunc1(int);
>    CREATE FUNCTION internalfunc2(int);
>    ALTER EXTENSION myextension ADD FUNCTION internalfunc2(int);
>    CREATE FUNCTION externalfunc3(int);
>    ALTER FUNCTION myextension ADD FUNCTION externalfunc3(int);
> $$;
>
> That doesn't really look like an improvement to me.  What am I missing?

They should be able to simply slurp the extension from a file, possibly
even install it outside their own update mechanism. Given that you don't
know which version was installed beforehand thats not really possible
without some infrastructure.

And they should be able to drop the extension again afterwards without
it leaving a trace. Nearly all I have seen out there fails at that, and
the extension mechanism provides tracking of that.

> > Imagine embedding a PGXN module into your application which is used on
> > many servers and doesn't need superuser privileges or anything. Same
> > thing.
> >
> > That's not something all that uncommon is it?
>
> Not at all.  I'm not questioning the use case at all; I'm questioning
> whether extensions are the right tool for addressing it.

Do you have some alterantive suggestion?

Greetings,

Andres Freund

--Andres Freund                       http://www.2ndQuadrant.com/PostgreSQL Development, 24x7 Support, Training &
Services