Re: Storing images as BYTEA or large objects

Koen Vermeer wrote:
> Hi,
>
> I would like to store binary data in a PostgreSQL database. The size of
> the data is about 2 to 20 MB and is always stored or retrieved as a
> block (i.e., I do not need to get only part of the data). As I
> understand, I have two options for storing this data: As BYTEA or as
> large objects. As I understand, the disadvantage of the first method is
> that I need to write a custom routine to escape some binary values - or
> is there some code available to do this? The disadvantage of large
> objects is their existence outside of the main database, which may be
> problematic when backing up a database. In addition, I need special
> routines to store/retrieve the data.
>
I've used both methods. The only real problem is that none of the
trigger based replication schemes
such as Slony can't deal with large objects.

Depending on what programming language you're using you do *not* need to
escape the binary
data for BYTEA. Using libpq from C/C++ you can pass the binary data
straight into the database. See
PQexecParams :

http://www.postgresql.org/docs/8.3/interactive/libpq-exec.html#LIBPQ-EXEC-MAIN

We use server-side JavaScript here that makes use of that interface so I
can take an image directly
from the web-server and move it into the database with no escape overhead.

My preference : if I don't need the file-like interface to large objects
I'd use BYTEA every time.

Pete
--
http://www.whitebeam.org - JavaScript web application server.
http://www.yellowhawk.co.uk
> My two questions are: Is this summary correct? And: Which method should
> I choose?
>
> Best,
> Koen
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
> http://archives.postgresql.org/
>
>