Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1tGxsv-0025je-IZ for pgsql-admin@arkaria.postgresql.org; Fri, 29 Nov 2024 10:06:01 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1tGxst-0061mx-3v for pgsql-admin@arkaria.postgresql.org; Fri, 29 Nov 2024 10:06:00 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1tGxss-0061mn-GH for pgsql-admin@lists.postgresql.org; Fri, 29 Nov 2024 10:05:59 +0000 Received: from mail-oo1-xc35.google.com ([2607:f8b0:4864:20::c35]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1tGxsr-000995-9J for pgsql-admin@postgresql.org; Fri, 29 Nov 2024 10:05:58 +0000 Received: by mail-oo1-xc35.google.com with SMTP id 006d021491bc7-5f1d1fdb328so645177eaf.3 for ; Fri, 29 Nov 2024 02:05:57 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1732874756; x=1733479556; darn=postgresql.org; h=to:subject:message-id:date:from:mime-version:from:to:cc:subject :date:message-id:reply-to; bh=xFGdqVWqHS6yAUXGNwT+WyYNtmTPi/3DiR4tLdQ580c=; b=h08k0q8IjOyDpBjqwEcpCc89FWxrekv1+OcwBpJ4589kZAQskdYvfCkRNGRVTn5OgB gJ05KTtHkhfU2ANEV0xvJcWHCE0EBuzrAa9yNB6WVrDIP9ls2SgiH2c0UZDnsg4QQtg+ SVIjJ0U8LFEWTA0h3ycQEUXgkLjcBxfiNI397uP7slqDozaje050fr2vEPNRYhiS2gRX kHSfqGvTKaNovK/VEnHUCru2/4ZqNb5DDmvDh+OvWZhyWMZcb0joIU16lK+abBMNNbMx 0K/zjVDhDQFMy6evX3vCUXxLJLMYsckkhaqiDBAxaqED+87H7gL0xf9z0/RAE6CZsAbT FKwg== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1732874756; x=1733479556; h=to:subject:message-id:date:from:mime-version:x-gm-message-state :from:to:cc:subject:date:message-id:reply-to; bh=xFGdqVWqHS6yAUXGNwT+WyYNtmTPi/3DiR4tLdQ580c=; b=jB73CeK8mnd4Pf4NWAtDXp5TtCowOOwvMZZYT7gCleX55xZfUyrVAy1kKEMyL6hUSe GJ/3klgZb0uBXm5gURZx1oc/wKSyasxSXXpXW9P31rIoUx9fhxZf+MqWMBrXYwJnHmR7 HR3HbhN4Z6YW53bXVJMbkqsrqGXSxXgjokA1vvLbPQ5JEcRb3kquVrZ1z/G0VvlVrD0s DXU6iMAgyxN4TjaDqNMFZrNxfRj+2yyhOawiHAf6asJf1fpkHTtA587iKnhNJ3UrH38v ycPZYxYvGb/ul61w5rs5+7FOQXHJexKHmP2AHslw1yEqFcPjS/A6QopZpwBn6MOKXOOd EckA== X-Gm-Message-State: AOJu0Yy/R4P+Ls8qzkLXQTNrDZ17MlOgvgM0PmI67tcQ7d3p/aGjGouZ 0QjTSxANv1rXtcCdiX94yAW8IRgO8SlHP9XzlXEI+6PNxe0op2W3xSO9hn6+c0izR7SjpB4bGiL 1VcdMUgbC5NxayYpQSkGFjOAm5u1jzDZm X-Gm-Gg: ASbGncsDw/lGPdItnTCMcvgtMF9HuzYcufXTJc7iTL828d0g7OWvRL/IKf4VOBBv69A MD3fVbqFl7aZJhYneLqJ+eShrxWNV9us= X-Google-Smtp-Source: AGHT+IGanPwLFjIiP/5jC449foFIOcmM9FXe7Am9L0dluAO7wYcb+CSl6l2PV1mpXS0KWwtUMoT2870lz8a/CIwYrFU= X-Received: by 2002:a05:6820:1b94:b0:5ee:d89f:1d2c with SMTP id 006d021491bc7-5f20a133071mr8189672eaf.1.1732874756418; Fri, 29 Nov 2024 02:05:56 -0800 (PST) MIME-Version: 1.0 From: Debraj Manna Date: Fri, 29 Nov 2024 15:35:30 +0530 Message-ID: Subject: Do postgres use implicit transaction in UPDATE ... RETURNING queries? To: pgsql-admin Content-Type: multipart/alternative; boundary="0000000000004bf61906280a58d2" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --0000000000004bf61906280a58d2 Content-Type: text/plain; charset="UTF-8" Can someone let me know if Postgres 15 uses implicit transactions for UPDATE...RETURNING queries? For example, is there any differences between the two queries BEGIN;UPDATE counterSET value = value + 1 RETURNING value;COMMIT; and UPDATE counterSET value = value + 1 RETURNING value; Table is like below CREATE TABLE IF NOT EXISTS counter ( value bigint PRIMARY KEY NOT NULL DEFAULT 0, updated_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP ); --0000000000004bf61906280a58d2 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable

Can someone le= t me know if Postgres 15 uses implicit transactions for=C2=A0UPDATE...RETURNING=C2=A0queries?

For example, is there any differences betwee= n the two queries

BEGIN;
UPDATE counter
SET value =3D value + 1
RETURNING value;
COMMIT;

and

<=
span class=3D"gmail-hljs-keyword" style=3D"margin:0px;padding:0px;border:0p=
x;font-style:inherit;font-variant:inherit;font-weight:inherit;font-stretch:=
inherit;line-height:inherit;font-family:inherit;font-size-adjust:inherit;fo=
nt-kerning:inherit;font-feature-settings:inherit;font-size:13px;vertical-al=
ign:baseline;box-sizing:inherit">UPDATE counter
SET value =3D value + 1
RETURNING value;

Table is like belo= w

CREATE TABLE IF NOT EXISTS co=
unter
(
    value      bigint PRIMARY =
KEY NOT NULL DEFAULT 0,
    updated_at TIMESTAMPTZ        NOT NULL DEFAULT CURRENT_TIMESTA=
MP
);
--0000000000004bf61906280a58d2--