Обсуждение: Primary key definition?

I  started doing the following to define my primary keys long ago and in a
universe far away:

CREATE TABLE employee (
employee_key           integer DEFAULT nextval('employee_key_serial')
PRIMARY KEY ,

WEE ran into a scenario, after a total db restore on a project where we got
errors inserting new data because the keys were duplicated. Looking at a
pg_dump, it appears to me that I now understand why. Although the values f
the keys, and the data structures that reference them look like they will
get restored correctly, it appears to me that the sequences get recreated with
an initial value of 1, which means that on the next insert we will get 1 for
a key, which likely is already used. Looks like this is a different way of
defining this:


CREATE TABLE books (
id              SERIAL PRIMARY KEY,

Which has the advantage of not having to manually create the sequences. Will
this also enforce that the "internally created sequence" will be initialized
to a value above the maximum key in use on a pg_restore?



-- 
"They that would give up essential liberty for temporary safety deserve
neither liberty nor safety."
                        -- Benjamin Franklin

On 10/22/19 12:47 PM, stan wrote:
> I  started doing the following to define my primary keys long ago and in a
> universe far away:
> 
> CREATE TABLE employee (
> employee_key           integer DEFAULT nextval('employee_key_serial')
> PRIMARY KEY ,
> 
> WEE ran into a scenario, after a total db restore on a project where we got
> errors inserting new data because the keys were duplicated. Looking at a
> pg_dump, it appears to me that I now understand why. Although the values f
> the keys, and the data structures that reference them look like they will
> get restored correctly, it appears to me that the sequences get recreated with
> an initial value of 1, which means that on the next insert we will get 1 for
> a key, which likely is already used. Looks like this is a different way of
> defining this:
> 
> 
> CREATE TABLE books (
> id              SERIAL PRIMARY KEY,
> 
> Which has the advantage of not having to manually create the sequences. Will
> this also enforce that the "internally created sequence" will be initialized
> to a value above the maximum key in use on a pg_restore?

See my reply to your off-list post for why this is not correct. My guess 
is you got the duplicate key errors because the new data you inserted 
after the restore was using PK values that overlapped the old data. That 
is probably down to some script not starting at a value > max(PK).

> 
> 
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com

On 10/22/19 2:47 PM, stan wrote:
> I  started doing the following to define my primary keys long ago and in a
> universe far away:
>
> CREATE TABLE employee (
> employee_key           integer DEFAULT nextval('employee_key_serial')
> PRIMARY KEY ,
>
> WEE ran into a scenario, after a total db restore on a project where we got
> errors inserting new data because the keys were duplicated. Looking at a
> pg_dump, it appears to me that I now understand why. Although the values f
> the keys, and the data structures that reference them look like they will
> get restored correctly, it appears to me that the sequences get recreated with
> an initial value of 1, which means that on the next insert we will get 1 for
> a key, which likely is already used. Looks like this is a different way of
> defining this:
>
>
> CREATE TABLE books (
> id              SERIAL PRIMARY KEY,
>
> Which has the advantage of not having to manually create the sequences. Will
> this also enforce that the "internally created sequence" will be initialized
> to a value above the maximum key in use on a pg_restore?

When I restored an 8.4 database to 9.6 using pg_dump/pg_restore, all of the 
sequences were restored using the sequence values at the time the pg_dump ran.


-- 
Angular momentum makes the world go 'round.