Gebruik een begrensde recursieve query in een AFTER STATEMENT-trigger om categorieën af te wijzen die dieper zijn dan de toegestane limiet. Vereis schrijftransacties op serialiseerbaar niveau en probeer transacties met serialisatiefouten opnieuw, zodat gelijktijdige wijzigingen samen geen ongeldige hiërarchie kunnen creëren.

Deze handleiding is bedoeld voor een ontwikkelaar die in Terminal op een Mac werkt met een lokale PostgreSQL-database. De concrete taak is om maximaal drie categorieniveaus af te dwingen, ook wanneer een bestaande deelboom wordt verplaatst. Alle voorbeelden gebruiken fictieve artikelcategorieën.

Bepaal precies wat de dieptelimiet betekent

Hier heeft een hoofdcategorie diepte 1. Het onderliggende item heeft diepte 2 en het kleinkind diepte 3. Een vierde niveau is ongeldig. Als je toepassing hoofdcategorieën als diepte nul telt, pas die conventie dan aan voordat je de limiet kiest.

De tabel gebruikt een adjacency list: elke categorie slaat een optionele parent_id op die naar een andere categorie verwijst. Een foreign key zorgt ervoor dat een verwijzende hoofdcategorie bestaat, maar voor de diepteregel moeten meerdere rijen worden onderzocht. PostgreSQL ondersteunt expliciet geen CHECK-beperking die afhankelijk is van andere rijen als voortdurend afgedwongen garantie. De beperking verdwijnt niet door de traversering in een functie te plaatsen die door CHECK wordt aangeroepen. Beperkingen.

De implementatie hieronder is gericht op PostgreSQL 18, de huidige stabiele documentatie die voor dit artikel is geraadpleegd. Gebruik een geïnstalleerde PostgreSQL 18-server en -client, plus Node.js 24 en npm voor het JavaScript-voorbeeld. Eerdere PostgreSQL-versies vallen buiten de compatibiliteitsclaim van deze handleiding. De node-postgres-documentatie vermeldt Node.js 24 als een compatibele versie. PostgreSQL 18-documentatie, compatibiliteit van node-postgres.

Waarom de volledige hiërarchie na een statement valideren?

Alleen de hoofdcategorieën van de nieuwe bovenliggende categorie controleren is onvoldoende bij het verplaatsen van een deelboom. Stel dat Artikelen → Handleidingen → Bewerken al drie niveaus beslaat. Als je Artikelen onder een andere hoofdcategorie verplaatst, wordt Bewerken een categorieniveau vier, hoewel de verplaatste rij zelf slechts niveau twee bereikt.

De beslisregel voor een voorgestelde verplaatsing is: de nieuwe diepte van de wortel van de deelboom + de hoogte van de deelboom mag de maximumdiepte niet overschrijden. De hoogte telt het aantal verbindingen van de wortel van de deelboom tot de diepste afstammeling. Een nieuwe worteldiepte van 2 plus een deelboomhoogte van 2 leidt bijvoorbeeld tot een diepste diepte van 4.

In plaats van afzonderlijke berekeningen voor bovenliggende en onderliggende categorieën bij te houden, controleert deze implementatie na elk statement elke categorie. PostgreSQL documenteert dat een AFTER-trigger de wijzigingen ziet die door de bewerking zijn aangebracht, en dat een statementtrigger eenmaal per statement wordt uitgevoerd. Zo worden ook invoegingen en updates van meerdere rijen ondersteund, zonder afhankelijk te zijn van de verwerkingsvolgorde van rijen. CREATE TRIGGER.

De begrensde traversering begint bij elke categorie en volgt de verwijzingen naar de hoofdcategorie. Als een opwaartse wandeling vier knooppunten bevat, is bewezen dat de regel van drie niveaus wordt overtreden. Ook een cyclus levert een wandeling van vier knooppunten op, omdat die nooit een hoofdcategorie bereikt. Deze begrenzing wijst dus zowel overmatige diepte als cycli af, zonder een onbeperkte recursieve zoekopdracht. Dit ontwerp is afgeleid van de gedocumenteerde evaluatie van recursieve CTE's in PostgreSQL. WITH-query's.

Maak de tabel en dieptetrigger

Sla de volgende volledige setup in de teksteditor op je Mac op als category-depth.sql. De setup maakt een nieuw schema. Voer deze daarom één keer uit in een lokale ontwikkelingsdatabase waarin dat schema nog niet bestaat. Bestaande tabellen worden niet verwijderd of vervangen.

De triggerfunctie retourneert trigger, zoals vereist voor een PL/pgSQL-trigger voor gegevenswijzigingen. Met RAISE EXCEPTION en expliciete foutcodes maakt de functie onderscheid tussen een ongeldige hiërarchie en een onjuiste transactie-instelling. Triggerfuncties, Fouten en berichten.

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;

De functie leest de isolatie-instelling van de transactie via de gedocumenteerde functie current_setting van PostgreSQL. De ene constante max_levels bepaalt zowel de recursiegrens als de afwijsdrempel. Functies voor configuratie-instellingen.

Open Terminal, ga naar de map met het bestand en voer de volgende opdracht uit. Hierbij wordt ervan uitgegaan dat je bestaande lokale verbindingsinstellingen verbinding kunnen maken met de database postgres op localhost. De optie -X slaat persoonlijke psql-opstartbestanden over; ON_ERROR_STOP stopt de bestandsverwerking bij een fout. psql.

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

De foreign key behoudt het standaardverwijdergedrag: het verwijderen van een categorie met onderliggende categorieën wordt afgewezen, in plaats van de deelboom stilzwijgend te verwijderen. De index op de bovenliggende categorie ondersteunt zoekopdrachten op parent_id; deze voorkomt niet dat de trigger de hele tabel valideert. PostgreSQL maakt niet automatisch een index aan aan de verwijzende kant van een foreign key. Foreign keys.

Test invoegingen, verplaatsingen van deelbomen en cycli

Controleer voordat je een toepassing aansluit de grensgevallen aan de hand van de vooraf ingevulde gegevens. Elke onderstaande bewerking gaat ervan uit dat de oorspronkelijke setup ongewijzigd is gebleven.

Bewerking Verwacht resultaat Reden
Categorie 5 onder categorie 3 invoegen Afwijzen De nieuwe categorie zou op niveau 4 komen.
Categorie 1 onder categorie 4 verplaatsen Afwijzen Categorie 3 zou naar niveau 4 verhuizen.
Categorie 1 onder categorie 3 verplaatsen Afwijzen Door de verplaatsing ontstaat een cyclus.
Categorie 3 onder categorie 4 verplaatsen Accepteren Categorie 3 wordt een categorie op niveau 2.
Een naam bijwerken in een standaardtransactie met Read Committed Afwijzen Deze implementatie vereist serialiseerbare transacties voor elke categoriewijziging.

Voer voor een concrete test van een deelboom een interactieve sessie met psql -X -h localhost -d postgres en voer vervolgens dit blok uit. De update hoort de diepte-uitzondering te veroorzaken. Met ROLLBACK wordt de mislukte transactie beëindigd, zodat de oorspronkelijke hiërarchie intact blijft.

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;

De traversering rapporteert overmatige diepte en cycli bewust samen. Het doel is de invariant af te dwingen; het beginpunt van een cyclus wordt niet vastgesteld. Een diagnostische query kan bezochte ID's afzonderlijk bijhouden, maar voor deze begrensde beslissing tussen accepteren en afwijzen is dat niet nodig.

Zorg dat gelijktijdige schrijfopdrachten aan dezelfde regel voldoen

Een recursieve trigger lost het gelijktijdigheidsprobleem op zichzelf niet op. Twee transacties kunnen elk een hiërarchie valideren zonder de niet-vastgelegde wijziging van de andere transactie te kunnen zien. De documentatie van PostgreSQL over consistentie beschrijft dit probleem en noemt serialiseerbare transacties als een ondersteunde manier om regels af te dwingen die meerdere rijen betreffen. Consistentiecontroles op applicatieniveau.

Dit ontwerp vereist daarom serialiseerbare isolatie voor elke categoriewijziging. Geslaagde serialiseerbare transacties gedragen zich alsof ze één voor één zijn uitgevoerd. PostgreSQL kan een transactie afwijzen als gelijktijdige activiteit die volgorde onmogelijk maakt. Een geslaagde update blijft dus voorlopig totdat de transactie is vastgelegd. Transactie-isolatie.

Je kunt het probleem demonstreren met de twee fictieve hoofdcategorieën 10 en 11. Voer in twee interactieve psql-sessies de update van elke sessie uit voordat je in een van beide sessies een commit geeft.

-- 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;

Geef daarna in elke sessie COMMIT. De twee tegengestelde wijzigingen van de bovenliggende categorie mogen niet allebei worden vastgelegd: samen vormen ze een cyclus. Een serialisatiefout kan tijdens een statement of bij het vastleggen optreden. Als de ene wijziging wordt vastgelegd voordat de andere sessie haar update uitvoert, kan de trigger de inmiddels zichtbare cyclus rechtstreeks afwijzen. Dit zijn verwachte gevolgen van de combinatie van de validatiequery en serialiseerbare uitvoering. Transactie-isolatie.

Verplaats een categorie met node-postgres

Voer in een projectmap npm init -y uit als je nog geen packagebestand hebt en installeer vervolgens de driver met npm install pg. Sla het onderstaande volledige programma op als move-category.mjs. Het gedocumenteerde installatiepakket voor de driver is pg. node-postgres.

Het programma maakt verbinding met dezelfde lokale database als de psql-opdracht. Voor de overige verbindingsvelden gebruikt de driver standaardwaarden op basis van omgevingsvariabelen. Zoals node-postgres vereist, gebruikt elke transactieopdracht één uitgecheckte client; transactieopdrachten mogen niet over afzonderlijke aanroepen van pool.query worden verdeeld. Verbinden, Transacties.

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();
}

Voer het programma uit met node move-category.mjs. Bij de oorspronkelijke seedgegevens wordt categorie 3 onder categorie 4 verplaatst en bevat de geretourneerde rij parent_id: 4. De query geeft waarden door via $1 en $2, volgens de gedocumenteerde parameterinterface van de driver. Query's.

Het programma geeft de client terug in finally, gooit deze weg als terugdraaien mislukt en sluit de pool wanneer het script klaar is. Deze levenscyclusbewerkingen volgen de gedocumenteerde pool-API. pg.Pool.

Probeer SQLSTATE 40001 opnieuw door de volledige transactie te herhalen, inclusief alle leesbewerkingen die de verplaatsing bepalen. Probeer de dieptefout 23514 van de trigger niet automatisch opnieuw: de voorgestelde wijziging moet worden aangepast. De limiet van vijf pogingen en de korte, steeds langere vertraging zijn illustratieve keuzes voor de toepassing, geen vereisten van PostgreSQL of garanties op uiteindelijke voltooiing. Afhandeling van serialisatiefouten.

Begrijp de beperkingen voordat je het voorbeeld aanpast

Deze implementatie controleert de hiërarchie na elk statement, ook bij updates die alleen namen wijzigen. Een transactie kan niet in één statement tijdelijk een te grote diepte creëren en die in een later statement herstellen. Zorg dat elk statement een geldige hiërarchie achterlaat. PostgreSQL ondersteunt uitgestelde constrainttriggers, maar dat moeten AFTER ROW-triggers zijn; deze statementtrigger kan niet simpelweg uitstelbaar worden gemaakt. CREATE TRIGGER.

De afweging is voorspelbare validatielast: bij een limiet van drie bevat de recursieve relatie maximaal vier wandelrijen per categorie, al zijn de werkelijke uitvoeringskosten afhankelijk van het queryplan en de database-inhoud. Meet de prestaties voordat je een volledige tabelscan toepast op grote tabellen of bij frequente kleine schrijfbewerkingen. Als je dit vervangt door een controle van de betrokken deelboom, moet je validatie van afstammelingen, afwijzing van cycli en de redenering over gelijktijdigheid behouden.

Bij een bestaande tabel controleert het installeren van een trigger de opgeslagen rijen niet. Pauzeer gewone schrijfopdrachten en gebruik de recursieve query uit de functie als zelfstandige audit. Vervang daarbij max_levels door 3 en selecteer elke overeenkomende origin_id. Los gerapporteerde paden op en controleer afzonderlijk de verwijzingen naar bovenliggende categorieën voordat je het schrijfpad inschakelt.

De nuttige invariant is eenvoudig: elke categorie moet binnen drie knooppunten een hoofdcategorie bereiken. De begrensde wandeling controleert die voorwaarde bij verplaatsingen van deelbomen en cycli; serialiseerbare transacties en volledige herhalingen handhaven haar bij gelijktijdige updates. Behoud deze onderdelen samen wanneer je het voorbeeld aanpast aan je categorietabel.

Bronnen