Un CTE che modifica i dati in PostgreSQL combina operazioni in un'unica istruzione con uno snapshot condiviso. Le istruzioni separate in una transazione vengono eseguite in sequenza e possono leggere le modifiche precedenti. Scegli in base a come ogni operazione deve ricevere il proprio input.

Per uno sviluppatore che lavora nel Terminale su un Mac, il compito concreto consiste nel decidere se un flusso di lavoro sintetico per articoli debba essere racchiuso in un'unica istruzione SQL o in più query transazionali. Gli esempi seguenti mostrano la differenza di visibilità, un flusso di lavoro CTE adatto e una transazione completa con node-postgres.

Cosa cambia quando si combinano operazioni in un CTE?

Un CTE che modifica i dati contiene un INSERT, UPDATE, DELETE o, nelle versioni correnti di PostgreSQL, MERGE all'interno di WITH. I suoi componenti condividono uno snapshot; l'ordine fisico delle modifiche è imprevedibile. Passa le righe modificate tramite RETURNING quando un altro componente ne ha bisogno. Scrivere i CTE dall'alto verso il basso non stabilisce un ordine di esecuzione procedurale. PostgreSQL: query WITH

Le istruzioni SQL separate all'interno di BEGIN e COMMIT hanno confini tra istruzioni. Al livello di isolamento predefinito Read Committed di PostgreSQL, ogni istruzione acquisisce un nuovo snapshot e può vedere le modifiche precedenti apportate dalla propria transazione. Tra un'istruzione e l'altra possono diventare visibili anche i commit di altre transazioni. PostgreSQL: isolamento delle transazioni

Entrambi gli approcci possono garantire un'operazione tutto o niente. Se non esiste un blocco di transazione esplicito, PostgreSQL esegue ogni singola istruzione all'interno di una transazione implicita; una transazione esplicita raggruppa più istruzioni. Un CTE che modifica i dati può essere anche una singola istruzione all'interno di una transazione più ampia. PostgreSQL: transazioni

Requisito Punto di partenza adatto
Passare direttamente le righe modificate a un'altra tabella Un CTE con RETURNING
Leggere una tabella dopo che un'istruzione precedente l'ha modificata Istruzioni sequenziali in una transazione
Modificare la stessa riga in due fasi deliberate Istruzioni sequenziali oppure combinare il calcolo finale in un unico aggiornamento
Esaminare un risultato intermedio prima di scegliere la query successiva Istruzioni sequenziali con logica applicativa
Ridurre il tempo trascorso in attesa tra richieste indipendenti Valutare le opzioni di trasporto separatamente dalla semantica SQL

Questa guida decisionale segue le regole di visibilità documentate. Inizia descrivendo la dipendenza: l'operazione successiva ha bisogno di una relazione restituita, di una nuova lettura della tabella o di una decisione presa da JavaScript? Questa domanda è più utile che contare quante operazioni rientrano in WITH.

Come si può riprodurre localmente la differenza di visibilità?

Usa una connessione esistente a PostgreSQL 18 in psql oppure un editor SQL sul Mac. Esegui il seguente script completo in un'unica sessione. Crea solo una tabella temporanea, usa un singolo articolo sintetico e termina con ROLLBACK. I valori attesi sono ricavati dalla documentazione; gli esempi di database non sono stati eseguiti durante la preparazione.

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;

Nella prima query, la scansione della tabella legge draft, mentre l'output del CTE contiene edited. L'istruzione successiva legge edited dalla tabella. Questo illustra la distinzione documentata tra la lettura della tabella di destinazione all'interno dell'istruzione che la modifica e la lettura delle righe restituite dal CTE. PostgreSQL: query WITH

Gli ultimi due aggiornamenti modificano intenzionalmente la stessa riga in istruzioni separate. Combinarli in CTE di modifica affiancati non è supportato: PostgreSQL non garantisce l'esecuzione affidabile di due modifiche sulla stessa riga all'interno di un'unica istruzione. Fare riferimento a un CTE precedente non elimina questa limitazione. PostgreSQL: SELECT

Quando un CTE che modifica i dati è una buona scelta?

Spostare righe di articoli selezionate in un archivio è un'attività relazionale adatta: l'eliminazione individua le righe esatte e la relazione restituita fornisce i dati per l'inserimento. Con DELETE, RETURNING espone il contenuto delle righe eliminate; con UPDATE, il suo output predefinito contiene i valori aggiornati. PostgreSQL: restituzione dei dati dalle righe modificate

Questo esempio autonomo fornisce le proprie tabelle e il proprio input. Eseguilo in un'unica sessione dopo aver terminato l'esempio precedente.

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;

L'inserimento usa direttamente moved. Il CTE di modifica WITH deve essere collegato all'istruzione di primo livello. Un CTE di modifica viene eseguito fino al completamento anche se il suo output non viene usato; omettere RETURNING rimuove la relazione di risultato utilizzabile, non la modifica. PostgreSQL: query WITH

Anche il confine della transazione è importante in caso di errore: un errore di inserimento impedisce che lo spostamento venga completato correttamente. In una transazione esplicita, esegui ROLLBACK dopo un errore. Non considerare la ricezione corretta di righe intermedie come prova che la transazione circostante sia stata confermata. PostgreSQL: transazioni

Come deve eseguire node-postgres istruzioni dipendenti?

Per un flusso di lavoro che esamina un risultato prima di inviare la query successiva, usa chiamate await sequenziali sullo stesso client. node-postgres richiede lo stesso client per ogni istruzione di una transazione; distribuire le fasi della transazione su chiamate pool.query() separate non garantisce la continuità della connessione. node-postgres: transazioni

I prerequisiti sono Node.js, npm e una connessione esistente a un database PostgreSQL 18 locale. Nella directory del progetto Node nel Terminale, esegui npm install pg. Salva il programma completo seguente come article-transaction.mjs, quindi esegui node article-transaction.mjs. new Client() usa le consuete variabili d'ambiente di connessione PostgreSQL; configurale per il tuo database esistente se i valori predefiniti non corrispondono. node-postgres: connessione

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

La riga stampata attesa ha id: 1 e stage: 'ready'. La tabella temporanea viene eliminata al commit. La sintassi JavaScript è stata verificata con Node.js; il database non è stato eseguito.

Le chiamate alle query passano i valori separatamente usando $1, $2 e $3, seguendo l'API delle query parametrizzate di node-postgres. Ogni chiamata contiene una singola istruzione SQL. node-postgres: query

Verificare rowCount trasforma un aggiornamento inaspettatamente privo di righe in un errore esplicito prima del commit. rowCount riporta il numero di righe elaborate dal comando, mentre rows contiene i record restituiti; un aggiornamento senza RETURNING può modificare righe pur restituendo un array rows vuoto. node-postgres: pg.Result

Raggruppare istruzioni cambia la loro semantica SQL?

Un batch descrive come viene inviato il lavoro. Non trasforma più istruzioni SQL in un unico CTE che modifica i dati.

Il protocollo simple-query di PostgreSQL accetta istruzioni separate da punti e virgola. Senza un controllo esplicito delle transazioni, le istruzioni in un singolo messaggio Query semplice vengono eseguite in una transazione implicita. Un errore interrompe l'esecuzione delle istruzioni rimanenti. Il messaggio Parse del protocollo esteso accetta una sola istruzione, quindi un batch separato da punti e virgola non sostituisce in modo generale le query parametrizzate separate. PostgreSQL: flusso dei messaggi

Un batch esplicito richiede anche la gestione degli errori al di fuori del batch: se si verifica un errore prima di un ROLLBACK finale, il rollback non viene mai raggiunto. Invia una richiesta di rollback separata per terminare la transazione fallita. PostgreSQL: più istruzioni in una query semplice

La documentazione attuale di node-postgres descrive inoltre il pipelining opzionale. Invia le richieste senza attendere ogni risposta, mentre PostgreSQL le elabora in ordine. La documentazione consiglia chiamate await sequenziali quando la query successiva di una transazione dipende da un risultato precedente. Il pipelining modifica i tempi delle richieste; non crea uno snapshot CTE condiviso. node-postgres: pipelining

Quali limiti di versione e isolamento devono guidare la scelta?

La documentazione attuale di PostgreSQL recuperata per questo articolo identifica la versione 18. PostgreSQL 16 consente INSERT, UPDATE e DELETE come corpi di CTE di modifica, ma esclude MERGE. La documentazione della versione 17 include MERGE e le note di rilascio documentano l'aggiunta di MERGE RETURNING. Per un flusso di lavoro PostgreSQL 16, usa istruzioni di modifica supportate o istruzioni separate invece di copiare la sintassi CTE più recente. PostgreSQL 16: query WITH, PostgreSQL 17: query WITH, Note di rilascio PostgreSQL 17

Repeatable Read non fa sì che le istruzioni sequenziali si comportino come CTE affiancati. Mantiene uno snapshot della transazione stabilito dalla prima istruzione che non è di controllo della transazione, ma le istruzioni successive continuano a vedere le scritture precedenti della transazione stessa. Le applicazioni che usano questo livello di isolamento devono anche essere pronte a ripetere le transazioni dopo errori di serializzazione. PostgreSQL: livello di isolamento Repeatable Read

MATERIALIZED e NOT MATERIALIZED non forniscono un modo per aggirare la visibilità dei CTE di modifica. PostgreSQL ignora NOT MATERIALIZED per i CTE che non sono privi di effetti collaterali. Questi controlli del planner non possono stabilire una sequenza di scritture sulla stessa riga. PostgreSQL: SELECT

Scegli un CTE di modifica quando le righe restituite costituiscono naturalmente l'input dell'operazione successiva. Scegli istruzioni sequenziali quando il lavoro successivo richiede modifiche precedenti alla tabella o una decisione dell'applicazione. Mantieni questa dipendenza quando ottimizzi i tempi delle richieste e usa una transazione esplicita ogni volta che più istruzioni devono completarsi insieme.

Fonti