Re: [GENERAL] 8k limit - Mailing list pgsql-general

On Wed, 28 Oct 1998, Herouth Maoz wrote:

    This is actually how I store the images in my photo album
(http://bleu.west.spy.net/~dustin/photo/)  I Base64 encode all of the
image data, then extract it with something like this:

    declare c cursor for select * from image_store where id=
              (select id from image_map where name='$img')
            order by line

    I decode it on the fly, and cache it so's that it doesn't have to
do that every time.  The speed isn't too bad when you index it right, it
just ends up taking some extra storage.  One problem I do have, though,
which is a bug in Postgres, is that sometimes it loses control of an Index
or something like that and I get all kinds of errors and it tells all the
connected clients to go away, so I get broken images.  Here's an example
of some of the errors:

Index images_id is not a btree
Index pg_class_oid_index is not a btree
OperatorObjectIdFillScanKeyEntry: unknown operator 676
SearchSysCache: Called while cache disabled
Index image_map_name is not a btree
fmgr_info: function 1043: cache lookup failed
Named portals may only be used in begin/end transaction blocks
FATAL 1:  SetUserId: user "nobody" is not in "pg_shadow"

    That's just a grep through my error logs.  Most of those are
incorrect, and happen sporadically throughout the day.  I'm hoping this is
fixed in 6.4...

# Here is an idea to solve the text problem.
#
# Create a table for the bodies of the messages. Something like
#
# CREATE TABLE bodies (
#     mesg_id int4,
#     chunk_no int4,
#     chunk    text );
#
# In the application, divide your text into chunks of, say, 6k. Suppose your
# application is written in perl, you'd have an array of chunks @chunks
#
# Then you write the headers of your message to the main mail table (the one
# which has the headers). The message gets an ID from a sequence. You get the
# value of that sequence.
#
# Then you loop over the @chunks array, in each iteration inserting into the
# bodies table the given message id, the loop iterator (as chunk_no), and the
# content of the $chunks[$i] itself.
#
# Then, in your app, if you want to reconstruct a message, you just retrieve
# its headers (including mesg_id). Then you:
#
# SELECT chunk_no, chunk
# FROM bodies
# WHERE mesg_id = <your message>
# ORDER BY chunk_no;
#
# Ignore the chunk_no in the returned set, and just concatenate all the
# returned chunks in order. Then you have the body.
#
# Advantage of this method: Unlike the large-objects interface,
#
# (a) The text will be visible in psql in case you need to fix something.
# (b) You will be able to do some rough searching in the form
#
#     SELECT DISTINCT mesg_id
#     FROM bodies
#     WHERE chunk like '%something%';
#
# (c) pg_dump will dump the table, so you needn't come up with an elaborate
#     backup scheme of your own (pg_dump DOES NOT backup LOBs).
#
# Herouth
#
# --
# Herouth Maoz, Internet developer.
# Open University of Israel - Telem project
# http://telem.openu.ac.il/~herutma
#
#
#
#

--
SA, beyond.com           My girlfriend asked me which one I like better.
pub  1024/3CAE01D5 1994/11/03 Dustin Sallings <dustin@spy.net>
|    Key fingerprint =  87 02 57 08 02 D0 DA D6  C8 0F 3E 65 51 98 D8 BE
L_______________________ I hope the answer won't upset her. ____________