[dev] TEXT vs. BLOB in postgres for VFS?

[Jan Schneider]

Zitat von Michael M Slusarz <slusarz at horde.org>:

> A client just brought this to my attention: using VFS storage for  
> IMP attachments with Postgresql results in massive memory usage that  
> quickly exhausts available memory.  A 7 MB attachment is causing  
> 150+ MB to be allocated.  Tracing the code, everything looks good  
> until VFS_sql::_writeBlob().  The code uses only ~12 MB until the  
> bin2hex() call to convert binary -> text for postgres on the data.   
> This causes memory to balloon to 26 MB+.  Then PEAR DB does a  
> fantastic job of just murdering memory usage after that (I have no  
> idea what it is doing).
>
> But I don't see any reason why we need to use a TEXT field here.   
> Postgres supports a BLOB field, and that field would remove the need  
> for the bin2hex() call (and the resulting pack() call when  
> unpacking).  Can't find anything in the archives why this choice was  
> made.  Since I'm not the DB expert here, can anyone else clue me in?  
>  Or is this a relic from the days when postgres lacked proper BLOB  
> support?

Some forensic CVS history analysis showed, that the bin2hex() call for  
PostgreSQL had been added when the blob methods() were moved from VFS  
to Horde_SQL (only to be later copied back to VFS).
But it was obviously necessary, due to this, even earlier, commit:
http://cvs.horde.org/diff.php/horde/scripts/db/Attic/vfs_pgsql.sql?sa=1&r1=1.1&r2=1.2
Why TEXT was chosen over LONGBLOB eludes me though.

I can't say anything about historic blob support in PostgreSQL itself  
though. but the most resource effective solution would probably be to  
use the pg_lo PHP functions.

This would require some flag though, since the data is incompatible.

Jan.

-- 
Do you need professional PHP or Horde consulting?
http://horde.org/consulting/