Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
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: Fredric_Fredricson.vcf
Description: text/x-vcard (207 bytes)

Responses

pgsql-general by date

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

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group