Re: PostgreSQL 9.0.4 blocking in lseek?

=?ISO-8859-1?Q?S=F6ren_Meyer-Eppler?= <soerenme(at)google(dot)com> writes:
> A fairly complex insert query on an empty destination table will run for
> an indefinite amount of time (we waited literally days for the query to
> complete). This does not happen every time we run the query but often.
> Now ordinarily I'd assume we did something wrong with our indices or
> query, but the execution plan looks sane and, more tellingly, we have:
> - no CPU load
> - no network traffic
> - no disk I/O
> - no other load on the server except this single query
> and strace displaying a seemingly endless list of lseek calls.

> So my assumption is that we are not running into bad Big-O() runtime
> behavior but rather into some locking problem.

If it were blocked on a lock, it wouldn't be doing lseeks().

The most obvious explanation for a backend that's doing repeated lseeks
and nothing else is that it's repeatedly doing seqscans on a table
that's fully cached in shared buffers. I'd wonder about queries
embedded in often-executed plpgsql functions, for instance. Can you
identify which table the lseeks are issued against?

(Now, having said that, I don't see how that type of theory explains no
CPU load. But you're really going to need to provide more info before
anyone can explain it, and finding out what the lseeks are on would be
one good step.)

regards, tom lane