Can't `Insert` models anymore: duplicate key value violates unique constraint

[andradei]

I am having this problem from what it seems to be out of nowhere. My project suddenly stopped being able to MyModel.Insert(...).

I made a throwaway repository with a project that reproduces the problem. If you run ./cmds.sh you'll get to the first Insert method call with the following error:

panic: no inserting thing: models: unable to insert into things: pq: duplicate key value violates unique constraint "things_pkey"

It is as though the Insert generated methods aren't figuring out a valid unique ID anymore.

Here's the small project that reproduces this: https://github.com/andradei/sqlboiler-case

Detail about the project:

The generation process and the Go source connect to a postgres docker container that is built and run locally through ./cmds.sh

[aarondl]

First, thank you for the incredible bug report. If every bug report was submitted with this much detail all open source maintainers would live happier lives.

Your problem doesn't exist in sqlboiler. It exists in postgresql.

You're using the column type serial, which behind the scenes creates a sequence object in postgresql. You can see it's details pretty easily like this:

postgres=# select * from things_id_seq;
 last_value | log_cnt | is_called 
------------+---------+-----------
          1 |      32 | t

Notice the last_value. When your datatype is a serial it'll take it's associated sequence and use the function nextval in postgres to get the value that should be next in line to be inserted.

See this documentation for more details: https://www.postgresql.org/docs/11/sql-createsequence.html

The first insert in your example provides this error in the psql logs:

2019-02-07 03:38:22.386 UTC [78] ERROR:  duplicate key value violates unique constraint "things_pkey"
2019-02-07 03:38:22.386 UTC [78] DETAIL:  Key (id)=(1) already exists.

The reason for this is because you already have data that has the value that comes out of nextval() as an ID. And postgres does no intelligent checking for this situation, it just fails with this error.

If you do not populate the database with any data, it should work. And because a sequence increments even if the insert fails, the sequence number is consumed (incremented) meaning if you do it enough times (run the example program 4 times), you eventually get this exact same code to work (or rather you get it to give you a different error).

I'm convinced that sqlboiler is doing the right thing here and that you'll have to understand and be more careful around your use of the serial data type.

I do get another error after the sequence increments far enough:

panic: no adding: failed to insert into join table: pq: insert or update on table "persons_things" violates foreign key constraint "persons_things_person_id_fkey"

But I didn't look into why this is happening as I don't believe it's of any consequence to sqlboiler. Thanks again for the great bug report.

[ivishnevs]

I stuck in very similar problem, not sure should i open new issue or not so... I get this error after trying to INSERT second time: models: unable to insert into customers: pq: duplicate key value violates unique constraint "pk_customers"

Customers schema:

CREATE TABLE "customers" (
    "id" SERIAL,
    ...
    "random_token" varchar(44)   NOT NULL,
    CONSTRAINT "pk_customers" PRIMARY KEY (
        "id"
    ),
    ...
    CONSTRAINT "uc_customers_random_token" UNIQUE (
        "random_token"
    )
);

It seems that for the some reason boil.Infer() don't skip PK column and every time pass 0 as default value for id. if I try to use boil.Blacklist(models.CustomerColumns.ID) it obviously works.

[aarondl]

It's possible for some reason it's not understanding the primary key? Check the --debug output and check that your primary keys are correctly listed.

[andradei]

@aarondl (regarding your first reply) Thank you for the detailed explanation. I'm wondering now what I did for this same logic (which was working before) stopped working with what seems to be the correct behavior for postgres.

But I didn't look into why this is happening as I don't believe it's of any consequence to sqlboiler. Thanks again for the great bug report.

This part is probably still the same error: nextval() giving me a new sequence value that already exists in the other table of the relationship.

So I'll have to find another way to seed the database. Maybe put everything in the code instead of in INSERT clauses in init.sql. Or maybe create a custom sequence that starts with a value I know won't cause a collision.

EDIT

For future reference, and to potentially help others:

Got it working and pushed the solution to the throwaway repo. I might create a Gist and delete the repo sometime in the future.

But to fix it I removed the manual addition of the id in init.sql, this will trigger SERIAL's nextval() and generate the IDs from the sequences correctly.

Then I had to make sure the person and thing were inserted in the table before the persons_things table could be populated. Notice I first insert aquaman, then I call the helper function aquaman.AddThings with the insert flag set to true. This will insert trident into the database, and then create the persons_things row.

Insertion order matters.

[ivishnevs]

@aarondl hmm...

var (
    customerColumns               = []string{"id", ..., "random_token"}
        customerColumnsWithoutDefault = []string{"id", ..., "random_token"}
    customerColumnsWithDefault    = []string{}
    customerPrimaryKeyColumns     = []string{"id"}
)

Seems like id is recognized like Pkey, but in Insert logic (generated from 15_insert.go.tpl) I could not find customerPrimaryKeyColumns usage:

wl, returnColumns := columns.InsertColumnSet(
            customerColumns,
            customerColumnsWithDefault,
            customerColumnsWithoutDefault,
            nzDefaults,
        )

UPDATE I figured out that id column should be populated with default value in the database layer to make sure that InsertColumnSet is computed properly. Anyway thanks for your replay!