0

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.

thanks

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.

  • An UPDATE doesn't lock the table, it only locks the rows that are updated and it certainly won't block an INSERT statements during that time – a_horse_with_no_name Oct 6 '18 at 14:22
  • If recID is a PK the additional recordTime constraint might not be needed, did you try to drop it, it might help to have less locking on an index. – eckes Oct 6 '18 at 16:27
  • If you are getting deadlock errors, you should show us what the error message says. – jjanes Oct 6 '18 at 19:02
  • If your UPDATE query runs longer with "shared access", that means that your UPDATE does not only touch yesterday's rows, but also other rows that are modified concurrently. INSERTs would only cause "lock waits" if the UPDATE modifies the primary key (or other unique keys) of the table while other transactions are trying to insert the same combination of (unique) keys. – a_horse_with_no_name Oct 8 '18 at 12:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.