pg_strict

Overview

manually upgraded PGRX from 0.16.1 to 0.17.0 by Vonng

Version

Build

You can build the RPM / DEB packages for pg_strict using pig build:

pig build pkg pg_strict         # build RPM / DEB packages

Install

You can install pg_strict 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:

• Install
• pig
• dnf
• apt

pig install pg_strict;          # Install for current active PG version

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

dnf install -y pg_strict_18       # PG 18
dnf install -y pg_strict_17       # PG 17
dnf install -y pg_strict_16       # PG 16
dnf install -y pg_strict_15       # PG 15
dnf install -y pg_strict_14       # PG 14

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

Preload:

shared_preload_libraries = 'pg_strict';

Create Extension:

CREATE EXTENSION pg_strict;

Usage

Source: README, Release v1.0.5, API source

pg_strict blocks or warns on UPDATE and DELETE statements that omit a WHERE clause. It installs a post_parse_analyze_hook, so it must be loaded from shared_preload_libraries.

Required setup

-- postgresql.conf
shared_preload_libraries = 'pg_strict'

CREATE EXTENSION pg_strict;

GUCs

• pg_strict.require_where_on_update
• pg_strict.require_where_on_delete

Each setting supports off, warn, and on.

SET pg_strict.require_where_on_update = 'on';
SET pg_strict.require_where_on_delete = 'warn';

Helper functions

SELECT pg_strict_version();
SELECT * FROM pg_strict_config();

SELECT pg_strict_check_where_clause('DELETE FROM t', 'DELETE');
SELECT pg_strict_validate_update('UPDATE t SET x = 1 WHERE id = 42');
SELECT pg_strict_validate_delete('DELETE FROM t WHERE id = 42');

SELECT pg_strict_enable_update();
SELECT pg_strict_warn_delete();
SELECT pg_strict_disable_delete();

• pg_strict_set_update_mode(mode) and pg_strict_set_delete_mode(mode) provide generic mode setters.
• SET LOCAL works for one-off bulk operations inside a transaction.

Caveats

• Enforcement is presence-based, not intent-based: any non-null WHERE clause satisfies the rule.
• Only UPDATE and DELETE are checked.
• Current upstream release is 1.0.5; the Pigsty note about pgrx 0.17.0 is packaging/build metadata, not a documented user-facing feature change.