PL/Proxy

From PostgreSQL wiki

(Difference between revisions)
Jump to: navigation, search
(Replace CVS with GIT links)
(update yum website URL)
 
Line 34: Line 34:
  
 
* Deb: PL/Proxy is included in [http://www.debian.org Debian] and [http://www.ubuntu.com Ubuntu] official repositories as postgresql-X.Y-plproxy package, where X.Y is postgres major version.
 
* Deb: PL/Proxy is included in [http://www.debian.org Debian] and [http://www.ubuntu.com Ubuntu] official repositories as postgresql-X.Y-plproxy package, where X.Y is postgres major version.
* RPM: http://www.pgsqlrpms.org
+
* RPM: [http://yum.postgresql.org/ PGDG Yum repository]
  
 
== Community Support ==
 
== Community Support ==

Latest revision as of 10:46, 25 May 2012

Contents

[edit] Project Overview

PL/Proxy is database partitioning system implemented as PL language. Main idea is that proxy function will be created with same signature as remote function to be called, so only destination info needs to be specified inside proxy function body.

[edit] Project Status

Stable, in production.

[edit] Features

  • PL/Proxy functions detect remote functions to be called from their own signature.
  • Function can be run on one, some or all members of the cluster.
  • If query is executed on several partitions, it will happen in parallel.
  • Queries are run in auto-commit mode on the remote server.
  • Query parameters are sent separately from query body, thus avoiding quoting/unquoting overhead on both sides.
  • Does not contain code connection pooling, works with external pooler if needed, preferably PgBouncer.

[edit] Documentation

  • FAQ, describes few design decisions.
  • Official tutorial

[edit] Downloads

Binary packages (may not be up-to-date)

  • Deb: PL/Proxy is included in Debian and Ubuntu official repositories as postgresql-X.Y-plproxy package, where X.Y is postgres major version.
  • RPM: PGDG Yum repository

[edit] Community Support

[edit] Quick Examples

[edit] Simple remote function call

Connect to database "users" on localhost and call SQL function there: SELECT * FROM get_user_email($1);

CREATE FUNCTION get_user_email(username text) RETURNS text AS $$

   CONNECT 'dbname=users';
 
$$ LANGUAGE plproxy;

[edit] Partitioned remote function call

Users are spread over several databases, partition number is acquired by taking hashtext(username). This needs also configuring the cluster, described in documentation. After this is done, actual proxy function looks following:

CREATE FUNCTION get_user_email(username text) RETURNS text AS $$

   CLUSTER 'userdb';
   RUN ON hashtext(username);

$$ LANGUAGE plproxy;
Personal tools