Обсуждение: problem updating from form
Hi,
I have a problem that has me stumped. We have a table that is the main table 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. We are using PostgreSQL 8.1.4 on Red Hat, Microsoft Access 2002 & psqlodbc_09_00_0200. I can update the field successfully in the table in Access, but not in a 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 two fields, the primary key & one other. In ODBC I made sure that row versioning was set as true and Text as LongVarChar was set as false. We have vacuumed, reindexed & performed a full vacuum on the Postgres DB multiple times. None of these actions have had a positive effect. I created an identical copy of the table & imported all of the data, over 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.
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")
)
WITHOUT OIDS;
I can provide the ODBC logs if helpful.
Thank you,
I have a problem that has me stumped. We have a table that is the main table 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. We are using PostgreSQL 8.1.4 on Red Hat, Microsoft Access 2002 & psqlodbc_09_00_0200. I can update the field successfully in the table in Access, but not in a 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 two fields, the primary key & one other. In ODBC I made sure that row versioning was set as true and Text as LongVarChar was set as false. We have vacuumed, reindexed & performed a full vacuum on the Postgres DB multiple times. None of these actions have had a positive effect. I created an identical copy of the table & imported all of the data, over 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.
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")
)
WITHOUT OIDS;
I can provide the ODBC logs if helpful.
Thank you,
Karen
Hello Karen,
Harald Armin Massa www.2ndQuadrant.com
intermittent problem, but I have been able to replicate the issue consistently on one particular record. We are using PostgreSQL 8.1.4 on Red Hat, Microsoft Access 2002 & psqlodbc_09_00_0200.
PostgreSQL 8.1 was EndOfLifed in November 2010, you are recommended to plan for an update ASAP http://wiki.postgresql.org/wiki/PostgreSQL_Release_Support_Policy
More important: the highest double-dot of the 8.1 series is 8.1.23; that are 17 generations of critical bugs fixed younger than your system. An update within the same major release consists of exchanging the binaries and should be done immediately.
I can update the field successfully in the table in Access, but not in a 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 two fields, the primary key & one other. In ODBC I made sure that row versioning was set as true and Text as LongVarChar was set as false. We have vacuumed, reindexed & performed a full vacuum on the Postgres DB multiple times. None of these actions have had a positive effect. I created an identical copy of the table & imported all of the data, over 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.
To narrow down the problem: please find out what is really ending up at the database server. In your test-cluster (where you were able to reproduce the problem), configure your postgresql.conf of "log every statement", as in: log_min_duration_statement = 0
(Warning: that may not be a good idea on production systems, as a lot of logging is done)
Now do your tests within access, and check what does get logged - i.e., which statements really make it down to the database. That should give you information to narrow down the bug.
Should there be a correct sequence of statements as in "begin; insert into mytable ('value','another_value');end;" which does NOT get honoured by PostgreSQL, then there is something wrong with your database configuration. That is very, very unlikely.
PostgreSQL is known to perfectly reliable store and update millions of records, so an error up in the line of command (Access Form Widgets, Access ODBC-Adapter, PGODBC...) is the more likely culprit.
best wishes,
Harald
P.S.:
Please let me recommend that you thoroughly invest the potential ROI of paid, onsite one-to-team consulting for your PostgreSQL usage. You can and will get all information to solve your problem for free out of documentation, mailing lists and search engines.
I would expect that for you and your team the time saved and knowledge gained by direct, paid mentoring will give an productivity boost way greater then the consulting fees of the professional service providers at http://www.postgresql.org/support/professional_support
(disclaimer: I am with one of them)
--
PostgreSQL Training, Services and Support
On 2011-01-02 08:31, Karen Springer wrote: > We are using PostgreSQL 8.1.4 on Red Hat, Microsoft Access 2002 & psqlodbc_09_00_0200. You don't say which RedHat. RHN offers 8.1.22 for RHEL5 currently. Are you not running regular updates? - Jeremy
Karen Springer <karen.springer@cobham.com> writes:
> I have a problem that has me stumped. We have a table that is the main
> table 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.
FWIW, that sounds like it's entirely a client-side issue. So you might
have better luck asking about it in pgsql-odbc.
> We are using PostgreSQL 8.1.4 on Red Hat, Microsoft Access 2002 &
> psqlodbc_09_00_0200.
As noted elsewhere, 8.1.4 is really old (more than 4 years old in fact).
It's likely not helping any that you're using a fresh-off-the-boat
psqlodbc version with it --- I don't know that anyone would have done
any significant testing of 09_00_0200 against an 8.1.x server.
Red Hat is currently shipping Postgres 8.4.x for RHEL-5 (see the
"postgresql84" package set). It'd be a good thing to consider updating
to 8.4.x sometime soon. I doubt it'll fix this immediate problem, but
it will likely save your data from getting eaten sometime in the future.
regards, tom lane
On Sunday 02 January 2011 12:31:43 am Karen Springer wrote: > Hi, > > I have a problem that has me stumped. We have a table that is the main > table 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. We > are using PostgreSQL 8.1.4 on Red Hat, Microsoft Access 2002 & > psqlodbc_09_00_0200. I can update the field successfully in the table > in Access, but not in a 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 two fields, > the primary key & one other. In ODBC I made sure that row versioning > was set as true and Text as LongVarChar was set as false. We have > vacuumed, reindexed & performed a full vacuum on the Postgres DB > multiple times. None of these actions have had a positive effect. I > created an identical copy of the table & imported all of the data, over > 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. > A couple of questions. 1) Has the increase in complaints tracked an increase in number of rows in the table? 2) Could we see the problem record or is it possible say how it differs from the other records? > > Thank you, > Karen -- Adrian Klaver adrian.klaver@gmail.com
Hi Tom,
Due to the responses I received, I am now thoroughly convinced of the need to upgrade & have passed this information on. We are running RHEL 4.1 which is why the newer version did not install with RHEL. I initially was using an older version of psqlodbc, but decided to upgrade to the latest on my test machine hoping it would help. The users have the older psqlodbc. I will ask the pgsql-odbc group next. Thank you for your suggestions!
Best regards,
Karen
On 01/02/2011 9:41 AM, Tom Lane wrote:
Due to the responses I received, I am now thoroughly convinced of the need to upgrade & have passed this information on. We are running RHEL 4.1 which is why the newer version did not install with RHEL. I initially was using an older version of psqlodbc, but decided to upgrade to the latest on my test machine hoping it would help. The users have the older psqlodbc. I will ask the pgsql-odbc group next. Thank you for your suggestions!
Best regards,
Karen
On 01/02/2011 9:41 AM, Tom Lane wrote:
Karen Springer <karen.springer@cobham.com> writes:I have a problem that has me stumped. We have a table that is the main table 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.FWIW, that sounds like it's entirely a client-side issue. So you might have better luck asking about it in pgsql-odbc.We are using PostgreSQL 8.1.4 on Red Hat, Microsoft Access 2002 & psqlodbc_09_00_0200.As noted elsewhere, 8.1.4 is really old (more than 4 years old in fact). It's likely not helping any that you're using a fresh-off-the-boat psqlodbc version with it --- I don't know that anyone would have done any significant testing of 09_00_0200 against an 8.1.x server. Red Hat is currently shipping Postgres 8.4.x for RHEL-5 (see the "postgresql84" package set). It'd be a good thing to consider updating to 8.4.x sometime soon. I doubt it'll fix this immediate problem, but it will likely save your data from getting eaten sometime in the future. regards, tom lane
--
Karen Springer
Cobham Avionics, Communications
Chelton Avionics dba Wulfsberg has begun its transition to Cobham Avionics
however, at this time, all business transactions continue to remain with
Wulfsberg Electronics.
Chelton Avionics dba Wulfsberg Electronics doing business as Cobham Avionics
Registered office: 6400 Wilkinson Drive, Prescott, AZ 86301 USA
Please consider the environment before printing this email.
THIS EMAIL AND ANY ATTACHED FILES ARE CONFIDENTIAL AND MAY BE
LEGALLY PRIVILEGED.
This e-mail and any files transmitted with it ("E-mail") is intended solely for the
addressee(s) and may contain confidential and/or legally privileged information.
If you are not the addressee(s), any disclosure, reproduction, copying, distribution
Or other use of the E-mail is prohibited. If you have received this E-mail in error,
please delete it and notify the sender immediately via our switchboard or return
e-mail.
Neither the company nor any subsidiary or affiliate or associated company nor any
individual sending this E-mail accepts any liability in respect of the content
(including errors and omissions) nor shall this e-mail be deemed to enter the
company or any subsidiary or affiliate or associated company into a contract or to
create any legally binding obligations unless expressly agreed to in writing under
separate cover and timeliness of the E-mail which arise as a result of transmission.
If verification is required, please request a hard copy version from the sender.
[Cobham Avionics, Inc.; Prescott, Arizona USA]
Hi Adrian,
Yes, the complaints have increased with the number or rows and the number of users accessing the DB.
The problem record looks like this.
BarCode: W205179
PartNumber: 380-013
LRU: 380-013
PartsListRev
SerialNumber
MTN
DocNum: 150-2380-XXX
Comments
SupplierNo
DrawingNoRev
WorkOrder
BldStdRev: 1.02
Quantity
Salesorder
WOQuantity: 1
xmin: 1933434795
There is nothing that stands out with the data. I have been trying to update the WorkOrder field in my tests. The users complain that they have problems updating the WorkOrder, MTN & Comments fields. This particular record has consistently had a problem (though not always), but users are unable to update other records as well. It doesn't occur with all records though. Most of the time the updates work fine. I test using a new Access DB that contains only a copy of this table and a form with the BarCode and WorkOrder fields. There isn't any code behind the form.
Best regards,
Karen
On 01/02/2011 12:22 PM, Adrian Klaver wrote:
Yes, the complaints have increased with the number or rows and the number of users accessing the DB.
The problem record looks like this.
BarCode: W205179
PartNumber: 380-013
LRU: 380-013
PartsListRev
SerialNumber
MTN
DocNum: 150-2380-XXX
Comments
SupplierNo
DrawingNoRev
WorkOrder
BldStdRev: 1.02
Quantity
Salesorder
WOQuantity: 1
xmin: 1933434795
There is nothing that stands out with the data. I have been trying to update the WorkOrder field in my tests. The users complain that they have problems updating the WorkOrder, MTN & Comments fields. This particular record has consistently had a problem (though not always), but users are unable to update other records as well. It doesn't occur with all records though. Most of the time the updates work fine. I test using a new Access DB that contains only a copy of this table and a form with the BarCode and WorkOrder fields. There isn't any code behind the form.
Best regards,
Karen
On 01/02/2011 12:22 PM, Adrian Klaver wrote:
A couple of questions. 1) Has the increase in complaints tracked an increase in number of rows in the table? 2) Could we see the problem record or is it possible say how it differs from the other records?
On 3 Jan 2011, at 8:12, Karen Springer wrote: > This particular record has consistently had a problem (though not always), but users are unable to update other recordsas well. It doesn't occur with all records though. Most of the time the updates work fine. I test using a newAccess DB that contains only a copy of this table and a form with the BarCode and WorkOrder fields. There isn't any codebehind the form. Seeing that the access db is probably where both the data is altered and displayed at the same time, would it be possiblethat the problematic are in an open transaction somewhere? I'd imagine that in such cases access tries to performan update, receives an error and changes the row contents back to what it was. You can look for open transactions and row locks in pg_stat_activity and pg_locks respectively. Alban Hertroys -- If you can't see the forest for the trees, cut the trees and you'll see there is no forest. !DSPAM:737,4d2179a911543699029975!
On 2011-01-03 06:29, Karen Springer wrote: > We are running RHEL 4.1 which is why the newer version did not install with RHEL. RHEL 4.1 should be offering pgsql 8.1.15 in the apps channel (Red Hat Application Stack v1). - Jeremy
On Sunday 02 January 2011 11:12:25 pm Karen Springer wrote: > Hi Adrian, > > Yes, the complaints have increased with the number or rows and the > number of users accessing the DB. > > The problem record looks like this. > > BarCode: W205179 > PartNumber: 380-013 > LRU: 380-013 > PartsListRev > SerialNumber > MTN > DocNum: 150-2380-XXX > Comments > SupplierNo > DrawingNoRev > WorkOrder > BldStdRev: 1.02 > Quantity > Salesorder > WOQuantity: 1 > xmin: 1933434795 > > There is nothing that stands out with the data. I have been trying to > update the WorkOrder field in my tests. The users complain that they > have problems updating the WorkOrder, MTN & Comments fields. This > particular record has consistently had a problem (though not always), > but users are unable to update other records as well. It doesn't occur > with all records though. Most of the time the updates work fine. I > test using a new Access DB that contains only a copy of this table and a > form with the BarCode and WorkOrder fields. There isn't any code behind > the form. > > Best regards, > Karen I agree nothing really stands out. I think you will find more information, as you suggested in another post, on the psqlodbc list. If you do that, might I suggest giving the full configuration for the ODBC connection. -- Adrian Klaver adrian.klaver@gmail.com
Le dimanche 02 janvier 2011 à 01:31 -0700, Karen Springer a écrit : > We are using PostgreSQL 8.1.4 on Red Hat, Microsoft Access 2002 That is one of the worst versions of Access ever. Lots of bugs. Do try an other version (2K, 2003 are much better) and see if the problem persists. -- Vincent Veyron http://marica.fr/ Logiciel de gestion des dossiers de contentieux et d'assurance pour le service juridique