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(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Sandeep Gupta <gupta(dot)sandeep(at)gmail(dot)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