Block a row in sql after an update, and trigger a new update if necessary

Learning from masters 0
Viewed 21

I have a table in a postgresql database where there are 4 columns:

  1. id (serial)
  2. total (int)
  3. done (int)
  4. status (bool).

Now, I have some asyncronous processes that do some work and update this table. Basically, they add 1 to the done field, but I also want to update the status to True if total=done after upgrading done.

However, these processes can have the same id, and therefore I should block all the transaction (Update done, check done=total and update status if required) to avoid any problem. How can I do this?

I'm working with python3 and psycopg2.

psycopg2 sql postgresql python
1 Answers

If you are using v12 or greater the define status as a generated column. status boolean generated always as (total = done). But make sure none of your DML sets or inserts a value for status

Belayer0
Related