How to get updated order data

On Thu, 2022-04-07 at 16:16 +0300, Andrus wrote:

Orders are in table

         create table order (
           dokumnr int primary key,
           packno char(10)
           );
        insert into order dokumnr values (123);

 One user sets pack number using

     update order set packno='Pack1' where dokumnr=123

 3 seconds later other user retrieves pack number using

     select packno from order where dokumnr=123

 However, other user gets null value, not Pack1 as expected. After some time later, correct value Pack1 is returned.

 How to get updated data from other user immediately?
 3 seconds is long time, it is expected that select suld retrieve update data.

 There are lot of transactions running concurrently. Maybe update command is not written to database if second user retrieves it.

 How to flush orders table so that current results are returned for second user select ?

 Using 
  PostgreSQL 13.2, compiled by Visual C++ build 1914, 64-bit
  and psqlODBC driver.

That cannot happen, unless

a) the UPDATE runs in a transaction that hasn't been committed

b) the SELECT is running on a standby server, and there is replication lag

Yours,
Laurenz Albe

--
Cybertec | https://www.cybertec-postgresql.com

On Fri, Apr 8, 2022 at 2:26 PM Laurenz Albe <laurenz.albe@cybertec.at>
wrote:

On Thu, 2022-04-07 at 16:16 +0300, Andrus wrote:

Orders are in table

create table order (
dokumnr int primary key,
packno char(10)
);
insert into order dokumnr values (123);

One user sets pack number using

update order set packno='Pack1' where dokumnr=123

3 seconds later other user retrieves pack number using

select packno from order where dokumnr=123

However, other user gets null value, not Pack1 as expected. After some

time later, correct value Pack1 is returned.

How to get updated data from other user immediately?
3 seconds is long time, it is expected that select suld retrieve update

data.

There are lot of transactions running concurrently. Maybe update

command is not written to database if second user retrieves it.

How to flush orders table so that current results are returned for

second user select ?

Using
PostgreSQL 13.2, compiled by Visual C++ build 1914, 64-bit
and psqlODBC driver.

That cannot happen, unless

a) the UPDATE runs in a transaction that hasn't been committed

b) the SELECT is running on a standby server, and there is replication lag

There's also:

c) The SELECT runs in a transaction stat *started* before the transaction
that a runs in. (Assuming it then retries with a new transaction later,
that is)

--
Magnus Hagander
Me: https://www.hagander.net/ <http://www.hagander.net/&gt;
Work: https://www.redpill-linpro.com/ <http://www.redpill-linpro.com/&gt;