错误：重复的键值违反了唯一约束“xak1fact_dim_relationship”

Question

我在删除一些行并根据 java 的条件更新表时收到以下错误。我的数据库是 PostgreSQL 8.4。下面是错误： 错误：重复的键值违反了唯一性 约束“xak1fact_dim_relationship”

导致此问题的代码如下：

/**
 * Commits job.  This does the following:
 * <OL>
 *  <LI> cancel previous datamart states </LI>
 *  <LI> drop diabled derived objects </LI>
 *  <LI> flip mirror relationships for objects with next states </LI>
 *  <LI> advance rolloff_state from start1 to complete1, from start2 to complete </LI>
 *  <LI> set 1/0 internal states to 0/-1 </LI>
 *  <LI> remove header objects with no letter rows </LI>
 *  <LI> mark mirror rel as OS if children are missing (e.g., semantic w/o agg build) </LI>
 *  <LI> mark mirror rel as OS if int-map not in sync with dim base (e.g., int-map SQL w/o semantic) </LI>
 * </OL>
 */
protected void CommitJobUnprotected()
    throws SQLException
{
    if (_repl.epiCenterCon== null)
        throw makeReplacementError(0);
    boolean oldAutoCommitStatus = _repl.epiCenterCon.getAutoCommit();

    try
    {

        _repl.epiCenterCon.setAutoCommit(false);

        Statement stmt = null;
        boolean committed = false;
        synchronized (SqlRepl.metaChangeLock)
        {
            try
            {
                stmt = _repl.epiCenterCon.createStatement();

                // update internal states for fact_dim_relationship
                metaExec(stmt, "DELETE from fact_dim_relationship WHERE internal_state = -1 AND "  +
                                " EXISTS (SELECT 1 FROM fact_dim_relationship WHERE internal_state = 1)",
                                " SELECT 1 from fact_dim_relationship WHERE internal_state = -1 AND "  +
                                " EXISTS (SELECT 1 FROM fact_dim_relationship WHERE internal_state = 1)"); /*1*/
                metaExec( stmt, "UPDATE fact_dim_relationship SET internal_state = internal_state - 1 " +
                             " WHERE EXISTS (SELECT 1 FROM fact_dim_relationship inner1 " +
                             "              WHERE inner1.internal_state = 1 " +
                             " AND inner1.fact_tbl_key = fact_dim_relationship.fact_tbl_key " +
                             " AND inner1.dim_base_key = fact_dim_relationship.dim_base_key ) ",
                             " SELECT 1 FROM fact_dim_relationship " +
                             " WHERE EXISTS (SELECT 1 FROM fact_dim_relationship inner1 " +
                             "              WHERE inner1.internal_state = 1 " +
                             " AND inner1.fact_tbl_key = fact_dim_relationship.fact_tbl_key " +
                             " AND inner1.dim_base_key = fact_dim_relationship.dim_base_key ) "); /*5*/

                System.out.println("Update done on fact_dim_relationship");
                _repl.doDrop(SqlReplLogger.DB_META, stmt, "fact_agg", "SELECT fact_agg_key FROM fact_agg f WHERE " +
                                                                      " NOT EXISTS (SELECT 1 FROM fact_agg_letter l WHERE " +
                                                                      "                 f.fact_agg_key = l.fact_agg_key) "); /*6*/

                _repl.doDrop(SqlReplLogger.DB_META, stmt, "dim_base_agg", "SELECT dim_base_agg_key FROM dim_base_agg d WHERE " +
                                                                          " NOT EXISTS (SELECT 1 FROM dim_base_agg_letter l WHERE " +
                                                                          "                 d.dim_base_agg_key = l.dim_base_agg_key) "); /*6*/

                CheckOutOfSync(stmt, "fact_agg", null); /*7*/
                CheckOutOfSync(stmt, "dim_base_agg", null); /*7*/

                metaExec( stmt, " update mirror_relationship set relation_to_current = 'Out Of Sync' " +
                                " where dim_col_intmap_key is not null " +
                                " and relation_to_current = 'One Back' " +
                                " and not exists ( " +
                                "       select 1 " +
                                "       from mirror_relationship m2, dim_col_view c, dim_col_intmap i " +
                                "       where m2.dim_base_key = c.dim_base_key " +
                                "       and c.dim_col_key = i.dim_col_key  " +
                                "       and i.dim_col_intmap_key = mirror_relationship.dim_col_intmap_key " +
                                "       and m2.relation_to_current = 'One Back') ",
                                " SELECT 1 FROM mirror_relationship " +
                                " where dim_col_intmap_key is not null " +
                                " and relation_to_current = 'One Back' " +
                                " and not exists ( " +
                                "       select 1 " +
                                "       from mirror_relationship m2, dim_col_view c, dim_col_intmap i " +
                                "       where m2.dim_base_key = c.dim_base_key " +
                                "       and c.dim_col_key = i.dim_col_key  " +
                                "       and i.dim_col_intmap_key = mirror_relationship.dim_col_intmap_key " +
                                "       and m2.relation_to_current = 'One Back') "); /*8*/

                // clean out the tables used by mombuilder, aggbuilder, and semantics
                metaExec( stmt,  "delete from relation_intermediary", "select 1 from relation_intermediary" );

                _repl.epiCenterCon.commit();
                committed = true;
            }
            finally
            {

                safeMetaRollbackIfNeeded( committed );
                _repl.safeClose( null, stmt );
            }
        } // end synchronized block
    }
    finally
    {
        _repl.epiCenterCon.setAutoCommit(oldAutoCommitStatus);
    }
}

第一个删除语句运行良好，但在运行更新时抛出上述异常....！我们支持 SQLServer、Oracle 和 DB2，相同的代码在其他 DB 上运行良好。顺便说一句，我们在 READ_COMMITTED 事务级别运行这些语句，并且如果在我们安全回滚之间出现任何失败，我们将设置自动提交。如果我使用 autocommit true 运行上面的代码，则代码可以正常工作！但我们不应该这样做。我怀疑 PostgreSQL 的多版本并发控制功能，我是否错误地设置了隔离级别？请尽早帮助我。我可以提供您想要的任何信息。

Answer 1

如果只是这组特定的查询，请使用 SET CONSTRAINT：

BEGIN;
SET CONSTRAINT = xak1fact_dim_relationship DEFERRED;
-- Do your SQL
COMMIT;

如果这是一种非常常见的情况，您可以更改数据库架构，更改数据库架构以支持 最初推迟。