The following bug has been logged online:
Bug reference: 3881
Logged by: Michael Akinde
Email address: [EMAIL PROTECTED]
PostgreSQL version: 8.2.5
Operating system: Linux Debian Etch
Description: lo_open leaks memory
Details:
We are using large objects to store gridded data, and wish to provide
function to permit the extraction of a single point from the data grids. One
issue we have run into, unfortunately, is that lo_open seems to leak memory
somewhat terribly.
Simplified test case:
create or replace function f() RETURNS setof bytea as
$body$
declare
r oid;
fd int;
ret bytea;
begin
for r in select gridoid FROM gridvalue LIMIT 150000
LOOP
fd := lo_open( r, 262144 ); -- 262144 = "INV_READ"
--PERFORM lo_lseek( fd, 120, 0 ); -- 0 = "SEEK_SET"
--ret := loread( fd, 4 );
PERFORM lo_close( fd );
--RETURN NEXT ret;
END LOOP;
end;
$body$
language plpgsql;
SELECT * FROM f();
(Note that several lines are commented out - behavior is essentially the
same with or without).
We find that the above function (on a 8.2.5 setup) will rapidly max out 1GB
of shared memory (it seems to goblle up 10-20kb for each lo_open), and
performs very poorly. Obviously, this only gets worse with increasing
queries (many of our queries will be retrieving points from over 10 million
grids, so simply increasing memory is unfortunately not a viable solution).
We are aware that this is a "known" bug (inasmuch as the comments on the
backend source seems to explicitly state that the lo_* functions are known
to leak memory).
Questions:
- Any likelihood that this may be fixed in the near future (or ever)?
- Might there be a simple workaround for this problem?
We have looked at the backend code ourselves, but I suspect that it would
probably be easier for us to use toasted binary objects (esentially
developing our own specialized lo_* system of functions) than to try and
patch this on our own. Or might that run into similar (or different)
problems?
Regards,
Michael Akinde
Database Architect, met.no
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
