When is commited data available
| От | Fredric Fredricson |
|---|---|
| Тема | When is commited data available |
| Дата | |
| Msg-id | 4DDE64C1.4090303@bonetmail.com обсуждение исходный текст |
| Ответы |
Re: When is commited data available
Re: When is commited data available Re: When is commited data available |
| Список | pgsql-general |
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.
Вложения
В списке pgsql-general по дате отправления: