PostgreSQL डेटा-संशोधित CTE साझा स्नैपशॉट के साथ कार्रवाइयों को एक स्टेटमेंट में जोड़ता है। ट्रांज़ैक्शन में अलग-अलग स्टेटमेंट क्रम से चलते हैं और पहले किए गए बदलावों को पढ़ सकते हैं। चुनाव इस आधार पर करें कि हर कार्रवाई को अपना इनपुट कैसे प्राप्त करना है।

Mac पर Terminal में काम करने वाले डेवलपर के लिए ठोस काम यह तय करना है कि सिंथेटिक लेख का वर्कफ़्लो एक SQL स्टेटमेंट में होना चाहिए या कई ट्रांज़ैक्शनल क्वेरी में। नीचे दिए उदाहरण दृश्यता का अंतर, उपयुक्त CTE वर्कफ़्लो और node-postgres का पूरा ट्रांज़ैक्शन दिखाते हैं।

कार्रवाइयों को CTE में जोड़ने पर क्या बदलता है?

डेटा-संशोधित CTE में INSERT, UPDATE, DELETE, या मौजूदा PostgreSQL में MERGE को WITH के भीतर रखा जाता है। इसके घटक एक स्नैपशॉट साझा करते हैं; उनके वास्तविक संशोधन का क्रम निश्चित नहीं होता। जब किसी अन्य घटक को बदली हुई पंक्तियों की ज़रूरत हो, तो उन्हें RETURNING के माध्यम से भेजें। CTE को ऊपर से नीचे लिखने से प्रक्रियात्मक निष्पादन क्रम तय नहीं होता। PostgreSQL: WITH Queries

BEGIN और COMMIT के भीतर अलग-अलग SQL स्टेटमेंट की सीमाएँ होती हैं। PostgreSQL के डिफ़ॉल्ट Read Committed आइसोलेशन स्तर पर हर स्टेटमेंट एक नया स्नैपशॉट लेता है और अपने ही ट्रांज़ैक्शन के पहले के बदलाव देख सकता है। दूसरे ट्रांज़ैक्शन के कमिट भी स्टेटमेंटों के बीच दिखाई दे सकते हैं। PostgreSQL: Transaction Isolation

दोनों तरीके ऐसी कार्रवाई दे सकते हैं जो या तो पूरी हो या बिल्कुल न हो। जब कोई स्पष्ट ट्रांज़ैक्शन ब्लॉक मौजूद न हो, तो PostgreSQL एक स्टेटमेंट को अंतर्निहित ट्रांज़ैक्शन में चलाता है; स्पष्ट ट्रांज़ैक्शन कई स्टेटमेंटों को एक साथ समूहित करता है। डेटा-संशोधित CTE किसी बड़े ट्रांज़ैक्शन के भीतर एक स्टेटमेंट भी हो सकता है। PostgreSQL: Transactions

आवश्यकता उपयुक्त शुरुआती तरीका
संशोधित पंक्तियाँ सीधे दूसरी टेबल में भेजना RETURNING वाला CTE
पहले के स्टेटमेंट में बदलाव के बाद टेबल पढ़ना ट्रांज़ैक्शन में क्रमिक स्टेटमेंट
एक ही पंक्ति को सोच-समझकर दो चरणों में बदलना क्रमिक स्टेटमेंट, या अंतिम गणना को एक अपडेट में जोड़ना
अगली क्वेरी चुनने से पहले बीच का परिणाम देखना एप्लिकेशन लॉजिक वाले क्रमिक स्टेटमेंट
स्वतंत्र अनुरोधों के बीच प्रतीक्षा का समय घटाना ट्रांसपोर्ट विकल्पों का SQL अर्थविज्ञान से अलग मूल्यांकन करें

यह निर्णय-सहायिका दस्तावेज़ित दृश्यता नियमों पर आधारित है। पहले निर्भरता स्पष्ट करें: क्या अगली कार्रवाई को लौटाया गया रिलेशन चाहिए, टेबल को नए सिरे से पढ़ना है, या JavaScript द्वारा लिया गया निर्णय चाहिए? यह सवाल WITH के भीतर कितनी कार्रवाइयाँ समा सकती हैं, यह गिनने से अधिक उपयोगी है।

स्थानीय रूप से दृश्यता का अंतर कैसे दोहरा सकते हैं?

अपने Mac पर psql में मौजूदा PostgreSQL 18 कनेक्शन या SQL एडिटर का उपयोग करें। नीचे दी गई पूरी स्क्रिप्ट एक ही सेशन में चलाएँ। यह केवल एक अस्थायी टेबल बनाती है, एक सिंथेटिक लेख का उपयोग करती है और अंत में ROLLBACK चलाती है। अपेक्षित मान दस्तावेज़ों से निकाले गए हैं; तैयारी के दौरान डेटाबेस उदाहरण नहीं चलाए गए थे।

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;

पहली क्वेरी में, टेबल स्कैन draft पढ़ता है, जबकि CTE के आउटपुट में edited होता है। अगला स्टेटमेंट टेबल से edited पढ़ता है। यह संशोधन करने वाले स्टेटमेंट के भीतर लक्ष्य टेबल को पढ़ने और CTE से लौटाई गई पंक्तियाँ पढ़ने के बीच दस्तावेज़ित अंतर दिखाता है। PostgreSQL: WITH Queries

आखिरी दो अपडेट जानबूझकर अलग-अलग स्टेटमेंटों में उसी पंक्ति को बदलते हैं। उन्हें साथ-साथ चलने वाले संशोधनकारी CTE में जोड़ना समर्थित नहीं है: PostgreSQL एक ही स्टेटमेंट के भीतर उसी पंक्ति पर दो संशोधन विश्वसनीय रूप से नहीं करता। पहले के CTE को संदर्भित करने से यह पाबंदी दूर नहीं होती। PostgreSQL: SELECT

डेटा-संशोधित CTE कब उपयुक्त है?

चुनी हुई लेख-पंक्तियों को आर्काइव में ले जाना एक उपयोगी रिलेशनल काम है: डिलीशन सटीक पंक्तियों की पहचान करता है और उससे लौटाया गया रिलेशन इन्सर्शन के लिए इनपुट देता है। DELETE के साथ, RETURNING हटाई गई पंक्ति की सामग्री उपलब्ध कराता है; UPDATE के साथ, इसका डिफ़ॉल्ट आउटपुट अपडेट की गई वैल्यू रखता है। PostgreSQL: Returning Data from Modified Rows

इस स्वतंत्र उदाहरण में अपनी टेबल और इनपुट दिए गए हैं। पिछले उदाहरण के पूरा होने के बाद इसे एक ही सेशन में चलाएँ।

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;

इन्सर्शन सीधे moved का उपयोग करता है। संशोधनकारी WITH को शीर्ष-स्तरीय स्टेटमेंट से जुड़ा होना चाहिए। संशोधनकारी CTE का आउटपुट इस्तेमाल न होने पर भी वह एक बार पूरा चलता है; RETURNING हटाने से उसका उपयोग योग्य परिणाम रिलेशन हटता है, संशोधन नहीं। PostgreSQL: WITH Queries

विफलता की स्थिति में ट्रांज़ैक्शन की सीमा भी मायने रखती है: इन्सर्शन विफल होने पर मूव सफलतापूर्वक पूरा नहीं होता। स्पष्ट ट्रांज़ैक्शन में, त्रुटि के बाद ROLLBACK जारी करें। बीच की पंक्तियाँ सफलतापूर्वक मिल जाने को इस बात का प्रमाण न मानें कि आसपास का ट्रांज़ैक्शन कमिट हो चुका है। PostgreSQL: Transactions

node-postgres को निर्भर स्टेटमेंट कैसे चलाने चाहिए?

जिस वर्कफ़्लो में अगली क्वेरी भेजने से पहले एक परिणाम की जाँच करनी हो, उसमें एक ही क्लाइंट पर क्रमिक await कॉल का उपयोग करें। node-postgres में ट्रांज़ैक्शन के हर स्टेटमेंट के लिए वही क्लाइंट ज़रूरी है; ट्रांज़ैक्शन के चरण अलग-अलग pool.query() कॉल से भेजने पर कनेक्शन की निरंतरता सुनिश्चित नहीं होती। node-postgres: Transactions

इसके लिए Node.js, npm और मौजूदा स्थानीय PostgreSQL 18 डेटाबेस कनेक्शन चाहिए। Terminal में अपने Node प्रोजेक्ट डायरेक्टरी से npm install pg चलाएँ। नीचे दिए पूरे प्रोग्राम को article-transaction.mjs नाम से सेव करें, फिर node article-transaction.mjs चलाएँ। new Client() PostgreSQL के सामान्य कनेक्शन एनवायरनमेंट वेरिएबल का उपयोग करता है; यदि डिफ़ॉल्ट आपके मौजूदा डेटाबेस से मेल न खाएँ, तो उन्हें कॉन्फ़िगर करें। 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();
}

अपेक्षित प्रिंट की गई पंक्ति में id: 1 और stage: 'ready' होंगे। कमिट होने पर अस्थायी टेबल हट जाती है। JavaScript सिंटैक्स को Node.js से जाँचा गया; डेटाबेस में चलाकर परीक्षण नहीं किया गया।

क्वेरी कॉल में node-postgres के पैरामीटराइज़्ड क्वेरी API के अनुसार $1, $2 और $3 से वैल्यू अलग से पास की जाती हैं। हर कॉल में एक SQL स्टेटमेंट होता है। node-postgres: Queries

rowCount की जाँच करने से शून्य-पंक्ति वाला अप्रत्याशित अपडेट कमिट से पहले स्पष्ट विफलता बन जाता है। rowCount कमांड द्वारा संसाधित पंक्तियों की संख्या बताता है, जबकि rows में लौटाए गए रिकॉर्ड होते हैं; RETURNING के बिना अपडेट पंक्तियों को प्रभावित कर सकता है, लेकिन खाली rows ऐरे लौटा सकता है। node-postgres: pg.Result

क्या स्टेटमेंटों को बैच करने से उनका SQL अर्थ बदल जाता है?

बैच यह बताता है कि काम कैसे भेजा जाता है। इससे कई SQL स्टेटमेंट एक डेटा-संशोधित CTE नहीं बन जाते।

PostgreSQL का simple-query प्रोटोकॉल सेमीकोलन से अलग किए गए स्टेटमेंट स्वीकार करता है। स्पष्ट ट्रांज़ैक्शन नियंत्रण के बिना, एक simple Query संदेश के स्टेटमेंट अंतर्निहित ट्रांज़ैक्शन में चलते हैं। त्रुटि आने पर बाकी स्टेटमेंट रुक जाते हैं। extended प्रोटोकॉल का Parse संदेश केवल एक स्टेटमेंट स्वीकार करता है, इसलिए सेमीकोलन से अलग किया गया बैच अलग-अलग पैरामीटराइज़्ड क्वेरी का सामान्य विकल्प नहीं है। PostgreSQL: Message Flow

स्पष्ट बैच के लिए बैच के बाहर त्रुटि-प्रबंधन भी ज़रूरी है: अगर आखिरी ROLLBACK से पहले त्रुटि हो जाए, तो वह रोलबैक कभी नहीं चलता। विफल ट्रांज़ैक्शन समाप्त करने के लिए अलग रोलबैक अनुरोध भेजें। PostgreSQL: Multiple Statements in a Simple Query

वर्तमान node-postgres दस्तावेज़ वैकल्पिक पाइपलाइनिंग का भी वर्णन करते हैं। इसमें हर प्रतिक्रिया की प्रतीक्षा किए बिना अनुरोध भेजे जाते हैं, जबकि PostgreSQL उन्हें क्रम से संसाधित करता है। दस्तावेज़ सलाह देते हैं कि जब ट्रांज़ैक्शन की अगली क्वेरी पहले के परिणाम पर निर्भर हो, तो क्रमिक await कॉल का उपयोग करें। पाइपलाइनिंग अनुरोधों के समय को बदलती है; इससे साझा CTE स्नैपशॉट नहीं बनता। node-postgres: Pipelining

चुनाव तय करने में कौन-सी संस्करण और आइसोलेशन सीमाएँ ध्यान में रखनी चाहिए?

इस लेख के लिए प्राप्त मौजूदा PostgreSQL दस्तावेज़ संस्करण 18 का उल्लेख करते हैं। PostgreSQL 16 संशोधनकारी CTE बॉडी में INSERT, UPDATE और DELETE की अनुमति देता है, लेकिन वहाँ MERGE की नहीं। संस्करण 17 के दस्तावेज़ों में MERGE शामिल है, और उसके रिलीज़ नोट्स MERGE RETURNING को जोड़े जाने का विवरण देते हैं। PostgreSQL 16 के वर्कफ़्लो में नए CTE सिंटैक्स की नकल करने के बजाय समर्थित संशोधनकारी स्टेटमेंट या अलग-अलग स्टेटमेंट इस्तेमाल करें। PostgreSQL 16: WITH Queries, PostgreSQL 17: WITH Queries, PostgreSQL 17 Release Notes

Repeatable Read क्रमिक स्टेटमेंटों को साथ-साथ चलने वाले CTE जैसा व्यवहार नहीं कराता। यह पहले गैर-ट्रांज़ैक्शन-नियंत्रण स्टेटमेंट द्वारा स्थापित ट्रांज़ैक्शन स्नैपशॉट बनाए रखता है, लेकिन बाद के स्टेटमेंट तब भी ट्रांज़ैक्शन के अपने पहले के बदलाव देख पाते हैं। इस आइसोलेशन स्तर का उपयोग करने वाले ऐप्लिकेशन को सीरियलाइज़ेशन विफलताओं के बाद ट्रांज़ैक्शन दोबारा चलाने के लिए भी तैयार रहना चाहिए। PostgreSQL: Repeatable Read Isolation Level

MATERIALIZED और NOT MATERIALIZED डेटा-संशोधित CTE की दृश्यता के लिए कोई वैकल्पिक उपाय नहीं देते। साइड इफ़ेक्ट-रहित न होने वाले CTE के लिए PostgreSQL NOT MATERIALIZED को अनदेखा करता है। प्लानर के ये नियंत्रण उसी पंक्ति पर क्रमिक लेखन स्थापित नहीं कर सकते। PostgreSQL: SELECT

संशोधनकारी CTE चुनें जब लौटाई गई पंक्तियाँ स्वाभाविक रूप से अगली कार्रवाई का इनपुट बनती हों। क्रमिक स्टेटमेंट चुनें जब बाद के काम को पहले के टेबल बदलाव या एप्लिकेशन का निर्णय चाहिए। अनुरोधों का समय बेहतर करते हुए भी इस निर्भरता को बनाए रखें, और जब कई स्टेटमेंटों को साथ पूरा होना हो, तो स्पष्ट ट्रांज़ैक्शन का उपयोग करें।

स्रोत