feature request: deterministic DSN

[stapelberg]

Hello again @zombiezen; I have another request but figured I’d file it separately from issue #2 so that you can consider them separately.

Currently, postgrestest sets a random password and creates a database with random name. This is great for isolation, but also means that passing a PostgreSQL environment URL to a whole bunch of tests via an environment variable will necessarily invalidate Go’s cache (because the environment variable contains different passwords and database names from invocation to invocation).

This can be fixed by making PostgreSQL listen on a UNIX domain socket instead of a TCP port, which obviates the need for a password. The database name can be hard-coded to “test”, as we’re working with ephemeral instances anyway that don’t contain any other tables.

Would this be a change for which you’d accept contributions? I have a proof-of-concept working, but will only invest time in cleaning it up if you’re interested in the change :)

Thanks

[zombiezen]

I'm not opposed in principle, but I have a hazy recollection of trying to use UNIX domain sockets in early drafts of this package (might never have left proof of concept, I don't see it in version history) and running into trouble. The best evidence I can come up with for this hunch is go.dev/issue/30844 and https://github.com/lib/pq/issues/796, but in re-reading now, I don't see anything stopping it.

Regardless, I think we will need to support TCP at least for Windows. Basically, as long as you have something that passes in CI, I would accept it. 👍

[stapelberg]

Good to know! Yeah, I figured that using TCP ports might be for Windows.

However, I have since seen that Go builds the unixsock_posix.go variant on Windows, too: https://cs.opensource.google/go/go/+/refs/tags/go1.22.1:src/net/unixsock_posix.go;l=5;drc=a81507868344dccebef13c6d8d890633e59a93e3 and there was a blog post by Microsoft from 2017 explaining that UNIX domain sockets now work on Windows, too: https://devblogs.microsoft.com/commandline/af_unix-comes-to-windows/

I’ll try to find some time to boot up a Windows instance and test it out. Maybe we can use UNIX Domain Sockets everywhere, perhaps with a fallback for (older?) Windows if needed.

[stapelberg]

I finally found some time to boot a Windows machine and indeed, PostgreSQL 17 can listen just fine on a UNIX socket on Windows 11 Home (Version 23H2):

Given that UNIX sockets were introduced in Windows 10 and given that the vast majority of Windows users are on either Windows 10 or Windows 11, I think we could switch to using UNIX sockets everywhere.

Do you have some time to review a PR if I prepared one? :)

Thanks in avdance

[stapelberg]

Do you have some time to review a PR if I prepared one? :)

I’ll take the silence as a “no” and have now published my version of postgrestest at https://github.com/stapelberg/postgrestest :)

The UNIX domain socket support on Windows turned out to require an ugly hack (though with a bit of luck the upstream fix will be merged into lib/pq soon and we can get rid of it).

I’ll give you another note when my blog post is finished. I rewrote it almost entirely, but I think I’m pretty close to a good state now.

[zombiezen]

Whoops, thanks for the ping. I would have time to review a PR for this issue, but I also totally understand if you want to fork, given your needs. 😄

[stapelberg]

Whoops, thanks for the ping. I would have time to review a PR for this issue, but I also totally understand if you want to fork, given your needs. 😄

Alright, cool! I have sent https://github.com/zombiezen/postgrestest/pull/5 for now, and will send the other PR once we can implement it without the lib/pq workaround.

[stapelberg]

I’ll give you another note when my blog post is finished. I rewrote it almost entirely, but I think I’m pretty close to a good state now.

As promised: https://michael.stapelberg.ch/posts/2024-11-19-testing-with-go-and-postgresql-ephemeral-dbs/

I’ll report back once the lib/pq fix is merged, at which point it will make sense to send the next PR.