Re: Extensions versus pg_upgrade

Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> writes:
> Like ALTER THING SET SCHEMA, ALTER THING SET EXTENSION is implicitly
> assuming that there can be only one owning extension for an object.

Yes, I worked from the SET SCHEMA variant and mentally mapped SET
EXTENSION there, if looked like the same idea applied to another
"property" of the object.

> Furthermore, it's not really intended for *removal* of an object from an
> extension (a concept that doesn't even exist for SET SCHEMA). We could
> take a page from COMMENT ON and use "SET EXTENSION NULL" for that, but
> that's surely more of a hack than anything else.
>
> In contrast, ALTER EXTENSION ADD doesn't presuppose that you couldn't
> add the object to multiple extensions; and it has a natural inverse,

Well I wouldn't want to get there. I'm not seeing what use case we
would solve by having more than one extension install the same object, I
would rather have a common extension that the others depend on.

> ALTER EXTENSION DROP. I am not necessarily suggesting that we will ever
> allow either of those things, but I do suggest that we should pick a
> syntax that doesn't look like it's being forced to conform if we ever
> want to do it. The DROP case at least seems like it might be wanted
> in the relatively near future.

I didn't think of that case because I would think the upgrade script
will just DROP OBJECT instead. But in some cases I can see extension
authors wanting to ALTER EXTENSION DROP OBJECT in their upgrade script
and provide a second-stage script or procedure to clean up the database
once upgraded. Only when you don't need the object anymore you can drop
it entirely.

I'm not sure how contrived the use case is here, but I agree that being
prepared for it makes sense.

Adding more that one object in one command is not of a great value I
think, because you still have to lock each object individually, and
that's transaction bound. Unlike ALTER TABLE … ADD COLUMN where it's a
huge benefit to be able to lock and update the table only once for a
number of columns (add and drops).

But at the same time once the work is done, adding some syntax
flexibility and a loop or two doesn't look too bad if you wanted to get
there. Well no strong opinion as I'm not doing the work :)

As far as upgrade script for contrib extensions are concerned, we will
be able to produce them from SQL, right? The trick is to install the
extension first, of course.

CREATE EXTENSION foo;

CREATE SCHEMA empty_place;
SET search_path TO empty_place;

SELECT 'ALTER EXTENSION ' || E.extname || ' ADD '
|| replace(pg_describe_object(classid, objid, 0),
N.nspname, '@extschema@')
|| ';'
FROM pg_depend D
JOIN pg_extension E ON D.refobjid = E.oid
AND D.refclassid = E.tableoid
JOIN pg_namespace N ON E.extnamespace = N.oid
WHERE deptype = 'e' AND E.extname = 'foo';

I think it would be a good idea to have that in the documentation to
help authors prepare their first upgrade script. Well to the extend
that a previous form of it is included in the docs I've put in the
upgrade patch :)

So replacing those scripts I've been working on to switch to the new
syntax would be a matter of running a shell script. The time consuming
part is definitely the testing, but that too can be scripted.

DROP EXTENSION foo;
\i path/to/share/contrib/foo.sql
create wrapper extension foo;
alter extension foo upgrade;
\dx foo

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