Re: Extensions vs PGXS' MODULE_PATHNAME handling

Tom Lane <tgl@sss.pgh.pa.us> writes:
>> Tom Lane <tgl@sss.pgh.pa.us> writes:
>>> I think it's better to keep it working as a textual substitution.

Thinking about this some more, it has the advantage that the effects of
the control file settings are kept within the script file processing and
pg_extension catalog.  The only backend impact is the dependency
tracking.

> OK, so with that, attached is an example of the complete conversion diff
> for a contrib module (hstore in particular).  Although "git status"

I see you're not using the @extschema@ placeholder in the upgrade
script.  It is intentional?  It's been common wisdom and practice to
edit the SQL file of any contrib or third party module to have it
installed in your preferred schema…

> reports hstore--1.0.sql as being a rename of hstore.sql.in, "git diff"
> doesn't seem to be exceedingly bright about presenting it that way :-(.
> But actually the change in that script other than renaming is just
> removing the "set search_path" command and adjusting the header comment.

And we don't have to rely on hstore.sql.in file anymore as the change is
done by the backend side of things.  That's a very good point for the
windows build system I think.

> Barring objections, I'll press on with fixing the rest of them.

I think you'd be interested into this reworked SQL query.  It should be
providing exactly the script file you need as an upgrade from unpackaged.

I took the time to finish this query (filter out array types, some
replacement in operator classes and families descriptions) because I
think it would be nice to offer it in the docs.  It could even be
proposed as a function :)

I hope you'll find it useful, but it could well be you finished the
search&replace of all contribs already (ah, emacs keyboard macros).
 CREATE EXTENSION hstore;
 CREATE SCHEMA empty_place; SET search_path TO empty_place;
 WITH objs AS (   SELECT classid, 'ALTER EXTENSION ' || E.extname || ' ADD '       ||
replace(pg_describe_object(classid,objid, 0),                  N.nspname, '@extschema@')       || ';' as sql     FROM
pg_dependD          JOIN pg_extension E ON D.refobjid = E.oid                             AND D.refclassid = E.tableoid
        JOIN pg_namespace N ON E.extnamespace = N.oid   WHERE CASE WHEN classid = 'pg_catalog.pg_type'::regclass
     THEN (SELECT typarray FROM pg_type WHERE oid=objid) != 0              ELSE true          END         AND deptype =
'e'AND E.extname = 'hstore' ) SELECT   CASE WHEN classid IN ('pg_catalog.pg_opclass'::regclass,
'pg_catalog.pg_opfamily'::regclass)       THEN replace(sql, 'for access method', 'using')        ELSE sql    END   FROM
objs;

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