8000 Prepared statement errors `there is no unique or exclusion constraint matching the ON CONFLICT specification` from database but unprepared statement works · Issue #2495 · SeaQL/sea-orm · GitHub
[go: up one dir, main page]
More Web Proxy on the site http://driver.im/
Skip to content

Prepared statement errors there is no unique or exclusion constraint matching the ON CONFLICT specification from database but unprepared statement works #2495

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.

Sign up for GitHub

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

Jump to bottom
Open
Lev1ty opened this issue Feb 15, 2025 · 1 comment
Open

Prepared statement errors there is no unique or exclusion constraint matching the ON CONFLICT specification from database but unprepared statement works #2495

Lev1ty opened this issue Feb 15, 2025 · 1 comment

Comments

@Lev1ty
Copy link
Lev1ty commented Feb 15, 2025

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

  1. 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?;
  1. Generate the entities via sea-orm-cli.
  2. 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",
] }
@Lev1ty Lev1ty mentioned this issue Feb 26, 2025
Closed
@abonander
Copy link
abonander commented Feb 27, 2025 •
edited
Loading

If I had to guess, I would say that Postgres is probably expecting the partial index expression to (more or less) exactly match the one on the index itself; it's probably not enough for them to be semantically equivalent.

Compare the generated CREATE INDEX statement with the generated query. If they're the same other than the query parameter, then most likely the query parameter itself is defeating the check. You could probably consider that a Postgres bug, but sea-orm could just inline the value instead.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
None yet
Projects
None yet
Milestone
No milestone
Development

No branches or pull requests
2 participants
@abonander @Lev1ty
0