On Mon, Jun 17, 2024 at 4:18 AM Tomas Vondra <tomas.von...@enterprisedb.com> wrote: > > On 6/14/24 13:29, Amit Kapila wrote: > > On Fri, Jun 14, 2024 at 12:10 AM Robert Haas <robertmh...@gmail.com> wrote: > >> > >> On Thu, May 23, 2024 at 2:37 AM shveta malik <shveta.ma...@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.
