Re: Concurrent psql API

On Tue, 2008-04-08 at 17:10 -0400, Tom Lane wrote:

> 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?)

Yes, I think the \g& conn syntax seems useful. Good thinking.

I agree also that the \S syntax has problems and we would wish to avoid
them. I would still like a way to change the default background session.
That will considerably reduce the number of changes people would need to
make to long scripts in order to be able to use this facility.

For example, if we have a script with 100 commands in, we may find that
commands 1-50 and 51-100 are in two groups. Commands 1-50 are each
dependent upon the previous command, as are 51-100. But the two groups
are independent of each other.

If we use the \g& syntax only, we would need to make 100 changes to the
script to send commands to the right session. If we had the capability
to say "use this background session as the default session to send
commands to", then we would be able to add parallelism to the script by
just making 2 changes: one prior to command 1 and one prior to command
51.

The original \S command had that capability, but was designed to
actually change into that session, giving the problems discussed.
Something like \S (don't care what syntax, though) would definitely
simplify scripting, which I think will translate directly into fewer
bugs for users.

I note \b is available... short for "background". Though I really don't
care what we call that command though, just want the capability.

Also, I don't want to have to count cwaits, so I'd like a command to say
"wait for all background sessions that have active statements" and for
that to be the default. For simplicity, \cwait would do this by default.

So this script

\c& conn1
\c& conn2
...
ALTER TABLE ... ADD PRIMARY KEY \g& conn1
ALTER TABLE ... ADD FOREIGN KEY \g& conn1
ALTER TABLE ... ADD FOREIGN KEY \g& conn1
ALTER TABLE ... ADD FOREIGN KEY \g& conn1
...

ALTER TABLE ... ADD PRIMARY KEY \g& conn2
ALTER TABLE ... ADD FOREIGN KEY \g& conn2
ALTER TABLE ... ADD FOREIGN KEY \g& conn2
ALTER TABLE ... ADD FOREIGN KEY \g& conn2
ALTER TABLE ... ADD FOREIGN KEY \g& conn2
...

\cwait
\cwait

would now become

\c& conn1
\c& conn2
...
\b conn1
ALTER TABLE ... ADD PRIMARY KEY ...
ALTER TABLE ... ADD FOREIGN KEY
ALTER TABLE ... ADD FOREIGN KEY
ALTER TABLE ... ADD FOREIGN KEY
...

\b conn2
ALTER TABLE ... ADD PRIMARY KEY ...
ALTER TABLE ... ADD FOREIGN KEY
ALTER TABLE ... ADD FOREIGN KEY
ALTER TABLE ... ADD FOREIGN KEY
ALTER TABLE ... ADD FOREIGN KEY
...

\cwait

Which seems much cleaner.

--
Simon Riggs
2ndQuadrant http://www.2ndQuadrant.com