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 1v87rr-00DAMk-GX for pgsql-general@arkaria.postgresql.org; Mon, 13 Oct 2025 02:00:55 +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 1v87ro-003TMC-PJ for pgsql-general@arkaria.postgresql.org; Mon, 13 Oct 2025 02:00:53 +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 1v87ro-003TM4-A4 for pgsql-general@lists.postgresql.org; Mon, 13 Oct 2025 02:00:53 +0000 Received: from mail-il1-x130.google.com ([2607:f8b0:4864:20::130]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.96) (envelope-from ) id 1v87rm-001qSJ-1H for pgsql-general@lists.postgresql.org; Mon, 13 Oct 2025 02:00:52 +0000 Received: by mail-il1-x130.google.com with SMTP id e9e14a558f8ab-42e6df6fe53so14906495ab.2 for ; Sun, 12 Oct 2025 19:00:49 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1760320848; x=1760925648; darn=lists.postgresql.org; h=in-reply-to:from:content-language:references:to:subject:user-agent :mime-version:date:message-id:from:to:cc:subject:date:message-id :reply-to; bh=HsGgTAzWJh0tOUpAB4fzHy1SVddbuOT3upXZxe2GhVU=; b=Y7t4EqOQsryVRbWnR68bVcnNeUrZHxoTtHMMW81S6tyRttp6K5eAm51jhEfGutjGnc XdNXbhkGqJXB65FwOSxrssRW329EdPBM4DzsA09m2pwtOJJ6RQJCEeIGOl7jd3t1cb8F Bbi7/Wwtqbwc5NnArnZlP1T5oAbZBDbmjXOxewPcdIVvqxPCjgSLkSh7qDZqF7mwCP67 rsBkHZ02Xiwm8r3SmLP8aYUzinBmQtlUKbrJ2OBg3c9S1c+gW7wn+YRnWm3ZDovERgmn +v/eYpldYa1XjThRxEoZfcXhLpbD/VQ9fqJQJfsy73F0eW3x/utQ/32T5BEZJDl6J3kA TKkA== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1760320848; x=1760925648; h=in-reply-to:from:content-language:references:to:subject:user-agent :mime-version:date:message-id:x-gm-message-state:from:to:cc:subject :date:message-id:reply-to; bh=HsGgTAzWJh0tOUpAB4fzHy1SVddbuOT3upXZxe2GhVU=; b=LNpSDv5wEX9FG/SMGWdSvZAq87F9MIxxKqXRGnH2/bAxuNrBpKl09jFgmWC1rulrLB LGfyGJ7cgFy5Nw2+muevwB4jUEXP19xBzDjlWXulZW+Y7vizkF7Uu8T+HxcrMIrpKMjQ +GDTRMWhrJUzkUYeG6aPBdKguxvbv1DSEp0SH/7eYrzrOvo6bXOYDSNYv9lwWHBCBlCg IltGRFErdDmdRwZve895oNi7EUwUBWioZxBJQhF8ksPt/L/mb3QM8c5OlPVQqaAp9SO2 ZUwF97aI42AHUFWgthaSpQ3gUSflnDlimFmfCH2SILLUxy28p3oNfEY3QYQbwecf6MWR pAZw== X-Gm-Message-State: AOJu0YxvhBDPfsmGsKvCHDBAe3ePZvsfTQluFG5Mcwln2sbjFCUI2cb1 aoPx8MMjODJa/MN+c8v/cCfDJYGm7ZFRXiVGRZLd5ZQTiN+A8bvV5wWzqA8Z0EDD X-Gm-Gg: ASbGncunbd1sO+ACuGRMsJHocbXU5mu0E10FkWYBPm0iGEUVT8XTGYFK3rlrAMn4jrC ZrvKL6YauzKyM9JE77X4hYiHdwTcNbKLUs9vhbrSz0SBrXXvULWsURyFyFyGCSlARszznnXfnuB +CwBQiecoFTLQW/irspO7ATUqKxZmbpo+nMOM7PHL2BV6FSj6eL6NaEtvSuXXxEs/00YpuDvmKr zqbfig522n13R9CUHse5lQbNbSMe8Gbgz+4YaFMnF6LaefN2ZGPCnFoev1tMMQpX8Y6RORXReCL 4M3FMSdRDy4QypBIl+EN3G6ATkF6pQiaZ8Z5SCo0nM3bifCRvBjmo0d18RoFY399DfRrf2LIZy4 +bbWIj920AASj+pK1ROlFg6HEKI+DOloPA9v2vBet9I+Jg/wfvvRsmdGVtz1D5fBA0maTSCdtj/ hQ8tJrcO5MZcRGKNs= X-Google-Smtp-Source: AGHT+IHHEYE+gdOTarHW6U9dL6hNJjMDu/pk5WNCXy05gqf3kbXoT+7UhSHBJteph8a0lRoRoB9Dhw== X-Received: by 2002:a05:6e02:1b01:b0:42f:8fcb:cfd8 with SMTP id e9e14a558f8ab-42f8fcbd118mr146932525ab.27.1760320847773; Sun, 12 Oct 2025 19:00:47 -0700 (PDT) Received: from ?IPV6:2601:681:4c01:7310:8a8a:b4ac:5130:71c9? ([2601:681:4c01:7310:8a8a:b4ac:5130:71c9]) by smtp.gmail.com with ESMTPSA id 8926c6da1cb9f-58f7200b0a4sm3172401173.38.2025.10.12.19.00.47 for (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Sun, 12 Oct 2025 19:00:47 -0700 (PDT) Content-Type: multipart/alternative; boundary="------------UvZgjRejREff0zUO0i1ZCPmn" Message-ID: <73ff46d2-0bad-4a8d-9928-e494c2d5b0d1@gmail.com> Date: Sun, 12 Oct 2025 20:00:46 -0600 MIME-Version: 1.0 User-Agent: Mozilla Thunderbird Subject: Re: Option on `postgres` CLI to shutdown when there are no more active connections? To: pgsql-general@lists.postgresql.org References: Content-Language: en-CA From: Rob Sargent In-Reply-To: List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk This is a multi-part message in MIME format. --------------UvZgjRejREff0zUO0i1ZCPmn Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit On 10/12/25 16:10, David Barsky wrote: > > stop mode shuts down the server that is running in the specified data > > > directory. Three different shutdown methods can be selected with the > -m > > > option. “Smart” mode disallows new connections, then waits for all > > existing > > clients to disconnect. If the server is in hot standby, > recovery and > > streaming replication will be terminated once all clients > have > disconnected. > > “Fast” mode (the default) does not wait for clients > to disconnect. All > > active transactions are rolled back and clients are > forcibly > disconnected, > > then the server is shut down. “Immediate” mode > will abort all server > > processes immediately, without a clean shutdown. > This choice will > lead to a > > crash-recovery cycle during the next server > start. > > Ah, I missed this, thanks! I'm still new to this and unsure when I > should use > `postgres` vs. `pg_ctl`. I can probably hack something together with this! > > > Postgres is not an embedded database, if you want that experience then > > use a database that is designed to be embedded. > > That's fair, especially from an operational standpoint. However, I _think_ > Postgres can get really close to an embedded database's development > experience > by doing a few tricks that I'll elaborate on later on in this email. > > > > I think OP is looking for AUTO_CLOSE, like SQL Server (and Rdb/VMS > before > > > it). Its only real utility is OP's use-case: a Windows desktop > running local > > > testing. > > > We in the shell scripting daemon world don't think like that. > > > From the original post: > > > "Is there any interest in adding a command line option to the `postgres` > > CLI" > > Which I took to mean: > > > > https://www.postgresql.org/docs/current/app-postgres.html > > I think Ron's interpretation is correct, but I also don't mind using > `pg_ctl`! > And yes, the thing I'm looking for looks pretty similar to SQL Server's > `AUTO_CLOSE`. > > More concretely, the desiderata are (some are more flexible then others): > > 1. Our test runner runs each test as a standalone process. While it > can _setup_ >    a test environment atomically, it can't tear down a test environment >    atomically. I think this is reasonable stance on the part of the > test runner >    to encourage reliable test suites. > 2. We started by using SQLite, which has the _really nice_ property of > being >    able to function entirely in-memory. This means that when the test > completes, >    cleanup of the entire database occurs due to the operating system >    deallocating the test process' memory; no orphaned processes to > think about. > 3. After someone installs all the tools that they need for their > development >    environment (language toolchains, editor, database), they shouldn’t > need to >    do any additional, ongoing maintenance. Having experienced a > workflow where >    the entire build/test process is almost entirely self-contained, the >    productivity benefits are massive and I really don’t want to go back. >    1. There's an additional benefit here: we're able to unit test > against the >       actual database we're running against in production with > complete fidelity >       (some people might say that that these are really integration > tests, but >       if each test completes in 0.02 milliseconds and scales to use > all cores on >       my machine, I consider them to be _morally_ unit tests) By "against the actual database..in production" do you mean the server type (e.g. postgres) or a verbatim data set?  I am assuming the former.  Also assuming this isn't the application code hitting the server directly. > > I'm pretty sure I want the following behavior from Postgres (this is > the part I > referred to above that would get Postgres pretty close to the development > experience of an embedded database!): > > 1. On test, create or connect to an existing Postgres instance. Since > each test >    is its own standalone process, I think something shaped like optimistic >    locking to launch Postgres at a given port suffices. The operating > system >    will complain if two processes are launched the same port and the > OS holding >    the lock on the port should prevent any TOCTOU bugs. > 2. Each test runs their own set of test transactions, which are > automatically >    rolled back at the end of each test. > 3. Postgres does some sort of connection-based reference counting > after the >    first connection. Once all connections close and a short timeout window >    passes (e.g., 100ms, but it should probably be configurable?) > Postgres shuts >    down and cleans up any on-disk data. "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. > > Best, > David 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. --------------UvZgjRejREff0zUO0i1ZCPmn Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 8bit

On 10/12/25 16:10, David Barsky wrote:
> stop mode shuts down the server that is running in the specified data >
> directory. Three different shutdown methods can be selected with the -m >
> option. “Smart” mode disallows new connections, then waits for all > existing
> clients to disconnect. If the server is in hot standby, > recovery and
> streaming replication will be terminated once all clients > have disconnected.
> “Fast” mode (the default) does not wait for clients > to disconnect. All
> active transactions are rolled back and clients are > forcibly disconnected,
> then the server is shut down. “Immediate” mode > will abort all server
> processes immediately, without a clean shutdown. > This choice will lead to a
> crash-recovery cycle during the next server > start.

Ah, I missed this, thanks! I'm still new to this and unsure when I should use
`postgres` vs. `pg_ctl`. I can probably hack something together with this!

> Postgres is not an embedded database, if you want that experience then
> use a database that is designed to be embedded.

That's fair, especially from an operational standpoint. However, I _think_
Postgres can get really close to an embedded database's development experience
by doing a few tricks that I'll elaborate on later on in this email.

> > I think OP is looking for AUTO_CLOSE, like SQL Server (and Rdb/VMS before
> > it). Its only real utility is OP's use-case: a Windows desktop running local
> > testing.
> > We in the shell scripting daemon world don't think like that.
> > From the original post:

> "Is there any interest in adding a command line option to the `postgres`
> CLI"
> Which I took to mean:
>

I think Ron's interpretation is correct, but I also don't mind using `pg_ctl`!
And yes, the thing I'm looking for looks pretty similar to SQL Server's
`AUTO_CLOSE`.

More concretely, the desiderata are (some are more flexible then others):

1. Our test runner runs each test as a standalone process. While it can _setup_
   a test environment atomically, it can't tear down a test environment
   atomically. I think this is reasonable stance on the part of the test runner
   to encourage reliable test suites.
2. We started by using SQLite, which has the _really nice_ property of being
   able to function entirely in-memory. This means that when the test completes,
   cleanup of the entire database occurs due to the operating system
   deallocating the test process' memory; no orphaned processes to think about.
3. After someone installs all the tools that they need for their development
   environment (language toolchains, editor, database), they shouldn’t need to
   do any additional, ongoing maintenance. Having experienced a workflow where
   the entire build/test process is almost entirely self-contained, the
   productivity benefits are massive and I really don’t want to go back.
   1. There's an additional benefit here: we're able to unit test against the
      actual database we're running against in production with complete fidelity
      (some people might say that that these are really integration tests, but
      if each test completes in 0.02 milliseconds and scales to use all cores on
      my machine, I consider them to be _morally_ unit tests)
By "against the actual database..in production" do you mean the server type (e.g. postgres) or a verbatim data set?  I am assuming the former.  Also assuming this isn't the application code hitting the server directly.

I'm pretty sure I want the following behavior from Postgres (this is the part I
referred to above that would get Postgres pretty close to the development
experience of an embedded database!):

1. On test, create or connect to an existing Postgres instance. Since each test
   is its own standalone process, I think something shaped like optimistic
   locking to launch Postgres at a given port suffices. The operating system
   will complain if two processes are launched the same port and the OS holding
   the lock on the port should prevent any TOCTOU bugs.
2. Each test runs their own set of test transactions, which are automatically
   rolled back at the end of each test.
3. Postgres does some sort of connection-based reference counting after the
   first connection. Once all connections close and a short timeout window
   passes (e.g., 100ms, but it should probably be configurable?) Postgres shuts
   down and cleans up any on-disk data.
  
"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.


Best,
David

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.

--------------UvZgjRejREff0zUO0i1ZCPmn--