Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1rkq4g-00FoXp-A1 for pgsql-novice@arkaria.postgresql.org; Thu, 14 Mar 2024 18:45:08 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1rkq4e-002IFN-BD for pgsql-novice@arkaria.postgresql.org; Thu, 14 Mar 2024 18:45:04 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1rkq4c-002IFA-3b for pgsql-novice@lists.postgresql.org; Thu, 14 Mar 2024 18:45:04 +0000 Received: from fout4-smtp.messagingengine.com ([103.168.172.147]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1rkq4U-004Vfj-Sp for pgsql-novice@lists.postgresql.org; Thu, 14 Mar 2024 18:45:01 +0000 Received: from compute3.internal (compute3.nyi.internal [10.202.2.43]) by mailfout.nyi.internal (Postfix) with ESMTP id ED508138011B for ; Thu, 14 Mar 2024 14:44:52 -0400 (EDT) Received: from imap47 ([10.202.2.97]) by compute3.internal (MEProxy); Thu, 14 Mar 2024 14:44:52 -0400 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=de.me.tz; h=cc :content-type:content-type:date:date:from:from:in-reply-to :message-id:mime-version:reply-to:subject:subject:to:to; s=fm2; t=1710441892; x=1710528292; bh=L0t7KYUBGShMNimSBQ8C+lhFrRF/hnSw NWcnDtBaEzk=; b=Ut9hraAaFgGCo4zgteF6TlxCZ3A0OBYsLAdCGjE6FG3xUyIR +o5SGJk7agZoUKATRmyXxTY2n0qWsjlMlTteDpH86jpqvJviJhV72gx6S5xFEmFD JyyEG5HAn+Rd3I/1j2iWGeMNZC7yAIloHjGCjhm/RUuC92AksNojvLSIba24pdeV cIu1NH6K60tC8tpXW28ybSsOMllaKnH9uPepk59fo66bY/FAKQdA4OEVf7Fo7M4t Sj6qwCwZFbU3Gw+r9WLsij2ESIMSBiYcC/iX9UF3lmtpit4H4u1fob0W13qsiQ4z u0tYnq00s15aU13h918rXZd0kJ3mmeIorI9eYw== DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d= messagingengine.com; h=cc:content-type:content-type:date:date :feedback-id:feedback-id:from:from:in-reply-to:message-id :mime-version:reply-to:subject:subject:to:to:x-me-proxy :x-me-proxy:x-me-sender:x-me-sender:x-sasl-enc; s=fm1; t= 1710441892; x=1710528292; bh=L0t7KYUBGShMNimSBQ8C+lhFrRF/hnSwNWc nDtBaEzk=; b=Bs7SL0SOeuxQ835BKe/jdu/FzbL8S1AFvCmIZ0yzBrrMJympdTw k63/DXFetyLQ0pyKDrxk6CC8i/CndMz94Z748UNH4TVGm296LL5ptJXLAK8TbCA0 iXpX5+DV5Md/4FbswNuDcz61PlEahlDBPWJrFgb4uQoPgi3QEcjOtsqtQRlwYeAE Kd82Uc9VgsIGHmJed8RGr+49CZolL0ektlE7+MzLQ/1TxYoYZb4ugGv4FCDFdcr0 o7lgq99VvoU5rDWolGi9/XH+gJ72ITUKqWpp7Ixpr6CzMniE0EqjBnXYsAwQ8ixj bZa8xJ9uoTMYt0o7WVza2bZJmOv25xJn9LA== X-ME-Sender: X-ME-Proxy-Cause: gggruggvucftvghtrhhoucdtuddrgedvledrjeejgdduudefucetufdoteggodetrfdotf fvucfrrhhofhhilhgvmecuhfgrshhtofgrihhlpdfqfgfvpdfurfetoffkrfgpnffqhgen uceurghilhhouhhtmecufedttdenucenucfjughrpefofgggkfffhffvufgtsegrtderre erreejnecuhfhrohhmpedfsfhuvghnthhinhcuuggvucfovghtiidfuceoqhhuvghnthhi nhesuggvrdhmvgdrthiiqeenucggtffrrghtthgvrhhnpeffkeetueehkeekffeggffgle fgtdehtddtkeeiffetffejgeetledtgeegveevffenucevlhhushhtvghrufhiiigvpedt necurfgrrhgrmhepmhgrihhlfhhrohhmpehquhgvnhhtihhnseguvgdrmhgvrdhtii X-ME-Proxy: Feedback-ID: i7a6842ed:Fastmail Received: by mailuser.nyi.internal (Postfix, from userid 501) id 7EF06A60078; Thu, 14 Mar 2024 14:44:52 -0400 (EDT) X-Mailer: MessagingEngine.com Webmail Interface User-Agent: Cyrus-JMAP/3.11.0-alpha0-287-gcf6d0d8ecf-fm-20240312.001-gcf6d0d8e MIME-Version: 1.0 Message-Id: <45e27182-8474-4dc5-be7b-adb87041a111@app.fastmail.com> Date: Thu, 14 Mar 2024 19:44:31 +0100 From: "Quentin de Metz" To: pgsql-novice@lists.postgresql.org Subject: Sharp edge when dropping columns Content-Type: multipart/alternative; boundary=944adb97106448a28be499d21a86a26f List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --944adb97106448a28be499d21a86a26f Content-Type: text/plain;charset=utf-8 Content-Transfer-Encoding: quoted-printable [This is my first message on a PG mailing list, here's hoping I'm not br= eaching etiquette somehow] Bonjour, I'd like to report a sharp edge which caused downtime in my production a= pplication: dropping a column which is INCLUDEd in an index drops the in= dex.=20 In the past, I have included a field in some of my indexes so that relat= ed queries can be performed as an index-only scan. This field was later = deprecated. When the related column was ultimately dropped, the performa= nce of the initial queries ground to a halt because the underlying index= had been dropped. I found this behavior unexpected. I think preventing the index from bein= g dropped in some circumstances would make this less surprising. The exa= ct circumstances likely need to be hashed out if there is a consensus th= at this is a (minor) problem. Please find below a simple reproduction script: =3D# CREATE TABLE test (id bigint, label text); CREATE TABLE =3D# CREATE INDEX pkey_with_label ON test USING btree (id) INCLUDE (labe= l); CREATE INDEX =3D# \d test Table "public.test" =E2=94=8C=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80= =E2=94=80=E2=94=AC=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80= =E2=94=80=E2=94=80=E2=94=AC=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80= =E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=AC=E2=94=80= =E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80= =E2=94=80=E2=94=AC=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80= =E2=94=80=E2=94=80=E2=94=80=E2=94=90 =E2=94=82 Column =E2=94=82 Type =E2=94=82 Collation =E2=94=82 Nullable= =E2=94=82 Default =E2=94=82 =E2=94=9C=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80= =E2=94=80=E2=94=BC=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80= =E2=94=80=E2=94=80=E2=94=BC=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80= =E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=BC=E2=94=80= =E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80= =E2=94=80=E2=94=BC=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80= =E2=94=80=E2=94=80=E2=94=80=E2=94=A4 =E2=94=82 id =E2=94=82 bigint =E2=94=82 =E2=94=82 = =E2=94=82 =E2=94=82 =E2=94=82 label =E2=94=82 text =E2=94=82 =E2=94=82 = =E2=94=82 =E2=94=82 =E2=94=94=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80= =E2=94=80=E2=94=B4=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80= =E2=94=80=E2=94=80=E2=94=B4=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80= =E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=B4=E2=94=80= =E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80= =E2=94=80=E2=94=B4=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80= =E2=94=80=E2=94=80=E2=94=80=E2=94=98 Indexes: "pkey_with_label" btree (id) INCLUDE (label) =3D# ALTER TABLE test DROP COLUMN label; ALTER TABLE =3D# \d test Table "public.test" =E2=94=8C=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80= =E2=94=80=E2=94=AC=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80= =E2=94=80=E2=94=80=E2=94=AC=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80= =E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=AC=E2=94=80= =E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80= =E2=94=80=E2=94=AC=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80= =E2=94=80=E2=94=80=E2=94=80=E2=94=90 =E2=94=82 Column =E2=94=82 Type =E2=94=82 Collation =E2=94=82 Nullable= =E2=94=82 Default =E2=94=82 =E2=94=9C=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80= =E2=94=80=E2=94=BC=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80= =E2=94=80=E2=94=80=E2=94=BC=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80= =E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=BC=E2=94=80= =E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80= =E2=94=80=E2=94=BC=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80= =E2=94=80=E2=94=80=E2=94=80=E2=94=A4 =E2=94=82 id =E2=94=82 bigint =E2=94=82 =E2=94=82 = =E2=94=82 =E2=94=82 =E2=94=94=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80= =E2=94=80=E2=94=B4=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80= =E2=94=80=E2=94=80=E2=94=B4=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80= =E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=B4=E2=94=80= =E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80= =E2=94=80=E2=94=B4=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80= =E2=94=80=E2=94=80=E2=94=80=E2=94=98 Thank you for reading, Quentin de Metz --944adb97106448a28be499d21a86a26f Content-Type: text/html;charset=utf-8 Content-Transfer-Encoding: quoted-printable
[This is my fir= st message on a PG mailing list, here's hoping I'm not breaching etiquet= te somehow]

Bonjour,

I'd like to report a sharp edge which caused downtime in my produ= ction application: dropping a column which is INCLUDEd in an index drops= the index.

In the past, I have included a= field in some of my indexes so that related queries can be performed as= an index-only scan. This field was later deprecated. When the related c= olumn was ultimately dropped, the performance of the initial queries gro= und to a halt because the underlying index had been dropped.

I found this behavior unexpected. I think preventing t= he index from being dropped in some circumstances would make this less s= urprising. The exact circumstances likely need to be hashed out if there= is a consensus that this is a (minor) problem.

=
Please find below a simple reproduction script:

<= /div>
=3D# CREATE TABLE test (id bigint, label text);
CREATE TABLE

=3D= # CREATE INDEX pkey_with_label ON test USING btree (id) INCLUDE (label);=
CREATE INDEX

=3D# \d test
Table = "public.test"
=E2=94=8C= =E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80= =E2=94=AC=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80= =E2=94=80=E2=94=AC=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80= =E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=AC=E2=94=80=E2=94=80= =E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80= =E2=94=AC=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80= =E2=94=80=E2=94=80=E2=94=90
=E2=94=82 Column =E2=94=82 Type   =E2=94=82 Collation =E2= =94=82 Nullable =E2=94=82 Default =E2=94=82
=
=E2=94=9C=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94= =80=E2=94=80=E2=94=80=E2=94=80=E2=94=BC=E2=94=80=E2=94=80=E2=94=80=E2=94= =80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=BC=E2=94=80=E2=94=80=E2=94= =80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94= =80=E2=94=BC=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94= =80=E2=94=80=E2=94=80=E2=94=80=E2=94=BC=E2=94=80=E2=94=80=E2=94=80=E2=94= =80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=A4
=E2=94=82 id    = =E2=94=82 bigint =E2=94=82       &nb= sp;   =E2=94=82        = ;  =E2=94=82         =E2=94= =82
=E2=94=82 label&nb= sp; =E2=94=82 text   =E2=94=82     &nbs= p;     =E2=94=82      =     =E2=94=82       &n= bsp; =E2=94=82
=E2=94=94= =E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80= =E2=94=B4=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80= =E2=94=80=E2=94=B4=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80= =E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=B4=E2=94=80=E2=94=80= =E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80= =E2=94=B4=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80= =E2=94=80=E2=94=80=E2=94=98
Indexes:
"pkey_w= ith_label" btree (id) INCLUDE (label)
<= div>
=3D# ALTER TABLE test DR= OP COLUMN label;
ALTER= TABLE

=3D# \d test
Table "public.test"
=E2=94=8C=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2= =94=80=E2=94=80=E2=94=AC=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2= =94=80=E2=94=80=E2=94=80=E2=94=AC=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2= =94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=AC=E2= =94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2= =94=80=E2=94=80=E2=94=AC=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2= =94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=90
<= /div>
=E2=94=82 Column =E2=94=82 Type   =E2=94= =82 Collation =E2=94=82 Nullable =E2=94=82 Default =E2=94=82
=E2=94=9C=E2=94=80=E2=94=80=E2=94= =80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=BC=E2=94=80=E2=94= =80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=BC=E2=94= =80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94= =80=E2=94=80=E2=94=80=E2=94=BC=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94= =80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=BC=E2=94=80=E2=94= =80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94= =A4
=E2=94=82 id  = ;   =E2=94=82 bigint =E2=94=82     &nb= sp;     =E2=94=82      = ;    =E2=94=82       &= nbsp; =E2=94=82
=E2=94= =94=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94= =80=E2=94=B4=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94= =80=E2=94=80=E2=94=B4=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94= =80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=B4=E2=94=80=E2=94= =80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94= =80=E2=94=B4=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94= =80=E2=94=80=E2=94=80=E2=94=98

Thank= you for reading,

Quentin de Metz
= --944adb97106448a28be499d21a86a26f--