MIME-Version: 1.0
References: <CAPR5_5psNqCm1OQr5LL4v6z02Sh=2YHYUg8NY+3xMzfGHfKs+w@mail.gmail.com>
 <ea93ff28-4496-4e3d-a529-191582e02344@aklaver.com> <CAPR5_5q2oCkw19rHb7oy-M0M0purWFM5Jc8ub5xDy_NdhUmRTw@mail.gmail.com>
 <73ff46d2-0bad-4a8d-9928-e494c2d5b0d1@gmail.com> <93702.1760321769@sss.pgh.pa.us>
 <CAFCRh-94eXetAjtCURs5F0JZQr=kso0rVgVSEmWiSD7wA6VcMw@mail.gmail.com>
 <CANzqJaDn_mVi4M0CbKnVNwK9RxYOneoCRmd_d69rbjBrwasuPQ@mail.gmail.com>
 <CAFCRh--P93bF0gpkdvg4MzMNqq5gK=4eWFkmBDR0xw9dmFVAPQ@mail.gmail.com> <233155.1760369829@sss.pgh.pa.us>
In-Reply-To: <233155.1760369829@sss.pgh.pa.us>
From: Dominique Devienne <ddevienne@gmail.com>
Date: Mon, 13 Oct 2025 18:43:15 +0200
Message-ID: <CAFCRh--_14QR1_=vZvKVZkcA_1LRO-pQd2p6BBRMreyAv2_z6w@mail.gmail.com>
Subject: Re: Option on `postgres` CLI to shutdown when there are no more
 active connections?
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Ron Johnson <ronljohnsonjr@gmail.com>, 
	"pgsql-generallists.postgresql.org" <pgsql-general@lists.postgresql.org>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
Archived-At: <https://www.postgresql.org/message-id/CAFCRh--_14QR1_%3DvZvKVZkcA_1LRO-pQd2p6BBRMreyAv2_z6w%40mail.gmail.com>
Precedence: bulk

On Mon, Oct 13, 2025 at 5:37=E2=80=AFPM Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Dominique Devienne <ddevienne@gmail.com> writes:
> > On Mon, Oct 13, 2025 at 5:08=E2=80=AFPM Ron Johnson <ronljohnsonjr@gmai=
l.com> wrote:
> >> Shared memory means that I can stomp all over you, and you can't stop =
me.  That's the antithesis of ACID.
>
> > SHM is how SQLite in WAL mode coordinates access to the same DB from
> > several connections. So if it's good enough for SQLite, I don't see
> > what it would be wrong for PostgreSQL too.
>
> SQLite has accepted the cost that comes with being embedded, which is
> that application-side memory-stomping bugs can destroy the database.
> Postgres is not willing to make that tradeoff.  From a pure
> developer's perspective, every time we got a bug report we'd have to
> ask "did you observe this while running embedded?" and then demand a
> repro that uses a non-embedded database.  We are not going to help
> application authors debug their own bugs, especially not when we have
> no visibility into what those are.

That's true for the embedded case, true.

There are some of those, on the ML (custom SQLite-based forum in
fact), but not that many in fact, far from it. So that concern does
exist, but maybe not to the extent one fears.

But not for the localhost case, which remains "client-server"
(multi-process). And SHM is then one of the options for the
"transport" between the libpq-based client, and the backends (running
on localhost). Unix Socket on Linux is almost perfect for the
localhost case, but again, is not portable. And I'd need simplified
authN, on the fly start if necessary, that kind of thing. Our apps are
multi-process themselves too, and each process can also be
multi-connection. In the localhost case, the data is private to you,
but can still be accessed concurrently across connections (from one or
more processes). And in that case, we shouldn't have to deal with
passwords, and everything should run as the OS user.

> > SQLite is also ACID.
>
> I guess they have a different set of assumptions about what that
> buzzword means.

As you wrote, there are existing footguns one can turn on to weaken
ACID already. PostgreSQL is superior to SQLite in many ways. I get
that the embedded use-case is a step too far, for a long time, but the
localhost case, for testing but also for localhost private-data
serving (a possibly cache of a larger remote server) is much more
attainable. And valuable IMHO.