Re: Extensions vs PGXS' MODULE_PATHNAME handling

Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> writes:
>> Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)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_depend D
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