Postgres transaction isolation issue ?
I am facing one issue. I have 3 IP. 1,2 are master and slave and 3 for pgpool. My application direct hit to 3 ,and 3 distribute the connection between master and slave. But in my web application have one function. This function have one update and same update value select in a function.
Issues is when this function execute in master server there is no issue. But when execute in slave ,update command send to master server but when same transaction read the data from slave server. Result will be no data found.
It deals with transaction isolation
I guess begin of the transaction (default transaction isolation is “Read committed”) is issued on the slave and then update was sent to master and when updated the transaction is not yet committed in the slave and the slave is trying to select the change from the latest update.
In fact actually no transaction happened on the slave
I assume if we change the default isolation to “Read uncommitted” it might work (not so sure)
Instead there is another approach, assign a user who executes that function common to master and slave then change isolation level to that user and check
If There is only one user postgres, Create a user and assign its no big deal.