Concurrent psql API

[ redirecting to -hackers since -patches isn't the place for general
discussion of feature specifications ]

Gregory Stark <stark(at)enterprisedb(dot)com> writes:
> So based on the feedback and suggestions here this is the interface I suggest:

> \connect& - to open a new connection keeping the existing one
> \g& - to submit a command asynchronously (like & in the shell)
> \S [Sess#] - to _S_witch to a different _S_ession
> - if no connection # specified list available _S_essions
> \D - _D_isconnect from current session (like ^D in the shell)

This is still the latest API suggestion for concurrent psql, right?

After reflecting on it for awhile it seems to me that the use of
automatically assigned numbers as connection IDs is kind of a wart.
It makes it difficult if not impossible to write context-insensitive
script fragments, and even for interactive use it doesn't seem
especially convenient. How about naming connections with user-assigned
strings, instead, eg

\connect& name [ optional connect params ]
\S name

This would require choosing a name for the default session, maybe "-".
Or you could use "1" if you figured that people really would prefer
numbers as IDs.

I'm not real thrilled with overloading \S with two fundamentally
different behaviors, either. Can't we find a different string to assign
to the listing purpose? Maybe \S without parameter should mean to
switch to the default session.

> Another thought I had for the future is a \C command to simulate C-c and send
> a query cancel. That would let us have regression tests that query
> cancellation worked.

Do we really need a regression test for that? I notice \C is already
taken. In general the space of backslash command names is taken up
densely enough that eating single-letter names for marginal functions
doesn't seem wise. (I also question giving \D a single-letter name.)

But the part of the API that really seems like a wart is

+ <varlistentry>
+ <term><varname>ASYNC_DELAY</varname></term>
+ <listitem>
+ <para>
+ Wait up to this period of time (in milliseconds) for output prior to
+ any connection switch. If no asynchronous command is pending or if any
+ output arrives <application>psql</> may not wait the full specified
+ time.
+ </para>

There is no way to select a correct value for ASYNC_DELAY --- any value
you might pick could be too small if the machine is heavily loaded.
In any case for safety's sake you'd need to pick values much larger than
(you think) are really needed, which is not cool for something we hope to
use in regression tests. Those of us who routinely run the tests many
times a day will scream pretty loudly if they start spending most of
their time waiting --- and the prospect of random failures on heavily
loaded buildfarm members is not appetizing either.

What seems possibly more useful is to reintroduce \cwait (or hopefully
some better name) and give it the semantics of "wait for a response from
any active connection; switch to the first one to respond, printing its
name, and print its result".

This would lead to code like, say,

\c& conn1
\c& conn2
...
\S conn1
CREATE INDEX ... \g&
\S conn2
CREATE INDEX ... \g&
...
\cwait
\cwait

The number of \cwaits you need is exactly equal to the number of
async commands you've issued. For regression testing purposes
you'd need to design the script to ensure that only one of the
connections is expected to respond next, but that seems necessary
anyway --- and you don't need any extra checks to catch the case
that you get an unexpected early response from another one.

Hmm, this still seems a bit notation-heavy, doesn't it? What if \g&
takes an arg indicating which connection to issue the command on:

\c& conn1
\c& conn2
...
CREATE INDEX ... \g& conn1
CREATE INDEX ... \g& conn2
...
\cwait
\cwait

Not totally sure about that one, but issuing a command on a background
connection seems appealing for scripting purposes. It eliminates the
risk that the query response comes back before you manage to switch away
from the connection; which would be bad because it would mess up your
count of how many cwait's you need. It seems a bit more analogous to
the use of & in shell scripts, too, where you implicitly fork away from
the async command. (Maybe c& shouldn't make the new connection
foreground either?)

regards, tom lane