From: Laurenz Albe <laurenz.albe@cybertec.at>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Thomas Munro <thomas.munro@gmail.com>
Cc: nik@postgres.ai
Cc: pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #18244: Corruption in indexes involving whole-row expressions
Date: Thu, 14 Dec 2023 06:43:26 +0100
Message-ID: <ce4165ffeac66281faa05cd9fc5ceabebe210af0.camel@cybertec.at> (raw)
In-Reply-To: <2528430.1702440277@sss.pgh.pa.us>
References: <18244-8a7897de2acd5a08@postgresql.org>
<f577abdc489e75a0b62f0648829d633d75ff8d95.camel@cybertec.at>
<CA+hUKGJPxjaD3d_qYrHPxHLS_=ktVMFK7O-+9RgTBzQUTB9q6A@mail.gmail.com>
<c37d4184c476a15ad4c488d272bdc95ea7b0ff49.camel@cybertec.at>
<2528430.1702440277@sss.pgh.pa.us>
On Tue, 2023-12-12 at 23:04 -0500, Tom Lane wrote:
> Laurenz Albe <laurenz.albe@cybertec.at> writes:
> > Should we forbid composite types in index declarations? Sounds posssible,
> > but very restrictive.
>
> That would make a lot of people very sad, I fear.
>
> I think a minimum answer could be to document that you might need to
> REINDEX such indexes after a change in the composite type. We could
> perhaps try to be more proactive, say by marking such indexes invalid.
> I think though that that would have deadlock problems as well as race
> conditions. Perhaps best to leave it at "reindex when necessary",
> especially in view of the tiny number of reports to date.
I agree. How about the attached documentation patch?
Yours,
Laurenz Albe
Attachments:0001-Document-the-dangers-of-composite-type-columns.patch (text/x-patch, 1.4K) download
From 2399b71203168f9905236380379ed3cadd6d3f4c Mon Sep 17 00:00:00 2001
From: Laurenz Albe <laurenz.albe@cybertec.at>
Date: Thu, 14 Dec 2023 06:41:18 +0100
Subject: [PATCH] Document the dangers of composite type columns
Table columns of a composite type are usually not a good idea,
as they violate the first normal form.
Moreover, if you create an index on a composite type and later
use ALTER TYPE to modify the type, the index can get corrupted.
Author: Laurenz Albe
Discussion: https://postgr.es/m/18244-8a7897de2acd5a08%40postgresql.org
---
doc/src/sgml/rowtypes.sgml | 10 ++++++++++
1 file changed, 10 insertions(+)
diff --git a/doc/src/sgml/rowtypes.sgml b/doc/src/sgml/rowtypes.sgmlindex 4d86f97c03..6a3beaf7d0 100644--- a/doc/src/sgml/rowtypes.sgml+++ b/doc/src/sgml/rowtypes.sgml@@ -67,6 +67,16 @@ SELECT price_extension(item, 10) FROM on_hand;
</para>
+ <note>+ <para>+ While you can use composite types in column definitions, there is usually no+ benefit in doing so. Also, if you create indexes on composite type columns,+ beware of using <command>ALTER TYPE</command> to modify the type definition:+ doing that could break the requirement that index expressions must be+ immutable and might render the index corrupted.+ </para>+ </note>+
<para>
Whenever you create a table, a composite type is also automatically
created, with the same name as the table, to represent the table's
--
2.43.0
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: pgsql-bugs@postgresql.org
Cc: laurenz.albe@cybertec.at, tgl@sss.pgh.pa.us, thomas.munro@gmail.com, nik@postgres.ai, pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #18244: Corruption in indexes involving whole-row expressions
In-Reply-To: <ce4165ffeac66281faa05cd9fc5ceabebe210af0.camel@cybertec.at>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox