Re: Upsert functions spinlock when doing insert first.

"Porte, Alexander" <Alexander(dot)Porte(at)agilysys(dot)com> writes:
> We have simple upsert functions part of our ETL process. These upsert functions execute an insert and update from staging to datawarehouse tables and finally mark all these staged rows as processed, all within the same transaction.

> In our tests and production we have encountered problems where these functions get stuck at full CPU with zero disk I/O. We were able to repro the problem on small dataset (<100'000 rows) with a single thread. We fiddled with settings following best practices, adding large amount of memory, increasing WAL segments, etc... to no avail.

This is an interesting report, but you have not really provided enough
detail to let anyone else reproduce the problem. Without sample data
and a script for how to call the function, we're unlikely to duplicate
your results.

Also, as a general rule, we'd rather that supporting files were attached
to the bug report, so that they get into the pgsql-bugs archives. A month
from now when those privatepaste entries are gone, this report will mean
nothing to anyone.

regards, tom lane