On 04/26/2018 01:32 PM, Vitaliy Garnashevich wrote:
Have not worked through all of the above, but a first draft suggestion:
Move the SELECT minus the aggregation functions into a sub-query that
uses FOR UPDATE. Then do the aggregation on the results of the sub-query.
The aggregated table has hundreds of millions of rows, and the query
runs for many hours (which is one of the reasons why it's better not to
fail). I really doubt that row level locking would work. That would be a
lot of RAM just to hold all the locks.
On the other hand, I don't see something like FOR KEY SHARE kind of
locks at table level, so that the query would try not to block most of
other existing activity (e.g. SELECTs, UPDATEs).
Maybe this could be solved by calculating results into a temporary
table, which would not check foreign key constraints, and then copy the
data into the actual results table, while checking each row for FK
consistency and skipping if necessary. But then I don't think it would
be possible for my transaction to see row deletions which other
transactions have done, and to check row existence (the transaction is
there, because the whole thing is implemented as a DO statement with
some local variables).
Thoughts?
The procedure seems to be fighting itself. There is an inherent conflict
between trying to keep up with data changes and presenting a consistent
result. Keeping up means constantly updating the aggregation
calculations which in turn means the result will continually changing.
As David and Alvaro have also suggested your best bet is to pick a point
in time and work off that.
Regards,
Vitaliy
--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx
