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 1uRYfV-00DKMr-3b for pgsql-hackers@arkaria.postgresql.org; Tue, 17 Jun 2025 15:56:13 +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 1uRYfS-00AweO-E6 for pgsql-hackers@arkaria.postgresql.org; Tue, 17 Jun 2025 15:56:11 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1uRYfS-00Awbm-2A for pgsql-hackers@lists.postgresql.org; Tue, 17 Jun 2025 15:56:10 +0000 Received: from mail-vs1-xe2b.google.com ([2607:f8b0:4864:20::e2b]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.96) (envelope-from ) id 1uRYfQ-002ZRv-19 for pgsql-hackers@postgresql.org; Tue, 17 Jun 2025 15:56:10 +0000 Received: by mail-vs1-xe2b.google.com with SMTP id ada2fe7eead31-4e77d1333aeso1578141137.0 for ; Tue, 17 Jun 2025 08:56:07 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1750175765; x=1750780565; darn=postgresql.org; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:from:to:cc:subject:date:message-id:reply-to; bh=xyZt97YCEeWHa0Y91+ASOe6L8C7vixCB9QsgAZmxtFs=; b=EfU5c+Pqme2+WsvmwUBTN8+QnvSXX8soFqg7CWovch5NP8SbBmYc3ZelVm5V1CCYik vYq66aK2kzf3V8tasHJHHGsiaR5p5DOEucmZjOcdvVZbqk+ddZ/q0MwNspTlL5k2ApST FFBcWU/NbSZKoGZDKGYhwkWe+3VVV8rhnNhWbcpf2T6RZM3prCaQlGVsxIK9hBo1LJJV T7TMpzWuasxswb0AYXsxr18cZzWorlBYz6O+T+ZFEtFRjo+Zkmlv1oQLpMTu26cnGbKt +En2D5OriEVOv87izFdlNl5g6T0VhdsrDGgaIdj5AyuFzNWM37B+oZ+YtZuRI0nrCyw5 u42g== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1750175765; x=1750780565; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:x-gm-message-state:from:to:cc:subject:date:message-id :reply-to; bh=xyZt97YCEeWHa0Y91+ASOe6L8C7vixCB9QsgAZmxtFs=; b=pEdme+50Jwppcj5EWg6t60PPnv1PQItNdVyXm7ogabKAZrGjF4+RhoOmVDJ/ppg48N o7NtWwNmDEVw3w3HTrYjYR0XHrT3CemNaoN5pAakIFTj2luomLIGlQJc54bKAG1pRBhf DWib29mj4GsuN784Fb4Sq2JebBnScfK8t5dHQZk2iwStSRlIwfxcaBx9RGD+8nacMeA3 FmZTKpKgPvkLIERHcHYdRHaAQ+pu2RhI0xoSApLdOicn0pbJ/pwdkkPoGCD0fmUmyQTh E/DzJQ2WmaOxqTNsZM9PjlGKjVeEVLZsP70jYBag44R8dhZjgwkPCA9j8KIeEZsCa4kW iwUA== X-Forwarded-Encrypted: i=1; AJvYcCUQIPsLIr3YCRbgOLpvmLiDozNSHjCzAoxKBzat/QIvmCqOpSmNTlNo0Pdtx5md0qlF1dtJ8B8n3DBbrjUx@postgresql.org X-Gm-Message-State: AOJu0YxIijO8c3xlhCV5bJHtlxCqaSnzsjps0Euxn+JDhRtWdOBQ8NtE mewY8wQkdi7FYSPUMI0oyEKebYcvTMNFHVqkAgBSfO7heICEbMYW2vmFbpIYzbIXJK+7l8+/Dtn 5vS5gIlf84nitDYTqc7zR3kLx0H1qbpM= X-Gm-Gg: ASbGncsPd8DlXM/I6Jpxe3y5g0nsSwdNkFtLIRckaRVVs91sPxkCBFhfJ7j8Iz+HLLs +wk+g/kPLmgAWMR7yNe3AweuJGHnE+zqmPAMUyTRTlpZf4OJOf9SVX7NRHuYSDIQt/N1aIMVoIv MsJ3dasXcifvkuWleY3RCCyvv8e8umbDTywrob8QXUWprrctZFYPxj8fq3 X-Google-Smtp-Source: AGHT+IHPx6PWfS6/lkj/LFCaiHvqnhsKQNJ+Vh4ZhsN48URIc9j5s0Yh8f8WOBuP3zFafVY+bv1L81XQ9RZaTkMz3Bo= X-Received: by 2002:a05:6102:6f0b:b0:4e9:8488:ceb0 with SMTP id ada2fe7eead31-4e98488d207mr1466759137.15.1750175765325; Tue, 17 Jun 2025 08:56:05 -0700 (PDT) MIME-Version: 1.0 References: <202505181556.3n6oiowvntyr@alvherre.pgsql> In-Reply-To: From: Sergey Sargsyan Date: Tue, 17 Jun 2025 18:55:54 +0300 X-Gm-Features: Ac12FXyrZ6EUt81O0Jz9VieVShb2hJ5eBRYGOd4rlKy0pTeMlOp6kCJxar3TKes Message-ID: Subject: Re: Revisiting {CREATE INDEX, REINDEX} CONCURRENTLY improvements To: Mihail Nikalayeu Cc: =?UTF-8?Q?=C3=81lvaro_Herrera?= , Andres Freund , Michael Paquier , PostgreSQL Hackers , Andrey Borodin , Melanie Plageman , Matthias van de Meent Content-Type: multipart/alternative; boundary="000000000000c96ddc0637c68ccc" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000c96ddc0637c68ccc Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable Hello Mihail, In patch v20-0006-Add-STIR-access-method-and-flags-related-to-auxi.patch, within the "StirMarkAsSkipInserts" function, a critical section appears to be left unclosed. This resulted in an assertion failure during ANALYZE of a table containing a leftover STIR index. Best regards, Sergey On Mon, Jun 16, 2025, 11:21=E2=80=AFPM Sergey Sargsyan < sergey.sargsyan.2001@gmail.com> wrote: > Thank you for the information. Tomorrow, I will also run a few tests to > measure the time required to collect tids from the index; however, since = I > do not work with vanilla postgres, the results may vary. > > If the results indicate that this procedure is time-consuming, I maybe > will develop an additional patch specifically for b-tree indexes, as they > are the default and most commonly used type. > > Best regards, > Sergey > > > On Mon, Jun 16, 2025, 11:01=E2=80=AFPM Mihail Nikalayeu > wrote: > >> Hello, Sergey! >> >> > I think it's to avoid duplicate errors when adding tuples from STIP to >> the main index, >> > but couldn't we just suppress that error during validation and skip th= e >> new tuple insertion if it already exists? >> >> In some cases, it is not possible: >> =E2=80=93 Some index types (GiST, GIN, BRIN) do not provide an easy way = to >> detect such duplicates. >> =E2=80=93 When we are building a unique index, we cannot simply skip >> duplicates, because doing so would also skip the rows that should >> prevent the unique index from being created (unless we add extra logic >> for B-tree indexes to compare TIDs as well). >> >> > The main index may get huge after building, and iterating over it in a >> single thread and then sorting tids can be time consuming. >> My tests indicate that the overhead is minor compared with the time >> spent scanning the heap and building the index itself. >> >> > At least I guess one can skip it when STIP is empty. >> Yes, that=E2=80=99s a good idea; I=E2=80=99ll add it later. >> >> > p.s. I noticed that `stip.c` has a lot of functions that don't follow >> the Postgres coding style of return type on separate line. >> Hmm... I=E2=80=99ll fix that as well. >> >> Best regards, >> Mikhail. >> > --000000000000c96ddc0637c68ccc Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Hello=C2=A0Mihail,

In patch v20-0006-Add-STIR-access-method-and-flags-related-to-auxi.p= atch, within the "StirMarkAsSkipInserts" function, a critical sec= tion appears to be left unclosed. This resulted in an assertion failure du= ring ANALYZE of a table containing a leftover STIR index.

Best regards,=C2=A0
Sergey

On Mon, Jun 16, 2025, 11:21=E2=80=AFPM Sergey Sargsyan <= sergey.sargsyan.2001@gmail.com> wrote:
Thank you for the = information. Tomorrow, I will also run a few tests to measure the time req= uired to collect tids from the index; however, since I do not work with van= illa postgres, the results may vary.

If the results indicate that this procedure = is time-consuming, I maybe will develop an additional patch specifically fo= r b-tree indexes, as they are the default and most commonly used type.

Best regards,=C2=A0
Sergey


On Mon, Jun 16, 2025, 11:01=E2=80=AFPM Mihail Nika= layeu <mihailnikalayeu@gmail.com> wr= ote:
Hello, Sergey!

> I think it's to avoid duplicate errors when adding tuples from STI= P to the main index,
> but couldn't we just suppress that error during validation and ski= p the new tuple insertion if it already exists?

In some cases, it is not possible:
=E2=80=93 Some index types (GiST, GIN, BRIN) do not provide an easy way to<= br> detect such duplicates.
=E2=80=93 When we are building a unique index, we cannot simply skip
duplicates, because doing so would also skip the rows that should
prevent the unique index from being created (unless we add extra logic
for B-tree indexes to compare TIDs as well).

> The main index may get huge after building, and iterating over it in a= single thread and then sorting tids can be time consuming.
My tests indicate that the overhead is minor compared with the time
spent scanning the heap and building the index itself.

> At least I guess one can skip it when STIP is empty.
Yes, that=E2=80=99s a good idea; I=E2=80=99ll add it later.

> p.s. I noticed that `stip.c` has a lot of functions that don't fol= low the Postgres coding style of return type on separate line.
Hmm... I=E2=80=99ll fix that as well.

Best regards,
Mikhail.
--000000000000c96ddc0637c68ccc--