Thread

1. How to get updated order data

   Andrus <kobruleht2@hot.ee> — 2022-04-07T13:16:17Z

   Hi!
   
   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.
   
   Andrus.
   

2. Re: How to get updated order data

   Laurenz Albe <laurenz.albe@cybertec.at> — 2022-04-08T12:26:42Z

   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
   
   
   
   
   

3. Re: How to get updated order data

   Magnus Hagander <magnus@hagander.net> — 2022-04-08T13:07:28Z

   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/>
    Work: https://www.redpill-linpro.com/ <http://www.redpill-linpro.com/>