Message-ID: <4FDA9B58.7040503@gmail.com>
Date: Thu, 14 Jun 2012 19:18:00 -0700
From: Adrian Klaver <adrian.klaver@gmail.com>
User-Agent: Mozilla/5.0 (X11; Linux i686;
	rv:12.0) Gecko/20120428 Thunderbird/12.0.1
MIME-Version: 1.0
To: Achilleas Mantzios <achill@matrix.gatewaynet.com>
CC: pgsql-sql@postgresql.org
Subject: Re: Insane behaviour in 8.3.3
References: <201206141139.35601.achill@matrix.gatewaynet.com>
In-Reply-To: <201206141139.35601.achill@matrix.gatewaynet.com>
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

On 06/14/2012 01:39 AM, Achilleas Mantzios wrote:
> Hello,one remote user reported a problem and i was surprised to witness the following behaviour.
> It is on postgresql 8.3.3
> 
> dynacom=# BEGIN;
> BEGIN
> dynacom=#
> dynacom=#
> dynacom=# insert into xadmin(appname,apptbl_tmp,gao,id,comment)
> dynacom-# values('PMS','overhaul_report_tmp','INSERT',nextval('overhaul_report_tmp_pkid_seq'),' zzz ');
> INSERT 0 1
> dynacom=#
> dynacom=# insert into items_tmp(id,vslwhid,serialno,rh,lastinspdate,classused,classsurvey,classsurveydate,classduedate,
> dynacom(# classpostponed,classcomment,defid,machtypecount,totalrh,comment,attachments,lastrepdate,pmsstate,xid,classaa)
> dynacom-# select id,vslwhid,serialno,rh,lastinspdate,classused,classsurvey,classsurveydate,classduedate,classpostponed,
> dynacom-# classcomment,defid,machtypecount,totalrh,comment,attachments,lastrepdate,pmsstate,currval('xadmin_xid_seq'),
> dynacom-# classaa from items where id=1261319;
> INSERT 0 1
> dynacom=# -- in the above 'xadmin_xid_seq' has taken a new value in the first insert
> dynacom=# SELECT currval('xadmin_xid_seq');
>   currval
> ---------
>     61972
> (1 row)
> dynacom=# SELECT id from items_tmp WHERE id=1261319 AND xid=61972;
>     id
> ---------
>   1261319
> (1 row)
> dynacom=# -- ok this is how it should be
> dynacom=# SELECT id from items_tmp WHERE id=1261319 AND xid=currval('xadmin_xid_seq');
>   id
> ----
> (0 rows)
> dynacom=# -- THIS IS INSANE
> 
> This code has run fine (the last SELECT returns exactly one row) for 5,409,779 total transactions thus far, in 70
> different postgresql slave installations (mixture of 8.3.3 and 8.3.13) (we are a shipping company),
> until i got this error report from a user yesterday.
> 
> What could be causing this? How could i further investigate this?

The only thing I could come up with is:

SELECT id, currval('xadmin_xid_seq') from items_tmp WHERE id=1261319 ;

Its grasping at straws, but I can not come up with a logical reason for the above.

> Achilleas Mantzios
> IT DEPT
> 


-- 
Adrian Klaver
adrian.klaver@gmail.com