Re: Review: extension template

Dimitri,

leaving the template vs link model aside for a moment, here are some
other issues I run into. All under the assumption that we want the link
model.

On 07/08/2013 11:49 AM, Dimitri Fontaine wrote:
> Please find attached to this mail version 9 of the Extension Templates
> patch with fixes for the review up to now.

First of all, I figured that creation of a template of a newer version
is prohibited in case an extension exists:

> db1=# CREATE TEMPLATE FOR EXTENSION foo VERSION '0.0' AS $foo$ SELECT 1; $foo$;
> CREATE TEMPLATE FOR EXTENSION
> db1=# CREATE EXTENSION foo;
> CREATE EXTENSION
> db1=# CREATE TEMPLATE FOR EXTENSION foo VERSION '0.1' AS $foo$ SELECT 2; $foo$;
> ERROR: extension "foo" already exists

Why is that?

I then came to think of the upgrade scripts... what do we link against
if an extension has been created from some full version and then one or
more upgrade scripts got applied?

Currently, creation of additional upgrade scripts are not blocked. Which
is a good thing, IMO. I don't like the block on newer full versions.
However, the upgrade doesn't seem to change the dependency, so you can
still delete the update script after the update. Consider this:

> db1=# CREATE TEMPLATE FOR EXTENSION foo VERSION '0.0' AS $$ $$;
> CREATE TEMPLATE FOR EXTENSION
> db1=# CREATE EXTENSION foo;
> CREATE EXTENSION
> db1=# CREATE TEMPLATE FOR EXTENSION foo FROM '0.0' TO '0.1' AS $$ $$;
> CREATE TEMPLATE FOR EXTENSION
> db1=# ALTER EXTENSION foo UPDATE TO '0.1';
> ALTER EXTENSION
> db1=# SELECT * FROM pg_extension;
> extname | extowner | extnamespace | extrelocatable | extversion | extconfig | extcondition
> ---------+----------+--------------+----------------+------------+-----------+--------------
> plpgsql | 10 | 11 | f | 1.0 | |
> foo | 10 | 2200 | f | 0.1 | |
> (2 rows)
>
> db1=# DROP TEMPLATE FOR EXTENSION foo FROM '0.0' TO '0.1';
> DROP TEMPLATE FOR EXTENSION
>> db1=# SELECT * FROM pg_extension;
>> extname | extowner | extnamespace | extrelocatable | extversion | extconfig | extcondition
>> ---------+----------+--------------+----------------+------------+-----------+--------------
>> plpgsql | 10 | 11 | f | 1.0 | |
>> foo | 10 | 2200 | f | 0.1 | |
>> (2 rows)
>>

In this state, extension foo as of version '0.1' is installed, but
running this through dump & restore, you'll only get back '0.0'.

Interestingly, the following "works" (in the sense that the DROP of the
upgrade script is prohibited):

> db1=# CREATE TEMPLATE FOR EXTENSION foo VERSION '0.0' AS $$ $$;
> CREATE TEMPLATE FOR EXTENSION
> db1=# CREATE TEMPLATE FOR EXTENSION foo FROM '0.0' TO '0.1' AS $$ $$;
> CREATE TEMPLATE FOR EXTENSION
> db1=# CREATE EXTENSION foo VERSION '0.1';
> CREATE EXTENSION
> db1=# DROP TEMPLATE FOR EXTENSION foo FROM '0.0' TO '0.1';
> ERROR: cannot drop update template for extension foo because other objects depend on it
> DETAIL: extension foo depends on control template for extension foo
> HINT: Use DROP ... CASCADE to drop the dependent objects too.

However, in that case, you are free to drop the full version:

> db1=# DROP TEMPLATE FOR EXTENSION foo VERSION '0.0';
> DROP TEMPLATE FOR EXTENSION

This certainly creates a bad state that leads to an error, when run
through dump & restore.

Maybe this one...

> db1=# CREATE TEMPLATE FOR EXTENSION foo VERSION '0.0' AS $$ $$;
> CREATE TEMPLATE FOR EXTENSION
> db1=# CREATE TEMPLATE FOR EXTENSION foo FROM '0.0' TO '0.1' AS $$ $$;
> CREATE TEMPLATE FOR EXTENSION
> db1=# DROP TEMPLATE FOR EXTENSION foo VERSION '0.0';
> DROP TEMPLATE FOR EXTENSION

... should already err out here ...

> db1=# CREATE EXTENSION foo;
> ERROR: Extension "foo" is not available from "/tmp/pginst/usr/local/pgsql/share/extension" nor as a template
> db1=# CREATE EXTENSION foo VERSION '0.1';
> ERROR: Extension "foo" is not available from "/tmp/pginst/usr/local/pgsql/share/extension" nor as a template

... and not only here.

I.e. the TO version should probably have a dependency on the FROM
version (that might even be useful in the template model).

Another thing that surprised me is the inability to have an upgrade
script *and* a full version (for the same extension target version).
Even if that's intended behavior, the error message could be improved:

> db1=# CREATE TEMPLATE FOR EXTENSION foo VERSION '0.0' AS $$ $$;
> CREATE TEMPLATE FOR EXTENSION
> db1=# CREATE TEMPLATE FOR EXTENSION foo FROM '0.0' TO '0.1' AS $$ $$;
> CREATE TEMPLATE FOR EXTENSION
> db1=# CREATE TEMPLATE FOR EXTENSION foo VERSION '0.1' AS $$ $$;
> ERROR: duplicate key value violates unique constraint "pg_extension_control_name_version_index"
> DETAIL: Key (ctlname, ctlversion)=(foo, 0.1) already exists.

Regards

Markus Wanner