Re: Semi-unable to add new records to table--primary key needed?

Thank you for the confirmation on the need for a primary key.  I suspected that, since the GUI needs an easy way to
referto 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 
enoughto 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
somecare.  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
dothat 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
referentialintegrity 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.