On 5/28/24 11:17, Nisha Moond wrote: > On Mon, May 27, 2024 at 11:19 AM shveta malik <shveta.ma...@gmail.com> wrote: >> >> On Sat, May 25, 2024 at 2:39 AM Tomas Vondra >> <tomas.von...@enterprisedb.com> wrote: >>> >>> ... >>> >>> I don't understand the why should update_missing or update_deleted be >>> different, especially considering it's not detected reliably. And also >>> that even if we happen to find the row the associated TOAST data may >>> have already been removed. So why would this matter? >> >> Here, we are trying to tackle the case where the row is 'recently' >> deleted i.e. concurrent UPDATE and DELETE on pub and sub. User may >> want to opt for a different resolution in such a case as against the >> one where the corresponding row was not even present in the first >> place. The case where the row was deleted long back may not fall into >> this category as there are higher chances that they have been removed >> by vacuum and can be considered equivalent to the update_ missing >> case. >> >> Regarding "TOAST column" for deleted row cases, we may need to dig >> more. Thanks for bringing this case. Let me analyze more here. >> > I tested a simple case with a table with one TOAST column and found > that when a tuple with a TOAST column is deleted, both the tuple and > corresponding pg_toast entries are marked as ‘deleted’ (dead) but not > removed immediately. The main tuple and respective pg_toast entry are > permanently deleted only during vacuum. First, the main table’s dead > tuples are vacuumed, followed by the secondary TOAST relation ones (if > available). > Please let us know if you have a specific scenario in mind where the > TOAST column data is deleted immediately upon ‘delete’ operation, > rather than during vacuum, which we are missing. >
I'm pretty sure you can vacuum the TOAST table directly, which means you'll end up with a deleted tuple with TOAST pointers, but with the TOAST entries already gone. regards -- Tomas Vondra EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company