pgbench Common Library Extraction & Server Extension

Hi

Currently pgbench allows only standard database initialisation to be
offloaded to the server side. It would be nice to do the same for any
initialisation or testing code.

Attached are two patches which extract the statistical random
distribution generators, domain permutations, and 64-bit hashing
routines from the current monolith contrib/pgbench/pgbench.c source
file and make them also available as a pgbench server extension so
that more workloads can run the same way in pgbench and the server.

The code is almost entirely generated by AI harness. However, since
this work involves well-defined refactoring and wrapping C code to be
callable from SQL, the code quality should not be affected. I found
nothing obvious to change during the review.

I have also attached a proposal for Refactoring pgbench for a Modular
Architecture to make both maintenance and future additions easier.
Since this is again mainly shuffling existing code around I expect
this to be a relatively low human effort endeavour but I would still
like to get some feedback before taking it on. The end of that
document hints at some possible enhancements.
----------------
Following is the rep[ort of the changes.

## Executive Summary

This patch set extracted the statistical distribution, pseudorandom
permutation, and 64-bit hashing algorithms from the pgbench frontend
client (src/bin/pgbench/pgbench.c) into PostgreSQL's shared common library
(src/common/pgbench_funcs.c and src/include/common/pgbench_funcs.h).

## The implementation is structured across two independent Git commits:

Commit 1 : Library extraction into src/common/ and
reintegration into src/bin/pgbench/pgbench.c.
Commit 2 : Addition of the contrib/pgbench server extension exposing
all functions in SCHEMA pgbench, with complete SGML documentation.

┌─────────────────────────────────────────┐
│ src/include/common/pgbench_funcs.h │
│ src/common/pgbench_funcs.c │
└────────────────────┬────────────────────┘
│
┌─────────────────┴─────────────────┐
│ │
▼ ▼
┌───────────────────────────┐ ┌───────────────────────────┐
│ src/bin/pgbench │ │ contrib/pgbench │
│ (Frontend Client) │ │ (Server Extension) │
│ │ │ │
│ Links: libpgcommon.a │ │ Links: pgbench_funcs.o │
│ Calls: │ │ Exposes: │
│ - pgbench_random() │ │ - pgbench.random() │
│ - pgbench_random_*() │ │ - pgbench.random_*() │
│ - pgbench_hash_*() │ │ - pgbench.hash_*() │
│ - pgbench_permute() │ │ - pgbench.permute() │
└───────────────────────────┘ └───────────────────────────┘

### Commit 1: Library Extraction & Frontend Reintegration

- Commit ID: 31e8e96c92e
- Message: Extract pgbench low-level functions into src/common

Key Changes

1. Header Definition (src/include/common/pgbench_funcs.h):

- Distribution parameter bounds: PGBENCH_MIN_GAUSSIAN_PARAM (2.0),
PGBENCH_MIN_ZIPFIAN_PARAM (1.001), PGBENCH_MAX_ZIPFIAN_PARAM (1000.0).
- Hashing constants: PGBENCH_FNV_PRIME, PGBENCH_FNV_OFFSET_BASIS,
PGBENCH_MM2_MUL, PGBENCH_MM2_MUL_TIMES_8, PGBENCH_MM2_ROT.
- Public function prototypes taking explicit pg_prng_state *state pointers
and primitive scalar types.

2. Common Implementation (src/common/pgbench_funcs.c):

- Implemented pure, zero-dependency routines compiled for both frontend
(-DFRONTEND) and backend without server header entanglements.
- Extracted routines:
- pgbench_random(pg_prng_state *state, int64 min, int64 max)
- pgbench_random_gaussian(pg_prng_state *state, int64 min, int64 max,
double parameter)
- pgbench_random_exponential(pg_prng_state *state, int64 min, int64
max, double parameter)
- pgbench_random_zipfian(pg_prng_state *state, int64 min, int64 max,
double s) & computeIterativeZipfian()
- pgbench_random_poisson(pg_prng_state *state, double center)
- pgbench_hash_fnv1a(int64 val, uint64 seed)
- pgbench_hash_murmur2(int64 val, uint64 seed)
- pgbench_permute(int64 val, int64 isize, int64 seed)

3. Build System Registration:

- src/common/Makefile: Added pgbench_funcs.o to OBJS_COMMON.
- src/common/meson.build: Added 'pgbench_funcs.c' to common_sources.

4. Frontend Reintegration (src/bin/pgbench/pgbench.c):

- Included common/pgbench_funcs.h.
- Removed duplicate static implementations (getrand, getGaussianRand,
getExponentialRand, getZipfianRand, getPoissonRand, getHashMurmur2,
getHashFnv1a, permute).
- Replaced call sites in evalStandardFunc(), chooseScript(), and
connection throttling loops with pgbench_* functions.

### Commit 2: contrib/pgbench Server Extension & Documentation

- Commit ID: 53c905816e2
- Message: Add contrib/pgbench extension exposing benchmark functions in
SCHEMA pgbench

Key Changes

1. Extension Control & Schema (contrib/pgbench/pgbench.control):

# pgbench extension
comment = 'pgbench distribution, permutation, and hashing functions'
default_version = '1.0'
module_pathname = '$libdir/pgbench'
relocatable = false
schema = 'pgbench'

2. SQL Interface Definition (contrib/pgbench/pgbench--1.0.sql):

- Bound to SCHEMA pgbench upon CREATE EXTENSION pgbench;.
- Complete function catalog:

SQL Function Signature

pgbench.setseed(seed double precision)
RETURNS void VOLATILE PARALLEL UNSAFE
"Seed PRNG with float in [-1.0, 1.0]"
pgbench.setseed(seed bigint)
RETURNS void VOLATILE PARALLEL UNSAFE
"Seed PRNG with 64-bit integer"
pgbench.random(min bigint, max bigint)
RETURNS bigint VOLATILE PARALLEL SAFE
"Uniform random integer in [min, max]"
pgbench.random_gaussian(min bigint, max bigint,
parameter double precision)
RETURNS bigint VOLATILE PARALLEL SAFE
"Gaussian random in [min, max], param >= 2.0"
pgbench.random_exponential(min bigint, max bigint,
parameter double precision)
RETURNS bigint VOLATILE PARALLEL SAFE
"Exponential random in [min, max], param > 0.0"
pgbench.random_zipfian(min bigint, max bigint,
parameter double precision)
RETURNS bigint VOLATILE PARALLEL SAFE
"Zipfian random in [min, max], param in [1.001, 1000.0]"
pgbench.random_poisson(center double precision)
RETURNS bigint VOLATILE PARALLEL SAFE
"Poisson random, center > 0.0"
pgbench.hash_murmur2(val bigint, seed bigint DEFAULT 0)
RETURNS bigint IMMUTABLE PARALLEL SAFE
"64-bit Austin Appleby MurmurHash2"
pgbench.hash_fnv1a(val bigint, seed bigint DEFAULT 0)
RETURNS bigint IMMUTABLE PARALLEL SAFE
"64-bit Fowler–Noll–Vo 1a hash"
pgbench.hash(val bigint, seed bigint DEFAULT 0)
RETURNS bigint IMMUTABLE PARALLEL SAFE
"Alias for pgbench.hash_murmur2"
pgbench.permute(val bigint, size bigint, seed bigint DEFAULT 0)
RETURNS bigint IMMUTABLE PARALLEL SAFE
"Bijective pseudorandom permutation of [0, size)"

3. Backend C Module (contrib/pgbench/pgbench.c):

- Per-backend PRNG state initialized via strong random
(pg_prng_strong_seed) or timestamp/PID fallback, customizable via
pgbench.setseed().
- Robust argument verification: range checks, numeric overflow detection
via common/int.h (pg_sub_s64_overflow, pg_add_s64_overflow), and
parameter domain validation throwing standard PostgreSQL ereport(ERROR,
...).

4. Documentation:

- Created doc/src/sgml/pgbench-ext.sgml documenting all functions,
argument limits, behaviors, and SQL examples.
- Registered in doc/src/sgml/filelist.sgml and doc/src/sgml/contrib.sgml
(including addition to trusted extensions list).
- Verified with SGML syntax validation (make -C doc/src/sgml check).

5. Build System & Regression Suites:

- contrib/pgbench/Makefile & contrib/pgbench/meson.build.
- Registered in parent contrib/Makefile and contrib/meson.build.
- Comprehensive regression test suite in contrib/pgbench/sql/pgbench.sql
and expected output in contrib/pgbench/expected/pgbench.out.

### Test Verification Results

1. pgbench Client TAP Tests

make -C src/bin/pgbench check

# +++ tap check in src/bin/pgbench +++
t/001_pgbench_with_server.pl .. ok
t/002_pgbench_no_server.pl .... ok
All tests successful.
Files=2, Tests=681, 5 wallclock secs
Result: PASS

2. contrib/pgbench Extension Regression Tests

make -C contrib/pgbench check

# +++ regress check in contrib/pgbench +++
# initializing database system by copying initdb template
# using temp instance on port 52544 with PID 2123253
ok 1 - pgbench 17 ms
1..1
# All 1 tests passed.

3. SGML Documentation Validation

make -C doc/src/sgml check

/usr/bin/xmllint --nonet --path . --path . --noout --valid postgres.sgml
Result: PASS (0 errors, 0 warnings)

### File Inventory & Git Diffs

Commit 1 Changes

src/bin/pgbench/pgbench.c | 342 ++--------------------------------
src/common/Makefile | 1 +
src/common/meson.build | 1 +
src/common/pgbench_funcs.c | 320 +++++++++++++++++++++++++++++++++
src/include/common/pgbench_funcs.h | 48 ++++++
5 files changed, 386 insertions(+), 326 deletions(-)

Commit 2 Changes

contrib/Makefile | 1 +
contrib/meson.build | 1 +
contrib/pgbench/Makefile | 30 +++++
contrib/pgbench/expected/pgbench.out | 153 ++++++++++++++++++++++
contrib/pgbench/meson.build | 36 ++++++
contrib/pgbench/pgbench--1.0.sql | 69 ++++++++++
contrib/pgbench/pgbench.c | 228 ++++++++++++++++++++++++++++++++
contrib/pgbench/pgbench.control | 6 +
contrib/pgbench/sql/pgbench.sql | 52 ++++++++
doc/src/sgml/contrib.sgml | 2 +
doc/src/sgml/filelist.sgml | 1 +
doc/src/sgml/pgbench-ext.sgml | 240 ++++++++++++++++++++++++++++++++++
12 files changed, 819 insertions(+)