Re: Extensions User Design

On Jun 23, 2009, at 3:02 PM, Dimitri Fontaine wrote:

> 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

As long as we don't need to implement a new data type, fine.

>> 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*)

Well, no. I might have written a function in PL/Perl. Is PostgreSQL
going to parse my Perl function for unqualified function calls?
Really? Hell, I don't think that PL/pgSQL is parsed until functions
are loaded, either, though I may be wrong about that.

Better is to have some magic so that functions in an extension
magically have their schema put onto the front of search_path when
they're called. Or when they're compiled. Or something.

> 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 :)

No, but a simple key/value table with an FK constraint should be
sufficient for non-core metadata.

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

Well, CREATE OR REPLACE FUNCTION is probably already in my install.sql
file. But I could see dropping deprecated functions and, of course,
altering tables.

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

Okay, that makes sense.

> 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 $3 TYPE 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.

Yes, whatever tools we can provide to make things easier for extension
authors/maintainers, the better. But I recognize that we might have to
wait and see what cow paths develop.

Best,

David