When is commited data available

From: Fredric Fredricson <Fredric(dot)Fredricson(at)bonetmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: When is commited data available
Date: 2011-05-26 14:33:37
Message-ID: 4DDE64C1.4090303@bonetmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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.

Attachment Content-Type Size
Fredric_Fredricson.vcf text/x-vcard 207 bytes

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Merlin Moncure 2011-05-26 14:39:06 Re: PostgreSQL 8.4.8 bringing my website down every evening
Previous Message Tomas Vondra 2011-05-26 14:01:24 Re: PostgreSQL 8.4.8 bringing my website down every evening