From: Tom Lane <tgl@sss.pgh.pa.us>
To: Jeff Davis <pgsql@j-davis.com>
cc: Andrew Dunstan <andrew@dunslane.net>, Jeff Davis <jdavis@postgresql.org>,
        pgsql-committers@lists.postgresql.org
Subject: Re: pgsql: Trial fix for old cross-version upgrades.
In-reply-to: <7fe52cfdc373df817e303050f1f10f25dcdf4390.camel@j-davis.com>
References: <E1tlBJt-000LUO-16@gemulon.postgresql.org>
 <003dc9936317ab987faa0242f8e33e1cd2fcaf57.camel@j-davis.com>
 <40c1e76334f2baa747334c8fc513d12b8cb297bf.camel@j-davis.com>
 <3815127.1740189601@sss.pgh.pa.us>
 <5ebc188e731ac2b98d68459ce1a9ef3066981774.camel@j-davis.com>
 <3892121.1740193866@sss.pgh.pa.us>
 <976dcc37-b629-490e-a052-a057477d062f@dunslane.net>
 <301492.1740249291@sss.pgh.pa.us> <684274.1740273302@sss.pgh.pa.us>
 <7fe52cfdc373df817e303050f1f10f25dcdf4390.camel@j-davis.com>
Comments: In-reply-to Jeff Davis <pgsql@j-davis.com>
	message dated "Sat, 22 Feb 2025 17:25:43 -0800"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <816166.1740278884.1@sss.pgh.pa.us>
Date: Sat, 22 Feb 2025 21:48:04 -0500
Message-ID: <816167.1740278884@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/816167.1740278884%40sss.pgh.pa.us>
Precedence: bulk

Jeff Davis <pgsql@j-davis.com> writes:
> It's also strange that copperhead is consistently failing on 12 with:

>   pg_restore: while PROCESSING TOC:
>   pg_restore: from TOC entry 4163; 0 0 STATISTICS DATA "vcharidx" (no
> owner)
>   pg_restore: error: could not execute query: ERROR:  column "text" of
> relation "vcharidx" does not exist

Ugh.  I see what is happening, and it's going to be problematic to
fix: our heuristics for assigning names to index expression columns
are not very consistent/stable.  It didn't matter up to now, but
this patch assumes that it can reference index columns by name.
The index in question is made in btree_gist's tests:

CREATE INDEX vcharidx ON vchartmp USING GIST ( text(a) );

The index column will be given the name "text".  However, it
dumps as

CREATE INDEX vcharidx ON public.vchartmp USING gist (((a)::text));

and when *that* gets loaded, the index column is given the name
"a", because FigureColname treats function-like constructs
differently from cast-like constructs.  Then
pg_restore_attribute_stats unsurprisingly fails.  I think the
reason that Andrew and I aren't seeing that is that we are
using machines that are fast enough to shut down the DB before
autovacuum gets around to populating some stats for this
expression index.

We have dealt with some similar issues in the past, and the
solution was to allow index columns to be referenced by
column number not name.  (ALTER INDEX ... ALTER COLUMN ...
SET STATISTICS does that, not sure if there are other places.)
Recommend adopting the same solution here.

			regards, tom lane