Re: Upgrading to v12

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).

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!