Re: Commit problem in read-commited isolation level

From: Adrian Klaver <adrian(dot)klaver(at)gmail(dot)com>
To: S H <msq001(at)live(dot)com>
Cc: "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org>
Subject: Re: Commit problem in read-commited isolation level
Date: 2013-08-16 14:32:51
Message-ID: 520E3813.1060508@gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

On 08/15/2013 10:45 PM, S H wrote:
>
> > Any triggers on the table?
> There are no trigger associated with this table.
>
> > FYI 8.1 is no longer supported.
> I understand that. If there are some known related issues, it will be
> easy to convince, Product mgmt team to upgrade the version of postgresql.
>
> Are there known issues related to commit problem in 8.1 version.

Could be, there where 24 releases over five years. Just not sure at this
point you actually are facing a commit problem and more information is
needed in any case.

1) What is your exact Postgres version i.e 8.1.5.?

2) In your original post you had this sequence:

Current value in colname = 5;
Update tablename set colname = 0 where key = 18;
commit , in parallel to above queries ( either vacuum or reindex of
table was running)

After 10 sec following query is executed.
select colname from tablename where key = 18 ;it is returning old value
i.e colname = 5.
After another few seconds select colname from tablename where key = 18
;it is returning new value i.e colname = 5.

You say at the top the new value is 0, but show 5 as the new value at
the bottom.

Which is correct?

3) You also say it works fine in two environments, but not one.

What are the environments?
OS and version, memory, Postgres versions, etc.

>
>
> > Date: Wed, 7 Aug 2013 17:05:59 -0700
> > From: adrian(dot)klaver(at)gmail(dot)com
> > To: msq001(at)live(dot)com
> > CC: pgsql-general(at)postgresql(dot)org
> > Subject: Re: [GENERAL] Commit problem in read-commited isolation level
> >
> > On 08/07/2013 04:54 PM, S H wrote:
> > > Hi,
> > >
> > > I have faced very strange problem in one of psotgresql query in one of
> > > the production environment. It is working fine in development and other
> > > environment.
> > >
> > > Current value in colname = 5;
> > > Update tablename set colname = 0 where key = 18;
> > > commit , in parallel to above queries ( either vacuum or reindex of
> > > table was running)
> > >
> > > After 10 sec following query is executed.
> > >
> > > select colname from tablename where key = 18 ;
> > > it is returning old value i.e colname = 5.
> > >
> > > After another few seconds
> > > select colname from tablename where key = 18 ;
> > > it is returning new value i.e colname = 5.
> >
> > I thought the new value is 0?
> > >
> > >
> > > Isolevel level is readcommited.
> > > Is there any possibility of bug in commit in V8.1 leading to delay of
> > > commit ?
> >
> > Any triggers on the table?
> >
> > FYI 8.1 is no longer supported.
> >
> > >
> > > I need to provide explanation of above behavior to my customer.
> > >
> > > Regards,
> >
> >
> > --
> > Adrian Klaver
> > adrian(dot)klaver(at)gmail(dot)com
> >
> >
> > --
> > Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> > To make changes to your subscription:
> > http://www.postgresql.org/mailpref/pgsql-general

--
Adrian Klaver
adrian(dot)klaver(at)gmail(dot)com

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message David Johnston 2013-08-16 14:34:02 Re: need help
Previous Message Adrian Klaver 2013-08-16 14:13:47 Re: what \ command actually run