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.
