GitHub - pgcentralfoundation/pgrx: Build Postgres Extensions with Rust!

Build Postgres Extensions with Rust!

pgrx is a framework for developing PostgreSQL extensions in Rust and strives to be as idiomatic and safe as possible. pgrx supports Postgres 13 through Postgres 18. Want to chat with us or get a question answered?

Please join our Discord Server.

We are also in need of financial sponsorship.

Key Features

A fully managed development environment with cargo-pgrx

cargo pgrx new: Create new extensions quickly cargo pgrx init: Install new (or register existing) PostgreSQL installs cargo pgrx run: Run your extension and interactively test it in psql (or pgcli) cargo pgrx test: Unit-test your extension across multiple PostgreSQL versions cargo pgrx package: Create installation packages for your extension More in the README.md!

Target Multiple Postgres Versions

Support from Postgres 13 to Postgres 17 from the same codebase Use Rust feature gating to use version-specific APIs Seamlessly test against all versions

Automatic Schema Generation

Implement extensions entirely in Rust Automatic mapping for many Rust types into PostgreSQL SQL schemas generated automatically (or manually via cargo pgrx schema) Include custom SQL with extension_sql! & extension_sql_file!

Safety First

Translates Rust panic!s into Postgres ERRORs that abort the transaction, not the process Memory Management follows Rust's drop semantics, even in the face of panic! and elog(ERROR) #[pg_guard] procedural macro to ensure the above Postgres Datums are Option<T> where T: FromDatum

NULL Datums are safely represented as Option::<T>::None

First-class UDF support

Annotate functions with #[pg_extern] to expose them to Postgres Return pgrx::iter::SetOfIterator<'a, T> for RETURNS SETOF Return pgrx::iter::TableIterator<'a, T> for RETURNS

TABLE (...) Create trigger functions with #[pg_trigger]

Easy Custom Types

#[derive(PostgresType)] to use a Rust struct as a Postgres type

By default, represented as a CBOR-encoded object in-memory/on-disk, and JSON as human-readable Supports #[pg_binary_protocol] to generate binary protocol send/recv functions Provide custom in-memory/on-disk/human-readable representations

#[derive(PostgresEnum)] to use a Rust enum as a Postgres enum Composite types supported with the pgrx::composite_type!("Sample") macro

Server Programming Interface (SPI)

Safe access into SPI Transparently return owned Datums from an SPI context

Advanced Features

Safe access to Postgres' MemoryContext system via pgrx::PgMemoryContexts Executor/planner/transaction/subtransaction hooks Safely use Postgres-provided pointers with pgrx::PgBox<T> (akin to alloc::boxed::Box<T>) #[pg_guard] proc-macro for guarding extern "C-unwind" Rust functions that need to be passed into Postgres Access Postgres' logging system through eprintln!-like macros Direct unsafe access to large parts of Postgres internals via the pgrx::pg_sys module New features added regularly!

System Requirements PGRX has been tested to work on x86_64 Linux, aarch64 Linux, aarch64 macOS, and x86_64 Windows targets⹋. It is currently expected to work on other "Unix" OS with possible small changes, but those remain untested.

A Rust toolchain: rustc, cargo, and rustfmt. The recommended way to get these is from https://rustup.rs † git libclang 11 or greater (for bindgen)

Debian-likes: apt install libclang-dev or apt install clang RHEL-likes: yum install clang Windows: download installers from https://github.com/llvm/llvm-project/releases

C compiler

Linux and MacOS: GCC or Clang if cshim feature is enabled, and no need if the cshim feature is disabled Windows: MSVC or Clang

PostgreSQL's build dependencies ‡

Debian-likes:

sudo apt-get install build-essential libreadline-dev zlib1g-dev flex bison libxml2-dev libxslt-dev libssl-dev libxml2-utils xsltproc ccache pkg-config RHEL-likes: sudo yum install -y bison-devel readline-devel zlib-devel openssl-devel wget ccache && sudo yum groupinstall -y 'Development Tools'

† PGRX has no MSRV policy, thus may require the latest stable version of Rust, available via Rustup ‡ A local PostgreSQL server installation is not required. On Linux and MacOS, cargo pgrx can download and compile PostgreSQL versions on its own. On Windows, cargo pgrx downloads precompiled PostgreSQL versions from EnterpriseDB. ⹋ PGRX has not been tested to work on 32-bit, but the library attempts to handle conversion of pg_sys::Datum to and from int8 and double types. Use it only for your own risk. We do not plan to add official support without considerable ongoing technical and financial contributions. macOS Running PGRX on a Mac requires some additional setup. The Mac C compiler (clang) and related tools are bundled with Xcode. Xcode can be installed from the Mac App Store. For additional C libraries, it's easiest to use Homebrew. In particular, you will probably need these if you don't have them already: brew install git icu4c pkg-config The config script that Postgres 17 uses in its build process does not automatically detect the Homebrew install directory. (Earlier versions of Postgres do not have this problem.) You may see this error: configure: error: ICU library not found To fix it, run export PKG_CONFIG_PATH=/opt/homebrew/opt/icu4c/lib/pkgconfig

on the command line before you run cargo pgrx init Troubleshooting

Every once in a while, Xcode will update itself and move the directory that contains the C compiler. When the Postgres ./config process runs during the build, it grabs the current directory and stores it, which means that there will be build errors if you do a full rebuild of your project and the old directory has disappeared. The solution is re-run cargo pgrx init so the Postgres installs get rebuilt.

If a build fails with errors like unknown type name: uint8_t: You may be using too-old Xcode. This often happens with an Apple Clang version below 15. This can be fixed by updating Xcode and the command-line tools. You may require an OS update if the Xcode version you must update to does not support your current macOS version.

Windows Running PGRX on Windows requires MSVC prerequisites. On Windows, please follow https://rust-lang.github.io/rustup/installation/windows-msvc.html to set up it. Getting Started Before anything else, install the system dependencies. Now install the cargo-pgrx sub-command. cargo install --locked cargo-pgrx Once cargo-pgrx is ready you can initialize the "PGRX Home" directory: cargo pgrx init The init command downloads all currently supported PostgreSQL versions, compiles them to ${PGRX_HOME}, and runs initdb. It's also possible to use an existing (user-writable) PostgreSQL install, or install a subset of versions, see the README.md of cargo-pgrx for details. Now you can begin work on a specific pgrx extension: cargo pgrx new my_extension cd my_extension This will create a new directory for the extension crate. $ tree . ├── Cargo.toml ├── my_extension.control ├── sql └── src └── lib.rs

2 directories, 3 files

The new extension includes an example, so you can go ahead and run it right away. cargo pgrx run This compiles the extension to a shared library, copies it to the specified Postgres installation, starts that Postgres instance and connects you to a database named the same as the extension. Once cargo-pgrx drops us into psql we can load the extension and do a SELECT on the example function. my_extension=# CREATE EXTENSION my_extension; CREATE EXTENSION

my_extension=# SELECT hello_my_extension(); hello_my_extension --------------------- Hello, my_extension (1 row) For more details on how to manage pgrx extensions see Managing pgrx extensions. Cross-compiling So far, cross-compiling extensions with pgrx has only been demonstrated under nix. Proper support in nixpkgs is still in flux, so watch this space.

In order to cross-compile outside of nix, you will need two ingredients:

A sysroot for the cross architecture, with rust configured to invoke the linker with flags to link against this sysroot. A pg_config program that provides values associated with postgres compiled for the desired cross architecture. This can be achieved by either emulating pg_config with qemu-user or similar, or replicating pg_config with a shell script.

Then, modify the standard build process in the following ways:

Pass --no-run to cargo pgrx init, since we cannot run postgres for the cross machine. Pass one of the --pgXX flags to cargo pgrx init with the aforementioned pg_config. Pass the --pg-config flag to cargo pgrx package with the aforementioned pg_config. Pass the --target flag to cargo pgrx package with the rust triple of the cross machine.

cargo pgrx run will not not work. Upgrading As new Postgres versions are supported by pgrx, you can re-run the pgrx init process to download and compile them: cargo pgrx init Mapping of Postgres types to Rust

Postgres Type Rust Type (as Option<T>)

bytea Vec<u8> or &[u8] (zero-copy)

text String or &str (zero-copy)

varchar String or &str (zero-copy) or char

"char" i8

smallint i16

integer i32

bigint i64

oid u32

real f32

double precision f64

bool bool

json pgrx::Json(serde_json::Value)

jsonb pgrx::JsonB(serde_json::Value)

date pgrx::Date

time pgrx::Time

timestamp pgrx::Timestamp

time with time zone pgrx::TimeWithTimeZone

timestamp with time zone pgrx::TimestampWithTimeZone

anyarray pgrx::AnyArray

anyelement pgrx::AnyElement

box pgrx::pg_sys::BOX

circle pgrx::pg_sys::CIRCLE

point

pgrx::pg_sys::Point

tid pgrx::pg_sys::ItemPointerData

cstring &core::ffi::CStr

inet pgrx::Inet(String) -- TODO: needs better support

numeric pgrx::Numeric<P, S> or pgrx::AnyNumeric

void ()

ARRAY[]::<type> Vec<Option<T>> or pgrx::Array<T> (zero-copy)

int4range pgrx::Range<i32>

int8range pgrx::Range<i64>

numrange pgrx::Range<Numeric<P, S>> or pgrx::Range<AnyRange>

daterange pgrx::Range<pgrx::Date>

tsrange pgrx::Range<pgrx::Timestamp>

tstzrange pgrx::Range<pgrx::TimestampWithTimeZone>

NULL Option::None

internal pgrx::PgBox<T> where T is any Rust/Postgres struct

uuid pgrx::Uuid([u8; 16])

There are also IntoDatum and FromDatum traits for implementing additional type conversions, along with #[derive(PostgresType)] and #[derive(PostgresEnum)] for automatic conversion of custom types. Note that text and varchar are converted to &str or String, so PGRX assumes any Postgres database you use it with has UTF-8-compatible encoding. Currently, PGRX will panic if it detects this is incorrect, to inform you, the programmer, that you were wrong. However, it is best to not rely on this behavior, as UTF-8 validation can be a performance hazard. This problem was previously assumed to simply not happen, and PGRX may decide to change the details of how it does UTF-8 validation checks in the future in order to mitigate performance hazards. The default Postgres server encoding is SQL_ASCII, and it guarantees neither ASCII nor UTF-8 (as Postgres will then accept but ignore non-ASCII bytes). For best results, always use PGRX with UTF-8, and set database encodings explicitly upon database creation.