Re: Insane behaviour in 8.3.3 - Mailing list pgsql-sql

From Adrian Klaver
Subject Re: Insane behaviour in 8.3.3
Date
Msg-id 4FDA9B58.7040503@gmail.com
Whole thread Raw
In response to Insane behaviour in 8.3.3  (Achilleas Mantzios <achill@matrix.gatewaynet.com>)
List pgsql-sql
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


pgsql-sql by date:

Previous
From: Achilleas Mantzios
Date:
Subject: Insane behaviour in 8.3.3
Next
From: Richard Huxton
Date:
Subject: Re: Insane behaviour in 8.3.3