postgresql and binary data

Do you have a question? Post it now! No Registration Necessary.  Now with pictures!

Threaded View
I have some postgresql database that stores binary data (windows/linux
executables). Field type I use for this is bytea.
Also I have php script that allows users download these binaries via
their web browser.
That stuff was running on ubuntu 7.10 with postgresql 8.2 and most
recent php version.

That was written like this:

$db_con = pg_connect(...);
/*determine file name (rg_target) and file
    size (l) with postgres' octet_length*/
header('Content-type: application/octet-stream');
/*header("Content-length: $l");*/
header("Content-disposition: attachment; filename=$rg_target");
$db_res = pg_query(..)
    or die('Error executing query: ' . pg_last_error());
$rg_binary = pg_fetch_result($db_res, 'rg_binary');
echo pg_unescape_bytea($rg_binary);

and that worked (with commented content-length) in ff and ie.

Then I've upgraded to ubuntu 8.04, postgres 8.3 and this code became
broken: when downloading file, only first ~175 kbytes get downloaded.
File in database, say, 4MB and user can download only first 175 kbyte

Re: postgresql and binary data

Quoted text here. Click to load it

Maybe you can run the query without using PHP, i.e. using psql. This
way, you can check whether this is a PHP issue or a Postgres issue.

Re: postgresql and binary data

Sjoerd wrote:
Quoted text here. Click to load it

Sure I can. That was clearly php issue but I didn't know which one.
Trying to figure out data length I found out that script execution
aborts due to exceeding allowed memory limit. In ubuntu 8.10 they set it
to 16 mbytes (looks like in 7.10 it was bigger).

So I set it to 100 mbytes and that works!


Re: postgresql and binary data

Quoted text here. Click to load it

My prior experience has tended to find that storing BLOBs in the
database itself is often problematic, and that the filesystem itself
is the best place to store data such as binaries.  Whenever I need a
database to manipulate binary data I store the binary itself to a file
on disk and just store the filename in the database itself.

If the reason you're storing binaries in the database is to prevent
unauthorized downloads then may I suggest that instead you store the
files to a directory that's outside your web tree (PHP can write
anywhere in the filesystem that the user your web server is running
under has write permission for) and use readfile() or
file_get_contents() to download it to the user?

Re: postgresql and binary data

Gordon wrote:
Quoted text here. Click to load it

Reason is I upload files via python script and it could be run on win32
or unix os. I.e. I searched for portable solution to execute some
scripting language that could execute some commands and transfer files
to another computer and one I found was python and RDBMS on distant

I agree that storing binary data in database is brittle and error-prone

Site Timeline