Re: [BUG?] check_exclusion_or_unique_constraint false negative

Hello, everyone!

Issue description is available at [0] (in few words - SnapshotDirty
scan may miss tuple in index because of race condition with update of
that tuple).

But I have realised there are cases much more severe than invalid
conflict messages for logical replication - lost delete/updates in
logical replication.
New tests with reproducers are included in the new patch version.

Short description up issues:

1) Lost delete

Setup:
CREATE TABLE conf_tab(a int PRIMARY key, data text);
CREATE INDEX data_index ON conf_tab(data);
INSERT INTO conf_tab(a, data) VALUES (1,'frompub');

On publisher:
DELETE FROM conf_tab WHERE a=1;

On subscriber:
UPDATE conf_tab SET data = 'fromsubnew' WHERE (a=1);

Expected result:
Tuple is deleted on both subscriber and publisher.

Actual result:
Either as expected, or:
- Tuple is deleted on publisher, but 'fromsubnew' remains on subscriber.

2) Lost update

Setup:

On publisher:
CREATE TABLE conf_tab(a int PRIMARY key, data text);
INSERT INTO conf_tab(a, data) VALUES (1,'frompub');

On subscriber:
-- note additional subscriber-only column - i
CREATE TABLE conf_tab(a int PRIMARY key, data text, i int DEFAULT 0);
CREATE INDEX i_index ON conf_tab(i);

On publisher:
UPDATE conf_tab SET data = 'frompubnew' WHERE (a=1);

On subscriber:
UPDATE conf_tab SET i = 1 WHERE (a=1);

Expected result:
On subscriber: tuple (a=1, data='frompubnew', i=1).

Actual result:
Either as expected, or:
- Publisher update is lost, leaving (a=1, data='frompub', i=1) on subscriber.

Best regards,
Mikhail.

[0]: https://www.postgresql.org/message-id/flat/CADzfLwWuXh8KO%3DOZvB71pZnQ8nH0NYXfuGbFU6FBiVZUbmuFGg%40mail.gmail.com#76f98a9ae3479bbaf5ee9262322d466e