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
