Re: When is commited data available

On Thu, May 26, 2011 at 9:33 AM, Fredric Fredricson
<Fredric(dot)Fredricson(at)bonetmail(dot)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