From: Adrian Klaver <adrian.klaver@gmail.com>
To: Achilleas Mantzios <achill@matrix.gatewaynet.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: Insane behaviour in 8.3.3
Date: Thu, 14 Jun 2012 19:18:00 -0700
Message-ID: <4FDA9B58.7040503@gmail.com> (raw)
In-Reply-To: <201206141139.35601.achill@matrix.gatewaynet.com>
References: <201206141139.35601.achill@matrix.gatewaynet.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: adrian.klaver@gmail.com, achill@matrix.gatewaynet.com
Subject: Re: Insane behaviour in 8.3.3
In-Reply-To: <4FDA9B58.7040503@gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox