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.klaver@aklaver.com