Mime-Version: 1.0 (Apple Message framework v746.2)
Content-Transfer-Encoding: 7bit
Message-Id: <06AA7287-656A-4E49-B1B5-68ACCC03741B@bignerdranch.com>
Content-Type: text/plain; charset=US-ASCII; delsp=yes; format=flowed
To: pgsql-interfaces@postgresql.org
From: Chris Campbell <chris@bignerdranch.com>
Subject: Sending messages inside a function
Date: Tue, 29 Nov 2005 10:44:11 -0500

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