Обсуждение: Errors while vacuuming large tables
We expire rows by a datestamp from a few fairly large tables in our schema (running 7.2.1). Table A: 140 Krows, 600 MB Table B: 100 Krows, 2.7 GB Table C: 140 Krows, 2.7 GB Table D: 3.2 Mrows, 500 MB so that something like 15-20% of each table is deleted at a crack (done on a weekend, of course). After the deletions, a VACUUM FULL is performed on each of these tables. Recently, we get this message quite often on table A: ERROR: Parent tuple was not found which I'm led to believe by things I've read here and elsewhere is caused by a bug in PostgreSQL having to do with rows marked as read-locked or something. I hope this gets repaired soon, because it's annoying not to be able to recover the space automatically on this table. But this weekend, we got a different set of errors: ERROR: cannot open segment 1 of relation table_D (target block 2337538109): No such file or directory and for table B: NOTICE: Child itemid in update-chain marked as unused - can't continue repair_frag ERROR: cannot open segment 3 of relation pg_toast_51207070 (target block 2336096317): No such file or directory What's the remedy to keep this from happening? We have an Apache mod_perl installation running queries against these tables; could an open read-only transaction cause problems like this? -- Jeff Boes vox 616.226.9550 ext 24 Database Engineer fax 616.349.9076 Nexcerpt, Inc. http://www.nexcerpt.com ...Nexcerpt... Extend your Expertise
Jeff Boes <jboes@nexcerpt.com> writes: > We expire rows by a datestamp from a few fairly large tables in our > schema (running 7.2.1). > Recently, we get this message quite often on > table A: > ERROR: Parent tuple was not found Please update to 7.2.3 and see if things get better. Also, if you're running on a recent Red Hat system, you might try Red Hat's 7.2.3-based RHDB release (7.2.3-RH), which is not out yet but should be out within days. It has a few additional VACUUM fixes back-patched from 7.3 work, which might solve your problem if the standard 7.2.3 release doesn't. (Note that I cannot recommend going to 7.2.2-RH in place of 7.2.3, due to the severity of the bugs that caused us to release 7.2.3; those are not fixed in 7.2.2-RH, as they were not found until after that release was frozen.) Keep an eye on http://www.redhat.com/software/database/ for the 7.2.3 update. regards, tom lane
On Mon, 14 Oct 2002 11:13:46 -0400, Jeff Boes wrote:
> ERROR: cannot open segment 1 of relation table_D (target block
> 2337538109): No such file or directory
>
> and for table B:
>
> NOTICE: Child itemid in update-chain marked as unused - can't continue
> repair_frag
> ERROR: cannot open segment 3 of relation pg_toast_51207070 (target
> block 2336096317): No such file or directory
>
Well, we attempted a VACUUM FULL on these tables again, but got the
dreaded:
NOTICE: Message from PostgreSQL backend:
The Postmaster has informed me that some other backend
died abnormally and possibly corrupted shared memory.
I have rolled back the current transaction and am
going to terminate your database system connection and exit.
Please reconnect to the database system and repeat your query.
We also had two load spikes several hours after this occurred, where the
system was unusable for 5 minutes (this has also happened before). We
suspect there was some corruption in the indexes. I did a REINDEX on
each affected table followed by a VACUUM FULL, and this time the VACUUMs
finished without error. All this may be superstition on my part, but I
report it here in case anyone recognizes the situation, or is as
desperate as I was to correct the problem ...
We are definitely moving to 7.2.3 ASAP, but it will be a week or two
before we can schedule this.
--
Jeff Boes vox 616.226.9550 ext 24
Database Engineer fax 616.349.9076
Nexcerpt, Inc. http://www.nexcerpt.com
...Nexcerpt... Extend your Expertise