Thread: 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 > 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 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 >