Re: Upgrading to v12

On 11/18/22 16:05, Brad White wrote:
> tl;dr  How do I turn up the logging so I can see what is failing?
>
> In our quest to get replication working, we are upgrading from v9.4 to
> v12.10.
>
> Access365 via ODBC
> Driver = "PostgreSQL Unicode" v13.02, Date 9/22/2021
>
> In testing the app against v12, I find this issue:
>
> On updating a record, I set values on several fields and call
> recordSet.Update after each one.
> After updating one particular field, calling Update gives
>
> --> The Microsoft Access database engine stopped the process because you
> and another user are attempting to change the same data at the same time.
>
> Code in question:
>      rst!Update  <-- success
>      rst!QtyDeliverable = rst!Quantity
>      rst.Update  <-- fails here
> The wisdom of the internet says that this is most likely with a BIT
> field that has null that Access can't handle. But that isn't the case
> here. Both are int4 fields and both have values before the update.

That wisdom:

https://odbc.postgresql.org/faq.html#6.4

also says

" PostgreSQL 7.2 and above can cause similar problems but for different
reasons:

Contributed by Sam Hokin (sam(at)ims(dot)net)

The new PostgreSQL timestamp data type defaults to microsecond
precision. This means that timestamp values are stored like 2002-05-22
09:00:00.123456-05. However, Access does not support the extra
precision, so the value that Access uses is 2002-05-22 09:00:00-05. When
one tries to update a record, one gets the error message above because
the value that Access uses in its UPDATE query does not match the value
in the PostgreSQL table, similar to the NULL vs. empty string conflict
that is already reported in this FAQ entry. "

The above is the problem I usually ran into with Access and Postgres and
updating.

Is there a timestamp field in the record you are updating?

>
> For context, this is after we've copied the order record. Then we copy
> this Order Item record. Then we copy all the child records. Lastly, we
> are updating a few fields in this Order Item record and the update fails.
>
> This only fails against PostgreSQL 12.10, compiled by Visual C++ build
> 1914, 64-bit
> Succeeds against PostgreSQL 9.4.1, compiled by Visual C++ build 1800, 64-bit
>
> I don't see anything in the data\logs folder that looks relevant. Where
> else should I look?
>

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com