From: Alvaro Herrera <alvherre@commandprompt.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: dx k9 <bitsandbytes88@hotmail.com>
Cc: pgsql-admin@postgresql.org
Cc: pgsql-hackers@postgresql.org
Subject: Re: reindexdb hangs
Date: Wed, 12 Sep 2007 16:30:28 -0400
Message-ID: <20070912203028.GB6208@alvh.no-ip.org> (raw)
In-Reply-To: <1367.1189470012@sss.pgh.pa.us>
References: <BAY116-F2261C1BEF758D30196729DD1420@phx.gbl>
<22018.1178144703@sss.pgh.pa.us>
<20070825222150.GU31461@alvh.no-ip.org>
<4909.1188082313@sss.pgh.pa.us>
<20070829222341.GX7911@alvh.no-ip.org>
<12577.1188428726@sss.pgh.pa.us>
<20070830164151.GS5872@alvh.no-ip.org>
<7460.1188500061@sss.pgh.pa.us>
<20070910220700.GC3060@alvh.no-ip.org>
<1367.1189470012@sss.pgh.pa.us>
Tom Lane wrote:
> Alvaro Herrera <alvherre@commandprompt.com> writes:
> > I am unsure if I should backpatch to 8.1: the code in cluster.c has
> > changed, and while it is relatively easy to modify the patch, this is a
> > rare bug and nobody has reported it in CLUSTER (not many people clusters
> > temp tables, it seems). Should I patch only REINDEX? How far back?
>
> I'd say go as far back as you can conveniently modify the patch for.
> This is a potential data-loss bug (even if only for temporary data)
> so we ought to take it seriously.
OK, I fixed it all the way back that it was needed: 7.4 for CLUSTER and
8.1 for REINDEX.
Before 7.4 there wasn't a database-wide version of CLUSTER. This wasn't
a very serious bug in any case, because it would have thrown an ERROR if
it tried to cluster a remote temp table. So apparently no one ever saw
it, because I can't remember any report about it.
For REINDEX the story is very different, because what happens is that
queries start silently returning wrong data. My test case was
alvherre=# create temp table foo (a int primary key);
NOTICE: CREATE TABLE / PRIMARY KEY creará el índice implícito «foo_pkey» para la tabla «foo»
CREATE TABLE
alvherre=# insert into foo select * from generate_series(1,40000);
INSERT 0 40000
-- "reindex database alvherre" in another session
alvherre=# select * from foo where a = 400;
a
-----
(0 rows)
If you now REINDEX this table in the current session, it correctly
returns one tuple. So if somebody is executing a procedure which
involve temp tables and someone else concurrently does a REINDEX
DATABASE, the queries of the first session are automatically corrupted.
It seems like the worst kind of bug.
Maybe we need additional protections on the bufmgr to prevent this kind
of problem.
We only introduced REINDEX DATABASE as a way to reindex user indexes in
8.1. Before that, it only considered system catalogs, which ISTM are
never temp.
Many thanks to dx k9 for the original report.
--
Alvaro Herrera http://www.PlanetPostgreSQL.org/
"Endurecerse, pero jamás perder la ternura" (E. Guevara)
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 from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-admin@postgresql.org
Cc: alvherre@commandprompt.com, tgl@sss.pgh.pa.us, bitsandbytes88@hotmail.com, pgsql-hackers@postgresql.org
Subject: Re: reindexdb hangs
In-Reply-To: <20070912203028.GB6208@alvh.no-ip.org>
References: <BAY116-F2261C1BEF758D30196729DD1420@phx.gbl> <22018.1178144703@sss.pgh.pa.us> <20070825222150.GU31461@alvh.no-ip.org> <4909.1188082313@sss.pgh.pa.us> <20070829222341.GX7911@alvh.no-ip.org> <12577.1188428726@sss.pgh.pa.us> <20070830164151.GS5872@alvh.no-ip.org> <7460.1188500061@sss.pgh.pa.us> <20070910220700.GC3060@alvh.no-ip.org> <1367.1189470012@sss.pgh.pa.us> <20070912203028.GB6208@alvh.no-ip.org>
* 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