Re: Option on `postgres` CLI to shutdown when there are no more active connections? - Mailing list pgsql-general

> 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.