Skip site navigation (1) Skip section navigation (2)

Re: Storing Binary Large Objects

From: "Ian Burrell" <ianburrell(at)gmail(dot)com>
To: pdxpug(at)postgresql(dot)org
Subject: Re: Storing Binary Large Objects
Date: 2007-03-29 17:37:13
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pdxpug
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

In response to

pdxpug by date

Next:From: David E. WheelerDate: 2007-03-29 18:00:25
Subject: Re: Storing Binary Large Objects
Previous:From: Mark WongDate: 2007-03-29 15:35:02
Subject: Re: Storing Binary Large Objects

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group