Re: Storing Binary Large Objects

On 3/29/07, Mark Wong <markwkm(at)gmail(dot)com> wrote:
>
> On paper, using bytea looks more straightforward the oid datatype.
> Does bytea have its own difficulties, other than having to find a way
> to escape the data?
>

The downside of bytea is that it doesn't have file-like random-access
functions. You can only read or write the whole value. The advantage
is that it uses normal SQL commands with wider driver support while
large objects require a special client API. The bytea value has a
size limit of 1 GB; large objects have a limit of 2 GB.

Large objects used to have limitations in being backed up and restored
but I think those have been fixed. They are now stored in a table,
pg_largeobjects, and broken up into chunks. Normal tables have a
toast table mechanism where large values are broken into chunks and
stored in a second toast table.

The database driver should handle the escaping for bytea columns. For
example, DBD::Pg needs to be told the type:

$sth->bind_param($param_num, $bind_value, { pg_type => DBD::Pg::PG_BYTEA });

- Ian