BUG #5439: Table crash after CLUSTER command
The following bug has been logged online:
Bug reference: 5439
Logged by: Stefan Kirchev
Email address: stefan.kirchev@gmail.com
PostgreSQL version: 8.3.3
Operating system: Linux
Description: Table crash after CLUSTER command
Details:
Hello,
I order to keep good performance on tables CLUSTER is done regularly on each
table every Sunday. Almost every time we loose a table which must be
recreated afterward. The error yield is:
pnp=# select * from alcatel_bss_kpi_tmp.cs_hourly_kpi limit 1;
ERROR: could not open relation 1663/16404/2426042: No such file or
directory
It is obvious the engine fails to replace the old relfilenode with the new
one.
Is it possible to recover the data from the pg_toast?
Is it mandatory to lock manually table with ACCESS EXCLUSIVE option or it is
done by the engine along with the CLUSTER command?
Thank you!
"Stefan Kirchev" <stefan.kirchev@gmail.com> wrote:
PostgreSQL version: 8.3.3
Description: Table crash after CLUSTER command
I order to keep good performance on tables CLUSTER is done
regularly on each table every Sunday. Almost every time we loose a
table which must be recreated afterward. The error yield is:
pnp=# select * from alcatel_bss_kpi_tmp.cs_hourly_kpi limit 1;
ERROR: could not open relation 1663/16404/2426042: No such file
or directory
My first recommendation would be to apply the fixes for the bugs
found during the last two years by upgrading your executable to
8.3.10. This does not require a dump and load, but if you have any
GiST indexes, or if you have hash indexes on intervals, you will
need to rebuild those indexes. To get more details, see:
http://www.postgresql.org/docs/8.3/static/release
FWIW, we CLUSTER a few very small, very frequently updated tables
daily in about 100 databases to ensure that we recover from bloat
from the occasional long-running transaction, and we've *never*
seen this.
If you actually need to cluster *every* table *every* week, you
should review your vacuum policy.
-Kevin
"Stefan Kirchev" <stefan.kirchev@gmail.com> writes:
Bug reference: 5439
Logged by: Stefan Kirchev
Email address: stefan.kirchev@gmail.com
PostgreSQL version: 8.3.3
Operating system: Linux
Description: Table crash after CLUSTER command
Details:
Hello,
I order to keep good performance on tables CLUSTER is done regularly on each
table every Sunday. Almost every time we loose a table which must be
recreated afterward. The error yield is:
pnp=# select * from alcatel_bss_kpi_tmp.cs_hourly_kpi limit 1;
ERROR: could not open relation 1663/16404/2426042: No such file or
directory
Can you generate a self-contained test case for this? Given the lack of
other reports, it seems like there must be something rather odd about
either your table definition or your clustering procedure.
Also, it would be good to update to a less obsolete 8.3.x release.
I don't recognize this offhand as a previously-fixed bug, but 8.3.3
was almost two years ago.
regards, tom lane
Thank you for the replay.
I will follow your advice. First will upgrade to 8.4 (or at least to the
latest 8.3 release), than will try to reproduce the error.
For now I will stick to using VACUUM FULL and REINDEX.
Thanks again.
Best Regards
Stefan Kirchev
On Mon, Apr 26, 2010 at 5:06 PM, Kevin Grittner <Kevin.Grittner@wicourts.gov
Show quoted text
wrote:
"Stefan Kirchev" <stefan.kirchev@gmail.com> wrote:
PostgreSQL version: 8.3.3
Description: Table crash after CLUSTER command
I order to keep good performance on tables CLUSTER is done
regularly on each table every Sunday. Almost every time we loose a
table which must be recreated afterward. The error yield is:
pnp=# select * from alcatel_bss_kpi_tmp.cs_hourly_kpi limit 1;
ERROR: could not open relation 1663/16404/2426042: No such file
or directoryMy first recommendation would be to apply the fixes for the bugs
found during the last two years by upgrading your executable to
8.3.10. This does not require a dump and load, but if you have any
GiST indexes, or if you have hash indexes on intervals, you will
need to rebuild those indexes. To get more details, see:http://www.postgresql.org/docs/8.3/static/release
FWIW, we CLUSTER a few very small, very frequently updated tables
daily in about 100 databases to ensure that we recover from bloat
from the occasional long-running transaction, and we've *never*
seen this.If you actually need to cluster *every* table *every* week, you
should review your vacuum policy.-Kevin