Re: BUG #18244: Corruption in indexes involving whole-row expressions

On Wed, 2023-12-13 at 14:38 +1300, Thomas Munro wrote:
> On Wed, Dec 13, 2023 at 11:53 AM Laurenz Albe <laurenz.albe@cybertec.at> wrote:
> > On Tue, 2023-12-12 at 19:28 +0000, PG Bug reporting form wrote:
> > > nik=# create index on t1 (hash_record(t1));
> > >
> > > Such an index can easily be corrupted, e.g.:
> > >
> > > nik=#  alter table t1 add column yo int default -1;
> > >
> > > Proposal: prohibit the use of whole-row expression – as it is already done
> > > for generated columns and produce a similar error ("cannot use whole-row
> > > variable in column generation expression")
> >
> > I reported that bug before:
> > https://www.postgresql.org/message-id/flat/e48a5d9a2d3d72985d61ee254314f5f5f5444a55.camel%40cybertec.at
>
> FTR I also posted a repro for another variation of that problem.  I
> think it's slightly more general that, it not just whole-row
> expressions (eg index on <table_name>), it's row types generally:
>
> https://www.postgresql.org/message-id/CA%2BhUKGKb4SB%2BqQ-vAVomxAvJY6um%2B5URyq2D0vv10g7mbYZ1Ww%40mail.gmail.com

Yes, and that makes it difficult to decide on the correct path.

Should we check all places where the type is used before allowing
ALTER TYPE?  Sounds complicated and error-prone.

Should we forbid composite types in index declarations?  Sounds posssible,
but very restrictive.  We might as well forbid composite types in table
definitions.  That would be a good thing in my opinion, but it would cause
a compatibility headache.

Perhaps the best thing would be to add a warning to chapter 8.16.4 that
it is "best practice" (and conforming to the first normal form) not to
use composite types in column definitions, and that altering a composite
type used in a table definition can lead to consistency problems.

Yours,
Laurenz Albe