RE: BUG #18016: REINDEX TABLE failure

Hi everyone,

Sorry for the delay, I was away from computer for a couple of days.

Tom is exactly right. I was just giving you a minimum number of steps to reproduce the issue. That being said, it is also a good idea to give you a bit of a background context and maybe start a broader discussion. However, I don't want to pollute a bug report with an unrelated topic so someone might suggest a more appropriate venue.

In my particular case, I didn't encounter some hardware failure that caused corruption of both TOAST table index and other dependent indexes, but instead I didn't have either of them in the first place (hence my suggestion to truncate them to accurately reproduce my exact setup). So in that sense Michael is also asking a legitimate question of how we got to where we are.

I was dissatisfied with storage layer performance, especially during the initial database population, so I rewrote it for my use case. I'm done with the heap, but for the moment I still rely on PostgreSQL to build indexes, specifically by using the REINDEX TABLE command for its convenience and that's how I discovered this problem with a couple of tables that had the required combination of indexes and data to trigger the original issue.

I won't derail this discussion any further, because some people downthread are already working on fixing/improving this scenario, but there's no shortage of people that suffer from sluggish pg_dump/pg_restore cycle and I imagine there are any number of people that would be interested in improving bulk ingestion which is often a bottleneck for analytical workloads as you are well aware. What's the best place to discuss this topic further - pgsql-performance or someplace else?

Best,
Richard

-----Original Message-----
From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Sent: Saturday, July 8, 2023 2:20 AM
To: Michael Paquier <michael(at)paquier(dot)xyz>
Cc: Richard Veselý <richard(dot)vesely(at)softea(dot)sk>; pgsql-bugs(at)lists(dot)postgresql(dot)org
Subject: Re: BUG #18016: REINDEX TABLE failure

Michael Paquier <michael(at)paquier(dot)xyz> writes:
> On Thu, Jul 06, 2023 at 08:29:19PM +0000, PG Bug reporting form wrote:
>> Given a table with a TOASTed variable length attribute, REINDEX TABLE
>> fails to rebuild indexes when you truncate (or otherwise corrupt)
>> relation files for both TOAST table index and a custom index on the varlena.

> Could you clarify what you have done here? Did you manipulate the
> physical files in the data folder before running the REINDEX commands
> you expected should work? There are many things that can go wrong if
> you do anything like that.

I think the point of that was just to have a way to reproduce the problem on-demand. I follow the argument, which is that if there's actual corruption in the TOAST index (for whatever reason) that might interfere with rebuilding the table's other indexes.

regards, tom lane