Hi,
On 2023-08-28 17:29:56 +1200, Thomas Munro wrote:
> As an experiment, I hacked up a not-good-enough-to-share experiment
> where $node->safe_psql() would automatically cache a BackgroundPsql
> object and reuse it, and the times for that test dropped ~51 -> ~9s on
> Windows, and ~7 -> ~2s on the Unixen. But even that seems non-ideal
> (well it's certainly non-ideal the way I hacked it up anyway...). I
> suppose there are quite a few ways we could do better:
>
> 1. Don't fork anything at all: open (and cache) a connection directly
> from Perl.
> 1a. Write xsub or ffi bindings for libpq. Or vendor (parts) of the
> popular Perl xsub library?
> 1b. Write our own mini pure-perl pq client module. Or vendor (parts)
> of some existing one.
> 2. Use long-lived psql sessions.
> 2a. Something building on BackgroundPsql.
> 2b. Maybe give psql or a new libpq-wrapper a new low level stdio/pipe
> protocol that is more fun to talk to from Perl/machines?
While we can't easily use plain long-lived psql everywhere, due to tests
depending on no additional connections being present, we could at least
partially address that by adding a \disconnect to psql. Based on your numbers
the subprocess that IPC::Run wraps around psql on windows is a substantial
part of the overhead. Even if we default to reconnecting after every
->psql(), just saving the fork of the wrapper process and psql should reduce
costs substantially.
Famous last words, but it seems like that it should be quite doable to add
that to psql and use it in Cluster->{psql,safe_psql,poll_query_until}? There
might be a few cases that expect the full connection error message, but I
can't imagine that to be too many?
Greetings,
Andres Freund