From: Gaetano Mendola <mendola@bigfoot.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Subject: Re: FlushRelationBuffers error
Date: Thu, 30 Sep 2004 20:28:53 +0200
Message-ID: <415C5065.6090209@bigfoot.com> (raw)
In-Reply-To: <18894.1096564105@sss.pgh.pa.us>
References: <cjgmdn$rd3$1@floppy.pyrenet.fr>
<18894.1096564105@sss.pgh.pa.us>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-hackers@postgresql.org
Cc: mendola@bigfoot.com, tgl@sss.pgh.pa.us
Subject: Re: FlushRelationBuffers error
In-Reply-To: <415C5065.6090209@bigfoot.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox