[GENERAL] When is newly inserted data visible to another connection?
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 -- Sent via pgsql-general mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] When is newly inserted data visible to another connection?
Yeb Havinga: > [email protected] 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 -- Sent via pgsql-general mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] libpq: compatibility with server versions
Hi all, I am using libpq 8.2.4 (and my own wrapper around it) for a long time now. Due to some performance penalties I would like to upgrade to 8.4.x libpq. Is it o.k. if I upgraded my libpq to the newer 8.4 libraries but would still connect to old 8.2 servers? Are there any compatibility issues to be aware of? (I am not talking about upgrading the server nor the steps to take for upgrading the data itself.) Thank You Felix -- Sent via pgsql-general mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] OIDs depending data -- how to dump/restore?
Hi all, I have several databases here which I would like to update from 8.2 to 8.4, which in turn requires a dump/restore. However, the databases are OIDs depending, so, some values depend on OIDs in other tables. AFAIK the dump/restore does not rebuild the original OID values, so all relations built accross OIDs fail. (1) Is there a way to keep the original OID values somehow? (2) If I need to go the long way and replace the OIDs with SERIALs first, updating all relations to it etc: Would a dump/restore then restore the original values in a SERIAL column? Thank You Felix -- Sent via pgsql-general mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] OIDs depending data -- how to dump/restore?
Adrian Klaver: > > AFAIK the dump/restore does not rebuild the original OID > > values, so all relations built accross OIDs fail. > > > > (1) > > Is there a way to keep the original OID values somehow? > > From here: > http://www.postgresql.org/docs/8.4/interactive/app-pgdump.html > > -o > --oids > > Dump object identifiers (OIDs) as part of the data for every table. Use > this > option if your application references the OID columns in some way (e.g., in a > foreign key constraint). Otherwise, this option should not be used. Thanks, but the problem is *restoring* OIDs afterwards, isn't it? AFAIK the OIDs being restored are not the same values as the ones being saved, so my internal relations to those OIDs are all mixed up after a restore. I'd be happy if someone told me that this was wrong. :-) Felix -- Sent via pgsql-general mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] libpq: compatibility with server versions
Vick Khera: > On Sat, Mar 13, 2010 at 4:33 PM, [email protected] > wrote: > > I am using libpq 8.2.4 (and my own wrapper around it) for a > > long time now. Due to some performance penalties I would > > like to upgrade to 8.4.x libpq. > > > > What gives you the impression that updating the client library will > improve your performance for anything? I don't see how that would > help at all. Misunderstanding, sorry: I meant the better performance of the 8.4 *server* and would like to update libpq therefore as well. The question was, what however happens if I then need to connect to older (8.2) servers using the newer (8.4) libpq libraries. May I, the other way round, assume from your reply that there is neither a disadvantage nor a compatibility issue when I stick to libpq 8.2 (not upgrading to 8.4) but update the *server* to 8.4? Felix -- Sent via pgsql-general mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] integer_datetime: 8.2/8.4 differences ?
Hi all, I updated the server from 8.2 to 8.4, but I am still using libpq 8.2 to write timestamps with the libpq C interface, using the binary format and PQexecParams(). While pgAdmin in 8.2 server shows correct timestamps, 8.4 displays garbage (year 15000 etc). This has probably to do with the compile time option integer_datetime -- however, did anything change? I could not find anything in the changelogs. What I do in C, and what worked in 8.2 but not in 8.4: (1) long t= (2) double d=(double)t; (3) (4) Use &d for the paramValues[] argument of PQexecParams() Felix -- Sent via pgsql-general mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
