SEO

I'm curious why this would happen:

pg_dump: dumpBlobs(): could not open large object: ERROR: inv_open: large
object 145391 not found

The db being dumped is 7.3.2 and the pg_dumpall is from a source compiled
8.1.4. The OS in question is Redhat 8 (soon to be upgraded). When I used the
7.3.2 pg_dump -b, everything dumped out ok, but using the 8.1.4 pg_dumpall
which is supposed to properly handle large objects, I received this error.
Note, there's only the single error above, and I still ended up with a 17gig
bzipped file. The 7.3.2 pg_dump yielded a 16gig file in custom format with
default compression.

I need to get a reasonable dump so I can upgrade this ancient system. Should
I upgrade to the most recent 7.3.x version before doing the dump to have the
most reasonable chance of success?

--
Jeff Frost, Owner <jeff@frostconsultingllc.com>
Frost Consulting, LLC http://www.frostconsultingllc.com/
Phone: 650-780-7908 FAX: 650-649-1954

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

Jeff Frost <jeff@frostconsultingllc.com> writes:
> I'm curious why this would happen:
> pg_dump: dumpBlobs(): could not open large object: ERROR: inv_open: large
> object 145391 not found

Some cursory trawling in the REL7_3 sources says that this means that
"SELECT DISTINCT loid FROM pg_largeobject" found a large object OID
that then could not be found by an indexscan of pg_largeobject. So
I'd try a REINDEX of pg_largeobject to see if that fixes it. See the
REINDEX man page concerning hoops you have to jump through to reindex
a system catalog --- IIRC, the hoops are much higher and narrower back
in 7.3.

> The db being dumped is 7.3.2

Of course, I'm reading 7.3.15 sources ...

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

On Tue, 6 Jun 2006, Tom Lane wrote:

> Some cursory trawling in the REL7_3 sources says that this means that
> "SELECT DISTINCT loid FROM pg_largeobject" found a large object OID
> that then could not be found by an indexscan of pg_largeobject. So
> I'd try a REINDEX of pg_largeobject to see if that fixes it. See the
> REINDEX man page concerning hoops you have to jump through to reindex
> a system catalog --- IIRC, the hoops are much higher and narrower back
> in 7.3.

Thanks Tom. BTW, you might be amused to see the uptime output prior to
shutting the machine down to add a PCI card:

10:17am up 1004 days, 7 min, 1 user, load average: 0.00, 0.00, 0.00


--
Jeff Frost, Owner <jeff@frostconsultingllc.com>
Frost Consulting, LLC http://www.frostconsultingllc.com/
Phone: 650-780-7908 FAX: 650-649-1954

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

On Wed, 7 Jun 2006, Jeff Frost wrote:

> On Tue, 6 Jun 2006, Tom Lane wrote:
>
>> Some cursory trawling in the REL7_3 sources says that this means that
>> "SELECT DISTINCT loid FROM pg_largeobject" found a large object OID
>> that then could not be found by an indexscan of pg_largeobject. So
>> I'd try a REINDEX of pg_largeobject to see if that fixes it. See the
>> REINDEX man page concerning hoops you have to jump through to reindex
>> a system catalog --- IIRC, the hoops are much higher and narrower back
>> in 7.3.
>

Got the REINDEX completed and found a new error that I haven't seen before:

pg_dump: SQL command failed
pg_dump: Error message from server: ERROR: Memory exhausted in AllocSetAlloc(96)
pg_dump: The command was: FETCH 100 IN blobcmt
pg_dumpall: pg_dump failed on database "vsl_cs", exiting

I was dumping like so:

/usr/local/pgsql-8.1.4/bin/pg_dumpall | /usr/bin/bzip2 > vsl_cs-20060608.sql.bz2

Would it be better and more efficient to just pg_dumpall the globals and
pg_dump in custom format the vsl_cs db? There's actually only one DB on the
systems besides the system dbs. Server is 7.3.2, and the plan is to upgrade
it.

--
Jeff Frost, Owner <jeff@frostconsultingllc.com>
Frost Consulting, LLC http://www.frostconsultingllc.com/
Phone: 650-780-7908 FAX: 650-649-1954

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

On Fri, 9 Jun 2006, Jeff Frost wrote:

> Got the REINDEX completed and found a new error that I haven't seen before:
>
> pg_dump: SQL command failed
> pg_dump: Error message from server: ERROR: Memory exhausted in
> AllocSetAlloc(96)
> pg_dump: The command was: FETCH 100 IN blobcmt
> pg_dumpall: pg_dump failed on database "vsl_cs", exiting
>
> I was dumping like so:
>
> /usr/local/pgsql-8.1.4/bin/pg_dumpall | /usr/bin/bzip2 >
> vsl_cs-20060608.sql.bz2
>
> Would it be better and more efficient to just pg_dumpall the globals and
> pg_dump in custom format the vsl_cs db? There's actually only one DB on the
> systems besides the system dbs. Server is 7.3.2, and the plan is to upgrade
> it.

Hit send before i was done:

ulimit -d shows:

bash-2.05b$ ulimit -d
unlimited

The free output on the system looks like this:

bash-2.05b$ free
total used free shared buffers cached
Mem: 1031024 481548 549476 0 30620 359524
-/+ buffers/cache: 91404 939620
Swap: 2562328 193992 2368336



--
Jeff Frost, Owner <jeff@frostconsultingllc.com>
Frost Consulting, LLC http://www.frostconsultingllc.com/
Phone: 650-780-7908 FAX: 650-649-1954

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

Jeff Frost <jeff@frostconsultingllc.com> writes:
>> On Tue, 6 Jun 2006, Tom Lane wrote:
>>> I'd try a REINDEX of pg_largeobject to see if that fixes it.

> Got the REINDEX completed and found a new error that I haven't seen before:

> 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.

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;

(Think I'll go change this in CVS, too, as it's obviously tremendously
inefficient if you've got big large objects...)

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

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@frostconsultingllc.com>
Frost Consulting, LLC http://www.frostconsultingllc.com/
Phone: 650-780-7908 FAX: 650-649-1954

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

I wrote:
>> This is computing obj_description() redundantly for each pg_largeobject
>> chunk. Perhaps there is a memory leak in obj_description() in 7.3.2?

Actually, obj_description() is a SQL-language function, and we had
horrendous problems with end-of-function-call memory leakage in SQL
functions in every version before 7.4. So this is undoubtedly the
source of Jeff's problem.

Jeff Frost <jeff@frostconsultingllc.com> writes:
> 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?

pg_dumpall calls pg_dump, so only one place to fix. I've already
committed the fix in CVS, if you'd prefer to use a tested patch.
http://developer.postgresql.org/cvsw...3;r2=1.422.2.4

regards, tom lane

---------------------------(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

On Fri, 9 Jun 2006, Tom Lane wrote:

> pg_dumpall calls pg_dump, so only one place to fix. I've already
> committed the fix in CVS, if you'd prefer to use a tested patch.
> http://developer.postgresql.org/cvsw...3;r2=1.422.2.4

I'm running a test dump now, so we'll see sometime tomorrow (it takes about 20
hrs with the current setup) if it worked properly or if I find a new problem.
:-)

--
Jeff Frost, Owner <jeff@frostconsultingllc.com>
Frost Consulting, LLC http://www.frostconsultingllc.com/
Phone: 650-780-7908 FAX: 650-649-1954

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

On Fri, 9 Jun 2006, Jeff Frost wrote:

> On Fri, 9 Jun 2006, Tom Lane wrote:
>
>> pg_dumpall calls pg_dump, so only one place to fix. I've already
>> committed the fix in CVS, if you'd prefer to use a tested patch.
>> http://developer.postgresql.org/cvsw...3;r2=1.422.2.4
>
> I'm running a test dump now, so we'll see sometime tomorrow (it takes about
> 20 hrs with the current setup) if it worked properly or if I find a new
> problem. :-)

You'll be happy to hear that the test dump was successful and actually only
required 12 hrs to complete. Now that we can create a valid db dump, we'll be
doing the upgrade as soon as a maintenance window presents itself.

Thanks Tom!

--
Jeff Frost, Owner <jeff@frostconsultingllc.com>
Frost Consulting, LLC http://www.frostconsultingllc.com/
Phone: 650-780-7908 FAX: 650-649-1954

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org