-
-
Notifications
You must be signed in to change notification settings - Fork 4.3k
New issue
Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.
By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.
Already on GitHub? Sign in to your account
Upsert uses a WHERE clause with both primary key and unique indices on Postgres #8552
Comments
Yea, definitely a bug. Please open a PR with changes from #6243 (Will need bit cleanup) |
is this not a case of the transaction being closed before the UPDATE statements are performed? ... it looks like there is a missing models.sequelize.transaction(() => (
return models.rule.upsert({
id: 5,
priority: 1,
parent_id: 20
})
.then(() => models.rule.upsert({
id: 10,
priority: 0,
parent_id: 20
}))
)) |
@iamjochem No return is required as that arrow function is using implicit return format |
d'oh I mistook the parens for curlies ... sorry for the noise. |
It's still happening for me. Isn't fixed yet? |
still an issue for me too |
This is actually fixed by #12301, so only primary key will be selected as conflict target, when update data got PK value const rule = sequelize.define(
"rule",
{
id: {
type: DataTypes.INTEGER,
primaryKey: true,
},
priority: {
type: DataTypes.INTEGER,
allowNull: false,
defaultValue: 0,
unique: "rules_priority_parent_id",
},
parent_id: {
type: DataTypes.INTEGER,
allowNull: false,
unique: "rules_priority_parent_id",
},
},
{
tableName: "rules",
timestamps: true,
}
);
(async () => {
await sequelize.sync({ force: true });
await rule.create({
id: 5,
priority: 0,
parent_id: 20,
});
await rule.create({
id: 10,
priority: 1,
parent_id: 20,
});
await sequelize.transaction((transaction) =>
rule
.upsert(
{
id: 5,
priority: 1,
parent_id: 20,
},
{ transaction }
)
.then(() =>
rule.upsert(
{
id: 10,
priority: 0,
parent_id: 20,
},
{ transaction }
)
)
);
})().catch((e) => console.error(e.original.message)); Executing (default): CREATE TABLE IF NOT EXISTS "rules" ("id" INTEGER , "priority" INTEGER NOT NULL DEFAULT 0, "parent_id" INTEGER NOT NULL, "createdAt" TIMESTAMP WITH TIME ZONE NOT NULL, "updatedAt" TIMESTAMP WITH TIME ZONE NOT NULL, UNIQUE ("priority", "parent_id"), PRIMARY KEY ("id"));
Executing (default): SELECT i.relname AS name, ix.indisprimary AS primary, ix.indisunique AS unique, ix.indkey AS indkey, array_agg(a.attnum) as column_indexes, array_agg(a.attname) AS column_names, pg_get_indexdef(ix.indexrelid) AS definition FROM pg_class t, pg_class i, pg_index ix, pg_attribute a WHERE t.oid = ix.indrelid AND i.oid = ix.indexrelid AND a.attrelid = t.oid AND t.relkind = 'r' and t.relname = 'rules' GROUP BY i.relname, ix.indexrelid, ix.indisprimary, ix.indisunique, ix.indkey ORDER BY i.relname;
Executing (default): INSERT INTO "rules" ("id","priority","parent_id","createdAt","updatedAt") VALUES ($1,$2,$3,$4,$5) RETURNING "id","priority","parent_id","createdAt","updatedAt";
Executing (default): INSERT INTO "rules" ("id","priority","parent_id","createdAt","updatedAt") VALUES ($1,$2,$3,$4,$5) RETURNING "id","priority","parent_id","createdAt","updatedAt";
Executing (4c0b09a0-b009-4410-8b65-adaf07e063f3): START TRANSACTION;
Executing (4c0b09a0-b009-4410-8b65-adaf07e063f3): INSERT INTO "rules" ("id","priority","parent_id","createdAt","updatedAt") VALUES ($1,$2,$3,$4,$5) ON CONFLICT ("id") DO UPDATE SET "id"=EXCLUDED."id","priority"=EXCLUDED."priority","parent_id"=EXCLUDED."parent_id","updatedAt"=EXCLUDED."updatedAt" RETURNING "id","priority","parent_id","createdAt","updatedAt";
Executing (4c0b09a0-b009-4410-8b65-adaf07e063f3): ROLLBACK;
duplicate key value violates unique constraint "rules_priority_parent_id_key" But it won't work for OP, as evident from SQL log for my SSCCE. When conflict target is using I don't think OP's case can be covered unless constraints are deferred from transaction, which we support already. Finally, I am closing this issue. As original request to only use PK as conflict target (when possible) has now been adopted by #12301 |
By OP, I meant original author of this thread. If you notice on first UPSERT (in my given SSCCE)
|
What are you doing?
I am using
upsert
to update models that have both a primary key and a composite unique constraint which isDEFERRABLE INITIALLY DEFERRED
. When I try to change the members of the unique constraint I get a key uniqueness error.Model:
Then do:
What do you expect to happen?
The
priority
of the two rows to be swappedWhat is actually happening?
Uniqueness constraint error
I get a constraint error on the uniqueness of the primary key.
Postgres output:
I see a fix for sequelize@3 for the same problem (issue #6240, PR #6243), but it appears to still be a problem in sequelize@4.
Dialect: postgres
Dialect version: pg@6.4.2
Database version: 9.6.3
Sequelize version: 4.19.0
Tested with master branch: Yes
Note : Your issue may be ignored by maintainers if it's not tested against
master
branch OR does not follow issue template.The text was updated successfully, but these errors were encountered: