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

On Wed, Oct 12, 2011 at 10:39, Heikki Linnakangas
<heikki(dot)linnakangas(at)enterprisedb(dot)com> wrote:
> 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.

Hmm. is there some way that we could make it do something that would
affect only psql? I guess not, because any kind of \-command would
actually break the CREATE EXTENSION running of the script, right?

But it would be useful to be able to inject something there that psql
would notice but the backend would ignore...

--
 Magnus Hagander
 Me: http://www.hagander.net/
 Work: http://www.redpill-linpro.com/