Search Postgresql Archives

Re: Long running INSERT+SELECT query

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 




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?

Regards,
Vitaliy





[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux