Re: Vacuumdb Fails: Huge Tuple

On Thu, Oct 1, 2009 at 5:02 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> APseudoUtopia <apseudoutopia(at)gmail(dot)com> writes:
>>> Here's what happened:
>>>
>>> $ vacuumdb --all --full --analyze --no-password
>>> vacuumdb: vacuuming database "postgres"
>>> vacuumdb: vacuuming database "web_main"
>>> vacuumdb: vacuuming of database "web_main" failed: ERROR:  huge tuple
>
>> PostgreSQL 8.4.0 on i386-portbld-freebsd7.2, compiled by GCC cc (GCC)
>> 4.2.1 20070719  [FreeBSD], 32-bit
>
> This is evidently coming out of ginHeapTupleFastCollect because it's
> formed a GIN tuple that is too large (either too long a word, or too
> many postings, or both).  I'd say that this represents a serious
> degradation in usability from pre-8.4 releases: before, you would have
> gotten the error upon attempting to insert the table row that triggers
> the problem.  Now, with the "fast insert" stuff, you don't find out
> until VACUUM fails, and you have no idea where the bad data is.  Not cool.
>
> Oleg, Teodor, what can we do about this?  Can we split an oversize
> tuple into multiple entries?  Can we apply suitable size checks
> before instead of after the fast-insert queue?
>
>                        regards, tom lane
>

Thanks for the explanation. Is there any way I can fix this? I'd
rather not recompile pgsql with the patch on my systems at the moment
because they are production servers. Would a dump/reload of the table
help anything? The output from vaccuumdb verbose is below, if it helps
narrow anything down. Does the log mean that the huge tuple is in the
search_index_idx index?

Thanks.

INFO: index "email_confirmations_pkey" now contains 0 row versions in 2 pages
DETAIL: 0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: vacuuming "pg_toast.pg_toast_31065"
INFO: "pg_toast_31065": found 0 removable, 0 nonremovable row
versions in 0 pages
DETAIL: 0 dead row versions cannot be removed yet.
Nonremovable row versions range from 0 to 0 bytes long.
There were 0 unused item pointers.
Total free space (including removable row versions) is 0 bytes.
0 pages are or will become empty, including 0 at the end of the table.
0 pages containing 0 free bytes are potential move destinations.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: index "pg_toast_31065_index" now contains 0 row versions in 1 pages
DETAIL: 0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: vacuuming "public.tags_groups"
INFO: "tags_groups": found 0 removable, 10 nonremovable row versions in 1 pages
DETAIL: 0 dead row versions cannot be removed yet.
Nonremovable row versions range from 40 to 52 bytes long.
There were 0 unused item pointers.
Total free space (including removable row versions) is 7708 bytes.
0 pages are or will become empty, including 0 at the end of the table.
1 pages containing 7708 free bytes are potential move destinations.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: index "tags_groups_pkey" now contains 10 row versions in 2 pages
DETAIL: 0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: "tags_groups": moved 0 row versions, truncated 1 to 1 pages
DETAIL: CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: vacuuming "public.tfiles"
INFO: "tfiles": found 232 removable, 1592 nonremovable row versions
in 806 pages
DETAIL: 0 dead row versions cannot be removed yet.
Nonremovable row versions range from 289 to 2036 bytes long.
There were 4893 unused item pointers.
Total free space (including removable row versions) is 4388880 bytes.
184 pages are or will become empty, including 2 at the end of the table.
791 pages containing 4370168 free bytes are potential move destinations.
CPU 0.00s/0.00u sec elapsed 0.30 sec.
INFO: index "tfiles_pkey" now contains 1592 row versions in 18 pages
DETAIL: 314 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.02 sec.
INFO: index "name_index_idx" now contains 1592 row versions in 51 pages
DETAIL: 1206 index row versions were removed.
4 index pages have been deleted, 16 are currently reusable.
CPU 0.00s/0.01u sec elapsed 0.28 sec.
INFO: index "search_index_idx" now contains 1592 row versions in 1289 pages
DETAIL: 36557 index row versions were removed.
118 index pages have been deleted, 159 are currently reusable.
CPU 0.03s/0.50u sec elapsed 8.77 sec.
vacuumdb: vacuuming of database "web_main" failed: ERROR: huge tuple