When is commited data available

Fredric Fredricson <Fredric.Fredricson@bonetmail.com> writes:

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.

No, there's no delay.

regards, tom lane

On Thu, May 26, 2011 at 10:33 AM, Fredric Fredricson
<Fredric.Fredricson@bonetmail.com> wrote:

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.

Check what your isolation level is on the other connections.

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