Re: problem updating from form

Hello Karen,

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)
--
Harald Armin Massa www.2ndQuadrant.com
PostgreSQL Training, Services and Support