Re: Upgrading to v12

On 11/22/22 12:53, Brad White wrote:
>
> On 11/18/2022 6:34 PM, Adrian Klaver wrote:
>> On 11/18/22 16:05, Brad White wrote:
>>>
>>> --> 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.
>>
>>
>> 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?
>>
> UPDATE:
>
> Yes, there are 5 timestamp fields.
>
> It seems unlikely to be the culprit for 3 reasons.
>
> 1) It worked fine in v9.4
> 2) It worked the previous 4 times I saved that record in v12.
> 3) As the data came from Access, there is no data in any of the fields
> in the last three decimal places.
> ex. 45.234000
>
> But as it is the best lead I have, and it could still be the culprit
> until proven otherwise, I'm working to convert those 5 fields from
> timestamp to timestamp(3).

It is worse then that:

https://learn.microsoft.com/en-us/office/troubleshoot/access/store-calculate-compare-datetime-data

Valid time values range from .0 (00:00:00) to .99999 (23:59:59)

So no fractional seconds.

Before you do any of the below I would set up a test table with
timestamps and verify they are the issue.

>
> Of course, PG doesn't allow to edit a table with dependent views.
>
> Which means that I'm attempting to modify a script that will allow me to
> save, drop, restore the views.
>
> Of course, PG coerces all table and field names to lowercase unless quoted.
>
> So I have to figure how to recognize all table names and add quotes.
>
> This table is core to the app, so a LOT of the views reference it.
>
> I may not be done anytime soon.
>
> Have a good vacation!
>
>
>

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