On Fri, 9 Jun 2006, Tom Lane wrote:
pg_dump: SQL command failed
pg_dump: Error message from server: ERROR: Memory exhausted in AllocSetAlloc(96)
Hm, I'm not sure why it did that. Possibly an ANALYZE on pg_largeobject
would change the plan for the SELECT DISTINCT and get you out of
trouble.
Would it be better and more efficient to just pg_dumpall the globals and
pg_dump in custom format the vsl_cs db?
Won't help AFAIK --- that query will be used for blob dumping in all
cases. Or actually, according to the cursor name, this is the query
for dumping blob comments.
Server is 7.3.2, and the plan is to upgrade it.
Might be worth your while to update the server to 7.3.latest in-place.
The list of bugs fixed in 7.3.x is very long; I'm too lazy to look and
see if any of them look related to this, but it's possible.
The in-place upgrade was going to be my next question, but that'll require
another maintenance window. I was really hoping to get a good backup before
the next time we take it down. :-/
Also, if you don't mind altering pg_dump, it looks to me like the query
being used here is unnecessarily inefficient:
/* Cursor to get all BLOB comments */
if (AH->remoteVersion >= 70200)
blobQry = "DECLARE blobcmt CURSOR FOR SELECT DISTINCT loid, obj_description(loid, 'pg_largeobject') FROM pg_largeobject";
This is computing obj_description() redundantly for each pg_largeobject
chunk. Perhaps there is a memory leak in obj_description() in 7.3.2?
If so it'd help to use
DECLARE blobcmt CURSOR FOR
SELECT loid, obj_description(loid,'pg_largeobject')
FROM (SELECT DISTINCT loid FROM pg_largeobject) ss;
I'll alter pg_dump and recompile, then give a test. Does pg_dumpall just call
pg_dump or do I need to change it there too? Thanks for the help Tom. I'll
report back with the status.
--
Jeff Frost, Owner <jeff@xxxxxxxxxxxxxxxxxxxxxx>
Frost Consulting, LLC http://www.frostconsultingllc.com/
Phone: 650-780-7908 FAX: 650-649-1954
