Re: Extensions User Design - Mailing list pgsql-hackers

[Skipping most of it as I'd like to read what other people think about
it before going in lengthy thread already] :)

Le 23 juin 09 à 23:41, David E. Wheeler a écrit :
> Yes, although as I said before, version numbers are hard to get
> right. We should keep them very simple, with a strict requirement as
> to the simple format (perhaps /\d+[.]\d{2}/) or perhaps NUMERIC or
> some other core data type, and then we'd be able to use simple
> operators:
>
> install extension foo with version = 1.2 OR version >= 1.4,
> search_path = foo;

If we happen to accept the debian policy versioning scheme, then the
hard work is already done for us, it seems:  http://packages.debian.org/fr/sid/postgresql-8.3-debversion

>> I don't think we want to cancel user ability to choose schema where
>> to install, so an idea could be to ask extensions author to
>> systematically use pg_extension (or non-qualify), and PostgreSQL
>> could replace this with the INSTALL EXTENSION command schema.
>
> Replace what? How would pg_extension or INSTALL EXTENSION know to
> magically schema-qualify the function calls internal to an extension?

It's "just" PostgreSQL reading an SQL file (foo.install.sql) and
parsing each statement etc, so we obviously have the machinery to
recognize SQL objects names and schema qualification. Replacing the
schema on-the-fly should be a SMOP? (*cough*)

> I think that people will want to be able to associate arbitrary
> metadata. It'd be useful for configuration, too.

Oh, you want EAV already? Or maybe a supplementary hstore column into
the pg_extension catalog... but I guess we can't have this dependancy :)

> The upgrade function stuff is what I understand least about this
> proposal. Can you provide a real-world type example of how it will
> be used?

You provide a function upgrade(old, new) where parameters are version
numbers. The body of the (typically plpgsql) function should implement
the ALTER TABLE or CREATE OR REPLACE FUNCTION stuff you need to do,
with some conditions on the version numbers.

I expect people would write a upgrade_10_to_11() function then call it
from upgrade() when old = 1.0 and new = 1.1, for example.

Maybe we should also provide some support functions to run the install
and uninstall script, and some more facilities, so that you could
implement as follow: BEGIN   -- loop over columns storing data from our type   FOR s, t, c IN SELECT nspname, relname,
attname                   FROM pg_find_columns('mytype'::regclass)   LOOP     EXECUTE 'ALTER TABLE $1.$2 ALTER COLUMN
$3TYPE text USING   
mycast($3)'       USING s, t, c;   END LOOP;
   PERFORM pg_extension_uninstall('foo', old);   PERFORM pg_extension_install('foo', new);
   -- ALTER TYPE the other way round END;

Some other stuff could be needed to check about indexes to, storing a
list of them in a temp table then recreating them, but it seems to me
you can already hand craft the catalog queries now. But as it becomes
common practise, we might want to offer them in a more ready for
public consumption way.

Regards,
--
dim