Help with Repo.update()

Questions / Help
,
1

Hello,

I am having some difficulty with ecto update. I am getting this {:ok, %Postgrex.Result{}} when I am updating the changeset that has changes in the primary_key, this changeset has a composite primary keys. the migration look like below -

create table(:stock_items, primary_key: false) do
  add :roll, :string, primary_key: true
  add :company_initial, references(:companies, column: :initial, type: :string), primary_key: true
  add :product_code, references(:products, column: :code, type: :string), primary_key: true
  add :start_quantity, :decimal
  add :current_quantity, :decimal 
  timestamps()
end

and the schema look like this -

# Other modules definition and changeset are ignore.
@primary_key false  
schema "stock_items" do
  belongs_to :company, Stock.Company, foreign_key: :company_initial, references: :initial, primary_key: :initial, type: string
  belongs_to :product, Stock.Product, foreign_key: :product_code, references: :code, primary_key: :code, type: :string
  field :roll, :string
  field :start_quantity, :decimal
  field :current_quantity, :decimal
  timestamps()
end

when I am trying to update the changeset (this changeset has changes that include it primary key.), I get the return back as {:ok, {:ok, %Postgrex.Result{columns: nil, command: :update, connection_id: 5356, num_rows: 2, rows: nil}}
My question and problem here is that what is it returning this isn’t it suppose to struct of the specific model type. If this the right result how do we pattern match further to get updated struct. like we usually get when we do normal Repo.update()

Thank you very much for your help

Dev.

1 Like
2

If you are getting that result, it is certainly an Ecto bug. However, notice that you are getting two rows back, which means Ecto is being unable to change a single record and that is messing things up. If you can provide a simple app that reproduces the issue, we would really appreciate it.

You can probably fix this by listing both keys as primary keys:

@primary_key [{:product_code, :string, []}, {:company_initial, :string, []}]
schema "stock_items" do
belongs_to :company, Stock.Company, foreign_key: :company_initial, references: :initial, primary_key: :initial, define_field: false
belongs_to :product, Stock.Product, foreign_key: :product_code, references: :code, primary_key: :code, define_field: false
...
1 Like
3

Btw, which Ecto version are you running? I just checked the source and you are not even supposed to be able to update a schema if it doesn’t have a primary key.

1 Like
4

Hi @josevalim,

I tried to use set attribute @primary_key as a list like you mentioned.
But I get

@primary_key must be false or {name, type, opts}

The database really has a primary key but composite of three fields.

I am using the following ecto -

“ecto”: {:hex, :ecto, “2.0.5”, “7f4c79ac41ffba1a4c032b69d7045489f0069c256de606523c65d9f8188e502d”
“phoenix_ecto”: {:hex, :phoenix_ecto, “3.0.1”, "42eb486ef732cf209d0a353e791806721f33ff40beab0a86f02070a5649ed00a

My mix.lock look like the following -

%{“connection”: {:hex, :connection, “1.0.4”, “a1cae72211f0eef17705aaededacac3eb30e6625b04a6117c1b2db6ace7d5976”, [:mix], },
“cowboy”: {:hex, :cowboy, “1.0.4”, “a324a8df9f2316c833a470d918aaf73ae894278b8aa6226ce7a9bf699388f878”, [:rebar, :make], [{:cowlib, “~> 1.0.0”, [hex: :cowlib, optional: false]}, {:ranch, “~> 1.0”, [hex: :ranch, optional: false]}]},
“cowlib”: {:hex, :cowlib, “1.0.2”, “9d769a1d062c9c3ac753096f868ca121e2730b9a377de23dec0f7e08b1df84ee”, [:make], },
“db_connection”: {:hex, :db_connection, “1.0.0”, “63c03e520d54886a66104d34e32397ba960db6e74b596ce221592c07d6a40d8d”, [:mix], [{:connection, “~> 1.0.2”, [hex: :connection, optional: false]}, {:poolboy, “~> 1.5”, [hex: :poolboy, optional: true]}, {:sbroker, “~> 1.0”, [hex: :sbroker, optional: true]}]},
“decimal”: {:hex, :decimal, “1.2.0”, “462960fd71af282e570f7b477f6be56bf8968e68277d4d0b641a635269bf4b0d”, [:mix], },
“distillery”: {:hex, :distillery, “0.10.1”, “14fccade4b8ab849b99e21c4bdfaa1092dbacdce8afd33f5c369c6e114385b0e”, [:mix], },
“ecto”: {:hex, :ecto, “2.0.5”, “7f4c79ac41ffba1a4c032b69d7045489f0069c256de606523c65d9f8188e502d”, [:mix], [{:db_connection, “~> 1.0-rc.4”, [hex: :db_connection, optional: true]}, {:decimal, “~> 1.1.2 or ~> 1.2”, [hex: :decimal, optional: false]}, {:mariaex, “~> 0.7.7”, [hex: :mariaex, optional: true]}, {:poison, “~> 1.5 or ~> 2.0”, [hex: :poison, optional: true]}, {:poolboy, “~> 1.5”, [hex: :poolboy, optional: false]}, {:postgrex, “~> 0.12.0”, [hex: :postgrex, optional: true]}, {:sbroker, “~> 1.0-beta”, [hex: :sbroker, optional: true]}]},
“fs”: {:hex, :fs, “0.9.2”, “ed17036c26c3f70ac49781ed9220a50c36775c6ca2cf8182d123b6566e49ec59”, [:rebar], },
“gen_stage”: {:hex, :gen_stage, “0.8.0”, “a76e3f0530f86fae8b8a1021c06527b1ec171cf4c0bdfecd8d5ad0376d1205af”, [:mix], },
“gettext”: {:hex, :gettext, “0.12.1”, “c0624f52763469ef7a3674919ae28b8286d88195b90fa1516180f31bbbd26d14”, [:mix], },
“mime”: {:hex, :mime, “1.0.1”, “05c393850524767d13a53627df71beeebb016205eb43bfbd92d14d24ec7a1b51”, [:mix], },
“phoenix”: {:hex, :phoenix, “1.2.1”, “6dc592249ab73c67575769765b66ad164ad25d83defa3492dc6ae269bd2a68ab”, [:mix], [{:cowboy, “~> 1.0”, [hex: :cowboy, optional: true]}, {:phoenix_pubsub, “~> 1.0”, [hex: :phoenix_pubsub, optional: false]}, {:plug, “~> 1.1”, [hex: :plug, optional: false]}, {:poison, “~> 1.5 or ~> 2.0”, [hex: :poison, optional: false]}]},
“phoenix_ecto”: {:hex, :phoenix_ecto, “3.0.1”, “42eb486ef732cf209d0a353e791806721f33ff40beab0a86f02070a5649ed00a”, [:mix], [{:ecto, “~> 2.0”, [hex: :ecto, optional: false]}, {:phoenix_html, “~> 2.6”, [hex: :phoenix_html, optional: true]}, {:plug, “~> 1.0”, [hex: :plug, optional: false]}]},
“phoenix_html”: {:hex, :phoenix_html, “2.7.0”, “19e12e2044340c2e43df206a06d059677c59ea1868bd1c35165438d592cd420b”, [:mix], [{:plug, “~> 1.0”, [hex: :plug, optional: false]}]},
“phoenix_live_reload”: {:hex, :phoenix_live_reload, “1.0.5”, “829218c4152ba1e9848e2bf8e161fcde6b4ec679a516259442561d21fde68d0b”, [:mix], [{:fs, “~> 0.9.1”, [hex: :fs, optional: false]}, {:phoenix, “~> 1.0 or ~> 1.2-rc”, [hex: :phoenix, optional: false]}]},
“phoenix_pubsub”: {:hex, :phoenix_pubsub, “1.0.1”, “c10ddf6237007c804bf2b8f3c4d5b99009b42eca3a0dfac04ea2d8001186056a”, [:mix], },
“plug”: {:hex, :plug, “1.2.2”, “cfbda521b54c92ab8ddffb173fbaabed8d8fc94bec07cd9bb58a84c1c501b0bd”, [:mix], [{:cowboy, “~> 1.0”, [hex: :cowboy, optional: true]}, {:mime, “~> 1.0”, [hex: :mime, optional: false]}]},
“poison”: {:hex, :poison, “2.2.0”, “4763b69a8a77bd77d26f477d196428b741261a761257ff1cf92753a0d4d24a63”, [:mix], },
“poolboy”: {:hex, :poolboy, “1.5.1”, “6b46163901cfd0a1b43d692657ed9d7e599853b3b21b95ae5ae0a777cf9b6ca8”, [:rebar], },
“postgrex”: {:hex, :postgrex, “0.12.1”, “2f8b46cb3a44dcd42f42938abedbfffe7e103ba4ce810ccbeee8dcf27ca0fb06”, [:mix], [{:connection, “~> 1.0”, [hex: :connection, optional: false]}, {:db_connection, “~> 1.0-rc.4”, [hex: :db_connection, optional: false]}, {:decimal, “~> 1.0”, [hex: :decimal, optional: false]}]},
“ranch”: {:hex, :ranch, “1.2.1”, “a6fb992c10f2187b46ffd17ce398ddf8a54f691b81768f9ef5f461ea7e28c762”, [:make], }}

For reproducing this. Let’s me commit this one and update you inanother reply.
Sorry for the delay. Was attending the class for the whole day.

Thanks @josevalim

Dev.

1 Like
6

Actually, please disregard my replies. Your first example was supposed to work just fine.

There are two issues here: it seems the same set of composite keys are identifying two rows in the database, can you please confirm if this is true? If so, maybe you should add a unique index that guarantees this won’t happen again?

Btw, I have verified and it seems Ecto 2.1 already raises a proper error message for those cases.

1 Like
7

Could you recommend step to verify this.
Is it like trying to update the changeset (changeset that has changes in one of the primary key.) and the I get above error. If this is the case then. It’s true.

I will add another unique key on the table. eventhough I already have the primary_key: true on the supposed column like below.

create table(:stock_items, primary_key: false) do
add :roll, :string, primary_key: true
add :company_initial, references(:companies, column: :initial, type: :string), primary_key: true
add :product_code, references(:products, column: :code, type: :string), primary_key: true
add :start_quantity, :decimal
add :current_quantity, :decimal
timestamps()
end

I try this an update you.
Thank you @josevalim

1 Like
8

I am still getting the same error here migrating the unique keys like below.

defmodule Stock.Repo.Migrations.AddKeyStockItem do
  use Ecto.Migration

  def change do
    create unique_index(:stock_items,[:roll, :product_code, :company_initial] )
  end
end

–
Dev

1 Like
9

If you define the primary_key: true in your migration then you don’t need the unique index. Then Ecto surely is not supposed to be returning two columns.

Only last question: can you please copy and paste the logs when the operation fails with the error you first reported? I would like to see which query it is running.

1 Like
10

Please see log below:

[info] POST /companies/1/stock_items/1-Cotton400-2
[debug] QUERY OK source="companies" db=0.5ms decode=0.1ms queue=0.1ms
SELECT c0."id", c0."initial", c0."name" FROM "companies" AS c0 []
[debug] Processing by Stock.StockItemController.update/2
  Parameters: %{"_csrf_token" => "BTdPUygmSzcvZiYtWgYfcwUXFlROEAAAINdgLGyQXPOL9vhF0np18w==", "_method" => "put", "_utf8" => "✓", "company_initial" => "1", "companyintial_productcode_roll_as_route_id" => "1-Cotton400-2", "stock_item" => %{"current_quantity" => "10", "product_code" => "Cotton300", "roll" => "2", "start_quantity" => "10"}}
  Pipelines: [:browser]
[debug] QUERY OK source="stock_items" db=0.6ms queue=0.1ms
SELECT s0."company_initial", s0."product_code", s0."roll", s0."start_quantity", s0."current_quantity", s0."inserted_at", s0."updated_at" FROM "stock_items" AS s0 WHERE (((s0."company_initial" = $1) AND (s0."product_code" = $2)) AND (s0."roll" = $3)) ["1", "Cotton400", "2"]
[debug] QUERY OK source="products" db=0.4ms queue=0.1ms
SELECT p0."id", p0."code" FROM "products" AS p0 []
[debug] %{"current_quantity" => "10", "product_code" => "Cotton300", "roll" => "2", "start_quantity" => "10"}
[debug] %Stock.StockItem{__meta__: #Ecto.Schema.Metadata<:loaded, "stock_items">, company: #Ecto.Association.NotLoaded<association :company is not loaded>, company_initial: "1", current_quantity: #Decimal<10>, inserted_at: #Ecto.DateTime<2016-11-06 07:04:42>, product: #Ecto.Association.NotLoaded<association :product is not loaded>, product_code: "Cotton400", roll: "2", start_quantity: #Decimal<10>, updated_at: #Ecto.DateTime<2016-11-06 07:04:42>}
[debug] QUERY OK db=0.2ms
begin []
[debug] QUERY OK db=3.1ms
UPDATE "stock_items" SET "product_code" = $1, "updated_at" = $2 WHERE "company_initial" = $3 AND "product_code" = $4 ["Cotton300", {{2016, 11, 11}, {18, 6, 5, 0}}, "1", "Cotton400"]
[debug] QUERY OK db=0.6ms
rollback []

I have also log the changeset below -

[debug] ecto.Changeset<action: nil, changes: %{product_code: “Cotton300”}, errors: , data: #Stock.StockItem<>, valid?: true>

The original item look like this -

%Stock.StockItem{meta: ecto.Schema.Metadata<:loaded, “stock_items”>, company: ecto.Association.NotLoaded, company_initial: “1”, current_quantity: #Decimal<10>, inserted_at: ecto.DateTime<2016-11-06 07:04:42>, product: ecto.Association.NotLoaded, product_code: “Cotton400”, roll: “2”, start_quantity: #Decimal<10>, updated_at: ecto.DateTime<2016-11-06 07:04:42>}

and the params is below -

%{“current_quantity” => “10”, “product_code” => “Cotton300”, “roll” => “2”, “start_quantity” => “10”}

@josevalim I am very appreciate on your help on this.

1 Like
11

I see from the log it’s showing rollback.
I am looking for why is it rollback. Eventhough it is valid.

12

Can you try running the query directly in the database:

UPDATE "stock_items" SET "product_code" = "Cotton300" WHERE "company_initial" = "1" AND "product_code" = "Cotton400" RETURNING *

And then see how many results they return and what are they? You may need to change the parameters in the WHERE clause to something that exists.

1 Like
13

Yes the problem is that it could not update.
Please see below.

Let me list the content of the table to see that things are valid -

stock_dev=# select * from products;
id | code
----±----------
1 | Cotton400
2 | Cotton300
(2 rows)

stock_dev=# select * from companies;
id | initial | name
----±--------±------------
10 | 1 | Tailormaker
11 | 2 | Cottonnmore
(2 rows)

stock_dev=# select * from companies;
id | initial | name
----±--------±------------
10 | 1 | Tailormaker
11 | 2 | Cottonnmore
(2 rows)

When I run this

stock_dev=# update stock_items set product_code = “Cotton300” where company_initial = “1” and product_code = “Cotton400”
;
ERROR: column “1” does not exist
LINE 1: …roduct_code = “Cotton300” where company_initial = “1” and pr…

The update couldn’t go through. But I see name and everything are collect here. And it has no problem when creating the item also.

1 Like
14

Try this one instead:

update stock_items set product_code = ‘Cotton300’ where company_initial = ‘1’ and product_code = ‘Cotton400’

Or:

update stock_items set product_code = ‘Cotton300’ where company_initial = 1 and product_code = ‘Cotton400’

1 Like
16

The last one work.
But it actually update two columns.

stock_dev=# update stock_items set product_code = ‘Cotton300’ where company_initial = ‘1’ and product_code = ‘Cotton400’;
UPDATE 2

stock_dev=# select * from stock_items;
roll | company_initial | product_code | start_quantity | current_quantity | inserted_at | updated_at
------±----------------±-------------±---------------±-----------------±--------------------±--------------------
1 | 1 | Cotton300 | 10 | 10 | 2016-11-06 07:02:11 | 2016-11-06 07:02:11
2 | 1 | Cotton300 | 10 | 10 | 2016-11-06 07:04:42 | 2016-11-06 07:04:42
(2 rows)

Now I see we can scope down the problem. The problem here is when updating the sql generated doesn’t have the roll field in the where clause.

1 Like
17

Is the roll part of the composite primary key? If so it should be added and then we are good to go!

1 Like
18

It’s already added.

19

And I also try changing the roll as a single field also. I am getting weird error I am getting that key roll has been already taken.
Please see below -

UPDATE “stock_items” SET “roll” = $1, “updated_at” = $2 WHERE “company_initial” = $3 AND “product_code” = $4 [“4”, {{2016, 11, 11}, {19, 8, 53, 0}}, “1”, “Cotton300”]
[debug] QUERY OK db=0.8ms
rollback

And the error I got back is roll is already been taken eventhough I has no 4 as roll here.
stock_dev=# select * from stock_items;
roll | company_initial | product_code | start_quantity | current_quantity | inserted_at | updated_at
------±----------------±-------------±---------------±-----------------±--------------------±--------------------
1 | 1 | Cotton300 | 10 | 10 | 2016-11-06 07:02:11 | 2016-11-06 07:02:11
2 | 1 | Cotton300 | 10 | 10 | 2016-11-06 07:04:42 | 2016-11-06 07:04:42
(2 rows)

1 Like
20

Are you sure roll has been added as a primary key both in your database and in the schema? If you did, we were supposed to see in WHERE. Also, wouldn’t it be simpler to just use an ID?

1 Like
21

Yes let’s me copy and paste migration and model code for u.

defmodule Stock.StockItem do
use Stock.Web, :model

require Logger
@primary_key false

schema “stock_items” do
belongs_to :company, Stock.Company, foreign_key: :company_initial, references: :initial, primary_key: :initial, type: :string
belongs_to :product, Stock.Product, foreign_key: :product_code, references: :code, primary_key: :code, type: :string
field :roll, :string
field :start_quantity, :decimal
field :current_quantity, :decimal
timestamps()
end

@doc “”"
Builds a changeset based on the struct and params.
“”"
def changeset(struct, params \ %{}) do
Logger.debug inspect params
Logger.debug inspect struct
struct
|> cast(params, [:company_initial, :product_code, :roll, :start_quantity, :current_quantity])
|> validate_format(:roll, ~r/[1]+$/)
|> validate_required([:company_initial, :product_code, :roll, :start_quantity, :current_quantity])
|> validate_number(:start_quantity, [greater_than_or_equal_to: 0])
|> validate_number(:current_quantity, [greater_than_or_equal_to: 0])
|> unique_constraint(:roll, name: :stock_items_pkey)
end
end

migration -

defmodule Stock.Repo.Migrations.UpdateStockItemTable do
use Ecto.Migration

def change do
drop table(:stock_items)
create table(:stock_items, primary_key: false) do
add :roll, :string, primary_key: true
add :company_initial, references(:companies, column: :initial, type: :string), primary_key: true
add :product_code, references(:products, column: :code, type: :string), primary_key: true
add :start_quantity, :decimal
add :current_quantity, :decimal
timestamps()
end
end
end

Do you think something wrong in here.

  1. a-zA-Z0-9-_ ↩︎

1 Like
22

In your schema you need:

field :roll, :string, primary_key: true

And you should be good to go!

2 Likes