Large object insert/update and oid use - Mailing list pgsql-general

Hey folks.  I'm new to the list, and not quite what you'd call a DB
Guru, so please be patient with me.  I'm afraid the lead up here is a
bit verbose . . .

I am working on an application that uses very high volume DB
transactions - in the order of tens of millions per day . . .

Anyway, the current database which will remain nameless, but begins
with O and rymes with debacle (sorta), has a problem with high volume
work when it comes to binary large objects and rowid use (or oid use
as I understand Postgres uses).

Here's the problem:

When a record containing a number of basic types (int, varchar(2048),
char(32), etc.) as well as a large object is updated with a LOB, we
used to use rowids returned in the update of the other fields.  The
rowid was then the condition used when selecting the locator for the
large object.

Unfortunately, when a rowid is returned, it is always the location of
the current location, but if the data in question  won't fit in the
current location, it is shifted to another rowid - but the application
can't really get notification of this.  Later, when the object is
inserted using that rowid, it gets put in the wrong place.  The result
is that sometimes, a request for one object will yield the wrong
object.

Of course, there are ways to simply update the whole record, object
and all, but there are bugs in the database version we are using that
causes the temp tablespace to fill up and not be reused when this
method is used, so we've had to change some of this stuff around to
use the key field as the condition, and selecting the locator after
the initial creation of the empty object.  The point is that the
direct updates of large objects have problems, as do the use of rowids
in high volume conditions.  Regardless, we've had to implement some
verification checks to ensure the objects don't get swapped - this is
done by prefixing the object with a 32 bit MD5 hash that is also
stored in a separate field of the same record.

I've been looking at the Postgres docs, and I've found the lo_*()
routines, which appear to use OIDs to write to the object.  Is there
any 'shifting' of records during insert because of space availability?
And, probably more important, is there a way to do a direct update of
a large object along with the other fields in a record - without
having to call the lo_creat() and lo_write() functions?  I've done
some searching in the archives, but can't find anything that appears
to answer the question.

If I've been unclear on any of this, I'm sorry, feel free to prod for
more info.

Thanks in advance

Lou
--
Louis LeBlanc               leblanc@keyslapper.org
Fully Funded Hobbyist, KeySlapper Extrordinaire :)
http://www.keyslapper.org                     Ô¿Ô¬

Scientists are people who build the Brooklyn Bridge and then buy it.
    -- William Buckley