Re: Dirty buffers with suppress_redundant_updates_trigger

On Mon, 14 Sep 2020 at 06:03, Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at> wrote:
> These are probably the "hint bits" set on newly committed rows by the first reader.
> Note that te blocks are dirtied during the sequential scan, not during the update.
>
> You could try VACUUMing the tables before the update (which will set hint bits)
> and see if you still get the dirtied blocks.

Thanks. I thought about that as well, but unfortunately VACUUMing does
not seem to solve this. Even a full VACUUM does not prevent the dirty
blocks, and even very old/never updated but often read tables see this
behaviour.

In my sample, the plain update w/ suppress_redundant_updates_trigger()
does cause the dirty blocks, but if I do an update that compares the
fields, like this:

update testtable as d set name = s.name from testtable_temp as s where
d.id = s.id and d.name <> s.name;

then no blocks are dirtied. So it seems
suppress_redundant_updates_trigger() does not entirely avoid writing
_something_ to the blocks, and I don't know what it is and how to
avoid it. I would of course like to avoid having to specify every
column in the where clause.

Kind regards,

Mike

(sorry Laurenz, forgot to include the list in my initial reply)