Overview

PL/Proxy is a proxy language used for remote database procedure calls and data partitioning between databases based on hashing field values.

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.

Documentation

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.

  • If proxy and partition backend versions match, PL/Proxy tries to use binary I/O if possible.

Restrictions

  • PL/Proxy launches connections into each part from each backend, so in heavy-duty environment is necessary to put pooler between PL/Proxy and partitions. We have specially for that developed ../PgBouncer which is a pooler that can do statement-based pooling - release server after each SQL statement.

Language examples

Simple remote function call

Connect to dbname=users and run following SQL there: SELECT * from get_user_email($1);

CREATE FUNCTION get_user_email(username text)
RETURNS text AS $$
    CONNECT 'dbname=users';
$$ LANGUAGE plproxy;

Partitioned remote 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;

Run user-specified SELECT statement remotely

By default, PL/Proxy generates query based on its own signature. But this can be overrided by giving explicit SELECT statement to run.

CREATE FUNCTION get_user_location(text)
RETURNS SETOF text AS $$
    CLUSTER 'userdb';
    RUN ON hashtext($1);
    SELECT email FROM users WHERE user = $1;
$$ LANGUAGE plproxy;