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 1v8O4f-00HLiH-B5 for pgsql-general@arkaria.postgresql.org; Mon, 13 Oct 2025 19:19:13 +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 1v8O4b-007NBa-EB for pgsql-general@arkaria.postgresql.org; Mon, 13 Oct 2025 19:19:10 +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 1v8O4a-007NBR-W7 for pgsql-general@lists.postgresql.org; Mon, 13 Oct 2025 19:19:10 +0000 Received: from mail-vs1-xe44.google.com ([2607:f8b0:4864:20::e44]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.96) (envelope-from ) id 1v8O4Z-001yvb-0X for pgsql-general@lists.postgresql.org; Mon, 13 Oct 2025 19:19:09 +0000 Received: by mail-vs1-xe44.google.com with SMTP id ada2fe7eead31-59a8c1eb90cso2168446137.0 for ; Mon, 13 Oct 2025 12:19:06 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=davidbarsky.com; s=google; t=1760383145; x=1760987945; darn=lists.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=91N4hr3jitFS+vIVRcM2l9hMWRjx3beOd+3vb/wDby4=; b=QPqSJeA0Q7wdE8Z9sMP8Ud0ghKkGAXd1re/pPMAc1bEyUegddnNeSXyHoJy+yKEKQV 2YX9ZVoz4YnNNLWGqK7m0WVgYaJEfC4F1otdkkTjrf74kbB9B7QQNFsdcKCQCxJQUEPU BdNZoq+oQjLz4lznUupTggabL+2fDKO7GI7WNBBJhtPbbkS1SzXNE95SWRzhxmZ8XRWj xiUJZ/DJXP1ReAwLDYZEHlwSfr3KDbEdAHzBA0LUJewqJBuh/E1Ly1lWD7Ycvx3aop9y 6pymRccKuUCEItLn5Yhfk9HcK6oEQJ9kM/J018wVbkJh28oYWj1oQTZUml0S0O8xkf4d mNRg== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1760383145; x=1760987945; 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=91N4hr3jitFS+vIVRcM2l9hMWRjx3beOd+3vb/wDby4=; b=iGgiS/DYTsZUmbhSFXyDUN2GFgEtLJEC3hF++BI46ITfa+G1KbYoCmESGnYfJGR9SS NEnbfHeRKAfQL/es3xpc2NRHD9tOUagBZm6qW1+xFLGAbwgW2sfVNvUwTBPflnQlg1Op BmHg/1XGnFLlJTo9a7mSub8TvKZxgjCjqj6nCDm2kCkbKc6cjHVIxlZAx2yj8rVXeZ9A qGtS4FsVLTlyFYVoRMnzVPsjtgtvnOsESxiKm9B5PstHVZ7KlD2K1UsR001WL+A9tcq6 c83lhTtR628kNtbEckABde/X9WxwLRhmCxY16Q2oUWqbcPFrNEC28Mq60Kdp/6Ihecxg zMJg== X-Forwarded-Encrypted: i=1; AJvYcCULDnoWweMzqWqqT2gu84xA1aQA8OCNpLbJMwiVIzR0gcEq3qg9mpbEBw4EVI7rpIkgOhEyerN+RnNqO0KM@lists.postgresql.org X-Gm-Message-State: AOJu0YxKdG17hS639kgNTLsuLLblEpZXmunTHQ3ufwCEnXOuqd/jw8sq iHD+QCM4CqodF3iQqXMWA5xWu5fm8x+W0Vit9Do/lvQ4L+w+EMdQHv5GfnyDSWiu8iqQWhfQtl3 dOvkb76Y596rO3OZ3Z8BwOwZz+p+3Kbryyz/gr3STqg== X-Gm-Gg: ASbGncu6x5MgW//LzJohfsTWcGsoDiaHqF0F5am1hfwnjhR1zyU3DfYv9cKQE7Htn4W MBzfvrnQgelKOazmm2Z1XLCNB1/gnIpvIfFNPAP1S3tBjJ4MC58wovtIO62DpmOy9I+58BrNtT0 vAOkg3j5iHY4vFbF7C331HEmA6/wLtOO888tyzd+S0V0S3n3nDZ5qlj+IpJgExUw7tqh+ijFLxL AvdIedB+3gE94PVbIygh4i6XeQw09W04FabKCtoIY9FIdpNZCgsYMkAFJI= X-Google-Smtp-Source: AGHT+IHB1L5XGx+GS0gK2SHNlycSF+rK2B5OOtl+fIvIE1oEeA+wjlSyex3KasP9lmr1LruzsDDFTFNw4+N5kCkALC8= X-Received: by 2002:a05:6102:290f:b0:5d5:f6ae:38ca with SMTP id ada2fe7eead31-5d5f6ae3bb8mr5393425137.41.1760383144578; Mon, 13 Oct 2025 12:19:04 -0700 (PDT) Received: from 1064022179695 named unknown by gmailapi.google.com with HTTPREST; Mon, 13 Oct 2025 12:19:04 -0700 Received: from 1064022179695 named unknown by gmailapi.google.com with HTTPREST; Mon, 13 Oct 2025 14:19:00 -0500 MIME-Version: 1.0 (Mimestream 1.8.3) References: <73ff46d2-0bad-4a8d-9928-e494c2d5b0d1@gmail.com> <93702.1760321769@sss.pgh.pa.us> <233155.1760369829@sss.pgh.pa.us> In-Reply-To: From: David Barsky Date: Mon, 13 Oct 2025 12:19:04 -0700 X-Gm-Features: AS18NWCKQJMgsKwlqTj125n8P3zuXwBWgbbA8X7wm4MmF1ixOnGhQkCWZ2vNc5E Message-ID: Subject: Re: Option on `postgres` CLI to shutdown when there are no more active connections? To: Dominique Devienne Cc: Ron Johnson , "pgsql-generallists.postgresql.org" , Tom Lane Content-Type: multipart/alternative; boundary="00000000000000628206410f24b7" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --00000000000000628206410f24b7 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable > "Testing" db interaction in a faked, circumscribed > only-my-stuff-is-there world is folly. Certainly each db developer > needs their own instance of the database (on their own box or a > server). And it needs to be kept current with both DDL and domain meta > data changes (see things like flyway) as regularly as is the source > code. It should have a decent representation of a production dataset > else reads and writes will always be fast. All the tests reading and > writing all the columns of all the tables generates a lot of "green > lights" but near zero practicable information in the developers' workflow= . This is not the extent of the testing we plan on using. In this email thread, I'm only referring to fast unit tests that ensure the correctness of the application's logic and behavior. These are paired with the proper, hygieni= c things you called out. > > Were I a betting man, I would bet heavily against this community, which > > prides itself on NOT losing data, allowing an option that would do just > > that. > Well, mumble ... we have any number of options that can be read that way. One > obvious one is that we don't try to prevent you from putting $PGDATA on a RAM > disk. Turning off fsync is another popular way to trade away durability for > speed. We already do both for unit tests, which helps a bunch :). > But I concur with the point that we're not here to pretend to be an embedded > database, as there are other projects that do that better (for example, our > good friends at SQLite). > The advice I'd give the OP is to take a look at our TAP-test infrastructure. > We've put a good deal of effort, and are continuing to do so, into lettin= g > those tests spin up transitory testing databases pretty cheaply. Thanks! I'll dig in. I'm guessing you're referring to these? https://www.postgresql.org/docs/current/regress-tap.html For what it's worth, I don't think Postgres _should_ be an embedded database, but I think there are some qualities of embedded databases that I would love to see in Postgres: namely, the self-contained cleanup. Regardless, I worry that me introducing SQLite into this discussion was a mistake and hurt the coherency of my request, so I apologize for that. > SQLite is fantastic, but it's type-system and lock-model are too restrictive, > for a general DB. Similar to David, I think PostgreSQL is close to my ideal > above, yet still far-enough (and perhaps unwilling enough, as a community= ) > to venture into embedded and localhost use-cases, that it's frustrating. Yup, Dominique understands what I want: Postgres' type system, query planner, and locking model, but _shades_ of SQLite's operational properties during local development. However, I don't really need Postgres to function like an embedded database; I just want the self-contained process cleanup. Connecting to Postgres over TCP over localhost is perfect for my use-case: the difference in performance for an in-process database (=C3=A0 la SQLite) vs. connecting ov= er localhost is the difference between single-digit microseconds and single-digit milliseconds. That difference matters in some cases, but not here: as far as a human running tests is concerned, both are instant. Here's someone at CrunchyData/Snowflake providing an experience report of this exact workflow= : https://www.crunchydata.com/blog/dont-mock-the-database-data-fixtures-are-p= arallel-safe-and-plenty-fast Anyways, I'll try to get at what motivated this whole discussion: would there be community opposition to adding a CLI flag that'd exit/shutdown all Postgres processes once all pending connections close? E.g., something similar to SQ= L Server's `auto_close` in the vein of `postgres -c "auto_close_after=3D100"` or `pg-ctl start --exit-mode=3Dsmart`? On Oct 13, 2025 at 9:43:15=E2=80=AFAM, Dominique Devienne wrote: > On Mon, Oct 13, 2025 at 5:37=E2=80=AFPM Tom Lane wrot= e: > > Dominique Devienne writes: > > > On Mon, Oct 13, 2025 at 5:08=E2=80=AFPM Ron Johnson > 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. > > > --00000000000000628206410f24b7 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
> "Testing" db i= nteraction in a faked, circumscribed
> only-my-stu= ff-is-there world is folly. Certainly each db developer
> needs their own instance of the database (on their own box or a
> server). And it needs to be kept current with both = DDL and domain meta
> data changes (see things lik= e flyway) as regularly as is the source
> code. It= should have a decent representation of a production dataset
> else reads and writes will always be fast. All the tests read= ing and
> writing all the columns of all the table= s generates a lot of "green
> lights" bu= t near zero practicable information in the developers' workflow.
<= div dir=3D"ltr">
This is not the extent of the te= sting we plan on using. In this email thread,
I'm= only referring to fast unit tests that ensure the correctness of the
=
application's logic and behavior. These are paired wit= h the proper, hygienic
things you called out.

> > Were I a betting man, I= would bet heavily against this community, which
>= > prides itself on NOT losing data, allowing an option that would do ju= st
> > that.
> Well, m= umble ... we have any number of options that can be read that way. One
> obvious one is that we don't try to prevent you = from putting $PGDATA on a RAM
> disk. Turning off = fsync is another popular way to trade away durability for
> speed.

We alread= y do both for unit tests, which helps a bunch :).
> But I concur with the point that we're not= here to pretend to be an embedded
> database, as = there are other projects that do that better (for example, our
> good friends at SQLite).

> The advice I'd give the OP is to take a look at our = TAP-test infrastructure.
> We've put a good de= al of effort, and are continuing to do so, into letting
> those tests spin up transitory testing databases pretty cheaply.

Thanks! I'll dig in. I&#= 39;m guessing you're referring to these?

<= /div>
For what it's worth, I don't think Postgres _= should_ be an embedded database,
but I think there ar= e some qualities of embedded databases that I would
l= ove to see in Postgres: namely, the self-contained cleanup. Regardless,
I worry that me introducing SQLite into this discussion = was a mistake and hurt
the coherency of my request, s= o I apologize for that.

&g= t; SQLite is fantastic, but it's type-system and lock-model are too res= trictive,
> for a general DB. Similar to David, I = think PostgreSQL is close to my ideal
> above, yet= still far-enough (and perhaps unwilling enough, as a community)
> to venture into embedded and localhost use-cases, that it&= #39;s frustrating.

Yup, Do= minique understands what I want: Postgres' type system, query planner,<= /div>
and locking model, but _shades_ of SQLite's opera= tional properties during local
development. However, = I don't really need Postgres to function like an embedded
database; I just want the self-contained process cleanup. Connecti= ng to Postgres
over TCP over localhost is perfect for= my use-case: the difference in
performance for an in= -process database (=C3=A0 la SQLite) vs. connecting over
localhost is the difference between single-digit microseconds and singl= e-digit
milliseconds. That difference matters in some= cases, but not here: as far as a
human running tests= is concerned, both are instant. Here's someone at
CrunchyData/Snowflake providing an experience report of this exact workfl= ow:

<= br>
Anyways, I'll try to get at what motivated th= is whole discussion: would there be
community opposit= ion to adding a CLI flag that'd exit/shutdown all Postgres
processes once all pending connections close? E.g., something sim= ilar to SQL
Server's `auto_close` in the vein of = `postgres
-c "auto_close_after=3D100"` or `= pg-ctl start --exit-mode=3Dsmart`?

On Oct 13, 2025 at 9:43:15=E2=80= =AFAM, Dominique Devienne <ddevie= nne@gmail.com> wrote:
=20
On Mon, Oct 13, 2025 at 5:37=E2=80=AFPM Tom Lane <tgl@sss.pgh.pa.us> wrote:
Dominique Devienne <ddevie= nne@gmail.com> writes:
&g= t; On Mon, Oct 13, 2025 at 5:08=E2=80=AFPM Ron Johnson <ronljohnsonjr@gmail.com> wrote:
>> Shared memory means that I can s= tomp all over you, and you can't stop me.=C2=A0 That's the antithes= is of ACID.

> SHM is how SQLite in WAL mode coordinates acces= s to the same DB from
> sever= al connections. So if it's good enough for SQLite, I don't see
<= /blockquote>
> what it would be wrong for Post= greSQL too.

SQLite has accepted the cost that comes with being e= mbedded, which is
that applicati= on-side memory-stomping bugs can destroy the database.
Postgres is not willing to make that tradeoff.=C2=A0= From a pure
developer's per= spective, every time we got a bug report we'd have to
<= blockquote type=3D"cite"> ask "did you observe this while running embe= dded?" and then demand a
re= pro that uses a non-embedded database.=C2=A0 We are not going to help
application authors debug their own b= ugs, 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
"transp= ort" between the libpq-based client, and the backends (running
on l= ocalhost). 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 t= hemselves too, and each process can also be
multi-connection. In the loc= alhost case, the data is private to you,
but can still be accessed concu= rrently across connections (from one or
more processes). And in that cas= e, we shouldn't have to deal with
passwords, and everything should r= un as the OS user.

> SQLite is also AC= ID.

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 weake= n
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
l= ocalhost 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.


--00000000000000628206410f24b7--