uol2@freenet.de writes:
> Francisco,
>
> I just read your mail in the pgsql archives.
> I have the same problem:
> On my server, pg_dump crashes the backend with a SELECT statement.
> I'm using pgsql 8.1 on FreeBSD 6
> Did you figure out what could be the cause?
>
> This could be a workaround:
> I issued the commands
> reindexdb -s -U <SU> <DB>
> and
> reindexdb -U <SU> <DB>
>
> and pg_dump worked again.
Those commands seem to have fixed my problem too.
In particular pg_dumpall works. Also checked and was able to access the
trouble DB with the super user.
> I found out the following:
> The original SELECT command
> (SELECT classid, objid, refclassid, refobjid, deptype FROM pg_depend
> WHERE deptype != 'p' ORDER BY 1,2)
> issued by pg_dump lets the backend process eat up CPU time.
> The command
> (SELECT classid, objid, refclassid, refobjid, deptype FROM pg_depend
> WHERE ORDER BY 1)
> also never gets finished but does NOT use CPU time.
>
> All other variations of this command work (at least on
> my server) if you drop the ORDER clause for column 1.
> E.g.
> (SELECT classid, objid, refclassid, refobjid, deptype FROM pg_depend
> WHERE deptype != 'p')
> works
> and
> (SELECT classid, objid, refclassid, refobjid, deptype FROM pg_depend
> WHERE deptype != 'p' ORDER BY 2)
> also works.
>
> So the "ORDER BY 1" lets postmaster hang and if it is issued together
> with the WHERE clause it additionally starts eating CPU time.
>
> It seems to me that a nightly vacuum script somehow leads to
> this bug: When this bug first ocurred, I switched off vacuum.
> Quite a long time nothing happened. A few days ago I switched
> it back on again and again the nightly pg_dump would fail.
It has been working for me the same.. I had a nighly vacuum too. After the
reindex I turned it off. I will turn it back on and see if it breaks again.
I am using 8.1.3, but plan to upgrade to 8.1.5 soon. Will test with 8.1.3..
if the nighly vacuum breaks the dump, will reindex again.. then try to see
if 8.1.5 has the same issue.
> If you are interested in these, please let me know.
Yes please.
