On Mon, 14 Sep 2020 at 17:36, Michael Lewis <mlewis@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
tablerecord 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
tablethat 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