Re: [HACKERS] I want to change libpq and libpgtcl for better handling of large query results

>
> The Hermit Hacker wrote:
> >
> > > What I would ask from you?
> > > 1) First of all, if my needs could be satisfied in other way with
> > > current functions in libpq of libpgtcl. I can assure you that with
> > > current libpgtcl is rather impossible. I am not sure if there is another
> > > mechanism using some subtle functions that I didn't know about them.
> >
> >         Bruce answered this one by asking about cursors...
>
> Yes. It's true. I have used cursors for speeding up opening tables in
> PgAccess fetching only the first 200 records from the table.
> But for a 10 thousand record table I will send over the network 10
> thousand "FETCH 1 IN CURSOR" because in a report table I am processing
> records one by one.
> The time for this kind of retrieval would be more than twice as in the
> 'callback' mechanism.

You can tell fetch to give you as many records as you want, so you can
read in 100-tuple blocks.

>
> If you think that is better to keep libpq and libpgtcl as they are, then
> I will use cursors.
> But using the 'callback' method it would increase performance.
>
> I am waiting for the final resolution :-)
>
> >         Basically, by "cloning", you are effectively looking at implementing ftp's
> > way of dealing with a connection, having one "control" channel, and one "data"
> > channel, is this right?  So that the "frontend" has a means of sending a STOP
> > command to the backend even while the backend is still sending the frontend
> > the data?
>
> Not exactly. Looking from Tcl/Tk point of view, the mechanism is
> transparent. I am using this structure :
>
> pg_loop $database "select * from sometable" record {
>     set something $record(somefield)
> }
>
> But the new libpgtcl is opening a 'cloned' connection in order to :
> - send the query through it
> - receive the data from it
> I am not able to break the connection using commands send through the
> 'original' one. The query is 'stopped' by breaking the connection.
> That's why we needed another connection. Because there isn't (yet) a
> mechanism to tell the backend to abort transmission of the rest of the
> query. I understand that the backend is not reading any more the socket
> in order to receive some CANCEL signal from the frontend. So, dropping
> the rest of the query results isn't possible without a hard break of the
> connection.

We have this on the TODO list.  We could use the TCP/IP out-of-band
connection option to inform the backend to stop things, but no one has
implemented it yet.  (For the new Unix domain sockets, we could use
signals.)  Anyone want to tackle it?

man send shows:

     The flags parameter may include one or more of the following:

     #define MSG_OOB        0x1  /* process out-of-band data */
     #define MSG_DONTROUTE  0x4  /* bypass routing, use direct interface */

     The flag MSG_OOB is used to send ``out-of-band'' data on sockets that
     support this notion (e.g.  SOCK_STREAM); the underlying protocol must al-
     so support ``out-of-band'' data.  MSG_DONTROUTE is usually used only by
     diagnostic or routing programs.


--
Bruce Momjian
maillist@candle.pha.pa.us