Less common features: rules, NOT ENFORCED constraints, network and XML types, leakproof functions, pipelining

conceptThe concept

• Rule system: rules rewrite a query before planning. Every view is a table with an ON SELECT DO INSTEAD rule named _RETURN. CREATE RULE for INSERT/UPDATE/DELETE still exists, but triggers are clearer and handle COPY, per-row logic and RETURNING predictably.
• NOT ENFORCED (18): CHECK and FOREIGN KEY constraints can be declared but not checked. They document relationships for tools and readers. A foreign key can later be switched to ENFORCED, which validates existing rows.
• Network types: inet (address with optional mask), cidr (network), macaddr. Operators such as << (contained in) and >>= (contains or equals) can use a GiST index with inet_ops.
• XML: the xml type, xmlelement/xmlforest/xmlagg to build documents, xpath and XMLTABLE to shred them into rows.
• security_barrier and LEAKPROOF: a view used to hide rows must not let a user's function see rows the view filters out. With security_barrier, the view's conditions run first unless the user's function is marked LEAKPROOF. Row-level security uses the same mechanism.
• Procedural languages: PL/pgSQL is built in and trusted. PL/Python (plpython3u) and PL/Perl untrusted (plperlu) can access the file system and network, so only superusers can create functions in them. Trusted languages (plperl, plv8, PL/Rust) are sandboxed.
• Wire protocol: the simple protocol sends SQL text; the extended protocol separates Parse, Bind and Execute, which enables parameters, prepared statements and binary results. Pipeline mode sends many queries without waiting for each result, removing one network round trip per query.

whyWhy it matters

You will meet these in existing systems: rules left from old partitioning setups, IP allow-lists stored as text, XML from enterprise integrations, warehouses migrated with unenforced foreign keys. You also need security barrier semantics to understand why some views and RLS policies are slower than expected, and pipelining when a chatty application is latency-bound across regions.

Use caseReal-world scenario

A service in another region runs 40 small queries per request, each costing a 30 ms round trip. Switching the driver to pipeline mode (or batching with one round trip) cuts request time from 1.2 s to under 100 ms with no SQL change. The same team replaces a firewall table of text addresses with cidr and a GiST index, turning a LIKE scan into an index lookup.

Try itExamples

Run and edit these in the interactive lesson.

SELECT '10.1.2.3'::inet << '10.1.0.0/16'::cidr AS in_subnet,
       network('10.1.2.3/20'::inet) AS network,
       broadcast('10.1.2.3/20'::inet) AS broadcast,
       family('2001:db8::1'::inet) AS family,
       inet_merge('10.1.2.3', '10.1.9.9') AS smallest_common;
BEGIN;
CREATE TABLE allowlist (net cidr PRIMARY KEY, label text);
INSERT INTO allowlist VALUES ('10.0.0.0/8', 'office vpn'), ('192.168.10.0/24', 'lab'), ('203.0.113.0/24', 'partner');
CREATE INDEX ON allowlist USING gist (net inet_ops);
SELECT label FROM allowlist WHERE net >>= '192.168.10.77'::inet;
ROLLBACK;

BEGIN;
CREATE TABLE legacy_orders (
  id int PRIMARY KEY,
  customer_id int REFERENCES customers (id) NOT ENFORCED,
  qty int CONSTRAINT qty_positive CHECK (qty > 0) NOT ENFORCED
);
INSERT INTO legacy_orders VALUES (1, 999999, -5);
SELECT conname, contype, conenforced, convalidated
FROM pg_constraint
WHERE conrelid = 'legacy_orders'::regclass AND contype IN ('c', 'f')
ORDER BY conname;
ROLLBACK;

BEGIN;
CREATE TABLE price_audit (product_id int, old_price numeric, new_price numeric);
CREATE RULE log_price AS ON UPDATE TO products
  WHERE NEW.price <> OLD.price
  DO ALSO INSERT INTO price_audit VALUES (OLD.id, OLD.price, NEW.price);
UPDATE products SET price = price * 1.10 WHERE id <= 3;
SELECT count(*) AS audited FROM price_audit;
CREATE VIEW cheap_products AS SELECT id, name FROM products WHERE price < 20;
SELECT ev_class::regclass AS relation, rulename, ev_type
FROM pg_rewrite
WHERE ev_class IN ('products'::regclass, 'cheap_products'::regclass)
ORDER BY rulename;
ROLLBACK;

BEGIN;
CREATE VIEW gold_plain AS SELECT id, name FROM customers WHERE tier = 'gold';
CREATE VIEW gold_barrier WITH (security_barrier) AS SELECT id, name FROM customers WHERE tier = 'gold';
CREATE FUNCTION peek(text) RETURNS boolean LANGUAGE plpgsql COST 0.0001
  AS $$ BEGIN RETURN true; END $$;
EXPLAIN (COSTS OFF) SELECT * FROM gold_plain WHERE peek(name);
EXPLAIN (COSTS OFF) SELECT * FROM gold_barrier WHERE peek(name);
SELECT proname, proleakproof FROM pg_proc WHERE proname IN ('texteq', 'peek') ORDER BY proname;
ROLLBACK;

SELECT xmlelement(name customer, xmlattributes(id AS id), name) AS xml_row
FROM customers ORDER BY id LIMIT 2;
SELECT *
FROM XMLTABLE('/orders/order'
  PASSING (xml '<orders><order id="1"><total>19.90</total></order><order id="2"><total>5.00</total></order></orders>')
  COLUMNS id int PATH '@id', total numeric PATH 'total');

-- untrusted language: superuser creates functions, others may be granted EXECUTE
CREATE EXTENSION plpython3u;
CREATE FUNCTION slugify(t text) RETURNS text LANGUAGE plpython3u IMMUTABLE AS $$
import re
return re.sub(r'[^a-z0-9]+', '-', t.lower()).strip('-')
$$;

-- psql 18: send several queries in one pipeline
\startpipeline
SELECT count(*) FROM orders WHERE status = $1 \bind shipped \sendpipeline
SELECT count(*) FROM orders WHERE status = $1 \bind pending \sendpipeline
\endpipeline

-- drivers: libpq PQenterPipelineMode, psycopg 3 conn.pipeline(), JDBC batches, pgx Batch

internalsUnder the hood

The rewriter runs between parsing and planning; a rule can turn one statement into several, and each resulting statement re-evaluates the original expressions, so a volatile function such as nextval() or random() in the original runs more than once. For a security barrier view, the planner keeps the view's quals below the user's quals in the plan unless all user functions are leakproof. Leakproof means the function has no side effects and cannot reveal its arguments through errors; most comparison operators on built-in types qualify, which is why simple equality filters can still use indexes through such views.

Avoid / preferDo this, not that

CREATE RULE audit AS ON UPDATE TO accounts
  DO ALSO INSERT INTO audit_log VALUES (OLD.id, now(), nextval('audit_seq'));

CREATE TRIGGER audit AFTER UPDATE ON accounts
  FOR EACH ROW EXECUTE FUNCTION audit_row();

CREATE TABLE ip_allow (addr text);
SELECT * FROM ip_allow WHERE '10.1.2.3' LIKE replace(addr, '*', '%');

CREATE TABLE ip_allow (net cidr PRIMARY KEY);
CREATE INDEX ON ip_allow USING gist (net inet_ops);
SELECT * FROM ip_allow WHERE net >>= '10.1.2.3'::inet;

-- new OLTP schema
customer_id int REFERENCES customers (id) NOT ENFORCED   -- "faster inserts"

customer_id int NOT NULL REFERENCES customers (id)

PitfallsCommon mistakes

• Marking a function LEAKPROOF without checking that its errors cannot reveal argument values.
• Expecting CREATE RULE ... DO INSTEAD on a table to fire for COPY.
• Storing IPv4 and IPv6 addresses as text and sorting them as strings.
• Assuming an extended-protocol prepared statement survives a transaction-mode pooler on old PgBouncer versions (fixed with max_prepared_statements in 1.21+).

LimitsWhen not to use it

Do not create new rules for logic a trigger or a view can express. Do not store XML when the data is relational or JSON; use XML only at integration boundaries. Do not use untrusted PLs for work that belongs in the application (HTTP calls, file access), since it runs inside the database server process.

SecuritySecurity notes

Only superusers can mark functions LEAKPROOF; review each one, since a leaky function on a security barrier view or RLS policy can reveal hidden rows. Untrusted languages can read files and open connections as the postgres OS user, so grant USAGE and function creation only to administrators, and never pass user input to shell or eval calls inside them.

ChallengeTry it yourself

BEGIN;
CREATE TABLE blocked_networks (net cidr PRIMARY KEY);
INSERT INTO blocked_networks VALUES ('198.51.100.0/24'), ('10.20.0.0/16');
SELECT addr, EXISTS (SELECT 1 FROM blocked_networks WHERE net >>= addr) AS blocked
FROM unnest(ARRAY['10.20.4.5', '10.21.0.1', '198.51.100.200']::inet[]) AS addr
ORDER BY addr;
ROLLBACK;

InterviewQuestions you should be able to answer

QuizCheck your understanding

4 questions with instant feedback in the interactive lesson; a wrong answer goes into your review deck.

Go deeperOfficial documentation

• Views
• Materialized views