MIME-Version: 1.0
References: <CACJufxGao-cypdNhifHAdt8jHfK6-HX=tRBovBkgRuxw063GaA@mail.gmail.com>
 <CALdSSPh1opqDbVSSrrWr40B-3TS+q8EfGRLqJRVw+j15X6nPDQ@mail.gmail.com>
 <CACJufxGu3jjiyKqPRKP+n92-7u3S_yexCLe1NCQywe96jQxzrg@mail.gmail.com>
 <CALdSSPiMrRhSyQvAqGxgLMdcmFLrXSDAsCt32pg=e2bh2k4_Tg@mail.gmail.com>
 <CACJufxFx2zmTnTrGaxfNpRpy3RbY9wpjBXqvfP5dWoxsW9f_FQ@mail.gmail.com>
 <CACJufxGgkH0PyyqP6ggqcEWHxZzmkV=puY8ad=s8kisss9MAwg@mail.gmail.com>
 <CACJufxFBJa2_0jm7q7ZhS5Y=ZTU_b+5YHdQ6vK4Dp8xChUj3Ww@mail.gmail.com>
 <CADkLM=dtJZTrbUdFq5UznRepSoYpB2ARm4Q6yGvcgPrxwTtMrQ@mail.gmail.com>
 <1178617.1753217685@sss.pgh.pa.us> <CADkLM=c09mZi_XFix5EyHXPz1Z0k7=-DBV+iAOicCrDiqCqFrw@mail.gmail.com>
 <CACJufxEygt-OkS9qSCshNu+fq4533AA0BCBtboW9Mi3xujp=Uw@mail.gmail.com>
In-Reply-To: <CACJufxEygt-OkS9qSCshNu+fq4533AA0BCBtboW9Mi3xujp=Uw@mail.gmail.com>
From: Soumya S Murali <soumyamurali.work@gmail.com>
Date: Fri, 27 Feb 2026 16:15:33 +0530
Message-ID: <CAMtXxw8_nBrTs2X9UudPnNjhVkJuJ8mQvVP8jqnWCyC3_KbZYA@mail.gmail.com>
Subject: Re: support create index on virtual generated column.
To: jian he <jian.universality@gmail.com>
Cc: Corey Huinker <corey.huinker@gmail.com>, Tom Lane <tgl@sss.pgh.pa.us>, 
	PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Content-Type: multipart/mixed; boundary="0000000000004af7ec064bcbeedf"
Archived-At: <https://www.postgresql.org/message-id/CAMtXxw8_nBrTs2X9UudPnNjhVkJuJ8mQvVP8jqnWCyC3_KbZYA%40mail.gmail.com>
Precedence: bulk

--0000000000004af7ec064bcbeedf
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

Hi all,

On Thu, Feb 19, 2026 at 5:18=E2=80=AFPM jian he <jian.universality@gmail.co=
m> wrote:
>
> On Thu, Jul 31, 2025 at 2:24=E2=80=AFAM Corey Huinker <corey.huinker@gmai=
l.com> wrote:
> >
> >>
> >> Besides, this does nothing you haven't been able to do for
> >> decades with expression indexes.
> >
> >
> > What I'm hoping for with this feature is a smoother transition when peo=
ple start introducing virtual columns. If there was already an index on tha=
t expression, and some queries start using the new virtual column (with the=
 same expression), will that cause those queries to miss the index we alrea=
dy have? If it doesn't, then a customer can roll out the query changes at w=
ill and not need to do some cut-over from using the expression to using the=
 virtual column.
> >
>
> hi.
> I am not sure whether this concern has been addressed, as I am still some=
what
> confused by the above paragraph.
>
> As noted earlier, creating an index on a virtual generated column results=
 in a
> new index, and that index behaves the same as a regular expression index.
>
> An updated and polished patch is attached. The regress tests are quite ve=
rbose
> at the moment, since I make it covered all index types (btree, gist, spgi=
st,
> hash, gin, and brin).
>

I went through the discussions and have reviewed the patch.
During testing, I encountered a segmentation fault during initdb which
occurred due to a crash in bootstrap mode when ComputeIndexAttrs() was
invoked with pstate =3D=3D NULL while system catalog indexes were being
created. The virtual generated column handling logic was executing
unconditionally, which is unsafe during bootstrap because catalog and
syscache state are not fully initialized. I fixed this by guarding the
virtual generated column logic to skip the logic when
IsBootstrapProcessingMode() is true so that it can prevent the
generated-column rewrite from executing during bootstrap. After the
fix, initdb no longer crashes in bootstrap mode, the full regression
test suite passes cleanly, the server starts normally and key
behaviors like index creation on virtual generated columns, planner
rewrite, dependency tracking, partitioned table support, and
dump/restore roundtrips are all correct. Also I performed some
additional validations to ensure consistent behaviours. With the
bootstrap guard in place, the patch seems functionally correct,
catalog-safe, and dump/restore safe.
Kindly review the patch. Looking forward to more feedback.

Regards,
Soumya

--0000000000004af7ec064bcbeedf
Content-Type: text/x-patch; charset="US-ASCII"; 
	name="0001-skip-virtual-generated-column-handling-during-bootst.patch"
Content-Disposition: attachment; 
	filename="0001-skip-virtual-generated-column-handling-during-bootst.patch"
Content-Transfer-Encoding: base64
Content-ID: <f_mm4r4ufl0>
X-Attachment-Id: f_mm4r4ufl0

RnJvbSAxZDgzNzhlZDA1ZTM1YzMwM2ExYWQ4MTIwYTU5YmNhOGExMTU3NTU0IE1vbiBTZXAgMTcg
MDA6MDA6MDAgMjAwMQpGcm9tOiBTb3VteWEgPHNvdW15YW11cmFsaS53b3JrQGdtYWlsLmNvbT4K
RGF0ZTogRnJpLCAyNyBGZWIgMjAyNiAxNjowMDowNSArMDUzMApTdWJqZWN0OiBbUEFUQ0hdIHNr
aXAgdmlydHVhbCBnZW5lcmF0ZWQgY29sdW1uIGhhbmRsaW5nIGR1cmluZyBib290c3RyYXAKClNp
Z25lZC1vZmYtYnk6IFNvdW15YSA8c291bXlhbXVyYWxpLndvcmtAZ21haWwuY29tPgotLS0KIHNy
Yy9iYWNrZW5kL2NvbW1hbmRzL2luZGV4Y21kcy5jIHwgNCArKystCiAxIGZpbGUgY2hhbmdlZCwg
MyBpbnNlcnRpb25zKCspLCAxIGRlbGV0aW9uKC0pCgpkaWZmIC0tZ2l0IGEvc3JjL2JhY2tlbmQv
Y29tbWFuZHMvaW5kZXhjbWRzLmMgYi9zcmMvYmFja2VuZC9jb21tYW5kcy9pbmRleGNtZHMuYwpp
bmRleCA3NTc5YWZmZmNjOS4uNzg3NWUxNzhhZWEgMTAwNjQ0Ci0tLSBhL3NyYy9iYWNrZW5kL2Nv
bW1hbmRzL2luZGV4Y21kcy5jCisrKyBiL3NyYy9iYWNrZW5kL2NvbW1hbmRzL2luZGV4Y21kcy5j
CkBAIC0xOTkxLDcgKzE5OTEsOSBAQCBDb21wdXRlSW5kZXhBdHRycyhQYXJzZVN0YXRlICpwc3Rh
dGUsCiAJICogaW5kZXhlcy4gV2UgdGhlcmVmb3JlIGNvbGxlY3QgdGhlIHZpcnR1YWwgZ2VuZXJh
dGVkIGNvbHVtbnMgYXR0cmlidXRlCiAJICogbnVtYmVyIGZvciBzdWJzZXF1ZW50IHZlcmlmaWNh
dGlvbiBvZiBleHByZXNzaW9uIGF0dHJpYnV0ZXMuCiAJICovCi0JaWYgKHJlbHR1cGxkZXNjLT5j
b25zdHIgJiYgcmVsdHVwbGRlc2MtPmNvbnN0ci0+aGFzX2dlbmVyYXRlZF92aXJ0dWFsKQorCWlm
ICghSXNCb290c3RyYXBQcm9jZXNzaW5nTW9kZSgpICYmCisgICAgCXJlbHR1cGxkZXNjLT5jb25z
dHIgJiYKKyAgICAJcmVsdHVwbGRlc2MtPmNvbnN0ci0+aGFzX2dlbmVyYXRlZF92aXJ0dWFsKQog
CXsKIAkJZm9yIChpbnQgaSA9IDA7IGkgPCByZWx0dXBsZGVzYy0+bmF0dHM7IGkrKykKIAkJewot
LSAKMi4zNC4xCgo=
--0000000000004af7ec064bcbeedf--