Re: Dumping an Extension's Script

On Wed, Dec 5, 2012 at 2:01 PM, Andres Freund <andres(at)2ndquadrant(dot)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);

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?

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

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