[Pgpool-hackers] First pcp command as PostgreSQL function

[Jehan-Guillaume (ioguix) de Rorthais]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

PFA a new version supporting postgresql foreign server capablility
(postgresql 9.0+). Same instruction than in the previous mail to build
it. Make sure to comment line 29 your include/pool_type.h:

  // #include "config.h"

After building and installing the module, here is a typical usage:

* Setup:

  $ createdb pgpool
  $ createuser -lRSD pgpool
  $ psql pgpool postgres
  =# CREATE FOREIGN DATA WRAPPER pgpool;

  =# CREATE SERVER pgpool0 FOREIGN DATA WRAPPER pgpool OPTIONS (
    host '127.0.0.1', port '9898', timeout '10'
  );

  =# CREATE USER MAPPING FOR pgpool SERVER pgpool0 OPTIONS (
    user 'pgpool_adm', password 'password'
  );

  =# CREATE EXTENSION CREATE EXTENSION pgpool_adm ;

* Usage:

  $ psql pgpool postgres
  =# -- raise an error if the connected role has no server mapping
  =# SELECT * FROM pcp_node_info(0, 'pgpool1');
  ERROR:  user mapping not found for "postgres"

  =# SET ROLE pgpool;

  => SELECT * FROM pcp_node_info(1, 'pgpool0');
   host  | port |             status              |   weight
  -------+------+---------------------------------+-------------
   /tmp/ | 5484 | Waiting for connection to start | 1.46602e+13
  (1 row)

  => -- is equivalent to
  => SELECT * FROM pcp_node_info(1, '127.0.0.1',
      10, 9898, 'pgpool_adm', 'pgpass'
    );
   host  | port |             status              |   weight
  -------+------+---------------------------------+-------------
   /tmp/ | 5484 | Waiting for connection to start | 1.46602e+13
  (1 row)

  => -- error if the server is unknown
  => SELECT * FROM pcp_node_info(1, 'pgpool10');
  ERROR:  server "pgpool10" does not exist

  => -- back as postgres so we can see user mapping options
  => SET ROLE postgres;

  =# \dew
        List of foreign-data wrappers
    Name  |  Owner   | Handler | Validator
  --------+----------+---------+-----------
   pgpool | postgres | -       | -
  (1 row)

  =# \des+
  List of foreign servers
  -[ RECORD 1 ]--------+--------------------------------------
  Name                 | pgpool0
  Owner                | postgres
  Foreign-data wrapper | pgpool
  Access privileges    |
  Type                 |
  Version              |
  Options              | {host=127.0.0.1,port=9898,timeout=10}

  =# \deu+
                    List of user mappings
   Server  | User name |              Options
  ---------+-----------+-----------------------------------
   pgpool0 | pgpool    | {user=pgpool_adm,password=password}


On 31/03/2011 13:18, Jehan-Guillaume (ioguix) de Rorthais wrote:
> Hello,
> 
> Following our previous discussions I created a PostgreSQL contrib module
> which brings pgpool's pcp command as function in PostgreSQL. I called it
> pgpool_adm, any better ideas ?
> 
> As a first step, I decided to focus on one function only: pcp_node_info.
> I'll add other ones as soon as this one will be committable.
> 
> INSTALL: (tested against current 9.1devel HEAD)
> 
>   $ cd $PGSRC/contrib
>   $ tar zxf pgpool_adm.tgz
>   $ cd pgpool_adm
>   $ make && sudo make install
> 
> then:
> 
>   $ psql pgpool
>   CREATE SCHEMA pgpool;
>   CREATE EXTENSION pgpool_adm WITH SCHEMA pgpool ;
>   SELECT * FROM pgpool.pcp_node_info(0, 'localhost', 10, 9898,
> 'pgpool_adm', 'password');
>      host    | port |             status              |   weight
>   -----------+------+---------------------------------+-------------
>    localhost | 5491 | Waiting for connection to start | 1.46602e+13
>   (1 ligne)
> 
> If you don't want/cannot test with 9.1, just adapt and run the "CREATE
> FUNCTION" query located in pgpool_adm/pgpool_adm--1.0.sql.
> 
> NOTE:
> I had to mess a bit to make the compilation work out of the box because
> of my setup of pgpool installing stuffs in /usr/local/{lib,include}.
> Basically editing my system ldd setup and configuring postgresql with
> "--with-libraries" and "--with-includes". Drop me a mail if you have any
> trouble.
> 
> TODO:
>   * I will try to make it work with foreign server and user mappings so
> we will not have to give all servers parameters (host, port, timeout),
> but most importantly, not have to write in clear text authentication
> parameters.
>   * Why do we backend_weight/RAND_MAX in the code ?
> 
> Feedback & comments ?

_______________________________________________
Pgpool-hackers mailing list
Pgpool-hackers at pgfoundry.org
http://pgfoundry.org/mailman/listinfo/pgpool-hackers


- -- 
Jehan-Guillaume (ioguix) de Rorthais
DBA
http://www.dalibo.com
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.11 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/

iEYEARECAAYFAk2U1hEACgkQXu9L1HbaT6IncwCgyoZxyKUpdW8hI0B1e5rnL819
Js8AnjfmyXIblGbSBjyMEGF/OHF1vReB
=O+tQ
-----END PGP SIGNATURE-----
-------------- next part --------------
A non-text attachment was scrubbed...
Name: pgpool_adm_2.tgz
Type: application/x-gtar
Size: 2434 bytes
Desc: not available
URL: <http://pgfoundry.org/pipermail/pgpool-hackers/attachments/20110331/5721f275/attachment.gtar>