CODEWITHSUNDEEP
elixirphoenix-frameworkecto
Strawberry
Strawberry

Reputation: 67968

Why does Phoenix's Ecto skip IDs when the previous insert was an error?

I am new to Phoenix and I was just playing around in iex when I noticed this happened. I was trying to insert a record and it had a validation error. After successfully inserting it, I found out the ID was not the next expected ID, so in my database it just has 2 records with ID 1 and 4.

Here is a log for example:

This is the first error, and notice the ID is 2.


iex(7)> Test.Projects.create_project(%{name: "Test", user: user})
[debug] QUERY ERROR db=3.6ms queue=6.4ms idle=1633.0ms
INSERT INTO "projects" ("name","inserted_at","updated_at") VALUES ($1,$2,$3) RETURNING "id" ["Test", ~N[2021-12-15 09:37:46], ~N[2021-12-15 09:37:46]]
** (Postgrex.Error) ERROR 23502 (not_null_violation) null value in column "user_id" of relation "projects" violates not-null constraint

    table: projects
    column: user_id

Failing row contains (2, Test, null, 2021-12-15 09:37:46, 2021-12-15 09:37:46).
    (ecto_sql 3.7.1) lib/ecto/adapters/sql.ex:760: Ecto.Adapters.SQL.raise_sql_call_error/1
    (ecto 3.7.1) lib/ecto/repo/schema.ex:744: Ecto.Repo.Schema.apply/4
    (ecto 3.7.1) lib/ecto/repo/schema.ex:367: anonymous fn/15 in Ecto.Repo.Schema.do_insert/4

This is the second error, and notice the ID is 3.

iex(7)> user.id
1
iex(8)> Test.Projects.create_project(%{name: "Test", user_id: user.id})
[debug] QUERY ERROR db=2.1ms queue=2.1ms idle=1264.9ms
INSERT INTO "projects" ("name","inserted_at","updated_at") VALUES ($1,$2,$3) RETURNING "id" ["Test", ~N[2021-12-15 09:38:02], ~N[2021-12-15 09:38:02]]
** (Postgrex.Error) ERROR 23502 (not_null_violation) null value in column "user_id" of relation "projects" violates not-null constraint

    table: projects
    column: user_id

Failing row contains (3, Test, null, 2021-12-15 09:38:02, 2021-12-15 09:38:02).
    (ecto_sql 3.7.1) lib/ecto/adapters/sql.ex:760: Ecto.Adapters.SQL.raise_sql_call_error/1
    (ecto 3.7.1) lib/ecto/repo/schema.ex:744: Ecto.Repo.Schema.apply/4
    (ecto 3.7.1) lib/ecto/repo/schema.ex:367: anonymous fn/15 in Ecto.Repo.Schema.do_insert/4

This was a successful insert, but notice it's ID is 4. I would have expected 2.

iex(8)> recompile
Compiling 2 files (.ex)
:ok
iex(9)> Test.Projects.create_project(%{name: "Test", user_id: user.id})
[debug] QUERY OK db=9.7ms queue=5.4ms idle=1907.0ms
INSERT INTO "projects" ("name","user_id","inserted_at","updated_at") VALUES ($1,$2,$3,$4) RETURNING "id" ["Test", 1, ~N[2021-12-15 09:38:26], ~N[2021-12-15 09:38:26]]
{:ok,
 %Test.Projects.Project{
   __meta__: #Ecto.Schema.Metadata<:loaded, "projects">,
   id: 4,
   inserted_at: ~N[2021-12-15 09:38:26],
   name: "Test",
   updated_at: ~N[2021-12-15 09:38:26],
   user: #Ecto.Association.NotLoaded<association :user is not loaded>,
   user_id: 1
 }}

Upvotes: 2

Views: 353

Answers (1)

DogEatDog
DogEatDog

Reputation: 3047

In short, this is expected behavior from the database to avoid "... To avoid blocking concurrent transactions..."

If you log into Postgres and do a \d+ <table_name> on your table, you should see something like this:

my_db=# \d+ accounts
                                                              Table "public.accounts"
   Column    |              Type              | Collation | Nullable |               Default                | Storage  | Stats target | Description 
-------------+--------------------------------+-----------+----------+--------------------------------------+----------+--------------+-------------
 id          | bigint                         |           | not null | nextval('accounts_id_seq'::regclass) | plain    |              | 
 name        | character varying(255)         |           |          |                                      | extended |              | 
 balance     | integer                        |           |          |                                      | plain    |              | 
 inserted_at | timestamp(0) without time zone |           | not null |                                      | plain    |              | 
 updated_at  | timestamp(0) without time zone |           | not null |                                      | plain    |              | 
Indexes:
    "accounts_pkey" PRIMARY KEY, btree (id)
    "accounts_name_index" UNIQUE, btree (name)
Access method: heap

The id column is set to nextval('accounts_id_seq'::regclass) which is a sequence. In the PG Docs on Sequences it says that even if the transaction fails, as in your first instance, the nextval, will not be rolled back. Thus, the next transaction will receive the next number in the sequence.

This sort of bleeds into being more of a Postgres-related question rather than a Phoenix/Elixir/Ecto, but I can certainly understand the confusion for anyone getting started with things.

Upvotes: 3

Related Questions