From:	Jeremy	Date:	Tue Jul  8 21:20:35 2008
Subject:	LOBs in pgsql

PostgreSQL has two different ways of dealing with LOBs: the "distant" 
way, and the column way.  The "distant" way means that all LOBs are off 
on their own and are referred to only by oids.  The column way means 
that there is a table with a column of type bytea, and the column's data 
for each row is something of a LOB.

pdo_pgsql is equipped to handle both of these, but not equivalently. 
The OID method works well, using streams and whatnot.  The bytea method 
does not work well at all, especially for very large data (which is the 
reason to use a LOB in the first place).

When using the bytea type, if I use code like this to get a "stream" 
that reads from the LOB:

$stmt = $db->prepare("select data from large_things where id=5");
$stmt->bindColumn(1, $data, PDO::PARAM_LOB);
$stmt->execute();
$stmt->fetch(PDO::FETCH_BOUND);

it is prone to running out of memory if the size of the LOB is greater 
than memory_limit (even though I have not strictly *read* anything from 
the LOB yet).

Looking through the pdo_pgsql code, I can see why this is:  it makes a 
decision about LOB handling based on whether you are using the "distant" 
(or oid) method, or the bytea column method.  If you are using the 
former, it uses the appropriate functions to open a stream resource to 
the object and all is good.  However, if you are using the latter, it 
tries to allocate memory for the entire lob and unescape it all at once. 
  Obviously, this fails miserably in my case, or I wouldn't be posting 
here :-)

I could increase the memory_limit to be larger than a LOB will ever be. 
  But what if this were gigabytes?  Am I tied to the "distant" method, 
which is not particularly relational and ignores all concept of 
permissions?  I feel like the behavior should be able to be overridden 
in the driver options or some such, but there is obviously no way to do 
that.

The true LOB (using the distant method) functions use the lo_* functions 
from libpq behind the scenes, so obviously this is not possible with 
bytea fields.  However, there must be some way to use a buffered reader 
to avoid over-allocation here.

Am I wrong?

Thanks,
Jeremy