Обсуждение: bytea

when bytea, text, and varchar(no limit entered) columns are used, do
they ALWAYS use an extra table/file? Or do they only do it after a
certain size of input?

Also, if I wanted to put a *.pdf file in a bytea column, what functions
do I use to escape any characters in it?

I forgot, please CC me, I am on digest.
Dennis Gearon wrote:

> when bytea, text, and varchar(no limit entered) columns are used, do
> they ALWAYS use an extra table/file? Or do they only do it after a
> certain size of input?
>
> Also, if I wanted to put a *.pdf file in a bytea column, what
> functions do I use to escape any characters in it?
>

> Also, if I wanted to put a *.pdf file in a bytea column, what functions
> do I use to escape any characters in it?

What programming language are you using?

In Perl, you do something like:

$sth->bind_param(1, $file_data, DBI::SQL_BINARY); #DBI::SQL_BINARY is
deprecated, but it works

In php you do:

$file_data = pg_escape_bytea($file_data);

$db->query("insert into blah(blahh) values ('${file_data}'::bytea);

To retrieve the info in Perl, it's just a regular fetchrow()

my ($file_data) = $sth->fetchrow();

In php, you have to run stripcslashes() on the data.

list($file_data) = $query->fetchrow();
$file_data = stripcslashes($file_data);

Jon


>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>

On Tue, May 11, 2004 at 09:30:15AM -0700, Jonathan Bartlett wrote:
> > Also, if I wanted to put a *.pdf file in a bytea column, what functions
> > do I use to escape any characters in it?
>
> What programming language are you using?

Apparently if you are using C and libpq, you can use the version 3
protocol functions to send binary data without having to escape it.  I
haven't done it though so I may be wrong, but if I'm right please
somebody tell me because I usually recommend this :-)

Also if it works it would be useful to update the Perl/PHP/etc
interfaces to use it.

--
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
"El sudor es la mejor cura para un pensamiento enfermo" (Bardia)

Thanks for all the answers everybody, but I need to know also an answer
to the other question:

Does the bytea make its own files automatically for large objects?

Also, how about backups with tables having bytea columns.?

Jonathan Bartlett wrote:

>>Also, if I wanted to put a *.pdf file in a bytea column, what functions
>>do I use to escape any characters in it?
>>
>>
>
>What programming language are you using?
>
>In Perl, you do something like:
>
>$sth->bind_param(1, $file_data, DBI::SQL_BINARY); #DBI::SQL_BINARY is
>deprecated, but it works
>
>In php you do:
>
>$file_data = pg_escape_bytea($file_data);
>
>$db->query("insert into blah(blahh) values ('${file_data}'::bytea);
>
>To retrieve the info in Perl, it's just a regular fetchrow()
>
>my ($file_data) = $sth->fetchrow();
>
>In php, you have to run stripcslashes() on the data.
>
>list($file_data) = $query->fetchrow();
>$file_data = stripcslashes($file_data);
>
>Jon
>
>
>
>
>>---------------------------(end of broadcast)---------------------------
>>TIP 4: Don't 'kill -9' the postmaster
>>
>>
>>
>
>
>

On Tue, 11 May 2004, Dennis Gearon wrote:

> Thanks for all the answers everybody, but I need to know also an answer
> to the other question:
>
> Does the bytea make its own files automatically for large objects?

Bytea doesn't use large objects, which are kind of an artifact left over
from the days of the 8k row limit.  They use what are called "toast"
tables.  Toast tables allow for text/varchar/bytea types to overflow from
the base table as needed to occupy up to ~2 gigabytes of drive space per
field max.  Note that I'm pretty sure no one's really tried to put 2 gig
in one field, as that would probably take quite some time, and I'm not
sure how well most clients are gonna handle getting back a row with a 2
gig field in it. :-)

And yes, toasting is fully automatic.  Just insert a large
text/varchar/bytea field and the database does the rest.  which is why
they are generally recommended over using large objects, which require
specialized handling.

> Also, how about backups with tables having bytea columns.?

Just like any other field.  pg_dump will escape them as needed to make a
usable backup.

On Tue, May 11, 2004 at 11:50:06AM -0700, Dennis Gearon wrote:
> Thanks for all the answers everybody, but I need to know also an answer
> to the other question:
>
> Does the bytea make its own files automatically for large objects?

No, they are stored in tables.

> Also, how about backups with tables having bytea columns.?

What about them?  They should work just fine.

--
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
"La vida es para el que se aventura"

> Does the bytea make its own files automatically for large objects?

I do not believe so.  You'd have to check with someone else for sure,
though.

> Also, how about backups with tables having bytea columns.?

Regular pg_dump handles bytea columns just fine.

Jon

scott.marlowe wrote:
> And yes, toasting is fully automatic.  Just insert a large
> text/varchar/bytea field and the database does the rest.  which is why
> they are generally recommended over using large objects, which require
> specialized handling.

They are not always recommended though. The manual clearly states that
bytea/text's drawback is huge memory requirement (esp. for large
data/long text) and lack of chunk processing. Hopefully this will be
addressed in the future.

--
dave