Thread: How best to load modules?
Hello, all!
What are best practices regarding the loading of postgresql modules, say
from the contrib/ directory; specifically, with regard to portability?
I would like to distribute an SQL script which loads a module, and works
with as little further fiddling as possible.
known options
=============
Within a session, or in a script, one can use
\i <explicit file path>
But within a script this has the weakness that the file path varies from
one system distribution to another.
One can start psql with
psql ... -f <explicit file path>
but that's a measure taken outside the script, to done either with session,
or else be done by a further measure such as a shell script.
Ideally, the location of the default modules directory (or installation
directory) should be available within a session in some variable or from
some function call.
There are some pre-defined variables, listed in a session by
show all;
but I don't see anything like a directory path there.
Maybe a built-in function returning this directory? Searched to no avail:
http://www.postgresql.org/docs/8.2/interactive/functions.html
There has been talk about a bigger solution on
http://wiki.postgresql.org/wiki/Module_Manager
but little seems to have happened there in some years.
An environment variable
$libdir,
is mentioned
http://www.postgresql.org/docs/8.2/static/runtime-config-client.html
but this seems not to be present within a session.
It seems to be expanded within the LANGUAGE C environment, for instance in
tablefunc.sql
-------------------------------------------------
CREATE OR REPLACE FUNCTION crosstab2(text)
RETURNS setof tablefunc_crosstab_2
AS '$libdir/tablefunc','crosstab'
LANGUAGE C STABLE STRICT;
-------------------------------------------------
Thanks!
--
| - - - - - - - - - - - - - - - - - - - - - - - - -
| Steve White +49(331)7499-202
| E-Science Zi. 27 Villa Turbulenz
| - - - - - - - - - - - - - - - - - - - - - - - - -
| Astrophysikalisches Institut Potsdam (AIP)
| An der Sternwarte 16, D-14482 Potsdam
|
| Vorstand: Prof. Dr. Matthias Steinmetz, Peter A. Stolz
|
| Stiftung privaten Rechts, Stiftungsverzeichnis Brandenburg: III/7-71-026
| - - - - - - - - - - - - - - - - - - - - - - - - -
Steve White <swhite@aip.de> writes: > What are best practices regarding the loading of postgresql modules, say > from the contrib/ directory; specifically, with regard to portability? > > I would like to distribute an SQL script which loads a module, and works > with as little further fiddling as possible. See about PGXS. http://www.postgresql.org/docs/9.0/static/xfunc-c.html#XFUNC-C-PGXS > known options > ============= > > Within a session, or in a script, one can use > \i <explicit file path> > But within a script this has the weakness that the file path varies from > one system distribution to another. > > One can start psql with > psql ... -f <explicit file path> > but that's a measure taken outside the script, to done either with session, > or else be done by a further measure such as a shell script. > > Ideally, the location of the default modules directory (or installation > directory) should be available within a session in some variable or from > some function call. You can use pg_config to get this PATH, and in recent versions of PostgreSQL you can use $libdir as the module directory name. select name, setting from pg_settings where name ~ 'dynamic_library_path'; > There has been talk about a bigger solution on > http://wiki.postgresql.org/wiki/Module_Manager > but little seems to have happened there in some years. It seemed stalled for a long time because the harder part of this development was to get an agreement among hackers about what to develop exactly. We've been slowly reaching that between developer meetings in 2009 and 2010, and the result should hit the official source tree before the next developer meeting in 2011 :) https://commitfest.postgresql.org/action/patch_view?id=471 https://commitfest.postgresql.org/action/patch_view?id=472 If you want to see more details about the expected-to-be-commited-soon development work, have a look there: http://pgsql.tapoueh.org/extensions/doc/html/extend-extension.html If you have enough time and interest into the feature, you can even clone the git repository where the development occurs (branches named "extension" and "upgrade") and try it for yourself, then maybe send a mail about your findings (we call that a review): http://git.postgresql.org/gitweb?p=postgresql-extension.git;a=summary http://wiki.postgresql.org/wiki/Reviewing_a_Patch Regards, -- Dimitri Fontaine http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support
Hi Dimitri! PGXS is interesting, but a bigger solution than I was looking for: ideally, some simple commands for loading the module from my .sql script. pg_config seems to be in yet another package, postgresql84-devel. It is a shell utility for getting such information. This is again far removed from the postgresql session, and more bother for the user to install. Maybe I could run it from the script, regex it for the SHAREDIR key, and construct from that '$SHAREDIR/contrib'. A simple variable or function returning the library path would have solved my present problem. Perhaps we should make a feature request. A proper notion of a module (something like the Python import command) would be really nice, of course, and should already have been there a long time ago, and it seems to be the aim of the Module_Manager proposal. Cheers! On 28.01.11, Dimitri Fontaine wrote: > Steve White <swhite@aip.de> writes: > > What are best practices regarding the loading of postgresql modules, say > > from the contrib/ directory; specifically, with regard to portability? > > > > I would like to distribute an SQL script which loads a module, and works > > with as little further fiddling as possible. > > See about PGXS. > > http://www.postgresql.org/docs/9.0/static/xfunc-c.html#XFUNC-C-PGXS > > > known options > > ============= > > > > Within a session, or in a script, one can use > > \i <explicit file path> > > But within a script this has the weakness that the file path varies from > > one system distribution to another. > > > > One can start psql with > > psql ... -f <explicit file path> > > but that's a measure taken outside the script, to done either with session, > > or else be done by a further measure such as a shell script. > > > > Ideally, the location of the default modules directory (or installation > > directory) should be available within a session in some variable or from > > some function call. > > You can use pg_config to get this PATH, and in recent versions of > PostgreSQL you can use $libdir as the module directory name. > > select name, setting from pg_settings where name ~ 'dynamic_library_path'; > > > There has been talk about a bigger solution on > > http://wiki.postgresql.org/wiki/Module_Manager > > but little seems to have happened there in some years. > > It seemed stalled for a long time because the harder part of this > development was to get an agreement among hackers about what to develop > exactly. We've been slowly reaching that between developer meetings in > 2009 and 2010, and the result should hit the official source tree before > the next developer meeting in 2011 :) > > https://commitfest.postgresql.org/action/patch_view?id=471 > https://commitfest.postgresql.org/action/patch_view?id=472 > > If you want to see more details about the expected-to-be-commited-soon > development work, have a look there: > > http://pgsql.tapoueh.org/extensions/doc/html/extend-extension.html > > If you have enough time and interest into the feature, you can even > clone the git repository where the development occurs (branches named > "extension" and "upgrade") and try it for yourself, then maybe send a > mail about your findings (we call that a review): > > http://git.postgresql.org/gitweb?p=postgresql-extension.git;a=summary > http://wiki.postgresql.org/wiki/Reviewing_a_Patch > > Regards, > -- > Dimitri Fontaine > http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support > -- | - - - - - - - - - - - - - - - - - - - - - - - - - | Steve White +49(331)7499-202 | E-Science Zi. 27 Villa Turbulenz | - - - - - - - - - - - - - - - - - - - - - - - - - | Astrophysikalisches Institut Potsdam (AIP) | An der Sternwarte 16, D-14482 Potsdam | | Vorstand: Prof. Dr. Matthias Steinmetz, Peter A. Stolz | | Stiftung privaten Rechts, Stiftungsverzeichnis Brandenburg: III/7-71-026 | - - - - - - - - - - - - - - - - - - - - - - - - -
Hi, Here is the best cludge so far. To load the module 'tablefunc' from the contrib/ directory, process the output of the 'pg_config' program with unix commands. The 'pg_config' program is often distributed in a package separate from postgresql. \set tablefunc `pg_config|grep SHAREDIR|sed "s/SHAREDIR = \(.*\)/\1\/contrib\/tablefunc.sql/g"` \i :tablefunc This isn't very robust, but at least it allows me to load and unload stuff from a single sql script on two different distros. Cheers! On 28.01.11, Steve White wrote: > Hello, all! > > What are best practices regarding the loading of postgresql modules, say > from the contrib/ directory; specifically, with regard to portability? > > I would like to distribute an SQL script which loads a module, and works > with as little further fiddling as possible. > > known options > ============= > > Within a session, or in a script, one can use > \i <explicit file path> > But within a script this has the weakness that the file path varies from > one system distribution to another. > > One can start psql with > psql ... -f <explicit file path> > but that's a measure taken outside the script, to done either with session, > or else be done by a further measure such as a shell script. > > Ideally, the location of the default modules directory (or installation > directory) should be available within a session in some variable or from > some function call. > > There are some pre-defined variables, listed in a session by > show all; > but I don't see anything like a directory path there. > Maybe a built-in function returning this directory? Searched to no avail: > http://www.postgresql.org/docs/8.2/interactive/functions.html > > There has been talk about a bigger solution on > http://wiki.postgresql.org/wiki/Module_Manager > but little seems to have happened there in some years. > > An environment variable > $libdir, > is mentioned > http://www.postgresql.org/docs/8.2/static/runtime-config-client.html > but this seems not to be present within a session. > It seems to be expanded within the LANGUAGE C environment, for instance in > tablefunc.sql > ------------------------------------------------- > CREATE OR REPLACE FUNCTION crosstab2(text) > RETURNS setof tablefunc_crosstab_2 > AS '$libdir/tablefunc','crosstab' > LANGUAGE C STABLE STRICT; > ------------------------------------------------- > > Thanks! > > -- > | - - - - - - - - - - - - - - - - - - - - - - - - - > | Steve White +49(331)7499-202 > | E-Science Zi. 27 Villa Turbulenz > | - - - - - - - - - - - - - - - - - - - - - - - - - > | Astrophysikalisches Institut Potsdam (AIP) > | An der Sternwarte 16, D-14482 Potsdam > | > | Vorstand: Prof. Dr. Matthias Steinmetz, Peter A. Stolz > | > | Stiftung privaten Rechts, Stiftungsverzeichnis Brandenburg: III/7-71-026 > | - - - - - - - - - - - - - - - - - - - - - - - - - -- | - - - - - - - - - - - - - - - - - - - - - - - - - | Steve White +49(331)7499-202 | E-Science Zi. 27 Villa Turbulenz | - - - - - - - - - - - - - - - - - - - - - - - - - | Astrophysikalisches Institut Potsdam (AIP) | An der Sternwarte 16, D-14482 Potsdam | | Vorstand: Prof. Dr. Matthias Steinmetz, Peter A. Stolz | | Stiftung privaten Rechts, Stiftungsverzeichnis Brandenburg: III/7-71-026 | - - - - - - - - - - - - - - - - - - - - - - - - -