Re: BUG #17478: Missing documents in the index after CREATE INDEX CONCURRENTLY (but existing in the table)

Hello,

Thank you for your answers.

I've upgraded the slave database to 14.2 and after rebuilding the primary
key concurrently, sadly but I have missing documents again.
The missing documents are created in the master within the time interval
while reindex is taking place in the slave, the table is big and it takes
around an hour to be rebuilt.

The same documents are visible in the table through other indexes:
For example the query:

SELECT * FROM table_name WHERE id = 5 and user_id = 1
UNION ALL
SELECT * FROM table_name WHERE id + 0 = 5 and user_id = 1

returns only one row(it must return 2):
the first query uses the primary key and does not find anything
the second query uses the index by user_id and finds the document with id=5

Tom, fixes in 13.5 and 13.6 are included in 14.2, aren't they? In my
opinion, the problem is in the slave host, which is running 14, not in the
master.

Thank you in advance!

Regards,
Stefan

On Mon, May 9, 2022 at 7:32 PM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Maxim Boguk <maxim(dot)boguk(at)gmail(dot)com> writes:
> > On Mon, May 9, 2022 at 5:58 PM PG Bug reporting form <
> noreply(at)postgresql(dot)org>
> > wrote:
> >> We’re observing a strange bug, according to me. We’ve got native logical
> >> replication where the master host is running on PostgreSQL 13.3 and the
> >> logical replica host is running on PostgreSQL 14.1. And the problem is:
> >> when we start to reindex concurrently the primary key of an 800M heavily
> >> inserted/updated(on the master)table on the logical replica database,
> >> several documents that have been inserted and existing in the master
> >> database during the index rebuild on the replica, are missing from the
> >> index
> >> structure on the replica host.
>
> > Please try to upgrade your slave to 14.2 where this problem was fixed (at
> > least it fixed the same issue for me).
>
> 13.6 might do as well, and would be a lot closer to a drop-in upgrade.
>
> The 13.5 and 13.6 release notes both mention problems with REINDEX
> CONCURRENTLY. Given the existence of those fixed bugs, nobody is
> likely to look too closely at this report unless you can still reproduce
> it on a current minor release.
>
> regards, tom lane
>