Re: BUG #5480: Autovacuum interferes with operations (e.g. truncate) on very large databases

"Sakari Maaranen" <sam(at)iki(dot)fi> writes:
> The documentation also says that the quickest way to empty a whole table
> would be by using the truncate command. However, if the autovacuum daemon
> happens to be working on the same table, it causes the truncate command to
> hang for a very long time to wait the vacuum to finish.

That is not supposed to happen: other commands attempting to access the
table should kick the autovacuum off it (ie, force cancellation of the
autovacuum operation).

Now it is possible that there's some path of control wherein vacuum
fails to check for a cancel interrupt for a very long time. That seems
possible if you are using an uncommon index type or (less likely) an
unusual data type. What's the schema of the problem table exactly?

> For some reason PostgreSQL failed when I had two separate processes working
> on different tables of the same very large database:

> 1. I was restoring one table via psql from a pg_dump that was created
> earlier in the default (COPY) mode.

> 2. At the same time there was another process inserting data in another
> table via a JDBC connection.

Well, this also opens the possibility that what was blocking the
truncate was not autovacuum at all, but some lock held by one of the
other active sessions. An open transaction that has even just selected
from a table will block truncate, since that needs an exclusive lock.

> WARNING: terminating connection because of crash of another server process
> ]

That is definitely an indication of a problem, but you've completely
failed to provide any useful data about it, only client-side reports from
sessions that weren't the one that crashed. You might try looking in
the postmaster log to see if there is anything relevant recorded there.
If there's a core dump file, a stack trace from that would be even
better.

> Please either make PostgreSQL work with very large databases, or give
> recommendations on the maximum size of tables that should work well with
> PostgreSQL autovacuum and all. Thank you!

There is really not much we can do with such a vague problem report.
If you can provide details sufficient to let someone else reproduce
the misbehavior, we'll certainly look into it.

regards, tom lane