pg.ddx.io  pgsql-general@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Brad White <b55white@gmail.com>
To: pgsql-general@lists.postgresql.org
Subject: Re: Upgrading to v12
Date: Tue, 22 Nov 2022 14:53:01 -0600
Message-ID: <360c4ab3-0ac7-2e6a-a40a-383c226df6eb@gmail.com> (raw)
In-Reply-To: <a3e654c6-3d16-f1b0-17be-6a1e5b8018d5@aklaver.com>
References: <CAA_1=91Vc9a2g4wKNsHoNoMuXDpqQXNhhWq0mRyJMxUAKiEkDQ@mail.gmail.com>
	<ec6d3d2e-18f7-db89-c269-b5f28cfe0183@aklaver.com>
	<CAA_1=91AU7WaHP-_4mwPVHZVskL6gQOYJ6Tm7VooEhFvtj2_cw@mail.gmail.com>
	<CAA_1=93vKW50Ny8kyEtrM=1s8CYU1isFueAOSL+zNpH1Pzzvmg@mail.gmail.com>
	<9aa1a829-3367-74cf-1630-a91a4d67d41d@aklaver.com>
	<CAA_1=90YFtyBmg-zKZ936H6ke-CmdjjKDS-xTBEfjqpAWxSKUA@mail.gmail.com>
	<CAA_1=91E8kcce-xuXTTPJaZnX-To8dOHPe3ZBKVoyZyvXNyaFw@mail.gmail.com>
	<c6fb9011-f99d-7a2e-4a29-05f4b8cdfe25@aklaver.com>
	<CAA_1=91PLFenp7aoZYy8rm76pBz4LcCScMP7cTfUKqb5PYP+gQ@mail.gmail.com>
	<c47f3b0d-042a-ea5f-6f19-593120c4815c@aklaver.com>
	<CAA_1=90r7BNtpV1_7kr44cW4tUfED_iNB+mrV1XMJ24s1x7Mkw@mail.gmail.com>
	<CAA_1=91BjFdp4D52=S1KeY10tm8_R8HiBUFgdc4g2Kh-rivroA@mail.gmail.com>
	<a3e654c6-3d16-f1b0-17be-6a1e5b8018d5@aklaver.com>


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!






view thread (31+ messages)  latest in thread

Message-ID: <360c4ab3-0ac7-2e6a-a40a-383c226df6eb@gmail.com>
Permalink:  ../360c4ab3-0ac7-2e6a-a40a-383c226df6eb@gmail.com/
Also on:    postgresql.org/message-id/360c4ab3-0ac7-2e6a-a40a-383c226df6eb@gmail.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-general@postgresql.org
  Cc: b55white@gmail.com
  Subject: Re: Upgrading to v12
  In-Reply-To: <360c4ab3-0ac7-2e6a-a40a-383c226df6eb@gmail.com>
  References: <CAA_1=91Vc9a2g4wKNsHoNoMuXDpqQXNhhWq0mRyJMxUAKiEkDQ@mail.gmail.com> <ec6d3d2e-18f7-db89-c269-b5f28cfe0183@aklaver.com> <CAA_1=91AU7WaHP-_4mwPVHZVskL6gQOYJ6Tm7VooEhFvtj2_cw@mail.gmail.com> <CAA_1=93vKW50Ny8kyEtrM=1s8CYU1isFueAOSL+zNpH1Pzzvmg@mail.gmail.com> <9aa1a829-3367-74cf-1630-a91a4d67d41d@aklaver.com> <CAA_1=90YFtyBmg-zKZ936H6ke-CmdjjKDS-xTBEfjqpAWxSKUA@mail.gmail.com> <CAA_1=91E8kcce-xuXTTPJaZnX-To8dOHPe3ZBKVoyZyvXNyaFw@mail.gmail.com> <c6fb9011-f99d-7a2e-4a29-05f4b8cdfe25@aklaver.com> <CAA_1=91PLFenp7aoZYy8rm76pBz4LcCScMP7cTfUKqb5PYP+gQ@mail.gmail.com> <c47f3b0d-042a-ea5f-6f19-593120c4815c@aklaver.com> <CAA_1=90r7BNtpV1_7kr44cW4tUfED_iNB+mrV1XMJ24s1x7Mkw@mail.gmail.com> <CAA_1=91BjFdp4D52=S1KeY10tm8_R8HiBUFgdc4g2Kh-rivroA@mail.gmail.com> <a3e654c6-3d16-f1b0-17be-6a1e5b8018d5@aklaver.com> <360c4ab3-0ac7-2e6a-a40a-383c226df6eb@gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox