plproxy

Overview

PIGSTY RPM and PGDG DEB are aligned at 2.12.0.; Upstream COPYRIGHT text matches ISC.

Version

Build

You can build the RPM packages for plproxy using pig build:

pig build pkg plproxy         # build RPM packages

Install

You can install plproxy directly. First, make sure the PGDG and PIGSTY repositories are added and enabled:

pig repo add pgsql -u          # Add repo and update cache

Install the extension using pig or apt/yum/dnf:

pig install plproxy;          # Install for current active PG version

pig ext install -y plproxy -v 18  # PG 18
pig ext install -y plproxy -v 17  # PG 17
pig ext install -y plproxy -v 16  # PG 16
pig ext install -y plproxy -v 15  # PG 15
pig ext install -y plproxy -v 14  # PG 14

dnf install -y plproxy_18       # PG 18
dnf install -y plproxy_17       # PG 17
dnf install -y plproxy_16       # PG 16
dnf install -y plproxy_15       # PG 15
dnf install -y plproxy_14       # PG 14

apt install -y postgresql-18-plproxy   # PG 18
apt install -y postgresql-17-plproxy   # PG 17
apt install -y postgresql-16-plproxy   # PG 16
apt install -y postgresql-15-plproxy   # PG 15
apt install -y postgresql-14-plproxy   # PG 14

Create Extension:

CREATE EXTENSION plproxy;

Usage

Sources:

• PL/Proxy 2.12.0 README
• PL/Proxy language syntax
• PL/Proxy cluster configuration
• PL/Proxy 2.12.0 release

PL/Proxy is a PostgreSQL procedural language handler that enables remote procedure calls between PostgreSQL databases, with optional sharding.

Create the Extension

CREATE EXTENSION plproxy;

Language Statements

PL/Proxy functions use four types of statements:

Cluster selection – connect to a pre-configured cluster:

CREATE FUNCTION get_user(i_id int) RETURNS SETOF users AS $$
    CLUSTER 'mycluster';
    RUN ON i_id;
$$ LANGUAGE plproxy;

Direct connection – use a connection string:

CREATE FUNCTION get_config(key text) RETURNS text AS $$
    CONNECT 'host=remotehost dbname=config';
    SELECT val FROM config WHERE key = $1;
$$ LANGUAGE plproxy;

Execution Modes

RUN ON hash – route to a specific partition based on a hash:

CREATE FUNCTION get_user_settings(i_username text) RETURNS SETOF user_settings AS $$
    RUN ON namehash(i_username);
$$ LANGUAGE plproxy;

RUN ON ALL – execute on all databases in parallel:

CREATE FUNCTION get_all_counts() RETURNS SETOF record AS $$
    RUN ON ALL;
    SELECT count(*) FROM users;
$$ LANGUAGE plproxy;

RUN ON ANY – randomly select a server:

CREATE FUNCTION get_random_quote() RETURNS text AS $$
    RUN ON ANY;
    SELECT quote FROM quotes ORDER BY random() LIMIT 1;
$$ LANGUAGE plproxy;

Cluster Configuration

Clusters are configured using SQL/MED (Management of External Data):

CREATE SERVER mycluster FOREIGN DATA WRAPPER plproxy
    OPTIONS (
        connection_lifetime '1800',
        p0 'host=node0 dbname=mydb',
        p1 'host=node1 dbname=mydb',
        p2 'host=node2 dbname=mydb',
        p3 'host=node3 dbname=mydb'
    );

CREATE USER MAPPING FOR CURRENT_USER
    SERVER mycluster
    OPTIONS (user 'proxy_user', password 'secret');

Caveats

• PL/Proxy routes function calls, not arbitrary cross-database transactions. Design remote functions to be retry-safe and keep transaction boundaries explicit.
• Cluster definitions and user mappings can expose connection details; protect catalog access and prefer restricted remote roles.
• Release 2.12.0 fixes quoted-identifier parsing in SELECT, a null-pointer issue in plproxy_fdw_validator, Windows builds, and PostgreSQL 19 compatibility. Existing SQL objects do not need a new usage pattern, but databases upgraded from an older extension version should run the matching ALTER EXTENSION plproxy UPDATE path.