0

If I attempt to edit a record that may (or may not) exist, but get a timeout after a few seconds, can I take that as evidence that the record exists?

Timeouts occur if another transaction hasn't completed on a record, so if I get a timeout then another session must have a lock on that record, therefore the record must exist? Or is there another way a timeout might occur?

Some background.... I have a process that implements an 'upsert'. I attempt to update a record, if the record doesn't exist and the UPDATE fails then I attempt an INSERT. This works fine most of the time, but occasionally the UPDATE fails due to a timeout, then the INSERT fails due to unique constraint violation. Since both failures waste a bit of time, can I just assume that if the UPDATE fails due to a timeout, then there's no point in bothering with the INSERT?

UPDATE - as far as I can tell, there's nothing in the application that might LOCK the whole table (unless there's some background postgres system stuff going on that I don't know about)

2
  • 1
    An UPSERT should not be implemented using separate UPDATE and INSERT statements, but by using insert ... on conflict (..) do update
    – user1822
    Jun 30, 2022 at 9:52
  • There are all different kinds of timeout. What specific one are you getting?
    – jjanes
    Jun 30, 2022 at 15:03

1 Answer 1

1

Does a record in a transaction that was rolled back, or that was just deleted exist? or maybe the table was locked and the record never existed.

Postgresql is going to delay you until it can give an answer.

Some background.... I have a process that implements an 'upsert'. I attempt to update a record, if the record doesn't exist and the UPDATE fails then I attempt an INSERT. This works fine most of the time, but occasionally the UPDATE fails due to a timeout, then the INSERT fails due to unique constraint violation. Since both failures waste a bit of time, can I just assume that if the UPDATE fails due to a timeout, then there's no point in bothering with the INSERT?

Or you could use INSERT ... ON CONSTRAINT ... DO UPDATE ... https://www.postgresql.org/docs/14/sql-insert.html

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.