I have just started using PostgreSQL as Back End database. As the title suggested, users will input data into the DB tables through MS ACCESS connected using psql ODBC driver. But I observed a very strange behavior from a linked table in ACCESS.
The table has Primary Key (PK) named transaction_id with sequence attached to the column, incrementing 1 at a time. When the transaction_id is left empty in MS ACCESS ON INSERT, PostgreSQL will automatically assign a number for transaction_id, as expected.
Frequently and randomly though, when inserting new data into the table, transaction_id would persistently take a previous value from the table, instead of incrementing at 1.
| table_id (PK) | date |
|---|---|
| 1 | 14-11-2022 |
| 2 | 14-11-2022 |
| 3 | 14-11-2022 |
| 4 | 14-11-2022 |
| 3 | 14-11-2022 |
| 3 | 14-11-2022 |
| 3 | 14-11-2022 |
| 3 | 14-11-2022 |
Refreshing the linked table would change transaction_id as it should be, but IMMEDIATE new insert on the linked table would use the same persistent previous value as a PK.
| transaction_id (PK) | date |
|---|---|
| 1 | 14-11-2022 |
| 2 | 14-11-2022 |
| 3 | 14-11-2022 |
| 4 | 14-11-2022 |
| 5 | 14-11-2022 |
| 6 | 14-11-2022 |
| 7 | 14-11-2022 |
| 8 | 14-11-2022 |
| 3 (immediate new data) | 14-11-2022 |
| 3 (immediate new data) | 14-11-2022 |
I have to wait for a while before the PK went back to normal behavior of incrementing at 1.
However, if I were to change the date value, the PK will reflect it's true number.
| transaction_id (PK) | date |
|---|---|
| ... | ... |
| 3 | 14-11-2022 |
| 3 | 14-11-2022 |
| 11 | 10-11-2022 |
| 12 | 12-11-2022 |
| 3 | 14-11-2022 |
| 14 | 01-02-2022 |
I have a subform that is dependent on transaction_id for its foreign field, therefore, if the linked table suddenly show previous value, the subform will take the value. Effectively, duplicating the id.
So far, I have tried to refresh the linked table in MS ACCESS, closing the table and reopening it, also changing refresh interval from 60s to 30s, and lastly deleting transaction_id sequence and replacing it with identity column, but none of those works.
Can anyone please help me with this? I am desperate...
I am running PostgreSQL 10 with MS ACCESS 2007
Edits:
Here is the table definition
CREATE TABLE transactionlist (
transaction_id bigint PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
transaction_date date,
description VARCHAR(255),
source VARCHAR(50),
input_user int,
input_date date);
CodePudding user response：
Based on what you’ve presented, I believe you have an issue with your table linkage. The PRIMARY KEY constraint disallows duplicate values, so if Access is showing duplicates, they aren’t in the database (barring any malfunctions of the database server). You should verify this with psql.
It should also be impossible to specify the id in any DML statement without also specifying OVERRIDING SYSTEM VALUE, and I think you’d know if you’ve done that.
A common mistake is to not change the ODBC provider and connect to a test database instead of the production one.
CodePudding user response：
Found the answer guys! Shout out to Zlatko Matic from this post: https://www.postgresql.org/message-id/011501c58732$096ab5b0$e4321dc3@zlatko58zaczpv
It has to do with the how PostgreSQL interact with MS ACCESS. Under the reference:
"The Microsoft Jet database engine is designed around a keyset-driven model. This means that data is retrieved, inserted, and updated based on key values (in the case of a linked ODBC table, the unique index of a table). After Microsoft Access performs an insert or an update of a linked ODBC table, it uses a Where criteria to select the record again to verify the insert or update. The Where criteria is based on the unique index."
"Microsoft Access uses a similar process to retrieve records from an linked ODBC table. First, it retrieves the key values and then the rest of the fields that match the key values. If Microsoft Access is not able to find that value again when it tries to find the rest of the record, it assumes that the record is deleted."
In my case, many records are identical. Since, PostgreSQL only provide identity sequence number after the record has been updated, the unique primary key is not a reliable way to do cross checking between PostgreSQL and MS ACCESS. But because the many of my records are indentical, MS ACCESS assumed that the record has been written previously and return previous record.
Solution:
I changed input_date from date type to timestamp type. This will ensure that each record is unique and therefore, ACCESS will no longer be confused. I've been trying this for a week now just to be sure, and so far, there has not been a issue related to this.
From this:
| table_id (PK) | date | input_date |
|---|---|---|
| 1 | 14-11-2022 | 15-11-2022 |
| 2 | 14-11-2022 | 15-11-2022 |
| 3 | 14-11-2022 | 16-11-2022 |
| 4 | 14-11-2022 | 16-11-2022 |
| 3 | 14-11-2022 | 16-11-2022 |
| 3 | 14-11-2022 | 16-11-2022 |
| 3 | 14-11-2022 | 16-11-2022 |
| 3 | 14-11-2022 | 16-11-2022 |
To this:
| table_id (PK) | date | input_date |
|---|---|---|
| 1 | 14-11-2022 | 15-11-2022 00:00:20 |
| 2 | 14-11-2022 | 15-11-2022 00:00:40 |
| 3 | 14-11-2022 | 16-11-2022 00:01:00 |
| 4 | 14-11-2022 | 16-11-2022 00:01:20 |
| 5 | 14-11-2022 | 16-11-2022 00:01:40 |
| 6 | 14-11-2022 | 16-11-2022 00:02:00 |
| 7 | 14-11-2022 | 16-11-2022 00:02:20 |
| 8 | 14-11-2022 | 16-11-2022 00:02:40 |
Thanks for the help everyone!