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