Re: Extensions User Design - Mailing list pgsql-hackers

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