Re: FlushRelationBuffers error

Tom Lane wrote:
> Gaetano Mendola <mendola@bigfoot.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
whileprocessing the request.
 




Regards
Gaetano Mendola