Обсуждение: Proposal for async support in libpq

Here's what I propose to do with libpq to make it more useful for
real-time applications.  Any comments or better ideas?

The point of these changes is first to allow frontend applications to
receive NOTIFY responses without having to generate dummy queries,
and second to allow a frontend to perform other work while awaiting
the result of a query.

We can't break existing code for this, so the behavior of PQexec()
can't change.  Instead, I propose new functions to add to the API.
Internally, PQexec will be reimplemented in terms of these new
functions, but old apps won't notice any difference.

The new functions are:

    int PQexecAsync (PGconn *conn, const char *query);

Submits a query without waiting for the result.  Returns TRUE if the
query has been successfully dispatched, otherwise FALSE (in the FALSE
case, an error message is left in conn->errorMessage).

    typedef enum {
        PGASYNC_IDLE,
        PGASYNC_BUSY,
        PGASYNC_DONE
    } PGAsyncStatusType;

    PGAsyncStatusType PQasyncStatus (PGconn *conn);

Indicates the current status of an asynchronous query:
    PGASYNC_IDLE: nothing doing
    PGASYNC_BUSY: async query in progress
    PGASYNC_DONE: query done, can retrieve result with PQasyncResult
When the state is PGASYNC_DONE, calling PQasyncResult will reset the state
to PGASYNC_IDLE.  A new query can only be submitted in the IDLE state.

    PGresult* PQasyncResult (PGconn *conn);

If the state is PGASYNC_DONE and the query was successful, a PGresult
block is returned (which the caller must eventually free).  In all other
cases, NULL is returned and a suitable error message is left in
conn->errorMessage.  Also, if the state is PGASYNC_DONE then it is
reset to PGASYNC_IDLE.

    void PQconsumeInput (PGconn *conn);

This can be called at any time to check for and process new input from
the backend.  It returns no status indication, but after calling it
the application can inspect PQasyncStatus() and/or PQnotifies()
to see if a query was completed or a NOTIFY message arrived.

    int PQsocket (PGconn *conn);

Returns the Unix file descriptor for the socket connection to the
backend, or -1 if there is no open connection.  This is a violation of
modularity, of course, but there is no alternative: an application using
this facility needs to be able to use select() to wait for input from
either the backend or any other input streams it may have.  To use
select() the underlying socket must be made visible.

    PGnotify *PQnotifies (PGconn *conn);

This function doesn't need to change; we just observe that notifications
may become available as a side effect of executing either PQexec() or
PQconsumeInput().


The general assumption is that the application's main loop will use
select() to wait for input.  If select() indicates that input is
pending from the backend, then the app will call PQconsumeInput,
followed by checking PQasyncStatus() and/or PQnotifies().

I expect a lot of people would build "partially async" applications that
still do all the queries through PQexec(), but detect notifies
asynchronously via select/PQconsumeInput/PQnotifies.  This compromise
would allow notifies to be detected without issuing null queries,
without complicating the basic logic of issuing a series of queries.

The same functionality should be added to libpq++.


Some issues to be resolved:

1. The above API assumes that only one query can be outstanding at a
time (per connection).  Is there any prospect that the backends will
ever be able to handle multiple concurrent queries?  If so, we should
design the API so that PQexecAsync returns some kind of "active query"
object that's separate from the connection object.  Then PQasyncStatus
and PQasyncResult would apply to these objects individually (probably
they should act a little differently than given above, too).

2. Any comments about the naming conventions I used?  The existing code
seems a tad inconsistent; what is considered the right practice as to
capitalization etc?

            regards, tom lane

>
> Here's what I propose to do with libpq to make it more useful for
> real-time applications.  Any comments or better ideas?
>
> The point of these changes is first to allow frontend applications to
> receive NOTIFY responses without having to generate dummy queries,
> and second to allow a frontend to perform other work while awaiting
> the result of a query.
>
> We can't break existing code for this, so the behavior of PQexec()
> can't change.  Instead, I propose new functions to add to the API.
> Internally, PQexec will be reimplemented in terms of these new
> functions, but old apps won't notice any difference.

This all looks good.  Another thing we really need it to be able to
cancel queries.  This would be a big win, and looks like it could fit
into the scheme here.

Ideally, I would like to control-c in psql, and have the query cancel,
instead of exiting from pgsql.

--
Bruce Momjian                          |  830 Blythe Avenue
maillist@candle.pha.pa.us              |  Drexel Hill, Pennsylvania 19026
  +  If your life is a hard drive,     |  (610) 353-9879(w)
  +  Christ can be your backup.        |  (610) 853-3000(h)

Bruce Momjian <maillist@candle.pha.pa.us> writes:
> This all looks good.  Another thing we really need it to be able to
> cancel queries.  This would be a big win, and looks like it could fit
> into the scheme here.

I thought about proposing a PQcancelAsync that would cancel the active
query-in-progress.  But that would require support on the backend side,
and I am far from competent to make it happen.  (libpq is simple enough
that I'm not afraid to rewrite it, but making major mods to the backend
is another story.  I just got here this week...)

If anyone who does know what they're doing is willing to make the
necessary backend mods, I'm all for it.  The libpq side would be
easy enough.

How would such cancellation interact with transactions, btw?  Would
you expect it to roll back only the current command, or abort the
whole transaction?  We'd also have to consider corner cases, like
when the backend has already finished the query by the time it gets
the cancel request.

            regards, tom lane

>
> Bruce Momjian <maillist@candle.pha.pa.us> writes:
> > This all looks good.  Another thing we really need it to be able to
> > cancel queries.  This would be a big win, and looks like it could fit
> > into the scheme here.
>
> I thought about proposing a PQcancelAsync that would cancel the active
> query-in-progress.  But that would require support on the backend side,
> and I am far from competent to make it happen.  (libpq is simple enough
> that I'm not afraid to rewrite it, but making major mods to the backend
> is another story.  I just got here this week...)
>
> If anyone who does know what they're doing is willing to make the
> necessary backend mods, I'm all for it.  The libpq side would be
> easy enough.
>
> How would such cancellation interact with transactions, btw?  Would
> you expect it to roll back only the current command, or abort the
> whole transaction?  We'd also have to consider corner cases, like
> when the backend has already finished the query by the time it gets
> the cancel request.

It is pretty easy, just an elog(ERROR) would do it.  The issue is
allowing the backend to see the request.  We can put some checks in
tcop/postgres.c as it moves from module to module, and something in the
executor to check for the cancel, and do an elog(ERROR).  It would be
nice if it arrived as out-of-band data, so we could check for input
quickly without having to actually process it if it is not a cancel
notification.

The out-of-band data will send a SIGURG signal to the backend, and we
can set a global variable, and check the variable at various places.

To do all this, we need to be able to send a query, and not have it
block, and it seems you are giving us this capability.

You supply the indication to the backend, and I will see that the
backend processes it properly.

--
Bruce Momjian                          |  830 Blythe Avenue
maillist@candle.pha.pa.us              |  Drexel Hill, Pennsylvania 19026
  +  If your life is a hard drive,     |  (610) 353-9879(w)
  +  Christ can be your backup.        |  (610) 853-3000(h)

Bruce Momjian <maillist@candle.pha.pa.us> writes:
> You supply the indication to the backend, and I will see that the
> backend processes it properly.

You're on ;-)

Signaling the cancel request via OOB sounds reasonable, as long as
nothing else is using it and all the systems we care about support it.
(I see a couple of routines to support OOB data in
src/backend/libpq/pqcomm.c, but they don't seem to be called from
anywhere.  Vestiges of an old protocol, perhaps?)

I still need to understand better what the backend will send back
in response to a cancel request, especially if it's idle by the
time the request arrives.  Will that result in an asynchronous error
response of some sort?  Do I need to make said response visible to
the frontend application?  (Probably not ... it will have already
discovered that the query completed normally.)

How should cancellation interact with copy in/out?

These are mostly documentation issues, rather than stuff that directly
affects code in libpq, but we ought to nail it down.

            regards, tom lane

b>
> Bruce Momjian <maillist@candle.pha.pa.us> writes:
> > You supply the indication to the backend, and I will see that the
> > backend processes it properly.
>
> You're on ;-)
>
> Signaling the cancel request via OOB sounds reasonable, as long as
> nothing else is using it and all the systems we care about support it.
> (I see a couple of routines to support OOB data in
> src/backend/libpq/pqcomm.c, but they don't seem to be called from
> anywhere.  Vestiges of an old protocol, perhaps?)

Probably.  There is a document on the libpq protocol somewhere.  I
assume you have that already.  It is pgsql/docs/programmer.ps.gz, around
page 118.

>
> I still need to understand better what the backend will send back
> in response to a cancel request, especially if it's idle by the
> time the request arrives.  Will that result in an asynchronous error
> response of some sort?  Do I need to make said response visible to
> the frontend application?  (Probably not ... it will have already
> discovered that the query completed normally.)

Not sure the backend has to signal that it received the cancel request.
Does it?  It could just return a NULL result, that I think is caused by
elog(ERROR) anyway, and we can put in some nice fancy text like 'query
aborted'.


>
> How should cancellation interact with copy in/out?

Not sure on that one.  May not be possible or desirable, but we could
put something in commands/copy.c to check for cancel request.



--
Bruce Momjian                          |  830 Blythe Avenue
maillist@candle.pha.pa.us              |  Drexel Hill, Pennsylvania 19026
  +  If your life is a hard drive,     |  (610) 353-9879(w)
  +  Christ can be your backup.        |  (610) 853-3000(h)

>
> Bruce Momjian <maillist@candle.pha.pa.us> writes:
> > This all looks good.  Another thing we really need it to be able to
> > cancel queries.  This would be a big win, and looks like it could fit
> > into the scheme here.
>
> I thought about proposing a PQcancelAsync that would cancel the active
> query-in-progress.  But that would require support on the backend side,
> and I am far from competent to make it happen.  (libpq is simple enough
> that I'm not afraid to rewrite it, but making major mods to the backend
> is another story.  I just got here this week...)

In backend/libpq/pqcomm.c, I see pg_sendoob() which sends out-of-band
data FROM the backend TO the client, but it is not called from anywhere.

This could be a method of signaling that a notification was pending, and
sending out-of-band data FROm the client TO the backend could be used
for cancelling a query.

out-of-band data causes a convenient signal to the process on the other
end, which can easily be used to handle these cases.

--
Bruce Momjian                          |  830 Blythe Avenue
maillist@candle.pha.pa.us              |  Drexel Hill, Pennsylvania 19026
  +  If your life is a hard drive,     |  (610) 353-9879(w)
  +  Christ can be your backup.        |  (610) 853-3000(h)

While I understand the desire to implement async support with the
minimum of fuss, SQL3 provides the ASYNC, TEST and WAIT statements.
This would be a more "standard" solution, but obviously requires
implementation in the backend.

Phil

> In backend/libpq/pqcomm.c, I see pg_sendoob() which sends out-of-band
> data FROM the backend TO the client, but it is not called from anywhere.
>
> This could be a method of signaling that a notification was pending, and
> sending out-of-band data FROm the client TO the backend could be used
> for cancelling a query.
>
> out-of-band data causes a convenient signal to the process on the other
> end, which can easily be used to handle these cases.

Wasn't the problem with OOB data that java doesn't support this? I
remember that OOB data has come up before on this list a long time ago,
and that at that time some java bloke (peter?) started to complain :)

Maarten

_____________________________________________________________________________
| TU Delft, The Netherlands, Faculty of Information Technology and Systems  |
|                   Department of Electrical Engineering                    |
|           Computer Architecture and Digital Technique section             |
|                          M.Boekhold@et.tudelft.nl                         |
-----------------------------------------------------------------------------

On Sat, 18 Apr 1998, Bruce Momjian wrote:

> In backend/libpq/pqcomm.c, I see pg_sendoob() which sends out-of-band
> data FROM the backend TO the client, but it is not called from anywhere.
>
> This could be a method of signaling that a notification was pending, and
> sending out-of-band data FROm the client TO the backend could be used
> for cancelling a query.
>
> out-of-band data causes a convenient signal to the process on the other
> end, which can easily be used to handle these cases.

Just a quick question: If you have an OOB packet sent to the backend, how
would we handle the case where a row is being sent to the backend, but the
OOB packet comes in the middle of it?

It may sound like a silly question, but I'm thinking if a client is on the
end of a slow network connection, then the packet containing the row could
become fragmented, and the OOB packet could get in the way.

Anyhow, I'm trying to find out how to implement OOB in Java. I know it's
there, as I've seen it in the past. Just can't find it at the moment.

--
Peter T Mount  petermount@earthling.net or pmount@maidast.demon.co.uk
Main Homepage: http://www.demon.co.uk/finder
Work Homepage: http://www.maidstone.gov.uk Work EMail: peter@maidstone.gov.uk

On Sat, 18 Apr 1998, Maarten Boekhold wrote:

> Wasn't the problem with OOB data that java doesn't support this? I
> remember that OOB data has come up before on this list a long time ago,
> and that at that time some java bloke (peter?) started to complain :)

I said at the time, that I wasn't certain that Java did or didn't support
it. Since then, I have noticed references to it, but I've lost them since.

I'm delving into the docs as I type, looking for this.

--
Peter T Mount  petermount@earthling.net or pmount@maidast.demon.co.uk
Main Homepage: http://www.demon.co.uk/finder
Work Homepage: http://www.maidstone.gov.uk Work EMail: peter@maidstone.gov.uk

A combined reply to several comments:

Phil Thompson <phil@river-bank.demon.co.uk> writes:
> While I understand the desire to implement async support with the
> minimum of fuss, SQL3 provides the ASYNC, TEST and WAIT statements.
> This would be a more "standard" solution,

... but it doesn't solve the problem.  My concern is first to get rid
of the need to issue dummy queries to detect NOTIFY responses.  This is
clearly not an SQL language issue, only a matter of poor design of libpq.
The business about supporting async queries is just a nice bonus as far
as I'm concerned.

I don't believe I would want to use the SQL3 approach even if we had it.
I don't know SQL3, but if I make the obvious guess about how these are
supposed to work, then you need a round trip to the server to issue an
async query, and another round trip every time you want to see if it's
done.  (Plus one to fetch the results?)  Now as far as I'm concerned,
the point of these changes is to *decrease* communication loads and
unproductive server cycles.  Not increase them.  I want a scheme that
doesn't require the client to create busywork for itself, the server,
and the network.


Peter T Mount <psqlhack@maidast.demon.co.uk> writes:
> Just a quick question: If you have an OOB packet sent to the backend, how
> would we handle the case where a row is being sent to the backend, but the
> OOB packet comes in the middle of it?

This is the same copy in/out issue I asked about yesterday, no?
I think what Bruce had in mind was that the backend would check for
an OOB cancel once per row, or something like that.  This needs to
be documented, but it seems reasonable...


Bruce Momjian <maillist@candle.pha.pa.us> writes:
> In backend/libpq/pqcomm.c, I see pg_sendoob() which sends out-of-band
> data FROM the backend TO the client, but it is not called from anywhere.
> This could be a method of signaling that a notification was pending, and
> sending out-of-band data FROm the client TO the backend could be used
> for cancelling a query.

I don't see any real need to issue outgoing notifications as OOB data.
If the client needed to interrupt current processing to handle a notify,
then maybe it'd be useful, but how likely is that?  Keep in mind that
the backend currently delays notify reports until end of transaction.
I don't think we really want to change that...

This brings up another issue that I meant to ask about before: exactly
what is the relation between interfaces/libpq and backend/libpq?  It
sorta looks like they were once the same code.  Why were they allowed to
become different, and is there any point in trying to unify them again?


Maarten Boekhold wrote:
> Wasn't the problem with OOB data that java doesn't support this?

I was a little concerned about that too, but as long as we only use it
for noncritical functionality like query cancellation, I don't see that
it's a killer if some environments don't have it.  But if the server
issued outgoing notifies via OOB, then we'd have a serious loss of
functionality in any environment without OOB.

            regards, tom lane

>
> > In backend/libpq/pqcomm.c, I see pg_sendoob() which sends out-of-band
> > data FROM the backend TO the client, but it is not called from anywhere.
> >
> > This could be a method of signaling that a notification was pending, and
> > sending out-of-band data FROm the client TO the backend could be used
> > for cancelling a query.
> >
> > out-of-band data causes a convenient signal to the process on the other
> > end, which can easily be used to handle these cases.
>
> Wasn't the problem with OOB data that java doesn't support this? I
> remember that OOB data has come up before on this list a long time ago,
> and that at that time some java bloke (peter?) started to complain :)

I sure don't remember this topic, or anyone complaining about OOB.

--
Bruce Momjian                          |  830 Blythe Avenue
maillist@candle.pha.pa.us              |  Drexel Hill, Pennsylvania 19026
  +  If your life is a hard drive,     |  (610) 353-9879(w)
  +  Christ can be your backup.        |  (610) 853-3000(h)

> Just a quick question: If you have an OOB packet sent to the backend, how
> would we handle the case where a row is being sent to the backend, but the
> OOB packet comes in the middle of it?
>
> It may sound like a silly question, but I'm thinking if a client is on the
> end of a slow network connection, then the packet containing the row could
> become fragmented, and the OOB packet could get in the way.
>
> Anyhow, I'm trying to find out how to implement OOB in Java. I know it's
> there, as I've seen it in the past. Just can't find it at the moment.
>

Because it is TCP/IP, the packets are re-assembled, so you can't get the
OOB inside a normal packet.  It is not like UDP.  Second, the OOB data
does not arrive in the normal data stream, but must be read by
specifiying the MSG_OOB flag to the the recv() system.  One issue raised
by Stevens' "Unix Network Programming"(p. 333) is that the OOB
signal(SIGURG) can arrive before the data is ready to be read.

I have the Stevens' book, and it will probably be required to get this
working properly.

--
Bruce Momjian                          |  830 Blythe Avenue
maillist@candle.pha.pa.us              |  Drexel Hill, Pennsylvania 19026
  +  If your life is a hard drive,     |  (610) 353-9879(w)
  +  Christ can be your backup.        |  (610) 853-3000(h)

> Bruce Momjian <maillist@candle.pha.pa.us> writes:
> > In backend/libpq/pqcomm.c, I see pg_sendoob() which sends out-of-band
> > data FROM the backend TO the client, but it is not called from anywhere.
> > This could be a method of signaling that a notification was pending, and
> > sending out-of-band data FROm the client TO the backend could be used
> > for cancelling a query.
>
> I don't see any real need to issue outgoing notifications as OOB data.
> If the client needed to interrupt current processing to handle a notify,
> then maybe it'd be useful, but how likely is that?  Keep in mind that
> the backend currently delays notify reports until end of transaction.
> I don't think we really want to change that...

Well, if you are trying to prevent from sending queries through libpq to
see if you have any notifications, how will you get notification without
an OOB-generated signal?  The notification would have to come through a
packet from the backend, and I thought you didn't want to have to deal
with that?

>
> This brings up another issue that I meant to ask about before: exactly
> what is the relation between interfaces/libpq and backend/libpq?  It
> sorta looks like they were once the same code.  Why were they allowed to
> become different, and is there any point in trying to unify them again?

interfaces/libpq is the client side, and backend/libpq is the server
side.  One sends queries, the other passes them to the backend
internals.  There has been some effort to merge code that is common to
both, but the definately don't do the same thing.


--
Bruce Momjian                          |  830 Blythe Avenue
maillist@candle.pha.pa.us              |  Drexel Hill, Pennsylvania 19026
  +  If your life is a hard drive,     |  (610) 353-9879(w)
  +  Christ can be your backup.        |  (610) 853-3000(h)

Bruce Momjian <maillist@candle.pha.pa.us> writes:
>> I don't see any real need to issue outgoing notifications as OOB data.

> Well, if you are trying to prevent from sending queries through libpq to
> see if you have any notifications, how will you get notification without
> an OOB-generated signal?  The notification would have to come through a
> packet from the backend, and I thought you didn't want to have to deal
> with that?

No, I have no problem with getting a regular packet from the backend
when the notify condition occurs.  What I don't like is creating excess
network traffic above and beyond the notification packet --- especially
not having to "poll" continuously to see whether the condition has
occurred.  But using select() to wait for something to happen does not
induce network traffic.

The only advantage of sending outgoing notifications as OOB is the fact
that a SIGURG signal gets delivered to the recipient, which could be
used to trigger abandonment of some current operation.  But I have a
hard time perceiving where a client would want that, as opposed to
detecting the notify after it completes whatever it's currently doing.

Sending cancellation requests inbound to the server is exactly what OOB
is for, because there you must interrupt current processing to get the
desired result.  Outbound notify signals are a different thing IMHO.
An SQL NOTIFY is typically going to trigger new processing in the
client, not cancel an operation in progress.

There are positive reasons *not* to force applications to handle
notifies as OOB data, primarily having to do with portability and risk
of breaking things.  For example, consider a frontend app that already
deals with OOB/SIGURG on a different input channel.  If libpq takes over
SIGURG signal handling, we break the app.  If not, we probably still
break the app, because its signal handling logic is likely expecting
SIGURG only from the other channel.

In short, inbound OOB to the server is OK because we have control of
everything that will be affected.  Outbound OOB is not OK because
we don't.

> One issue raised
> by Stevens' "Unix Network Programming"(p. 333) is that the OOB
> signal(SIGURG) can arrive before the data is ready to be read.

Right.  One advantage of using OOB only for cancel is that the SIGURG
signal itself is the interesting event; you don't really *need* to get
the OOB data to know what to do.  You can read and discard the OOB data
at any convenient point, perhaps just before trying to read normal data
from the client channel.

            regards, tom lane

Just a thought. Tom is after some scheme to receive notification messages,
out side the normal network stream, then why not add a second network
connection for this.

We could have in libpq, some new calls, to open, handle requests, and to
close the connection. It's then up to the client to handle these, so
existing clients will not be broken.

This would be a doddle to do in Java, and shouldn't be too difficult for
libpq, and libpgtcl (call backs are almost as simple to do in tcl as they
are in Java).

Just a couple of thoughts...

--
Peter T Mount  peter@retep.org.uk or petermount@earthling.net
Main Homepage: http://www.demon.co.uk/finder (moving soon to www.retep.org.uk)
************ Someday I may rebuild this signature completely ;-) ************
Work Homepage: http://www.maidstone.gov.uk Work EMail: peter@maidstone.gov.uk

Tom Lane wrote:
>
> Bruce Momjian <maillist@candle.pha.pa.us> writes:
> >> I don't see any real need to issue outgoing notifications as OOB data.
>
> > Well, if you are trying to prevent from sending queries through libpq to
> > see if you have any notifications, how will you get notification without
> > an OOB-generated signal?  The notification would have to come through a
> > packet from the backend, and I thought you didn't want to have to deal
> > with that?
>
> No, I have no problem with getting a regular packet from the backend
> when the notify condition occurs.  What I don't like is creating excess
> network traffic above and beyond the notification packet --- especially
> not having to "poll" continuously to see whether the condition has
> occurred.  But using select() to wait for something to happen does not
> induce network traffic.
>
> The only advantage of sending outgoing notifications as OOB is the fact
> that a SIGURG signal gets delivered to the recipient, which could be
> used to trigger abandonment of some current operation.  But I have a
> hard time perceiving where a client would want that, as opposed to
> detecting the notify after it completes whatever it's currently doing.
>
> Sending cancellation requests inbound to the server is exactly what OOB
> is for, because there you must interrupt current processing to get the
> desired result.  Outbound notify signals are a different thing IMHO.
> An SQL NOTIFY is typically going to trigger new processing in the
> client, not cancel an operation in progress.
>
> There are positive reasons *not* to force applications to handle
> notifies as OOB data, primarily having to do with portability and risk
> of breaking things.  For example, consider a frontend app that already
> deals with OOB/SIGURG on a different input channel.  If libpq takes over

When the Postgresql library installs its signal handler for SIGURG, it
can find out if one was already in place.  If so, it can check to see
if the SIGURG is for that other handler and the postgres handler can
call the other handler.

Ocie

Peter T Mount <psqlhack@retep.org.uk> writes:
> Just a thought. Tom is after some scheme to receive notification messages,
> out side the normal network stream, then why not add a second network
> connection for this.

*I* certainly am not after that, and I see no reason to create a second
network connection.

            regards, tom lane

ocie@paracel.com writes:
> When the Postgresql library installs its signal handler for SIGURG, it
> can find out if one was already in place.  If so, it can check to see
> if the SIGURG is for that other handler and the postgres handler can
> call the other handler.

Cool ... but what makes you think that you get to go second?  The app
could install or remove its SIGURG handler at any time.

Also, how would you tell whether the SIGURG was "for that other
handler"?  As Bruce pointed out, the signal may be delivered before any
OOB data is actually available to be read; therefore there is no way for
the signal handler to be sure whether the SIGURG came off the postgres
socket or some other one.

Basically, the Unix interface to OOB data is too brain-damaged to
be useful with more than one source of OOB data :-(.  We can usefully
use it in the backend, because we can just declare that that's all the
backend will ever use OOB input for.  But I don't think we can make
the same choice for frontend applications.

            regards, tom lane

>
> No, I have no problem with getting a regular packet from the backend
> when the notify condition occurs.  What I don't like is creating excess
> network traffic above and beyond the notification packet --- especially
> not having to "poll" continuously to see whether the condition has
> occurred.  But using select() to wait for something to happen does not
> induce network traffic.

Got it.  I guess I suspected that you would not necessarily be in a
select() call at all times.  If you are waiting for user input, or using
libpq and your app is waiting for some keyboard input, you really are
not hanging waiting for input from the backend, are you?

>
> The only advantage of sending outgoing notifications as OOB is the fact
> that a SIGURG signal gets delivered to the recipient, which could be
> used to trigger abandonment of some current operation.  But I have a
> hard time perceiving where a client would want that, as opposed to
> detecting the notify after it completes whatever it's currently doing.
>
> Sending cancellation requests inbound to the server is exactly what OOB
> is for, because there you must interrupt current processing to get the
> desired result.  Outbound notify signals are a different thing IMHO.
> An SQL NOTIFY is typically going to trigger new processing in the
> client, not cancel an operation in progress.
>
> There are positive reasons *not* to force applications to handle
> notifies as OOB data, primarily having to do with portability and risk
> of breaking things.  For example, consider a frontend app that already
> deals with OOB/SIGURG on a different input channel.  If libpq takes over
> SIGURG signal handling, we break the app.  If not, we probably still
> break the app, because its signal handling logic is likely expecting
> SIGURG only from the other channel.
>
> In short, inbound OOB to the server is OK because we have control of
> everything that will be affected.  Outbound OOB is not OK because
> we don't.
>
> > One issue raised
> > by Stevens' "Unix Network Programming"(p. 333) is that the OOB
> > signal(SIGURG) can arrive before the data is ready to be read.
>
> Right.  One advantage of using OOB only for cancel is that the SIGURG
> signal itself is the interesting event; you don't really *need* to get
> the OOB data to know what to do.  You can read and discard the OOB data
> at any convenient point, perhaps just before trying to read normal data
> from the client channel.
>
>             regards, tom lane
>


--
Bruce Momjian                          |  830 Blythe Avenue
maillist@candle.pha.pa.us              |  Drexel Hill, Pennsylvania 19026
  +  If your life is a hard drive,     |  (610) 353-9879(w)
  +  Christ can be your backup.        |  (610) 853-3000(h)

On Fri, 17 Apr 1998, Tom Lane wrote:

> Bruce Momjian <maillist@candle.pha.pa.us> writes:
> > You supply the indication to the backend, and I will see that the
> > backend processes it properly.
>
> You're on ;-)
>
> Signaling the cancel request via OOB sounds reasonable, as long as
> nothing else is using it and all the systems we care about support it.

   SSH doesn't have OOB. You can't send an OOB via SSH encrypted channel.

            Jan

 -- Gospel of Jesus is the saving power of God for all who believe --
Jan Vicherek ## To some, nothing is impossible. ##  www.ied.com/~honza
    >>>    Free Software Union President  ...  www.fslu.org    <<<
Interactive Electronic Design Inc.    -#-    PGP: finger honza@ied.com

Jan Vicherek <honza@ied.com> writes:
> On Fri, 17 Apr 1998, Tom Lane wrote:
>> Signaling the cancel request via OOB sounds reasonable, as long as
>> nothing else is using it and all the systems we care about support it.

>    SSH doesn't have OOB. You can't send an OOB via SSH encrypted channel.

I was afraid we'd run into something like that.

Well, here's how I see it: cancelling requests in progress is an
optional capability.  If it doesn't work on certain communication
channels I can live with that.  I would rather see that than see the
backend slowed down by checking for cancel requests sent as normal
data (without OOB).

A client app will actually have no way to tell whether a cancel request
has any effect; if the comm channel drops OOB requests on the floor,
it will look the same as if the cancel didn't get to the server until
after the server had finished the query.  So this shouldn't really
cause any software to fail anyway.

OTOH, I would not like to see NOTIFY broken when using an SSH channel,
so this is another reason not to try to use OOB for the outbound
direction.

            regards, tom lane

>
> On Fri, 17 Apr 1998, Tom Lane wrote:
>
> > Bruce Momjian <maillist@candle.pha.pa.us> writes:
> > > You supply the indication to the backend, and I will see that the
> > > backend processes it properly.
> >
> > You're on ;-)
> >
> > Signaling the cancel request via OOB sounds reasonable, as long as
> > nothing else is using it and all the systems we care about support it.
>
>    SSH doesn't have OOB. You can't send an OOB via SSH encrypted channel.

I have trouble buying that.  SSH is just the socket filter.  Perhaps the
OOB data is not encrypted like the normal data?

--
Bruce Momjian                          |  830 Blythe Avenue
maillist@candle.pha.pa.us              |  Drexel Hill, Pennsylvania 19026
  +  If your life is a hard drive,     |  (610) 353-9879(w)
  +  Christ can be your backup.        |  (610) 853-3000(h)

 for one, it is not encrypted,
 for two, the receiving end doesn't listen for OOB (most likely because
sending side doesn't encrypt it)
  The sending side doesn't encrypt it because OOB concept (flushing unsent
data) is incompatible with simple *single-stream* encryption. flushing
breaks the decryption of the data -- it corrupts the stream, so it becomes
unencryptable.

      Jan

On Tue, 21 Apr 1998, Bruce Momjian wrote:

> > On Fri, 17 Apr 1998, Tom Lane wrote:
> >
> > > Bruce Momjian <maillist@candle.pha.pa.us> writes:
> > > > You supply the indication to the backend, and I will see that the
> > > > backend processes it properly.
> > >
> > > You're on ;-)
> > >
> > > Signaling the cancel request via OOB sounds reasonable, as long as
> > > nothing else is using it and all the systems we care about support it.
> >
> >    SSH doesn't have OOB. You can't send an OOB via SSH encrypted channel.
>
> I have trouble buying that.  SSH is just the socket filter.  Perhaps the
> OOB data is not encrypted like the normal data?


 -- Gospel of Jesus is the saving power of God for all who believe --
Jan Vicherek ## To some, nothing is impossible. ##  www.ied.com/~honza
    >>>    Free Software Union President  ...  www.fslu.org    <<<
Interactive Electronic Design Inc.    -#-    PGP: finger honza@ied.com

Tom Lane wrote:
>
> Jan Vicherek <honza@ied.com> writes:
> >    SSH doesn't have OOB. You can't send an OOB via SSH encrypted channel.
>
> I was afraid we'd run into something like that.
>
> Well, here's how I see it: cancelling requests in progress is an
> optional capability.  If it doesn't work on certain communication
> channels I can live with that.  I would rather see that than see the
> backend slowed down by checking for cancel requests sent as normal
> data (without OOB).
>
> A client app will actually have no way to tell whether a cancel request
> has any effect; if the comm channel drops OOB requests on the floor,
> it will look the same as if the cancel didn't get to the server until
> after the server had finished the query.  So this shouldn't really
> cause any software to fail anyway.
>
> OTOH, I would not like to see NOTIFY broken when using an SSH channel,
> so this is another reason not to try to use OOB for the outbound
> direction.
>

We could use some kind of threaded model,
with the main thread running the current execution path
and a minimal thread just "select:ing on the socket".
A 2 process model would be most portable,
a pthread solution would be cleaner.

NOTIFY/CANCEL could be an option for modern
(in this case, pthreading) systems only.

    regards,
--
---------------------------------------------
Göran Thyni, sysadm, JMS Bildbasen, Kiruna

> > Bruce Momjian <maillist@candle.pha.pa.us> writes:
> > > This all looks good.  Another thing we really need it to be able to
> > > cancel queries.  This would be a big win, and looks like it could fit
> > > into the scheme here.
> >
> > I thought about proposing a PQcancelAsync that would cancel the active
> > query-in-progress.  But that would require support on the backend side,
> > and I am far from competent to make it happen.  (libpq is simple enough
> > that I'm not afraid to rewrite it, but making major mods to the backend
> > is another story.  I just got here this week...)
> >
> > If anyone who does know what they're doing is willing to make the
> > necessary backend mods, I'm all for it.  The libpq side would be
> > easy enough.
> >
> > How would such cancellation interact with transactions, btw?  Would
> > you expect it to roll back only the current command, or abort the
> > whole transaction?  We'd also have to consider corner cases, like
> > when the backend has already finished the query by the time it gets
> > the cancel request.
>
> It is pretty easy, just an elog(ERROR) would do it.  The issue is
> allowing the backend to see the request.  We can put some checks in
> tcop/postgres.c as it moves from module to module, and something in the
> executor to check for the cancel, and do an elog(ERROR).  It would be
> nice if it arrived as out-of-band data, so we could check for input
> quickly without having to actually process it if it is not a cancel
> notification.
>
> The out-of-band data will send a SIGURG signal to the backend, and we
> can set a global variable, and check the variable at various places.
>
> To do all this, we need to be able to send a query, and not have it
> block, and it seems you are giving us this capability.
>
> You supply the indication to the backend, and I will see that the
> backend processes it properly.

Old news I know, but I was saving it to follow up and then ...

I agree completely with Bruces proposal for handling this in the back-end.
I have recently done something very similar for another database product.

The important points are:

 - the signal handler can only set a single global variable. No other action
   is to be done in the handler.

 - the variable is to be tested only at well defined times where the recovery
   from an elog() can be handled correctly. It is nice if this test is
   "frequent, but not too frequent". At scan begin time is fairly good, and
   for large scans perhaps every few pages. Every row is too often. When
   stepping to a new plan node is also good.

 - There should be a further global flag to disable recognition of the
   cancel. This is used for example during an in progress elog() -> cleanup
   sequence. The cleanup code is not really reentrant so an elog() in the
   middle of an elog is likely to leave an inconsistant state.

-dg

David Gould            dg@illustra.com           510.628.3783 or 510.305.9468
Informix Software  (No, really)         300 Lakeside Drive  Oakland, CA 94612
"Of course, someone who knows more about this will correct me if I'm wrong,
 and someone who knows less will correct me if I'm right."
               --David Palmer (palmer@tybalt.caltech.edu)