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@sss.pgh.pa.us> 
Sent: Saturday, July 8, 2023 2:20 AM
To: Michael Paquier <michael@paquier.xyz>
Cc: Richard Veselý <richard.vesely@softea.sk>; pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #18016: REINDEX TABLE failure

Michael Paquier <michael@paquier.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