Is it possible to over-ride the locks that postgres chooses to create?
Here's the situation, I have a table something like this....
CREATE TABLE foo ( recId SERIAL PRIMARY KEY, recordTime TIMESTAMP, fieldA..... fieldB.... fieldC.... )
The table is appended to almost continuously throughout the day. (all day everyday) where recordTime is the time the record is added
Once a day I need to run a query to UPDATE the same table. Looks something like this...
WITH subquery AS ( SELECT blah, blah, blah FROM foo, bar, etc) UPDATE foo SET fieldX = subquery.result1, fieldY = subquery.result2 WHERE foo.recId = subquery.recId AND recordTime = yesterday
So the query is basically both selecting from and updating to the same table.
The UPDATE query runs just fine (takes about 5 minutes) when it has exclusive access to the table.
The problem is that in a live environment table foo is continuously being appended to, so the query can't run because it can't obtain a full lock on it (I think that's the problem - my understanding of deadlocks is hazy)
The UPDATE query is designed to process yesterdays records only, so to my way of thinking there's no reason it needs a full lock on the table. Is there anyway to allow the addition of new records whilst allowing the updating of yesterdays records? I could probably partition the table by day to achieve this, but was hoping to avoid this if there's a simpler solution.
Update in response to comments. Ok, I think it's back to the drawing board for me on this one. further tests show that with exclusive access to the DB the update query takes 4-5 minutes. With shared access to the database it either takes about 6 hours (9 times out of 10) or about 5 minutes. There are no reported errors.