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

Hi,


 Our usage of  postgres is bit unconventional. It is used for passing
data between programs (in real time).
 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. If I wait the reader  for 8 -- 10 secs, then for
the first day
it sees the value. For the subsequent days, the new values are not in
readers view.
Also, if the readers waits for lesser time then it does
not see the new inserts made by the writer.

The only other aspect that is unusual about the setup is that the
checkpoint segment, wal size, etc.
are all turned up to a high value (>16GB).

Any insights would be very helpful.

-sandeep

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

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