Gaetano Mendola <[EMAIL PROTECTED]> 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
---------------------------(end of broadcast)--------------------------- TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
