Horton

Thread

  1. bytea

    Dennis Gearon <gearond@fireserve.net> — 2004-05-11T16:06:35Z

    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?

  2. Re: bytea

    Dennis Gearon <gearond@fireserve.net> — 2004-05-11T16:18:28Z

    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?
>

  3. Re: bytea

    Jonathan Bartlett <johnnyb@eskimo.com> — 2004-05-11T16:30:15Z

    > 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
>

  4. Re: bytea

    Alvaro Herrera <alvherre@dcc.uchile.cl> — 2004-05-11T18:07:46Z

    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)

  5. Re: bytea

    Dennis Gearon <gearond@fireserve.net> — 2004-05-11T18:50:06Z

    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
>>
>>    
>>
>
>  
>

  6. Re: bytea

    scott.marlowe <scott.marlowe@ihs.com> — 2004-05-11T20:04:01Z

    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.

  7. Re: bytea

    Alvaro Herrera <alvherre@dcc.uchile.cl> — 2004-05-11T20:49:12Z

    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"

  8. Re: bytea

    Jonathan Bartlett <johnnyb@eskimo.com> — 2004-05-11T21:06:51Z

    > 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

  9. Re: bytea

    David Garamond <lists@zara.6.isreserved.com> — 2004-05-12T15:28:03Z

    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