Thread: When is commited data available
Hi,
I wonder when the committed data is available to other connections, or more specifically if there is a delay after COMMIT return successfully and the data will appear in SELECTs made by other connections.
A more detailed description of my problem:
I use postgresql as backend to a REST service. The REST service is written php and run on an Apache server. For all reads I use a persistent connection (php function pg_pconnect()) and for all write operations I create a new connection for each call (php function pg_connect()).
What I think I see now and then is that a client make a call to update the database and after the call has returned the client immediately (20-150ms delay) access the database again only to find that it looks like the update was never made. There are a lot of triggers involved and some have (intentional) side effects such as inserting into other tables and stuff. Later investigation reveals that the update was indeed made.
Am I totally barking up the wrong tree here or could this happen? And if it can, is there a reliable work around that does not involve waiting X ms and hope for the best.
These are the config variables that I assume may have something to do with it:
#fsync = on
#synchronous_commit = on
#wal_sync_method = fsync
#wal_writer_delay = 200ms
#commit_delay = 0
(all default values)
Regards,
Fredric
PS. I realize this could be a caching problem in http but I have spent some time investigating this and I am pretty sure it is not.
I wonder when the committed data is available to other connections, or more specifically if there is a delay after COMMIT return successfully and the data will appear in SELECTs made by other connections.
A more detailed description of my problem:
I use postgresql as backend to a REST service. The REST service is written php and run on an Apache server. For all reads I use a persistent connection (php function pg_pconnect()) and for all write operations I create a new connection for each call (php function pg_connect()).
What I think I see now and then is that a client make a call to update the database and after the call has returned the client immediately (20-150ms delay) access the database again only to find that it looks like the update was never made. There are a lot of triggers involved and some have (intentional) side effects such as inserting into other tables and stuff. Later investigation reveals that the update was indeed made.
Am I totally barking up the wrong tree here or could this happen? And if it can, is there a reliable work around that does not involve waiting X ms and hope for the best.
These are the config variables that I assume may have something to do with it:
#fsync = on
#synchronous_commit = on
#wal_sync_method = fsync
#wal_writer_delay = 200ms
#commit_delay = 0
(all default values)
Regards,
Fredric
PS. I realize this could be a caching problem in http but I have spent some time investigating this and I am pretty sure it is not.
Attachment
Fredric Fredricson <Fredric.Fredricson@bonetmail.com> writes:
> I wonder when the committed data is available to other connections, or
> more specifically if there is a delay after COMMIT return successfully
> and the data will appear in SELECTs made by other connections.
No, there's no delay.
regards, tom lane
On Thu, May 26, 2011 at 10:33 AM, Fredric Fredricson <Fredric.Fredricson@bonetmail.com> wrote: > I wonder when the committed data is available to other connections, or more > specifically if there is a delay after COMMIT return successfully and the > data will appear in SELECTs made by other connections. Check what your isolation level is on the other connections.
On Thu, May 26, 2011 at 9:33 AM, Fredric Fredricson <Fredric.Fredricson@bonetmail.com> wrote: > Hi, > I wonder when the committed data is available to other connections, or more > specifically if there is a delay after COMMIT return successfully and the > data will appear in SELECTs made by other connections. > > A more detailed description of my problem: > > I use postgresql as backend to a REST service. The REST service is written > php and run on an Apache server. For all reads I use a persistent connection > (php function pg_pconnect()) and for all write operations I create a new > connection for each call (php function pg_connect()). > What I think I see now and then is that a client make a call to update the > database and after the call has returned the client immediately (20-150ms > delay) access the database again only to find that it looks like the update > was never made. There are a lot of triggers involved and some have > (intentional) side effects such as inserting into other tables and stuff. > Later investigation reveals that the update was indeed made. > > Am I totally barking up the wrong tree here or could this happen? And if it > can, is there a reliable work around that does not involve waiting X ms and > hope for the best. > > These are the config variables that I assume may have something to do with > it: > #fsync = on > #synchronous_commit = on > #wal_sync_method = fsync > #wal_writer_delay = 200ms > #commit_delay = 0 > (all default values) > > Regards, > Fredric > > PS. I realize this could be a caching problem in http but I have spent some > time investigating this and I am pretty sure it is not. There is no delay. In fact, it is the lack of delay between commit and constraint checking of data that is the principle advantage of databases over the various nosql systems. You are almost certainly leaking transaction due to the spectacularly broken mechanics of pg_pconnect(), which is widely understood to be broken even by php standards. check out pgbouncer. merlin