Re: [GENERAL] How best to load modules?
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-ScienceZi. 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-ScienceZi. 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 | - - - - - - - - - - - - - - - - - - - - - - - - - -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] 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-ScienceZi. 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 | - - - - - - - - - - - - - - - - - - - - - - - - - -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How best to load modules?
Steve White swh...@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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How best to load modules?
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 swh...@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-ScienceZi. 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 | - - - - - - - - - - - - - - - - - - - - - - - - - -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
