Re: Conflict Detection and Resolution

On Mon, Jun 17, 2024 at 4:18 AM Tomas Vondra
<tomas.vondra@enterprisedb.com> wrote:
>
> On 6/14/24 13:29, Amit Kapila wrote:
> > On Fri, Jun 14, 2024 at 12:10 AM Robert Haas <robertmhaas@gmail.com> wrote:
> >>
> >> On Thu, May 23, 2024 at 2:37 AM shveta malik <shveta.malik@gmail.com> wrote:
> >>> c) update_deleted: The row with the same value as that incoming
> >>> update's key does not exist. The row is already deleted. This conflict
> >>> type is generated only if the deleted row is still detectable i.e., it
> >>> is not removed by VACUUM yet. If the row is removed by VACUUM already,
> >>> it cannot detect this conflict. It will detect it as update_missing
> >>> and will follow the default or configured resolver of update_missing
> >>> itself.
> >>
> >> I think this design is categorically unacceptable. It amounts to
> >> designing a feature that works except when it doesn't. I'm not exactly
> >> sure how the proposal should be changed to avoid depending on the
> >> timing of VACUUM, but I think it's absolutely not OK to depend on the
> >> timing of VACUUm -- or, really, this is going to depend on the timing
> >> of HOT-pruning, which will often happen almost instantly.
> >>
> >
> > Agreed, above Tomas has speculated to have a way to avoid vacuum
> > cleaning dead tuples until the required changes are received and
> > applied. Shveta also mentioned another way to have deads-store (say a
> > table where deleted rows are stored for resolution) [1] which is
> > similar to a technique used by some other databases. There is an
> > agreement to not rely on Vacuum to detect such a conflict but the
> > alternative is not clear.
>
> I'm not sure I'd say I "speculated" about it - it's not like we don't
> have ways to hold off cleanup for a while for various reasons
> (long-running query, replication slot, hot-standby feedback, ...).
>
> How exactly would that be implemented I don't know, but it seems like a
> far simpler approach than inventing a new "dead store". It'd need logic
> to let the vacuum to cleanup the stuff no longer needed, but so would
> the dead store I think.
>

The difference w.r.t the existing mechanisms for holding deleted data
is that we don't know whether we need to hold off the vacuum from
cleaning up the rows because we can't say with any certainty whether
other nodes will perform any conflicting operations in the future.
Using the example we discussed,
Node A:
  T1: INSERT INTO t (id, value) VALUES (1,1);
  T2: DELETE FROM t WHERE id = 1;

Node B:
  T3: UPDATE t SET value = 2 WHERE id = 1;

Say the order of receiving the commands is T1-T2-T3. We can't predict
whether we will ever get T-3, so on what basis shall we try to prevent
vacuum from removing the deleted row? The one factor could be time,
say we define a new parameter vacuum_committs_age which would indicate
that we will allow rows to be removed only if the modified time of the
tuple as indicated by committs module is greater than the
vacuum_committs_age. This needs more analysis if we want to pursue
this direction.

OTOH, in the existing mechanisms, there is a common factor among all
which is that we know that there is some event that requires data to
be present. For example, with a long-running query, we know that the
deleted/updated row is still visible for some running query. For
replication slots, we know that the client will acknowledge the
feedback in terms of LSN using which we can allow vacuum to remove
rows. Similar to these hot_standby_feedback allows the vacuum to
prevent row removal based on current activity (the xid horizons
required by queries on standby) on hot_standby.

> > Currently, we are thinking to consider such
> > a conflict type as update_missing (The row with the same value as that
> > incoming update's key does not exist.). This is how the current HEAD
> > code behaves and LOGs the information (logical replication did not
> > find row to be updated ..).
> >
>
> I thought the agreement was we need both conflict types to get sensible
> behavior, so proceeding with just the update_missing (mostly because we
> don't know how to detect these conflicts reliably) seems like maybe not
> be the right direction ...
>

Fair enough. I am also not in favor of ignoring this but if as a first
step, we want to improve our current conflict detection mechanism and
provide the stats or conflict information in some catalog or view, we
can do that even if update_delete is not detected. For example, as of
now, we only detect update_missing and simply LOG it at DEBUG1 level.
Additionally, we can detect update_differ (the row updated by a
different origin) and have some stats. We seem to have some agreement
that conflict detection and stats about the same could be the first
step.

--
With Regards,
Amit Kapila.