Thread: When is newly inserted data visible to another connection?

When is newly inserted data visible to another connection?

From
"fkater@googlemail.com"
Date:
Hi all,

If there is nothing else wrong in our test case we noticed
the following:

We have done a test with two connections to the database on
different computers.  After the first client (writer) had
inserted new data into a quite simple table, it told another
client (by TCP communication) to be ready, however, this
second client (reader) did not see the data then immediately
in the database. So we delayed the reading client from 2 to
5s to have it see all data in the table.

So, firstly: Is that a possible scenario from postgre's view
(and do we have to address it here) ?

Secondly: If yes, is there a way to determine when newly
inserted data is visible to other clients?

Remark: In case transacted write access keeps the inserted
lines together and therefore make it either visible in whole
or nothing of it -- this would not be a solution for the
question *when* it appears visible to other clients.

Thank You
 Felix



Re: When is newly inserted data visible to another connection?

From
Yeb Havinga
Date:
fkater@googlemail.com wrote:
> Hi all,
>
> If there is nothing else wrong in our test case we noticed
> the following:
>
> We have done a test with two connections to the database on
> different computers.  After the first client (writer) had
> inserted new data into a quite simple table, it told another
> client (by TCP communication) to be ready, however, this
> second client (reader) did not see the data then immediately
> in the database. So we delayed the reading client from 2 to
> 5s to have it see all data in the table.
>
Essential information is missing. Did the first client COMMIT before
toggling client 2? Also you might find the information from
http://developer.postgresql.org/pgdocs/postgres/transaction-iso.html
interesting, since that specifies how to control behaviour of concurrent
transactions looking at each others data.
> Secondly: If yes, is there a way to determine when newly
> inserted data is visible to other clients?
>
Not before it is committed. To which clients the just committed data is
visible depends on the transaction isolation level (see link above).

regards,
Yeb Havinga


Re: When is newly inserted data visible to another connection?

From
"fkater@googlemail.com"
Date:
Yeb Havinga:

> fkater@googlemail.com wrote:

[...]

> > We have done a test with two connections to the database
> > on different computers.  After the first client (writer)
> > had inserted new data into a quite simple table, it told
> > another client (by TCP communication) to be ready,
> > however, this second client (reader) did not see the
> > data then immediately in the database. So we delayed the
> > reading client from 2 to 5s to have it see all data in
> > the table.

> Essential information is missing. Did the first client
> COMMIT before toggling client 2?

Yes, of course, the commit was done before toggling client
2. I would like to mention that the table itself is simple
however contains a bytea column and some of the inserted
rows contain some MBs of binary data which usually take a
while.  But, yes, we trigger client 2 only *after* the
commit was done and returned successfully (using v8.2.4 on
win32 via libpq).

> Also you might find the information from
> http://developer.postgresql.org/pgdocs/postgres/transaction-iso.html
> interesting, since that specifies how to control behaviour
> of concurrent transactions looking at each others data.

Thank you for the interesting link. I think, though, that
this does not address the question why there is a delay
between the point in time A that client 1 has successfully
commited and the point in time B when client 2 can see all
new rows! Even in pure serialization it should be possible
that client 2 can immediately start reading *after* client 1
has completely commited, shouldn't it? FYI: We are using the
default setup for transaction isolation.


> > Secondly: If yes, is there a way to determine when newly
> > inserted data is visible to other clients?
> >
> Not before it is committed. To which clients the just
> committed data is visible depends on the transaction
> isolation level (see link above).

Hm, I do not get it -- AFAIK the article you mentioned deals
with the question what a concurrent transaction can see from
another one which is pending/not committed. But this is not
the case here. The first transaction is commited before.

To sum up our question:

If client 1 has commited some rows, when is the moment that
client 2 can see/read all that data? Do we have to consider
a gap and if yes how to determine it?

Thank You!
 Felix



Re: When is newly inserted data visible to another connection?

From
Scott Ribe
Date:
On Aug 18, 2010, at 6:57 AM, fkater@googlemail.com wrote:

> Even in pure serialization it should be possible
> that client 2 can immediately start reading *after* client 1
> has completely commited, shouldn't it?

Unless client 2 had previously started a transaction and is reading from that.

--
Scott Ribe
scott_ribe@elevated-dev.com
http://www.elevated-dev.com/
(303) 722-0567 voice





Re: When is newly inserted data visible to another connection?

From
Tom Lane
Date:
"fkater@googlemail.com" <fkater@googlemail.com> writes:
> Thank you for the interesting link. I think, though, that
> this does not address the question why there is a delay
> between the point in time A that client 1 has successfully
> commited and the point in time B when client 2 can see all
> new rows!

There is no such delay.  Either you forgot to commit in client 1,
or client 2 had a query snapshot that was taken before client 1
committed.

            regards, tom lane