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

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.

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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).

OS and version, memory, Postgres versions, etc.RHEL 5.2 , 32 GB , 8.1.18
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 = 0.

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.

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

There is one bug mentioned commit hung for days..
/messages/by-id/1AF3044FCAB26F4DB1AE551F8A33634B3D22FB@mail.digital-rapids.com > The interesting thing would be to see the server logs, not the> application logs. Specifically, an issue that could look just likethis> was fixed 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.
Regards,S H

S H wrote:

There is one bug mentioned commit hung for days..
/messages/by-id/1AF3044FCAB26F4DB1AE551F8A33634B3D22FB@mail.digital-rapids.com > The interesting thing would be to see the server logs, not the> application logs. Specifically, an issue that could look just likethis> was fixed 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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Can i see list of commit related bugs in postgresql. In one of customer, commit returned successfully but operation is actually committed after an hour or so successful ( Postgres version -8.1.18).
I am proposing customer to shift to latest version as there is many fixes and major performance improvement in latest 9.x versions.

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

I am sorry i could not understand it. What will be impact to postgresql user in this bug.