Re: Insane behaviour in 8.3.3

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(dot)klaver(at)gmail(dot)com