Een PostgreSQL data-modifying CTE combineert operaties in één statement met een gedeelde snapshot. Afzonderlijke statements in een transactie worden sequentieel uitgevoerd en kunnen eerdere writes lezen. Kies op basis van hoe elke operatie zijn invoer moet ontvangen.

Voor een ontwikkelaar die in Terminal op een Mac werkt, is de concrete taak het beslissen of een synthetische artikelworkflow hoort in één SQL-statement of in meerdere transactionele queries. De onderstaande voorbeelden tonen het zichtbaarheidsverschil, een geschikte CTE-workflow en een complete node-postgres-transactie.

Wat verandert wanneer je operaties combineert in een CTE?

Een data-modifying CTE bevat een INSERT, UPDATE, DELETE, of, in de huidige PostgreSQL, MERGE binnen WITH. De componenten delen een snapshot; hun fysieke wijzigingsvolgorde is onvoorspelbaar. Geef gewijzigde rijen door via RETURNING wanneer een andere component ze nodig heeft. Het schrijven van CTE's van boven naar beneden stelt geen procedurale uitvoeringsvolgorde vast. PostgreSQL: WITH Queries

Afzonderlijke SQL-statements binnen BEGIN en COMMIT hebben statement-grenzen. Op PostgreSQL's standaard Read Committed-isolatieniveau neemt elk statement een nieuwe snapshot en kan het eerdere wijzigingen zien die door de eigen transactie zijn gemaakt. Commits van andere transacties kunnen ook zichtbaar worden tussen statements. PostgreSQL: Transaction Isolation

Beide benaderingen kunnen een all-or-nothing-operatie bieden. PostgreSQL voert een individueel statement uit binnen een impliciete transactie wanneer geen expliciet transactieblok bestaat; een expliciete transactie groepeert meerdere statements. Een data-modifying CTE kan ook één statement zijn binnen een grotere transactie. PostgreSQL: Transactions

Vereiste Geschikt startpunt
Gewijzigde rijen direct in een andere tabel voeden Een CTE met RETURNING
Een tabel lezen na een eerdere statement die het wijzigt Sequentiële statements in een transactie
Dezelfde rij in twee bewuste stadia wijzigen Sequentiële statements, of de finale berekening combineren in één update
Een tussenresultaat inspecteren voordat de volgende query wordt gekozen Sequentiële statements met applicatielogica
Wachttijd tussen onafhankelijke verzoeken verminderen Transportopties apart evalueren van SQL-semantiek

Deze beslissingshulp volgt de gedocumenteerde zichtbaarheidsregels. Begin met het beschrijven van de afhankelijkheid: heeft de volgende operatie een geretourneerde relatie nodig, een verse tabel-leesactie of een beslissing die door JavaScript wordt gemaakt? Die vraag is nuttiger dan het tellen hoeveel operaties er in WITH passen.

Hoe kun je het zichtbaarheidsverschil lokaal reproduceren?

Gebruik een bestaande PostgreSQL 18-verbinding in psql of een SQL-editor op je Mac. Voer het volgende complete script uit in één sessie. Het maakt alleen een tijdelijke tabel, gebruikt één synthetisch artikel en eindigt met ROLLBACK. De verwachte waarden zijn afgeleid van de documentatie; de databasevoorbeelden zijn niet uitgevoerd tijdens de voorbereiding.

BEGIN ISOLATION LEVEL READ COMMITTED;

CREATE TEMP TABLE cte_demo_articles (
    id integer PRIMARY KEY,
    title text NOT NULL,
    stage text NOT NULL
) ON COMMIT DROP;

INSERT INTO cte_demo_articles (id, title, stage)
VALUES (1, 'Sample article', 'draft');

WITH edited AS (
    UPDATE cte_demo_articles
    SET stage = 'edited'
    WHERE id = 1
    RETURNING id, stage
)
SELECT a.stage AS table_stage,
       e.stage AS returned_stage
FROM cte_demo_articles AS a
JOIN edited AS e ON e.id = a.id;
-- Expected: table_stage = draft, returned_stage = edited

SELECT stage FROM cte_demo_articles WHERE id = 1;
-- Expected: edited, because this is a subsequent statement.

UPDATE cte_demo_articles SET stage = 'draft' WHERE id = 1;

UPDATE cte_demo_articles SET stage = 'edited' WHERE id = 1;
UPDATE cte_demo_articles
SET stage = 'ready'
WHERE id = 1 AND stage = 'edited';

SELECT stage FROM cte_demo_articles WHERE id = 1;
-- Expected: ready

ROLLBACK;

In de eerste query leest de tabel-scan draft, terwijl de uitvoer van de CTE edited bevat. Het volgende statement leest edited uit de tabel. Dit illustreert het gedocumenteerde onderscheid tussen het lezen van een doeltabel binnen het wijzigende statement en het lezen van de geretourneerde rijen van de CTE. PostgreSQL: WITH Queries

De laatste twee updates wijzigen bewust dezelfde rij in afzonderlijke statements. Het combineren ervan in sibling modifying CTE's zou niet-ondersteund zijn: PostgreSQL voert niet betrouwbaar twee wijzigingen van dezelfde rij binnen één statement uit. Het refereren aan een eerdere CTE verwijdert die beperking niet. PostgreSQL: SELECT

Wanneer is een data-modifying CTE een goede keuze?

Geselecteerde artikelrijen verplaatsen naar een archief is een nuttige relationele taak: de verwijdering identificeert de exacte rijen, en de geretourneerde relatie levert de invoer voor de invoeging. Met DELETE blootlegt RETURNING de inhoud van de verwijderde rij; met UPDATE bevat de standaarduitvoer de bijgewerkte waarden. PostgreSQL: Returning Data from Modified Rows

Dit zelfstandige voorbeeld voorziet in eigen tabellen en invoer. Voer het uit in één sessie nadat het vorige voorbeeld is voltooid.

BEGIN;

CREATE TEMP TABLE move_demo_articles (
    id integer PRIMARY KEY,
    title text NOT NULL,
    stage text NOT NULL
) ON COMMIT DROP;

CREATE TEMP TABLE move_demo_archive (
    id integer PRIMARY KEY,
    title text NOT NULL,
    stage text NOT NULL
) ON COMMIT DROP;

INSERT INTO move_demo_articles (id, title, stage)
VALUES
    (1, 'Sample article A', 'ready'),
    (2, 'Sample article B', 'draft');

WITH moved AS (
    DELETE FROM move_demo_articles
    WHERE stage = 'ready'
    RETURNING id, title, stage
)
INSERT INTO move_demo_archive (id, title, stage)
SELECT id, title, stage
FROM moved
RETURNING id, title, stage;
-- Expected: article 1, with stage ready.

SELECT id, stage FROM move_demo_articles ORDER BY id;
-- Expected: article 2, with stage draft.

ROLLBACK;

De invoeging verbruikt moved direct. Het wijzigende WITH moet aan het top-level statement gekoppeld zijn. Een modifying CTE draait één keer tot voltooiing zelfs als de uitvoer ongebruikt blijft; het weglaten van RETURNING verwijdert de bruikbare resultaatrelatie, niet de wijziging. PostgreSQL: WITH Queries

De transactiegrens is ook van belang voor foutafhandeling: een invoegfout voorkomt dat de verplaatsing met succes voltooid wordt. In een expliciete transactie, geef ROLLBACK uit na een fout. Behandel het succesvol ontvangen van tussenrijen niet als bewijs dat een omringende transactie is gecommit. PostgreSQL: Transactions

Hoe moet node-postgres afhankelijke statements uitvoeren?

Voor een workflow die één resultaat onderzoekt voordat de volgende query wordt verstuurd, gebruik sequentiële await-aanroepen op één client. node-postgres vereist dezelfde client voor elk statement in een transactie; het verzenden van transactiestappen via afzonderlijke pool.query()-aanroepen creëert geen verbindingscontinuïteit. node-postgres: Transactions

Vereisten zijn Node.js, npm en een bestaande lokale PostgreSQL 18-databaseverbinding. In je Node-projectmap in Terminal, voer npm install pg uit. Sla het complete programma hieronder op als article-transaction.mjs, en voer vervolgens node article-transaction.mjs uit. new Client() gebruikt de gebruikelijke PostgreSQL-verbindingsomgevingsvariabelen; configureer ze voor je bestaande database als de standaarden niet overeenkomen. node-postgres: Connecting

import pg from 'pg';

const { Client } = pg;
const client = new Client();
await client.connect();

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

  await client.query(`
    CREATE TEMP TABLE articles (
      id integer PRIMARY KEY,
      title text NOT NULL,
      stage text NOT NULL
    ) ON COMMIT DROP
  `);

  await client.query(
    'INSERT INTO articles VALUES ($1, $2, $3)',
    [1, 'Sample article', 'draft']
  );

  const first = await client.query(
    `UPDATE articles SET stage = $1
     WHERE id = $2 RETURNING id, stage`,
    ['edited', 1]
  );
  if (first.rowCount !== 1) {
    throw new Error('Expected one article');
  }

  const second = await client.query(
    `UPDATE articles SET stage = $1
     WHERE id = $2 AND stage = $3
     RETURNING id, stage`,
    ['ready', 1, 'edited']
  );
  if (second.rowCount !== 1) {
    throw new Error('Expected one edited article');
  }

  await client.query('COMMIT');
  console.log(second.rows);
} catch (error) {
  await client.query('ROLLBACK');
  throw error;
} finally {
  await client.end();
}

De verwachte afgedrukte rij heeft id: 1 en stage: 'ready'. De tijdelijke tabel wordt gedropt bij commit. JavaScript-syntaxis is gecontroleerd met Node.js; database-uitvoering is niet uitgevoerd.

De query-aanroepen geven waarden apart door via $1, $2 en $3, volgend node-postgres's parameterized query API. Elke aanroep bevat één SQL-statement. node-postgres: Queries

Het controleren van rowCount maakt een onverwachte nul-rijen-update een expliciete fout voordat de commit plaatsvindt. rowCount rapporteert het verwerkte-rijen-aantal van het commando, terwijl rows geretourneerde records bevat; een update zonder RETURNING kan rijen beïnvloeden terwijl een lege rows-array wordt geretourneerd. node-postgres: pg.Result

Verandert batchen van statements hun SQL-semantiek?

Een batch beschrijft hoe werk wordt ingediend. Het verandert meerdere SQL-statements niet in één data-modifying CTE.

PostgreSQL's simple-query-protocol accepteert puntkomma-gescheiden statements. Zonder expliciete transactiecontrole worden statements in één simple Query-bericht uitgevoerd in een impliciete transactie. Een fout stopt de overige statements. Het extended protocol's Parse-bericht accepteert slechts één statement, dus een puntkomma-gescheiden batch is geen algemene vervanging voor afzonderlijke geparametriseerde queries. PostgreSQL: Message Flow

Een expliciete batch heeft ook foutafhandeling buiten de batch nodig: als een fout optreedt vóór een afsluitende ROLLBACK, wordt die rollback nooit bereikt. Stuur een aparte rollback-aanvraag om de mislukte transactie te beëindigen. PostgreSQL: Multiple Statements in a Simple Query

De huidige node-postgres-documentatie beschrijft bovendien opt-in pipelining. Het verstuurt verzoeken zonder te wachten op elk antwoord, terwijl PostgreSQL ze in volgorde verwerkt. De documentatie adviseert sequentiële await-aanroepen wanneer de volgende query van een transactie afhangt van een eerdere resultaat. Pipelining verandert de aanvraagtiming; het creëert geen gedeelde CTE-snapshot. node-postgres: Pipelining

Welke versie en isolatielimieten moeten de keuze leiden?

De huidige PostgreSQL-documentatie die voor dit artikel is geraadpleegd, identificeert versie 18. PostgreSQL 16 staat INSERT, UPDATE en DELETE toe als modifying CTE-bodies, maar sluit MERGE daaruit. Versie 17-documentatie omvat MERGE, en de release notes documenteren de toevoeging van MERGE RETURNING. Voor een PostgreSQL 16-workflow, gebruik ondersteunde modifying statements of afzonderlijke statements in plaats van nieuwere CTE-syntax te kopiëren. PostgreSQL 16: WITH Queries, PostgreSQL 17: WITH Queries, PostgreSQL 17 Release Notes

Repeatable Read doet sequentiële statements niet gedragen als sibling CTE's. Het behoudt een transactiesnapshot die wordt vastgelegd door het eerste non-transaction-control-statement, maar latere statements zien nog steeds de eigen eerdere writes van de transactie. Applicaties die dit isolatieniveau gebruiken, moeten ook voorbereid zijn om transacties te herhalen na serialisatiefouten. PostgreSQL: Repeatable Read Isolation Level

MATERIALIZED en NOT MATERIALIZED bieden geen workaround voor modifying CTE-zichtbaarheid. PostgreSQL negeert NOT MATERIALIZED voor CTE's die niet side-effect-free zijn. Die planner-controls kunnen geen sequentie van writes naar dezelfde rij vastleggen. PostgreSQL: SELECT

Kies een modifying CTE wanneer geretourneerde rijen natuurlijk de invoer van de volgende operatie vormen. Kies sequentiële statements wanneer later werk eerdere tabelwijzigingen of een applicatiebeslissing nodig heeft. Behoud die afhankelijkheid bij het optimaliseren van verzoektiming, en gebruik een expliciete telkens wanneer meerdere statements samen moeten voltooien.

Bronnen