On Mon, 14 Sep 2020 at 17:36, Michael Lewis <mle...@entrata.com> 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 trigger, it would probably start adding locks to the WAL again. As the application knows the schema, this was not much of a problem. I initially did compare the whole row (not as a record, but just as i1.* IS DISTINCT FROM i2.*) which worked, but as there are quite some tables with a rather large primary key and almost no additional fields, I anticipated that it may have been faster to only compare the remaining fields (but I have not verified this). > I would probably delete records in the regular table that do not exist in the > temp table, then delete from the temp table that already exists in the main > table and then update the remaining rows. That's an interesting approach, it may be that shuffling around the insert/update/delete improves things indeed - at the moment I don't do anything with the temp table after it has been created. I'm a bit short on time at the moment, but may further look into this when I find some time for it. Thanks, Mike
