On 12/21/19 12:21 PM, Boylan, Ross wrote:
Thank you for the confirmation on the need for a primary key. I suspected that, since the GUI needs an easy way to refer to a particular row. I think I saw such a restriction in the Qt documentation on a different project (just to be
clear: no Qt involved in this one--just more evidence this is a pretty general pattern).
It's interesting that Access does not behave this way if the backend is an Access (.mdb) file; I suppose it knows enough to get some unique identifier in that case. The
https://support.office.com/en-us/article/Add-or-change-a-table-s-primary-key-in-Access-07b4a84b-0063-4d56-8b00-65f2975e4379
"When you create a new table in Datasheet view, Access automatically
creates a primary key for you and assigns it a field name of "ID" and
the AutoNumber data type."
difference is not that the backend table in Access has a primary key;
the reason the PG table lacked a primary key was that the Access table
from which it was migrated didn't have one.
My guess is the migration process missed that aliquotid was the PK.
I've read more about sequences, and it seems mine just aren't in sync with the data, and that using sequences requires some care. Easier to discuss with an example.
CREATE TABLE tx (
xid serial,
a int8
);
This will produce a sequence tx_xid_seq.
If I do
INSERT INTO tx VALUES (3, 4);
the sequence doesn't know about it. So if I later create a default value with
INSERT INTO tx (a) VALUES (7);
or
INSERT INTO tx VALUES (DEFAULT, 9);
I'll just get the next value in the sequence.
The 3rd time I use the default value it will return 3, same as the record already there. If there is a PRIMARY KEY (or UNIQUE) constraint on xid the insertion will fail.
So....
1. When I migrate data, as I have done, I should ensure that the sequences are at safe values. The obvious way to do that would be setval using the max of the values in the data.
2. In operations, the program needs to either be consistent about getting id values from the default, or be very careful. Since I'm using an inherited application, I need to check.
When I migrate the data I do NOT want to use the sequence to generate the identifiers, since that will trash the referential integrity of the data
That rather depends on what you using to migrate the data. Postgres
pg_dump will not do that if you dump the table.
Here's the transcript of my tests. BTW, why is log_cnt jumping to 32 on the sequence?
log_cnt is an internal counter:
https://www.postgresql.org/message-id/7487.1049476267%40sss.pgh.pa.us
and does not really apply to the sequence value. The value is determined
by last_value and is_called. For more information see:
https://www.postgresql.org/docs/11/sql-createsequence.html
You skipped over the sequence by starting with a value of 2 and then you
went back and used the sequence, so when it got to 2 it threw a
duplicate error which is correct as the field was a PK. Best practice on
a sequence is to let it run on its own and not try to override it. If
you do then you will need to familiarize yourself with the functions here:
https://www.postgresql.org/docs/11/functions-sequence.html
--Now with a PRIMARY KEY constraint
testNTB=> CREATE TABLE ty (
testNTB(> yid serial,
testNTB(> a int8,
testNTB(> PRIMARY KEY (yid));
CREATE TABLE
testNTB=> INSERT INTO ty VALUES (2, 10);
INSERT 0 1
testNTB=> select * from ty;
yid | a
-----+----
2 | 10
(1 row)
testNTB=> select * from ty_yid_seq;
last_value | log_cnt | is_called
------------+---------+-----------
1 | 0 | f
(1 row)
testNTB=> INSERT INTO ty VALUES (DEFAULT, 20);
INSERT 0 1
testNTB=> select * from ty;
yid | a
-----+----
2 | 10
1 | 20
(2 rows)
testNTB=> select * from ty_yid_seq;
last_value | log_cnt | is_called
------------+---------+-----------
1 | 32 | t
(1 row)
testNTB=> INSERT INTO ty VALUES (DEFAULT, 30);
ERROR: duplicate key value violates unique constraint "ty_pkey"
DETAIL: Key (yid)=(2) already exists.
--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx
