On Mon, 14 Sep 2020 at 17:36, Michael Lewis wrote:
> Just curious, are you doing this in a trigger or in your application code?
> Either way, I'd think you could use the table record type to compare the temp
> vs real table values as an entire unit.
Application code - if I would put it in a tri
For future reference, in the end I have just added a WHERE clause
comparing all fields with IS DISTINCT FROM. This seems to work well,
prevents any locks/WAL-generation and is about as fast as the previous
solution with suppress_redundant_updates_trigger().
Kind regards,
Mike
So it seems that when before triggers are handled, a SELECT FOR UPDATE
row-level lock is taken before the triggers are run. This causes a
write to the heap, as row-level locks are stored on-heap. This has the
unfortunate effect that suppress_redundant_updates_trigger() is not
able to prevent all wr
On Mon, 14 Sep 2020 at 06:32, Mike Noordermeer wrote:
> 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.
Looking at the pg_waldump output, it seems to write som
On Mon, 14 Sep 2020 at 06:03, Laurenz Albe 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 se
Hi,
I am currently working on a data sync solution, where data is synced
from external systems to Postgres. As I do not know what data changed
in the source data store, I have opted for the following flow:
- Load all data in a temp table
- Update existing rows main table based on contents of temp
