Use a bounded recursive query in an AFTER STATEMENT trigger to reject categories deeper than the allowed limit. Require serializable write transactions and retry serialization failures so concurrent changes cannot jointly create an invalid hierarchy.

This tutorial is for a developer working in Terminal on a Mac with a local PostgreSQL database. The concrete task is to enforce a maximum of three category levels, including when an existing subtree moves. All examples use synthetic article categories.

Define exactly what the depth limit means

Here, a root category has depth 1. Its child has depth 2, and its grandchild has depth 3. A fourth level is invalid. If your application counts roots as depth zero, translate that convention before choosing the limit.

The table uses an adjacency list: each category stores an optional parent_id pointing to another category. A foreign key ensures that a referenced parent exists, but the depth rule requires examining multiple rows. PostgreSQL explicitly does not support a CHECK constraint that depends on other rows as a continuously maintained guarantee. Putting the traversal inside a function called by CHECK does not remove that limitation. Constraints.

The implementation below targets PostgreSQL 18, the current stable documentation retrieved for this article. Use an installed PostgreSQL 18 server and client, plus Node.js 24 and npm for the JavaScript example. Earlier PostgreSQL versions are outside this tutorial’s compatibility claim. The node-postgres documentation lists Node.js 24 among its compatible versions. PostgreSQL 18 documentation, node-postgres compatibility.

Why validate the entire hierarchy after a statement?

Checking only the new parent’s ancestors is insufficient for a subtree move. Suppose Articles → Guides → Editing already occupies three levels. Moving Articles beneath another root makes Editing a fourth-level category, although the moved row itself reaches only depth two.

The decision rule for a proposed move is: new depth of subtree root + subtree height must not exceed the maximum, where height counts edges from the subtree root to its deepest descendant. For example, a new root depth of 2 plus a subtree height of 2 produces a deepest depth of 4.

Instead of maintaining separate ancestor and descendant calculations, this implementation checks every category after each modifying statement. PostgreSQL documents that an AFTER trigger sees the changes made by the operation, and a statement trigger runs once per statement. This also supports multirow inserts and updates without relying on row-processing order. CREATE TRIGGER.

The bounded traversal starts at every category and follows parent links. Finding four nodes in any upward walk proves that the three-level rule fails. A cycle also produces a four-node walk because it never reaches a root. The same bound therefore rejects excessive depth and cycles without an unbounded recursive search. This is a design derived from PostgreSQL’s documented recursive CTE evaluation. WITH Queries.

Create the table and depth trigger

In your Mac text editor, save the following complete setup as category-depth.sql. It creates a new schema, so run it once in a local development database where that schema does not already exist. It does not drop or replace existing tables.

The trigger function returns trigger, as required for a PL/pgSQL data-change trigger. It uses RAISE EXCEPTION with explicit error codes to distinguish an invalid hierarchy from an incorrect transaction setup. Trigger Functions, Errors and Messages.

BEGIN ISOLATION LEVEL SERIALIZABLE;

CREATE SCHEMA category_depth_demo;

CREATE TABLE category_depth_demo.category (
    id integer PRIMARY KEY,
    name text NOT NULL,
    parent_id integer REFERENCES category_depth_demo.category(id),
    CONSTRAINT category_not_own_parent
        CHECK (parent_id IS NULL OR parent_id <> id)
);

CREATE INDEX category_parent_idx
    ON category_depth_demo.category(parent_id);

CREATE FUNCTION category_depth_demo.enforce_depth()
RETURNS trigger
LANGUAGE plpgsql
AS $$
DECLARE
    max_levels CONSTANT integer := 3;
    bad_id integer;
BEGIN
    IF current_setting('transaction_isolation') <> 'serializable' THEN
        RAISE EXCEPTION
            'Category writes require a SERIALIZABLE transaction'
            USING ERRCODE = '25000';
    END IF;

    WITH RECURSIVE walk(origin_id, parent_id, levels) AS (
        SELECT id, parent_id, 1
        FROM category_depth_demo.category

        UNION ALL

        SELECT w.origin_id, p.parent_id, w.levels + 1
        FROM walk AS w
        JOIN category_depth_demo.category AS p
          ON p.id = w.parent_id
        WHERE w.levels <= max_levels
    )
    SELECT origin_id INTO bad_id
    FROM walk
    WHERE levels = max_levels + 1
    LIMIT 1;

    IF FOUND THEN
        RAISE EXCEPTION
            'Category % exceeds % levels or belongs to a cycle',
            bad_id, max_levels
            USING ERRCODE = '23514';
    END IF;

    RETURN NULL;
END;
$$;

CREATE TRIGGER category_depth_guard
AFTER INSERT OR UPDATE OR DELETE
ON category_depth_demo.category
FOR EACH STATEMENT
EXECUTE FUNCTION category_depth_demo.enforce_depth();

INSERT INTO category_depth_demo.category(id, name, parent_id)
VALUES
    (1, 'Articles', NULL),
    (2, 'Guides', 1),
    (3, 'Editing', 2),
    (4, 'Reference', NULL),
    (10, 'Notes', NULL),
    (11, 'Examples', NULL);

COMMIT;

The function reads the transaction’s isolation setting through PostgreSQL’s documented current_setting function. The single max_levels constant controls both the recursion bound and the rejection threshold. Configuration Settings Functions.

Open Terminal, change to the directory containing the file, and run the following command. It assumes your existing local connection settings can connect to the postgres database on localhost. The -X option skips personal psql startup files; ON_ERROR_STOP stops file processing on an error. psql.

psql -X -h localhost -d postgres -v ON_ERROR_STOP=1 -f category-depth.sql

The foreign key keeps its default deletion behavior: deleting a category that still has children is rejected rather than silently deleting its subtree. The parent index supports lookups by parent_id; it does not eliminate the trigger’s whole-table validation. PostgreSQL does not automatically create an index on the referencing side of a foreign key. Foreign Keys.

Check inserts, subtree moves, and cycles

Before connecting an application, verify the boundary cases against the seeded data. Each operation below assumes the original setup remains unchanged.

Operation Expected result Reason
Insert category 5 beneath category 3 Reject The new category would occupy level 4.
Move category 1 beneath category 4 Reject Category 3 would move to level 4.
Move category 1 beneath category 3 Reject The move creates a cycle.
Move category 3 beneath category 4 Accept Category 3 becomes a level-2 category.
Update a name in a default Read Committed transaction Reject This implementation requires serializable transactions for every category write.

For a concrete subtree test, start an interactive session using psql -X -h localhost -d postgres, then execute this block. The update should raise the depth exception. ROLLBACK ends the failed transaction, leaving the original hierarchy intact.

BEGIN ISOLATION LEVEL SERIALIZABLE;

UPDATE category_depth_demo.category
SET parent_id = 4
WHERE id = 1;

ROLLBACK;

SELECT id, name, parent_id
FROM category_depth_demo.category
ORDER BY id;

The traversal deliberately reports excessive depth and cycles together. Its job is to enforce the invariant; it does not identify a cycle’s exact entry point. A diagnostic query could track visited IDs separately, but that is unnecessary for this bounded accept-or-reject decision.

Make concurrent writes obey the same rule

A recursive trigger alone does not settle concurrency. Two transactions can each validate a hierarchy while being unable to see the other transaction’s uncommitted change. PostgreSQL’s consistency documentation describes this problem and recommends serializable transactions as one supported way to enforce rules involving multiple rows. Data Consistency Checks at the Application Level.

This design therefore requires serializable isolation for every category write. Successful serializable transactions behave as though they ran one at a time. PostgreSQL can reject a transaction when concurrent activity would prevent that ordering, so a successful update is provisional until its transaction commits. Transaction Isolation.

You can demonstrate the issue using the two synthetic roots, 10 and 11. In two interactive psql sessions, execute each session’s update before issuing either commit.

-- Session A
BEGIN ISOLATION LEVEL SERIALIZABLE;
UPDATE category_depth_demo.category
SET parent_id = 11
WHERE id = 10;

-- Session B, in a separate psql session
BEGIN ISOLATION LEVEL SERIALIZABLE;
UPDATE category_depth_demo.category
SET parent_id = 10
WHERE id = 11;

Then issue COMMIT in each session. The two opposing parent changes must not both commit: together they form a cycle. A serialization failure may occur during a statement or at commit. If one change commits before the other session performs its update, the trigger can instead reject the now-visible cycle directly. These are expected consequences of combining the validation query with serializable execution. Transaction Isolation.

Run a category move with node-postgres

In a project directory, run npm init -y if you do not already have a package file, then install the driver with npm install pg. Save the complete program below as move-category.mjs. The driver’s documented installation package is pg. node-postgres.

The program connects to the same local database as the psql command. Other connection fields use the driver’s environment-based defaults. Every transaction statement uses one checked-out client, as node-postgres requires; transaction statements must not be distributed through separate pool.query calls. Connecting, Transactions.

import pg from 'pg';
import { setTimeout as delay } from 'node:timers/promises';

const { Pool } = pg;
const pool = new Pool({
  host: 'localhost',
  database: 'postgres',
  connectionTimeoutMillis: 5000
});

pool.on('error', (error) => {
  console.error('Idle database client error:', error.message);
  process.exitCode = 1;
});

async function moveCategory(id, parentId) {
  const maxAttempts = 5;

  for (let attempt = 1; attempt <= maxAttempts; attempt++) {
    const client = await pool.connect();
    let discard = false;
    let retry = false;

    try {
      await client.query('BEGIN ISOLATION LEVEL SERIALIZABLE');

      const result = await client.query(
        `UPDATE category_depth_demo.category
         SET parent_id = $1
         WHERE id = $2
         RETURNING id, name, parent_id`,
        [parentId, id]
      );

      if (result.rowCount !== 1) {
        throw new Error(`Category ${id} does not exist`);
      }

      await client.query('COMMIT');
      return result.rows[0];
    } catch (error) {
      try {
        await client.query('ROLLBACK');
      } catch {
        discard = true;
      }

      retry = !discard && error.code === '40001'
        && attempt < maxAttempts;

      if (!retry) throw error;
    } finally {
      client.release(discard);
    }

    if (retry) await delay(25 * attempt);
  }
}

try {
  const moved = await moveCategory(3, 4);
  console.log(moved);
} catch (error) {
  console.error(error.code ?? 'APP_ERROR', error.message);
  process.exitCode = 1;
} finally {
  await pool.end();
}

Run it with node move-category.mjs. Against the original seed, category 3 moves beneath category 4 and the returned row contains parent_id: 4. The query supplies values through $1 and $2, following the driver’s documented parameter interface. Queries.

The program returns the client in finally, discards it if rollback fails, and closes the pool when the script finishes. These lifecycle operations follow the documented pool API. pg.Pool.

Retry SQLSTATE 40001 by repeating the entire transaction, including any reads that determine the move. Do not automatically retry the trigger’s 23514 depth error: the proposed change needs correction. The five-attempt limit and brief increasing delay are illustrative application choices, not PostgreSQL requirements or guarantees of eventual completion. Serialization Failure Handling.

Understand the limits before adapting the example

This implementation checks the hierarchy after every statement, including name-only updates. A transaction cannot temporarily create an excessive depth in one statement and repair it in a later statement. Plan each statement to leave a valid hierarchy. PostgreSQL supports deferred constraint triggers, but they must be AFTER ROW triggers; this statement trigger cannot simply be marked deferrable. CREATE TRIGGER.

The tradeoff is predictable validation work: for a limit of three, the recursive relation contains at most four walk rows per category, although actual execution cost depends on the query plan and database contents. Large tables or frequent small writes warrant measurement before adopting a whole-table scan. Replacing it with an affected-subtree check requires preserving descendant validation, cycle rejection, and the concurrency argument.

For an existing table, installing a trigger does not itself audit stored rows. With ordinary writers paused, use the recursive query from the function as a standalone audit, replacing max_levels with 3 and selecting every matching origin_id. Resolve any reported paths and separately verify parent references before enabling the write path.

The useful invariant is straightforward: every category must reach a root within three nodes. The bounded walk checks that condition across subtree moves and cycles; serializable transactions and complete retries carry it through concurrent updates. Keep those parts together when adapting the example to your category table.

Sources