Open
Description
Description
When running a query using on conflict update clauses, it errored from the database on execution:
2025-02-15T00:32:52.275908Z ERROR run: task::review: error=Execution Error: error returned from database: there is no unique or exclusion constraint matching the ON CONFLICT specification.
However, manually testing the interpolated query in TablePlus, the query executed successfully,
So, I tried building the query into a string first and then executing an unprepared statement in the transaction and it works fine.
The canonical path of executing the prepared statement ought to work though.
Steps to Reproduce
- Migrate up the schema like so, taking liberties to ensure it builds.
Table::create()
.table(crate::ReviewComment::Table)
.if_not_exists()
.col(pk_auto(crate::ReviewComment::CommentId))
.col(integer(crate::ReviewComment::ReviewId))
.foreign_key(
ForeignKey::create()
.name("review_comment_review_fkey")
.from(crate::ReviewComment::Table, crate::ReviewComment::ReviewId)
.to(crate::Review::Table, crate::Review::ReviewId)
.on_delete(ForeignKeyAction::Cascade)
.on_update(ForeignKeyAction::Cascade),
)
.col(integer(crate::ReviewComment::AccountId))
.foreign_key(
ForeignKey::create()
.name("review_comment_account_fkey")
.from(crate::ReviewComment::Table, crate::ReviewComment::AccountId)
.to(crate::Account::Table, crate::Account::AccountId)
.on_delete(ForeignKeyAction::Cascade)
.on_update(ForeignKeyAction::Cascade),
)
.col(enumeration(
crate::ReviewComment::ReviewStage,
crate::ReviewStage::ReviewStage,
crate::ReviewStage::iter().skip(1),
))
.col(enumeration(
crate::ReviewComment::CommentKind,
crate::ReviewCommentKind::ReviewCommentKind,
crate::ReviewCommentKind::iter().skip(1),
))
.col(enumeration(
crate::ReviewComment::CommentStatus,
crate::ReviewCommentStatus::ReviewCommentStatus,
crate::ReviewCommentStatus::iter().skip(1),
))
.col(
array(
crate::ReviewComment::InferenceReasoning,
ColumnType::string(None),
)
.default(Expr::val("{}")),
)
.col(
array(
crate::ReviewComment::InferenceSnippets,
ColumnType::string(None),
)
.default(Expr::val("{}")),
)
.col(string(crate::ReviewComment::Content))
.col(string_null(crate::ReviewComment::Preview))
.col(integer_null(crate::ReviewComment::CriteriaId))
.foreign_key(
ForeignKey::create()
.name("review_comment_criteria_fkey")
.from(
crate::ReviewComment::Table,
crate::ReviewComment::CriteriaId,
)
.to(crate::Criteria::Table, crate::Criteria::CriteriaId)
.on_delete(ForeignKeyAction::Cascade)
.on_update(ForeignKeyAction::Cascade),
)
.col(enumeration_null(
crate::ReviewComment::InferenceDecision,
crate::InferenceDecision::InferenceDecision,
crate::InferenceDecision::iter().skip(1),
))
.col(
timestamp_with_time_zone(crate::ReviewComment::CreatedAt)
.default(Expr::current_timestamp()),
)
.col(
timestamp_with_time_zone(crate::ReviewComment::UpdatedAt)
.default(Expr::current_timestamp()),
)
.pipe(std::mem::take)
.pipe(|table| manager.create_table(table))
.await?;
Index::create()
.if_not_exists()
.name("review_comment_review_id_review_stage_key")
.table(crate::ReviewComment::Table)
.col(crate::ReviewComment::ReviewId)
.col(crate::ReviewComment::ReviewStage)
.pipe(std::mem::take)
.pipe(|index| manager.create_index(index))
.await?;
Index::create()
.if_not_exists()
.name("review_comment_review_id_review_stage_criteria_id_for_comment_kind_kritiq_unique_key")
.table(crate::ReviewComment::Table)
.col(crate::ReviewComment::ReviewId)
.col(crate::ReviewComment::ReviewStage)
.col(crate::ReviewComment::CriteriaId)
.unique()
.cond_where(
Expr::col(crate::ReviewComment::CommentKind).is_in::<Value, _>([
entity::sea_orm_active_enums::ReviewCommentKind::KritiqScreening.into(),
entity::sea_orm_active_enums::ReviewCommentKind::KritiqExtraction.into(),
]),
)
.pipe(std::mem::take)
.pipe(|index| manager.create_index(index))
.await?;- Generate the entities via sea-orm-cli.
- Insert a record like so. Uncomment the code to reproduce the error.
ReviewComment::insert_many(comments)
.on_conflict(
sea_orm::sea_query::OnConflict::columns([
entity::review_comment::Column::ReviewId,
entity::review_comment::Column::ReviewStage,
entity::review_comment::Column::CriteriaId,
])
.target_and_where(
Expr::col(entity::review_comment::Column::CommentKind).is_in([
entity::sea_orm_active_enums::ReviewCommentKind::KritiqScreening.as_enum(),
entity::sea_orm_active_enums::ReviewCommentKind::KritiqExtraction.as_enum(),
]),
)
.update_columns([
entity::review_comment::Column::AccountId,
entity::review_comment::Column::CommentStatus,
entity::review_comment::Column::InferenceReasoning,
entity::review_comment::Column::InferenceSnippets,
entity::review_comment::Column::Content,
entity::review_comment::Column::Preview,
entity::review_comment::Column::InferenceDecision,
entity::review_comment::Column::UpdatedAt,
])
.to_owned(),
)
// HACK: Using the canonical execution method, we get the error:
// 2025-02-15T00:32:52.275908Z ERROR run: task::review: error=Execution Error: error returned from database: there is no unique or exclusion constraint matching the ON CONFLICT specification
// So avoid it by building the query first and executing an unprepared statement.
// .exec_without_returning(transaction)
// .await?;
.build(sea_orm::DbBackend::Postgres)
.to_string()
.pipe_deref(|sql| transaction.execute_unprepared(sql))
.await?;Expected Behavior
Executing via exec_without_returning should work.
Actual Behavior
Have to first build the query into string, and then execute an unprepared statement for it to work.
Reproduces How Often
Always reproducible.
Workarounds
Have to first build the query into string, and then execute an unprepared statement for it to work.
Reproducible Example
See above for steps to reproduce.
Versions
NeonDB Postgres 17
sea-orm = { version = "1.1.4", features = [
"debug-print",
"macros",
"runtime-tokio-rustls",
"sqlx-postgres",
] }Metadata
Metadata
Assignees
Labels
No labels