From: Gaetano Mendola <mendola@bigfoot.com>
Subject: Re: FlushRelationBuffers error
Date: Thu, 30 Sep 2004 20:28:53 +0200
Organization: PYRENET Midi-pyrenees Provider
Lines: 71
Message-ID: <415C5065.6090209@bigfoot.com>
References: <cjgmdn$rd3$1@floppy.pyrenet.fr> <18894.1096564105@sss.pgh.pa.us>
Mime-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
To: Tom Lane <tgl@sss.pgh.pa.us>
User-Agent: Mozilla Thunderbird 0.8 (X11/20040913)
In-Reply-To: <18894.1096564105@sss.pgh.pa.us>
To: pgsql-hackers@postgresql.org

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 while processing the request.


Regards
Gaetano Mendola