I am using
When I update row in a table my flow looks like this:
- Fetch old row (ex.
SELECT * FROM tbl WHERE id = 1)
- Validate new data in scope of old data (old row fetched in point 1) - this is done by my app outside of postgres.
- Update row with new data (ex.
UPDATE tbl SET .... WHERE id = 1)
The problem is that
old data may change during point 2 so validation, even if passed may be
outdated during point 3 (updating row). I know I could perform update like this
UPDATE .... WHERE id = 1 AND column1='oldValue' to ensure certain fields didn't change, however my validation (also comparising new and old values) is very complex and I can't "write" it in SQL UPDATE statement.
So I thought about something like this:
SELECT * FROM tbl WHERE id = 1
- Perform validation on my side
UPDATE SET=... WHERE id = 1
And I would like to
COMMIT fail if row with id = 1 changed during transaction execution. Is it possible to use transaction like this? If not what is other solution?