public inbox for [email protected]  

help / color / mirror / Atom feed

From: Tom Lane <[email protected]>
To: Jeff Davis <[email protected]>
Cc: Andrew Dunstan <[email protected]>
Cc: Jeff Davis <[email protected]>
Cc: [email protected]
Subject: Re: pgsql: Trial fix for old cross-version upgrades.
Date: Sat, 22 Feb 2025 21:48:04 -0500
Message-ID: <[email protected]> (raw)
In-Reply-To: <[email protected]>
References: <[email protected]>
	<[email protected]>
	<[email protected]>
	<[email protected]>
	<[email protected]>
	<[email protected]>
	<[email protected]>
	<[email protected]>
	<[email protected]>
	<[email protected]>

Jeff Davis <[email protected]> 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

view thread (30+ messages)  latest in thread

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected], [email protected], [email protected], [email protected]
  Subject: Re: pgsql: Trial fix for old cross-version upgrades.
  In-Reply-To: <[email protected]>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox