From: Achilleas Mantzios <achill@matrix.gatewaynet.com>
To: pgsql-sql@postgresql.org
Subject: Insane behaviour in 8.3.3
Date: Thu, 14 Jun 2012 11:39:35 +0300
Message-ID: <201206141139.35601.achill@matrix.gatewaynet.com> (raw)
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? Unfortunately the remote installations are neither
physically accessible nor by TCP/IP accesible (comms are done via UUCP and administration via minicom, and the costs are
just huge 5 USD/min for 33Kbits/sec). So, i would exhaust all posibilities before deciding to ship a new postgresql version there,
and remotely upgrade, physically travel to the ship or even trying to do a backup/initdb/restore in the existing version.
Any help would be really really appreciated.
Also, as you might have understood, upgrading, although generally a good idea, does not apply so easily in our case.
Some information about the schema :
dynacom=# \d xadmin
Table "public.xadmin"
Column | Type | Modifiers
------------+-----------------------------+--------------------------------------------------------------
xid | bigint | not null default nextval(('xadmin_xid_seq'::text)::regclass)
appname | text | not null
apptbl_tmp | text | not null
gao | character varying(40) | not null
id | integer | not null
comment | text |
state | text | not null default 'NPY'::text
arcedon | timestamp without time zone | default now()
Indexes:
"xa_pk" PRIMARY KEY, btree (xid)
"xa_appname_idx" btree (appname)
"xa_appname_state_idx" btree (appname, state)
"xa_state_idx" btree (state)
dynacom=# \d items_tmp
Table "public.items_tmp"
Column | Type | Modifiers
-----------------+------------------------+-----------
id | integer | not null
vslwhid | integer |
serialno | character varying(40) |
rh | integer |
lastinspdate | date |
classused | integer |
classaa | text |
classsurvey | character varying(100) |
classsurveydate | date |
classduedate | date |
classpostponed | date |
classcomment | text |
defid | integer |
machtypecount | integer |
totalrh | integer |
comment | character varying(200) |
attachments | text[] |
lastrepdate | date |
pmsstate | character varying(200) |
xid | bigint | not null
Indexes:
"it_tmp_pk" PRIMARY KEY, btree (id, xid)
Foreign-key constraints:
"items_tmp_xid_fkey" FOREIGN KEY (xid) REFERENCES xadmin(xid)
-
Achilleas Mantzios
IT DEPT
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: achill@matrix.gatewaynet.com
Subject: Re: Insane behaviour in 8.3.3
In-Reply-To: <201206141139.35601.achill@matrix.gatewaynet.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