Re: Re: Large Objects

> currently PG stores each BLOB as an independent table --- and just to
> add insult to injury, creates an index for it :-(. So you actually have
> *two* added files in the DB directory per BLOB. Needless to say, this
> does not scale real well to large numbers of BLOBs.
>
> Denis Perchine has done the legwork to store BLOBs in a more reasonable
> fashion, ie all together in one big (but indexed!) table. His patch
> needs review by a core person before we'll consider it safe to commit,
> but I have that as a personal "must do for 7.1". So those MySQL results
> should also apply to Postgres in 7.1 and later.

I was experimenting with the patches in question, after applying
them to postgres-7.0.2. I'm encountering a nasty problem when
inserting my own huge (several megabyte) files into the relation,
that I can't quite figure out.

Apparently, what's going on is that the inserts/updates into the
pg_largeobject table result in buffers getting leaked (as
reported by BufferPoolCheckLeak()). Eventually, importing
a file results in exhausting the block free list. I think
there were a couple of places where a ReleaseBuffer was
needed, but not called...i've modified his code for inv_write_existing
to look more like:

------------
buffer = InvalidBuffer;
while ( index_getnext(...) ) {
if ( buffer != InvalidBuffer ) {
ReleaseBuffer( buffer );
buffer = InvalidBuffer;
}
heap_fetch( ..., &buffer );
index_endscan(...);

{
/* build a block of data */
...
ntup = heap_modifytuple( ... );
heap_update( ... );
heap_freetuple( ntup );

}
if ( buffer != InvalidBuffer ) {
WriteBuffer( buffer );
}
------------

The Leak-check seems to indicate that the pages that are getting
leaked are all dirty pages, which seems slightly odd to me...if
I call it at the end of this loop function (which gets called over
and over again during an import), I end up getting something that
looks like this:
-------
lockNum=56, flags=0x85, refcount=1 1)
NOTICE: Buffer Leak: [058] (freeNext=-3, freePrev=-3, relname=pg_largeobject, blockNum=9, flags=0x85, refcount=1 1)
NOTICE: Buffer Leak: [059] (freeNext=-3, freePrev=-3, relname=pg_largeobject, blockNum=58, flags=0x85, refcount=1 3)
lockNum=49, flags=0x85, refcount=1 1)
NOTICE: Buffer Leak: [061] (freeNext=-3, freePrev=-3, relname=pg_largeobject, blockNum=51, flags=0x85, refcount=1 3)
NOTICE: Buffer Leak: [062] (freeNext=-3, freePrev=-3, relname=pg_largeobject, blockNum=37, flags=0x85, refcount=1 3)
ERROR: out of free buffers: time to abort !
-----

The number of buffers leaked monotonically increases as the
import proceeds...I can't quite pin down at what location the
Buffers in question are being pinned down...

-David

----------------------[=========]------------------------
David T. McWherter udmcwher(at)mcs(dot)drexel(dot)edu

Many Myths are based on truth
-- Spock, "The Way to Eden", stardate 5832.3