Skip site navigation (1) Skip section navigation (2)

Re: Insane behaviour in 8.3.3

From: Adrian Klaver <adrian(dot)klaver(at)gmail(dot)com>
To: Achilleas Mantzios <achill(at)matrix(dot)gatewaynet(dot)com>
Cc: pgsql-sql(at)postgresql(dot)org
Subject: Re: Insane behaviour in 8.3.3
Date: 2012-06-15 02:18:00
Message-ID: 4FDA9B58.7040503@gmail.com (view raw or flat)
Thread:
Lists: 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(dot)klaver(at)gmail(dot)com

In response to

pgsql-sql by date

Next:From: Richard HuxtonDate: 2012-06-15 06:34:16
Subject: Re: Insane behaviour in 8.3.3
Previous:From: Achilleas MantziosDate: 2012-06-14 08:39:35
Subject: Insane behaviour in 8.3.3

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group