Sending messages inside a function - Mailing list pgsql-interfaces
From | Chris Campbell |
---|---|
Subject | Sending messages inside a function |
Date | |
Msg-id | 06AA7287-656A-4E49-B1B5-68ACCC03741B@bignerdranch.com Whole thread Raw |
Responses |
Re: Sending messages inside a function
(Tom Lane <tgl@sss.pgh.pa.us>)
|
List | pgsql-interfaces |
I'm trying to write a set of C functions that mimic the COPY TO and COPY FROM SQL commands so I can use them to stream binary data to/ from the client. This seems more efficient to me than passing BYTEAs back and forth. Maybe that's the best way to go, but the COPY mechanism seems perfect for this. Just look how straightforward the send_to_client() function was to write (below). I've been using large objects to accomplish this for the past few years, but as customers' databases get larger and larger (the bulk being entirely large objects), backing up and restoring and recovering from corruption is taking a really long time. So I'm going to store the data elsewhere on the server (outside the database) and piggy-back the PostgreSQL connection to send the data back and forth. This way, the database stays much smaller and makes backup and restore much easier. I'm envisioning something like this: (1) Data transfer from Server -> Client --------------------------------------- Client: SELECT send_to_client() Server: Sends an 'H' message to begin a COPY TO Server: Sends a bunch of 'd' messages with the data Server: Sends a 'c' message to end the COPY operation Client: Gets PGRES_COPY_OUT from PQresultStatus() Client: Calls PQgetCopyData() to retrieve data until it returns -1 Client: Calls PQgetResult() to retrieve the results of the SELECT query (2) Data transfer from Client -> Server --------------------------------------- Client: SELECT receive_from_client() Server: Sends a 'G' message to begin a COPY FROM Client: Gets PGRES_COPY_IN from PQresultStatus() Client: Calls PQputCopyData() to send the data Server: Receives a bunch of 'd' messages Server: Receives a 'c' message to end the COPY operation Client: Calls PQgetResult() to retrieve the results of the SELECT query I've gotten #1 working. But the client isn't able to retrieve the SELECT results from calling the send_to_client() function (which really doesn't bother me too much -- the send_to_client() function returns NULL). Instead, I just call PQgetResult() until it returns NULL and ignore all results. However, my receive_from_client() function will need to return a status, so I'd like to get this figured out for send_to_client() before moving on to receive_from_client(). I'm thinking that the server sends a tuple description message ('T') before my function is invoked (and sends all the 'H', 'd', and 'c' messages) and then sends the tuple data message ('D') with the function's return value afterwards. So, by spitting out messages inside my function, I'm disconnecting the tuple data from the descriptions. I get the following error message: server sent data ("D" message) without prior row description ("T" message) I could remedy this by "faking" my own 'T' message after I've performed the COPY operation (to match the 'D' message that's sent later). But that seems ugly. If the 'T' message wasn't sent until after my function was invoked, that would solve all my problems (if my assumption about the 'T' message being sent before the function's invoked is correct). I'm trying to find where in the code the 'T' message is sent and try to delay it until after the function is actually invoked, but I'm not having much success. Any comments or pointers? Here's what my send_to_client() function looks like: Datum send_to_client(PG_FUNCTION_ARGS) { // Begin a COPY TO STDOUT operation StringInfoData buf; pq_beginmessage(&buf, 'H'); pq_sendbyte(&buf, 1); // binary format (1) pq_sendint(&buf, 0, 2); // 0 attributes pq_endmessage(&buf); // TODO: Send the data to the client // For now, just send a simple binary message char *buffer = "Hello, world!"; pq_putmessage('d', buffer, strlen(buffer)); // End the COPY operation pq_putemptymessage('c'); PG_RETURN_NULL(); } I've called the function from my libpq-based program ("SELECT send_to_client()") and then retrieved the data using PQgetCopyData() and it works, as long as I don't care about the results of the SELECT query and just throw away any PGresults I get after the COPY operation. But that's going to cause me problems when I implement receive_from_client(). Thanks! - Chris
pgsql-interfaces by date: