Saturday, June 2, 2012

Compiling PL/Proxy with PostgresPlus Advance Server 9.1

PostgresPlus Advance Server 9.1(PPAS) is EnterpriseDB product, which comes with enterprise features as additional with community PostgreSQL. Most of the contrib modules(pgfoundry) can be pluged into this product using Stackbuilder. However,currently Pl/Proxy is not bundled or downloadable with Stack-builder. So,here is how you can compile the Pl/Proxy with PPAS 9.1.

1. Download Pl/Proxy.
wget http://pgfoundry.org/frs/download.php/3274/plproxy-2.4.tar.gz
tar -xvf plproxy-2.4.tar.gz
make PG_CONFIG=/opt/PostgresPlus/9.1AS/bin/pg_config
make intall PG_CONFIG=/opt/PostgresPlus/9.1AS/bin/pg_config

Note: Flex & Bison must be installed before compiling pl/proxy.

2. After sucessfull configuration, you get two files, plproxy.so in $PGPATH/lib & plproxy--2.4.0.sql in $PGPATH/share/extention/ location.
Execute the .sql file which creates call_handler & language.
bash-4.1$ psql -p 5444 -U enterprisedb -d edb -f /opt/PostgresPlus/9.1AS/share/extension/plproxy--2.4.0.sql
CREATE FUNCTION
CREATE LANGUAGE
CREATE FUNCTION
CREATE FOREIGN DATA WRAPPER

Now you can see the language installed.
edb=# \dL
        List of languages
  Name   |    Owner     | Trusted
---------+--------------+---------
 edbspl  | enterprisedb | t
 plpgsql | enterprisedb | t
 plproxy | enterprisedb | f
(3 rows)

3. Lets test the sample code with pl/proxy.
create table users(username text,blog text);
insert into users values('Raghav','raghavt.blogspot.com');

CREATE or replace  FUNCTION get_user_blog(i_username text)
RETURNS SETOF text AS $$
    CONNECT 'dbname=edb';
    SELECT blog FROM users WHERE username = $1;
$$ LANGUAGE plproxy;

edb=# select * from get_user_blog('Raghav');
          get_user_blog
----------------------------------
 raghavt.blogspot.com
(1 rows)

All set to go testing with pl/proxy on PPAS 9.1. If you want to know how to setup pl/proxy, follow below links.
http://www.depesz.com/2011/12/02/the-secret-ingredient-in-the-webscale-sauce/
http://kaiv.wordpress.com/2007/07/27/postgresql-cluster-partitioning-with-plproxy-part-i/

--Raghav

3 comments :

Anonymous said...

What's up, I would like to subscribe for this webpage to obtain
latest updates, therefore where can i do it please help.



my page :: homepage []

IPN Bala said...

This is clear steps to compile PL/Proxy on Unix/ Linux server. Is there any method for compiling PL/Proxy on windows server with Postgres Advanced server?.
We did the same steps in windows server with the help of Cygwin software (Which help to run Wget, Tar, Make command) but it couldn't be done. We are struct on step 3.


saandhuna@GSSTVL59 /cygdrive/c/ProgramFiles/PostgresPlus/9.4AS/bin/plproxy-2.4
$ make PG_CONFIG=c:/ProgramFiles/Postgresplus/9.4AS/bin/pg_config
Makefile:77: C:/ProgramFiles/Postgresplus/9.4AS/lib/pgxs/src/makefiles/pgxs.mk: No such file or directory
make: *** No rule to make target 'C:/ProgramFiles/Postgresplus/9.4AS/lib/pgxs/src/makefiles/pgxs.mk'. Stop.

Please help to solve this.

Thanks in Advance.

Raghavendra said...

Correct below path from

make PG_CONFIG=c:/ProgramFiles/Postgresplus/9.4AS/bin/pg_config

to

make PG_CONFIG=c:/ProgramFiles/PostgresPlus/9.4AS/bin/pg_config

I never used windows though for this testing.

Post a Comment

Creative Commons License
This work is licensed under a Creative Commons Attribution-NonCommercial 4.0 International License