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
alsoa good idea to give you a bit of a background context and maybe start a broader discussion. However, I don't want
topollute 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
otherdependent indexes, but instead I didn't have either of them in the first place (hence my suggestion to truncate
themto accurately reproduce my exact setup). So in that sense Michael is also asking a legitimate question of how we
gotto where we are. 

I was dissatisfied with storage layer performance, especially during the initial database population, so I rewrote it
formy use case. I'm done with the heap, but for the moment I still rely on PostgreSQL to build indexes, specifically by
usingthe REINDEX TABLE command for its convenience and that's how I discovered this problem with a couple of tables
thathad 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
anynumber of people that would be interested in improving bulk ingestion which is often a bottleneck for analytical
workloadsas 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
thatif there's actual corruption in the TOAST index (for whatever reason) that might interfere with rebuilding the
table'sother indexes. 

            regards, tom lane