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