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.