Postgresql manually inserted rows vs Hibernate save with sequence

Question

I've got an entity with @Id column when I have:

@Id
@GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "seq")
@SequenceGenerator(name = "seq", sequenceName = "user_accounts_id_seq")
public Long getId(){
    return id;
}

When I manually insert data into table by executing:

insert into user_accounts(id, name) values(1, "John");

I'm not using sequence but manually adding id equals to 1.

Then I create user account in Java by

UserAccount user = new UserAccount(null, "Paul") // where null => id

and save it with UserAccountService's method save and get an error like there is duplicate in id key.

I am not sure if I understand the strategies well. I want to be able to add some values manually in database editor and then in program when saving if value with id exists hibernate should take next possible value.

Answer 1

If type of id is serial and user_accounts_id_seq is auto generated sequence for this attribute then for manually inserting use:

insert into user_accounts(name) values("John");

id will be getted from user_accounts_id_seq sequence.
So, you and hibernate will be use same user_accounts_id_seq sequence that warrants unique values for id.

Answer 2

It is not a good idea to mix hand-made ids with ids generated by the database. You should avoid it as much as possible.

How sequences work

Let's say this is your table:

CREATE TABLE user_accounts
(
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL CHECK(trim(name) > '')
) ;

-- The previous `CREATE` has actually worked as if it were defined like:
CREATE TABLE user_accounts
(
    -- Name of sequence = name of table || '_' || name of column || '_id'
    id INTEGER NOT NULL PRIMARY KEY DEFAULT nextval('user_accounts_id_seq'),
    name TEXT NOT NULL CHECK(trim(name) > '')
) ;

At this point, you can actually check that one sequence has been created:

SELECT *
  FROM information_schema.sequences;

sequence_catalog | sequence_schema             | sequence_name        | data_type | numeric_precision | numeric_precision_radix | numeric_scale | start_value | minimum_value | maximum_value       | increment | cycle_option
:--------------- | :-------------------------- | :------------------- | :-------- | ----------------: | ----------------------: | ------------: | :---------- | :------------ | :------------------ | :-------- | :-----------
postgres         | fiddle_sehahfpstptzxjchrypb | user_accounts_id_seq | bigint    |                64 |                       2 |             0 | 1           | 1             | 9223372036854775807 | 1         | NO          

At this point, you can insert one row into the table, without specifying the value for column id, the id column will take its default value, which will be taken as nextval for the sequence named user_accounts_id_seq:

INSERT INTO user_accounts
    (name)
VALUES
    ('First inserted user account') ;

In a single user setup (nobody else doing anything at all with user_accounts), we'll have gotten id = 1:

SELECT * FROM user_accounts;

id | name                       
-: | :--------------------------
 1 | First inserted user account

You can now check he sequence current value, it is 1: SELECT currval('user_accounts_id_seq') ;

| currval |
| ------: |
|       1 |

We can now do weird things. First we insert a row with explicit NULL value for id. It won't work in SQL (I don't know whether Hibernate might manipulate this INSERT by itself and remove the NULL and convert it to DEFAULT):

INSERT INTO user_accounts
    (id, name)
VALUES
    (NULL, 'It won''t work');

ERROR:  null value in column "id" violates not-null constraint
DETAIL:  Failing row contains (null, It won't work).

The sequence current value continues to be the same

SELECT currval('user_accounts_id_seq') ;

| currval |
| ------: |
|       1 |

At thsi point, we can insert a row with id = 2. It will work, because there's not any row with that id already in the table:

INSERT INTO user_accounts
    (id, name)
VALUES
    (2, 'Inserted a 2 id, it will work, but will produce problems');

1 rows affected

However, the sequence is not changed, and this will lead to later problems:

SELECT currval('user_accounts_id_seq') ;

| currval |
| ------: |
|       1 |

If we now try to insert with a sequence number, we will be out of luck, the sequence will give a nextval of 2 (currval + 1). As id = 2 is already on the table, it will produce a PK violation:

INSERT INTO user_accounts
    (name)
VALUES
    ('This won''t work either, we broke the sequence');

ERROR:  duplicate key value violates unique constraint "user_accounts_pkey"
DETAIL:  Key (id)=(2) already exists.

You can see all the setup and experiment at: dbfiddle here

---

Workaround:

If you really need to both work with automatically generated ids and also with manually generated ids, the safest way is to make sure they are in ranges that do not overlap. For instance, reserve id 1 ... 10000 for manual input, and start your sequence at 10001 for automatic input.

CREATE TABLE user_accounts
(
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL CHECK(trim(name) > '')
) ;
ALTER SEQUENCE user_accounts_id_seq RESTART WITH 10001 ;

I'd recommend not to try to have the database (via trigger or however) to use the next available id it tehre is a PK violation. You'll need to have SERIALIZABLE isolation level for it to work, or you still will have a high chance of having problems in concurrent scenarios.

Check it at dbfiddle here