framework: Postgres sequences don’t seem to be set if running queries in transaction

Laravel Version: 5.5.28
PHP Version: 7.2.0 (Homestead default)
Database Driver & Version: Postgres, 2.27.1 (Homestead default)

Description:

I’ve created an Artisan command that imports data into a Postgres database using the DB façade. These insert statements run inside a DB::transaction() call. When I then go to interact with my application (i.e. register), I get the following error:

SQLSTATE[23505]: Unique violation: 7 ERROR: duplicate key value violates unique constraint “users_pkey” DETAIL: Key (id)=(1) already exists.

Steps To Reproduce:

Insert a record inside a transaction:

DB::transaction(function () {
    DB::table('users')->insert([
        // values
    ]);
});

Interact with application.
Get above error.

I can also confirm if I connect to the Postgres database and run SELECT nextval('users_id_seq'), I get:

ERROR: currval of sequence “users_id_seq” is not yet defined in this session

About this issue

Original URL
State: closed
Created 6 years ago
Comments: 23 (17 by maintainers)

Most upvoted comments

New & improved @phfoxer.

DO $$
DECLARE
rec RECORD;
LAST_ID integer;
BEGIN
FOR rec IN SELECT
table_name
FROM information_schema.columns WHERE table_schema='public' and column_name='id' and data_type='integer'
LOOP
execute 'SELECT (id + 1) as id FROM ' || rec.table_name || ' ORDER BY id DESC LIMIT 1' into LAST_ID;
RAISE NOTICE 'ALTER SEQUENCE public.%_id_seq INCREMENT 1 MINVALUE 1 MAXVALUE 9223372036854775807 START 1 RESTART % CACHE 1 NO CYCLE;', rec.table_name, LAST_ID;
END LOOP;
END; $$

lastlink on Jul 24, 2018

I improve the following code to avoid empty table results in LAST_ID <NULL>,

RAISE NOTICE 'ALTER SEQUENCE public.%_id_seq INCREMENT 1 MINVALUE 1 MAXVALUE 9223372036854775807 START 1 RESTART % CACHE 1 NO CYCLE;', rec.table_name, LAST_ID;

should be:

RAISE NOTICE 'ALTER SEQUENCE public.%_id_seq INCREMENT 1 MINVALUE 1 MAXVALUE 9223372036854775807 START 1 RESTART % CACHE 1 NO CYCLE;', rec.table_name, COALESCE(LAST_ID, 1);

josephMG on Dec 17, 2018

yes and yes

However, it’s not an “issue” of pgsql, it’s document and expected behaviour and unlikely to change.

mfn on Mar 12, 2022

You can reset it with something like (untested, should get you started):

SELECT SETVAL('nameofsequence', (SELECT MAX(column) FROM table));

mfn on Jan 9, 2018

Postgresql only reads (and consumes) from the sequence if it has to. If you specify the id yourself the sequence is left untouched, and when you in the future attempt to insert something the sequence says “sure, use id 1” which introduces the conflict.

The solution is to reset the sequence to the next available identifier. I do not know postgresql commands to do that.

I would classify this as a user-error when working with postgresql. Mysql does indeed support what you’re doing, but postgresql requires some additional magic.

sisve on Jan 9, 2018