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
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
