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

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
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 
 


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
requiressome 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
(orUNIQUE) 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
todo 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
verycareful.  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
 

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@aklaver.com