Обсуждение: 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.klaver@gmail.com

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.klaver@gmail.com
>  > To: msq001@live.com
>  > CC: pgsql-general@postgresql.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.klaver@gmail.com
>  >
>  >
>  > --
>  > Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
>  > To make changes to your subscription:
>  > http://www.postgresql.org/mailpref/pgsql-general


--
Adrian Klaver
adrian.klaver@gmail.com

On 08/16/2013 10:38 PM, S H wrote:
>  > 1) What is your exact Postgres version i.e 8.1.5.?
> V - 8.1.18
>
>  > 3) You also say it works fine in two environments, but not one.
>  >
>  > What are the environments?
>  > OS and version, memory, Postgres versions, etc.
>
> It is working on most of the production system ( more than 100) but
> failed once in one of the system ( not always).

Not sure I understand failed once, not always. Not always would seem to
mean it failed more than once. Could you elaborate?

Always the same machine or does that change?

If the same machine, is there something different about it?

>
>> OS and version, memory, Postgres versions, etc.
> RHEL 5.2 , 32 GB , 8.1.18

All the systems are exactly the same?

>  > You say at the top the new value is 0, but show 5 as the new value at
>  > the bottom.
>  >
>
> Sorry it is returning new value ie 0 after some time. Corrected the
> above description.
>

So the commit happens, it just takes time. First guess would be some
sort of disk I/O contention problem. Honestly though there is not enough
information available to do more than guess. We would need a clearer
picture of what happens during these periods before it would be possible
to formulate a clearer answer. In other words log information from
Postgres, the application and the OS and ideally load information from
the system. Also are you talking about a specific procedure that causes
this, or seemingly random event? Basically, at this point it would be
better to provide as much information as possible.



--
Adrian Klaver
adrian.klaver@gmail.com

S H wrote:
> There is one bug mentioned  commit hung for days..
> http://www.postgresql.org/message-id/1AF3044FCAB26F4DB1AE551F8A33634B3D22FB@mail.digital-rapids.com > The interesting
thingwould be to see the server logs, not the> application logs. Specifically, an issue that could look just likethis>
wasfixed in 8.1.7, in which case you would see weird error messages> about permission denied or such in the *server*
logs.None of thatwould> show up in the client logs. 
> Any idea what exactly is this bug.
> I could not make out relation between release notes mentioned in
http://www.postgresql.org/docs/8.1/static/release-8-1-7.html
> and above comment.

Maybe it's this commit, which was part of 8.1.6:

commit 9f1b531420ee13d04c7701b34bb4b874df7ff2fa
Author: Teodor Sigaev <teodor@sigaev.ru>
Date:   Fri Oct 13 14:00:17 2006 +0000

    Fix infinite sleep and failes of send in Win32.

    1) pgwin32_waitforsinglesocket(): WaitForMultipleObjectsEx now called with
    finite timeout (100ms) in case of FP_WRITE and UDP socket. If timeout occurs
    then pgwin32_waitforsinglesocket() tries to write empty packet goes to
    WaitForMultipleObjectsEx again.

    2) pgwin32_send(): add loop around WSASend and pgwin32_waitforsinglesocket().
    The reason is: for overlapped socket, 'ok' result from
    pgwin32_waitforsinglesocket() isn't guarantee that socket is still free,
    it can become busy again and following WSASend call will fail with
    WSAEWOULDBLOCK error.

    See http://archives.postgresql.org/pgsql-hackers/2006-10/msg00561.php


It's troubling to be talking about a bug that was patched in 2006 for
the 8.1.6 release, however.  Anything prior to that is not something
anyone should be using anymore.  At the very least, you should have
migrated to 8.1.23; but 8.1 has been unsupported altogether for more
than two years now.  Even 8.2 is out of support.

--
Álvaro Herrera                http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services