Insane behaviour in 8.3.3

From: Achilleas Mantzios <achill(at)matrix(dot)gatewaynet(dot)com>
To: pgsql-sql(at)postgresql(dot)org
Subject: Insane behaviour in 8.3.3
Date: 2012-06-14 08:39:35
Message-ID: 201206141139.35601.achill@matrix.gatewaynet.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

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

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message Adrian Klaver 2012-06-15 02:18:00 Re: Insane behaviour in 8.3.3
Previous Message Craig Ringer 2012-06-13 02:39:33 Re: how to use schema with data type