Re: FlushRelationBuffers error

Tom Lane wrote:
> Gaetano Mendola <mendola(at)bigfoot(dot)com> writes:
>
>>I'm running postgres 7.4.5 on a linux box, this morning I got this error on my logs:
>
>
>>WARNING: FlushRelationBuffers("exp_provider", 1836): block 1460 is referenced (private 0, global 1)
>>ERROR: FlushRelationBuffers returned -2
>
>
>>Info for hackers: exp_provider is an index and during that message a reindex was in place.
>
>
> AFAICS, the only place that FlushRelationBuffers is called on an index
> with a nonzero second argument is in btvacuumcleanup, ie, shortening an
> index at the conclusion of VACUUM FULL. REINDEX wouldn't have anything
> to do with it.
>
> The message appears to indicate that some other backend was holding a
> pin on a buffer containing one of the blocks of the index. Which should
> definitely not happen, because VACUUM has an exclusive lock at the table
> level, and so no other backend should be touching the index at all.
>
> One thing I am wondering about is that VACUUM FULL relies on an
> exclusive lock on a table to prevent outside access to the table's
> indexes as well as the table itself. It might be better to spend a few
> more cycles and obtain locks on the indexes too. I don't think there
> are any situations where the main backend could access an index without
> having any lock on the parent table, but there are some contrib
> functions like pgstattuple that I'm not so sure about. Are you using
> any contrib or locally written C functions in your database?

Unfortunately no contrib and even no C functions at all.

Looking better at what was happening this morning indeed was a vacuum
full, I have in the morning a vacuum followed by some reindex, and the
failure was during the vacuum:

INFO: vacuuming "public.ua_user_data_exp"
INFO: "ua_user_data_exp": found 15232 removable, 33609 nonremovable row
versions in 6073 pages

[SNIP]

CPU 0.07s/0.05u sec elapsed 0.24 sec.
INFO: index "exp_mac_address_normal" now contains 33609 row versions in
644 pages
DETAIL: 3275 index row versions were removed.
1 index pages have been deleted, 1 are currently reusable.
CPU 0.00s/0.04u sec elapsed 0.25 sec.
WARNING: FlushRelationBuffers("exp_provider", 1836): block 1460 is
referenced (private 0, global 1)
vacuumdb: vacuuming of database "empdb" failed: ERROR:
FlushRelationBuffers returned -2
PANIC: cannot abort transaction 354676201, it was already committed
server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.

Regards
Gaetano Mendola