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

problem updating from form

From: Karen Springer <karen(dot)springer(at)cobham(dot)com>
To: pgsql-odbc(at)postgresql(dot)org
Subject: problem updating from form
Date: 2011-01-06 06:12:29
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-odbc

I have a problem that has me stumped.

We have a table that is the main table (tbl_Assemblies) in a production 
Microsoft Access application.  Our users are complaining that sometimes 
they enter data in the form, move off of the record & then upon 
returning to the record the data reverts back to what existed in the 
field prior to the entry.  These complaints have recently become more 
frequent.  This is an intermittent problem, but I have been able to 
replicate the issue consistently on one particular record.  There isn't 
anything unusual about the data.

We are using PostgreSQL 8.1.4 on RHEL 4.1, Microsoft Access 2002 & 
psqlodbc 8.01.0200 though I have tried psqlodbc 9 with this 
configuration.  I am aware that our postgres major & minor versions are 
out of date.  We are looking at that now.

When we have a problem record, I can update at the table level from 
Access, but not in the form.  I have isolated the table, removing any 
foreign keys from the table.  There aren't any triggers.  I created a 
new Access DB with only the table & a crude form with only the primary 
key & the work order field.  I have played around with the ODBC 
settings.  We have vacuumed, reindexed & performed a full vacuum on the 
Postgres DB multiple times.  None of these actions have had a positive 
effect.  I have looked at pg_stat_activity & pg_locks for open 
transactions & row locks.   I created an identical copy of the table & 
imported all of the data, about 194,000 records, into it.  I saw the 
same problem results until I removed all, but about 20 records.  Only 
then was I able to get the update from a form to stick on our test 
record.  Incidentally, the production Access DB grows in size rapidly, 
from 14MB after a repair & compact to 400 MBs a day or two later.

Here is the table structure with the foreign keys removed.
CREATE TABLE "Production_Tracking"."tbl_Assemblies"
   "BarCode" varchar(12) NOT NULL,
   "PartNumber" varchar(32) NOT NULL,
   "LRU" varchar(20),
   "PartsListRev" varchar(10),
   "SerialNumber" float8,
   "MTN" varchar(15),
   "DocNum" varchar(50),
   "Comments" text,
   "SupplierNo" varchar(7),
   "DrawingNoRev" varchar(10),
   "WorkOrder" varchar(8),
   "BldStdRev" varchar(15),
   "Quantity" int4 NOT NULL DEFAULT 1,
   "Salesorder" varchar(8),
   "WOQuantity" int4,
   CONSTRAINT "pkey_BarCode" PRIMARY KEY ("BarCode")

I can provide the ODBC logs if helpful.  I've noticed that the logs do 
not show the xmin field.

Here are the ODBC settings:
Disable Genetic Optimizer = True
KSQO(Keyset Query Optimization) = True
Recognize Unique Indexes = True
Use Declare/Fetch = False
ComLog (C:\psqlodbc_xxx.log) = False
Parse Statements = False
Cancel as Free Stmt (Exp) = False
MyLog (C:\mylog_xxxx.log) = False
Unknown Sizes = Maximum
Text as LongVarChar = False
Unknowns as LongVarChar = False
Bools as Char = True
Max Varchar = 254
Max LongVarChar = 8190
Cache Size = 100
SysTable Prefixes:  dd_;
Read Only = False
Show System Tables = False
LF <-> CR/LF conversion = False
Updateable Cursors = False
byea as LO = False
Row Versioning = True
Disallow Premature = False
True is -1 = True
Server side prepare = False
use gssapi for GSS request = False
Int8 As = default
Level of rollback on errors = Transaction

I apologize for the length & detail of this message.  I would greatly 
appreciate any help.

Thank you,


pgsql-odbc by date

Next:From: Christopher HotchkissDate: 2011-01-12 13:06:03
Subject: Using bytea as an argument.
Previous:From: Andriy RysinDate: 2010-12-22 19:51:14
Subject: Re: pgsqlodbc 09_00_0200 is twice as slow on inserts as jdbc driver

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