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 1uwPy6-001mDn-HM for pgsql-general@arkaria.postgresql.org; Wed, 10 Sep 2025 18:54:58 +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 1uwPy4-002lzm-M6 for pgsql-general@arkaria.postgresql.org; Wed, 10 Sep 2025 18:54:57 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1uwPy4-002lzb-9q for pgsql-general@lists.postgresql.org; Wed, 10 Sep 2025 18:54:56 +0000 Received: from mail-lf1-x131.google.com ([2a00:1450:4864:20::131]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.96) (envelope-from ) id 1uwPy0-0003Sf-1M for pgsql-general@postgresql.org; Wed, 10 Sep 2025 18:54:56 +0000 Received: by mail-lf1-x131.google.com with SMTP id 2adb3069b0e04-5608b619cd8so8589179e87.2 for ; Wed, 10 Sep 2025 11:54:53 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1757530493; x=1758135293; darn=postgresql.org; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:from:to:cc:subject:date:message-id:reply-to; bh=pzYNOdIk5h1rSstqZU3GjqpUfwgJ37r/FLnExPvlvW8=; b=TrBdZ63QZvbR5aHNNSKolx2i+jE0I3EL2zM9AVxIN1oezL2cVbJD8zs8xTgTfJ3O0G BrgS6hU98RNOhHM+7OwAJRAv2hmwg/+jKHXDyWg8zn8MPVbRlWLZzuZZk4X6d4ZBNfIq YVbejQZjq9OaHdBAgQd+YmHxuVUM75f8fEqaSQ95Q6jXjZefZqOYALB3ufqJapPu4GAb rKFdhkuSvHpMSTRg2WA+vjvnSc23B/CvKXMFxyIgZw4vGFfSdYW1cZiTzJOfHTPt0xPi FCk//5fGLPBPtur3nret045aEs7k2mftviop6t2D23PYsJiishhUEN73+7IAAapOXwfy PDAw== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1757530493; x=1758135293; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:x-gm-message-state:from:to:cc:subject:date:message-id :reply-to; bh=pzYNOdIk5h1rSstqZU3GjqpUfwgJ37r/FLnExPvlvW8=; b=uJ56LCKNxtl9ipnOFUm64/2GuQi0XJX1O5LflhGyU6FLrcd0LHSBmK1j3S7VpNlcuD 2I53wFC+P87PYT7XvuLEsjmi8/ohwlONd8R+MR07XdjLYXM6Vb+r8/xu2IVzg78P7MJi ZONgjbG+JujC6QoCY0PvhVdYuMdxkMhBTB9g7x8jVqJJa0Nl5Q+AmvemGi6eXvCKxa4e 829K39DVHBmE05WYnH+9HJELcBCwzQEDMgjMTd5oLupQC1RD/jP6Dgs760bYCB0sorCg XPpMKpXtztBn9uYhQD5gDf9SG2Z08d1gRN+xRuAmo4eWe1msphnr7Z1vKYUeSdQL2ppf EcIA== X-Gm-Message-State: AOJu0YwRLiYCUXBaQstPxXW8VaymEKEhkq/Kod4CWkoAQGStSrDlOn8B MiViBq16ydluol1lWvbz/J4mopXVkjSISPV8z4woEJagoGn7Dwbmi07w/CaeQzRKaDNib6G8Z0v 5kdz+7CXDSMXc20p6Ba0l7jdOHVRBo4w= X-Gm-Gg: ASbGnctTWlgSwSmtv7BI1d5WOEbooGa487bEGBXOub6vZDwLaowybCgc3NXQax7dbMj UsBNWSuAM7yPluzHokcfVPfpZRZE8rhToHPlwpDxRm84k54wHE857tkK++la4Kdcg6jf05EfmNc 9cfl0DpSqzSYMtxFQgEwMwV0Eww0IJs2URrdmDobTCmlPvSSS3zfYc//oBKuFVt97jVoM+TX/Ev rfPdex/xcIVXn+iEHKg9RYvyurFMc3rhZfrPri7pozQIF6WiQKkeHtRhgIm04i1ptgRIL/X1w== X-Google-Smtp-Source: AGHT+IHuy+l7VkZw2uzYknsi4CIDxBvAnpafUhpCyNqE1R8nJ4jpcnAV9JvBx9gRFYAb+n5F+FkUqHtcLt5/HfsLZpg= X-Received: by 2002:ac2:4bcc:0:b0:560:8b56:5da6 with SMTP id 2adb3069b0e04-56261219cedmr5331095e87.37.1757530492128; Wed, 10 Sep 2025 11:54:52 -0700 (PDT) MIME-Version: 1.0 References: In-Reply-To: From: Merlin Moncure Date: Wed, 10 Sep 2025 12:54:39 -0600 X-Gm-Features: AS18NWDjSYiafWo2b2uJAHt37XWQ5kuWnMuyP67DmVgm6_5KZZHZ9s2ABCyB9Cs Message-ID: Subject: Re: MVCC and all that... To: Ron Johnson Cc: pgsql-general Content-Type: multipart/alternative; boundary="000000000000aa5562063e76f4e7" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000aa5562063e76f4e7 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable On Tue, Sep 9, 2025 at 7:11=E2=80=AFPM Ron Johnson wrote: > On Tue, Sep 9, 2025 at 8:41=E2=80=AFPM Justin wrot= e: > >> >> On autonomous transactions we have procedures now that allow transaction= s >> inside of transactions that can be committed and rollbacked. that has b= een >> around for several years now. >> > [snip] >> > anonymous functions in PG have several limitation not just input >> arguments (not sure i see the need for that) >> > > Aren't transience and "ad hockery" the whole point of anonymous > procedures? Thus, I don't see the point of passing them parameters, eith= er. > > (When I *do* need something similar, I build the DO block as a bash > string variable with environment variables as "parameters", and then > execute it via psql -c "$sql" More like a template, TBH. It's great for > purging old data from tables, since I can bypass records who's DELETE > statements fail due to a FK constraint. > IMO, you can't equate procedures to autonomous transactions. The only way to do AT in a procedure today is with dblink if you are limited to core/contrib. pg_background can do them, as can my library, pgasync. I would humbly state it's a very high quality library to deal with them in a general way :). Autonomous transactions are basically threading at the SQL level. The classic use case for them is to emit log records while you're processing some bigger transaction, so that if/when rollback occurs you have some evidence of processing. There are many, many other use cases for them however if you are deep into backend programming. I will say that the stored procedure COMMIT feature made this library possible as before, it was not possible to have long running processes in the database at the SQL level. Personally, rather than having explicit syntax supporting AT (or at least, in addition to-), I would like to see the dblink library boned up; we ought to have asynchronous connections, and a multi connection dblink_poll() to avoid the dblink_is_busy polling loop. Getting back to firebase, I suppose we ought not to treat the article author too harshly. There's nothing wrong with advocating for your solution although a more balanced treatment might have been more compelling= . merlin --000000000000aa5562063e76f4e7 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
On Tue, Sep 9, 2025 at 7:11=E2=80=AFPM Ro= n Johnson <ronljohnsonjr@gmai= l.com> wrote:
=
On Tue, Sep 9, 2025 at 8:41=E2=80=AFPM Justin <zzzzz.graf@gmail.com> wrot= e:
=C2=A0
On=C2=A0autonomous transactions = we have procedures now that allow transactions inside of transactions that = can be committed and rollbacked.=C2=A0 that has been around for several yea= rs now.=C2=A0
<= div>=C2=A0[snip]
anonymous functions in PG have several limitation not just input arg= uments=C2=A0(not sure i see the need for that)=C2=A0

Aren't transience and "ad hockery" = the whole point of anonymous procedures?=C2=A0 Thus, I don't see the po= int of passing them parameters, either.

(When I do=C2=A0need something similar, I build the DO block as a bash string = variable with environment variables as "parameters", and then exe= cute it via psql -c "$sql"=C2=A0 More like a template, TBH.=C2=A0= It's great for purging old data from tables, since I can bypass record= s who's DELETE statements fail due to a FK constraint.

IMO, you can't equate procedures to a= utonomous transactions.=C2=A0 The only way to do AT in a procedure today is= with dblink if you are limited to core/contrib.=C2=A0 pg_background can do= them, as can my library,=C2=A0pgasync.=C2=A0 I would humbly state it's a very high quality l= ibrary to deal with them in a general way :).

Auto= nomous transactions are basically threading at the SQL level.=C2=A0 The cla= ssic use case for them is to emit log records while you're processing s= ome bigger transaction, so that if/when rollback occurs you have some evide= nce of processing.=C2=A0 =C2=A0There are many, many other use cases for the= m however if you are deep into backend programming.

I will=C2=A0say that the stored procedure COMMIT feature made this librar= y possible as before, it was not possible to have long running processes in= the database at the SQL level.=C2=A0

Personally, = rather than having explicit syntax supporting AT (or at least, in addition = to-), I would like to see the dblink library boned up; we ought to have asy= nchronous connections, and a multi connection dblink_poll() to avoid the db= link_is_busy polling loop.

Getting back to firebas= e, I suppose we ought not to treat the article author too harshly.=C2=A0 Th= ere's nothing wrong with advocating for your solution although a more b= alanced treatment might have been more compelling.

merlin

--000000000000aa5562063e76f4e7--