Re: how to serialize insert followed by read(select) by different clients

Hi Tom,

 Appreciate so much for looking into this. This is a single database instance.
I debugged a bit more
 after I posted the problem and realized that writer was actually working in
 asynchronous mode. Once I fixed that the program is working as expected.

Thanks.
sandeep




On Sun, Aug 7, 2016 at 11:43 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Sandeep Gupta <gupta.sandeep@gmail.com> writes:
>>  First program, lets say the writer, (using psql) appends to a table
>> in the  database.
>>  Second program, the reader, (python using alchemy) reads the data.
>>  This happens in loop, one for each day.The programs are fired in that order
>>  and the first program always commits after it inserts new rows.
>
>>  The problem is that the second program does not see the updates of the first
>> program consistently.
>
> There are only two possible explanations for that:
>
> 1. The writer isn't actually issuing a COMMIT when you think it is.
>
> 2. The reader is using a stale snapshot, ie it's using SERIALIZABLE
> or REPEATABLE READ transaction mode and its transaction started before
> the writer committed.
>
> If you're having trouble identifying the cause of the problem you
> might try setting "log_statement = all" and looking at where BEGINs
> and COMMITs get issued.
>
> (Well, I guess that only exhausts the possibilities as long as this is
> happening on a single database server.  If the reader is reading from
> a hot-standby slave then replication delays might explain your problem.
> But that would be a rather material omission of facts.)
>
>                         regards, tom lane