Search Postgresql Archives

Re: Upgrading to v12

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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@xxxxxxxxxxx






[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux