Hello all,
I'm facing what seems like a bug and I've reached a dead end without finding a solution.
I'm executing a script that work on a postgresql table. This script is executed more than once at a time.
The table contains a column called status.
One of the scripts is checking the value on the column status and, if it's different than 1, it should do nothing. If it's 1 it changes its value to the new status (new value can be 1,2,3)
I provide logs from postgresql:
2020-04-24 02:27:02.694 CEST [704] uxie@log_central LOG: statement: SELECT * FROM table1 WHERE column1='XXXXXXXXXXX' ORDER BY creation DESC LIMIT 1
2020-04-24 02:27:02.698 CEST [704] uxie@log_central LOG: statement: UPDATE table1 SET column2=to_timestamp(1587688014),status=2 WHERE column1= 'XXXXXXXXXXX ' AND column3='YYYYYYYYYYYY'
2020-04-24 02:27:02.759 CEST [735] uxie@log_central LOG: statement: SELECT * FROM table1 WHERE column1='XXXXXXXXXXX' AND column3= 'YYYYYYYYYYYY' ORDER BY creation DESC LIMIT 1
2020-04-24 02:27:02.762 CEST [735] uxie@log_central LOG: statement: UPDATE table1 SET column2=to_timestamp(1587688014),status=1 WHERE column1= 'XXXXXXXXXXX' AND column3='YYYYYYYYYYYY'
I did some verbose from the script in order to confirm the value OldStatusVal (which is status received with the SELECT query), and returned the following:
2020-04-24 02:27:02,762 2020-04-24 02:26:54 - Column3: VAR3 - Old Status: 1 - New Status: 1
So the SELECT is taking a value (Old Status) that should be evaluated to 2, but it returns 1 instead.
Could it be possible that, somehow, the select query starts before the update one, although it's printed as if it's being executed after it?
I'd love to know why it happens in order to understand how postgresql queries work and find a solution to it.
Thank you,
Eudald