Re: [BUGS] *.sql contrib files contain unresolvable MODULE_PATHNAME

On 12.10.2011 10:58, Stefan Kaltenbrunner wrote:
> On 10/12/2011 09:53 AM, Martin Pitt wrote:
>> Hello all,
>>
>> In https://launchpad.net/bugs/835502 it was reported that the 9.1.1
>> contrib *.sql files contain the token "MODULE_PATHNAME", which is
>> unknown:
>>
>> psql test< /usr/share/postgresql/9.1/extension/intarray--1.0.sql
>>
>> This fails with a ton of errors about the file "MODULE_PATHNAME" not
>> existing.
>>
>> When I replace this with "$libdir/_int", it works:
>>
>> sed 's!MODULE_PATHNAME!$libdir/_int!g' /usr/share/postgresql/9.1/extension/intarray--1.0.sql | psql test
>>
>> Is that something I'm doing wrong in the packaging, or should the
>> contrib Makefiles be fixed to do this substitution?
>>
>> It doesn't only affect intarray, but pretty much all *.sql files.
>
> uh - the reason is that contrib is now packaged as extensions and that
> you are supposed to use "CREATE EXTENSION intarray;" on the SQL level
> instead of manually loading sql-scripts through psql.

9.1 has been out for only a couple of months, and we've seen a lot of
people trying to do that already. In hindsight, we probably should've
chosen a different filename extension for those files, to make it clear
that you can't just run them in psql. It's too late for that, but a
comment at the top of the .sql files would be good:

--- a/contrib/intarray/intarray--1.0.sql
+++ b/contrib/intarray/intarray--1.0.sql
@@ -1,4 +1,8 @@
-/* contrib/intarray/intarray--1.0.sql */
+/*
+ * contrib/intarray/intarray--1.0.sql
+ *
+ * Script file to be run by CREATE EXTENSION.
+ */

--
-- Create the user-defined type for the 1-D integer arrays (_int4)

The people trying to run these files with psql look inside the file when
they get the error, so mentioning "CREATE EXTENSION" should give a hint
on what to do.

--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com