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 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.
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
Here's the transcript of my tests. BTW, why is log_cnt jumping to 32 on the sequence?
testNTB=> CREATE TABLE tx (
testNTB(> xid serial,
testNTB(> a int8
testNTB(> );
CREATE TABLE
testNTB=> select * from tx_xid_seq;
last_value | log_cnt | is_called
------------+---------+-----------
1 | 0 | f
(1 row)
testNTB=> INSERT INTO tx VALUES (3, 4);
INSERT 0 1
testNTB=> select * from tx_xid_seq;
last_value | log_cnt | is_called
------------+---------+-----------
1 | 0 | f
(1 row)
testNTB=> select * from tx;
xid | a
-----+---
3 | 4
(1 row)
testNTB=> INSERT INTO tx (a) VALUES (7);
INSERT 0 1
testNTB=> select * from tx;
xid | a
-----+---
3 | 4
1 | 7
(2 rows)
testNTB=> select * from tx_xid_seq;
last_value | log_cnt | is_called
------------+---------+-----------
1 | 32 | t
(1 row)
testNTB=> INSERT INTO tx VALUES (DEFAULT, 9);
INSERT 0 1
testNTB=> select * from tx_xid_seq;
last_value | log_cnt | is_called
------------+---------+-----------
2 | 31 | t
(1 row)
testNTB=> select * from tx;
xid | a
-----+---
3 | 4
1 | 7
2 | 9
(3 rows)
testNTB=> INSERT INTO tx (a) VALUES (77);
INSERT 0 1
testNTB=> select * from tx_xid_seq;
last_value | log_cnt | is_called
------------+---------+-----------
3 | 30 | t
(1 row)
testNTB=> select * from tx;
xid | a
-----+----
3 | 4
1 | 7
2 | 9
3 | 77
(4 rows)
--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.
