Thread: Using TEXT columns for binary content

Hello,

I work with Postgresql 7.2 using Python and Zope as frontend. I have to deal
with some binary content (just pictures) so I considered the options: BLOB and
BYTEA.

BLOB doesn't works for me (because I have no control on the web hosting
environment).

BYTEA seems to be very slow. So far I know, bytea implies tree steps of
processing: string-literal parser, bytea input function and my own encoding
function (of course, a very lazy one...).

I tried a third way: to use simple TEXT columns for storing the pictures. Using
Python library functions for base64 encoding and decoding everything works fine
(and very fast).

My question is:

There are some hidden drawbacks for this approach? (I don't need any further
processing on the binary content)

Thanks for your advice,

Mircea

Mircea Sarbu wrote:
> There are some hidden drawbacks for this approach? (I don't need any further
> processing on the binary content)
>

There are people who have reported using this method successfully on the
Postgres lists. Only downsides I can think of are:

1) You have to encode to base64 on the way in (bytea has to be escaped
    on INSERT/UPDATE too)
2) You have to decode to binary on the way out (depending on your
    middleware, you can use a binary cursor for bytea and avoid the
    decode on SELECT)
3) The on disk representation is about 33% bigger compared to bytea
4) TEXT datatype is subject to being multibyte encoded -- I don't think
    this affects the characters in the base64 set, but I'm not sure there
    won't be any undesireable interactions. If you are using other than
    'C' locale, test carefully.

Joe

On Monday 21 April 2003 12:01, Joe Conway wrote:
> Mircea Sarbu wrote:
> > There are some hidden drawbacks for this approach? (I don't need any
> > further processing on the binary content)

> There are people who have reported using this method successfully on the
> Postgres lists. Only downsides I can think of are:

> 1) You have to encode to base64 on the way in (bytea has to be escaped
>     on INSERT/UPDATE too)

OpenACS uses this technique.  The PostgreSQL driver for AOLserver (which
OpenACS uses) has code -- aolserver.sourceforge.net -- files, nspostgres.
GPL/APL dual-licensed, if that matters.
--
Lamar Owen
WGCR Internet Radio
1 Peter 4:11

Joe Conway <mail@joeconway.com> writes:
> 1) You have to encode to base64 on the way in (bytea has to be escaped
>     on INSERT/UPDATE too)
> 2) You have to decode to binary on the way out (depending on your
>     middleware, you can use a binary cursor for bytea and avoid the
>     decode on SELECT)

Note that the 7.4 protocol should make it a lot easier to use bytea for
binary data without any escaping ... although some amount of client
reprogramming would be needed ...

            regards, tom lane

Tom Lane wrote:
> Joe Conway <mail@joeconway.com> writes:
>
>>1) You have to encode to base64 on the way in (bytea has to be escaped
>>    on INSERT/UPDATE too)
>>2) You have to decode to binary on the way out (depending on your
>>    middleware, you can use a binary cursor for bytea and avoid the
>>    decode on SELECT)
>
> Note that the 7.4 protocol should make it a lot easier to use bytea for
> binary data without any escaping ... although some amount of client
> reprogramming would be needed ...
>

Yeah -- client library support (e.g. PHP, Python, etc) will probably lag
7.4's release by a bit, but I expect bytea will become the most sensible
option once everything is in place. Even now, I find bytea use for small
images pretty quick and easy (with PHP at least).

Joe

Good day to all readers:

Where I could find the postgresql log. My postgresql doesn't start and I'm
looking for a reason.

Thanks in advance
Enediel
Linux user 300141

Happy who can penetrate the secret causes of the things
¡Use Linux!

"enediel" <enediel@com.ith.tur.cu> writes:
> Where I could find the postgresql log. My postgresql doesn't start and I'm
> looking for a reason.

Read the startup script and see where it sends the postmaster's stderr.

Alternatively, adjust postgresql.conf to log to syslog, and then you can
look in the standard system syslog file.

            regards, tom lane