Обсуждение: ON CONFLlCT DO UPDATE command cannot affect row a second time

This is a Python script that runs every night, but it stopped working. It
pulls data from a tool and moves them to Postgres databases.

In the Python shell I get the following error/hint:

psycopg2.ProgrammingError: ON CONFLICT DO UPDATE command cannot affect row a
second time

HINT: Ensure that no rows proposed for insertion within the same command
have duplicate constrained values.

Here is the code part:

def update_tbl(job):
""" Try to insert new record into the table. If that
    record already exists, then update that row. This is based
    off primary keys marked in the settings file."""

to_tbl = job.get('to_table')
from_tbl = get_temp_name(job['to_table'])
col_map = job.get('col_map')
cols = ', '.join([x[1] for x in col_map])
sel_cols = ', '.join(map(
    lambda x: x[1] if len(x) <= 4 else x[4].replace('__alias__', x[1]), 
    col_map))
uids = ', '.join([x[1] for x in col_map if x[3]])
exclusions = list(filterfalse(lambda x: x[3], col_map))
live_excs = ', '.join([x[1] for x in exclusions])
temp_excs = ', '.join(["Excluded.%s" %(x[1]) for x in exclusions])
clause_up = build_exc_clause(col_map, 'live', 'Excluded')
conn = get_conn(job.get('to_db'))

with conn.cursor() as cur:
    upsert = """
        INSERT INTO {t} as live ({cols}) (
            SELECT {sel_cols} FROM {f}
        )
    """.format(t=to_tbl, f=from_tbl, cols=cols, sel_cols=sel_cols)

    # If there are no fields other than primary keys
    #  (such as document_type_map),
    # just do the insert
    if job.get("noupdateondupe", False):
           upsert += """
            ON CONFLICT ({uids})
            DO NOTHING
        """.format(uids=uids)
    elif len(uids) > 0 and len(live_excs) > 0:
        upsert += """
            ON CONFLICT ({uids})
            DO UPDATE SET ({lexc}) = ({exc})
            WHERE ({c})
        """.format(uids=uids, lexc=live_excs, exc = temp_excs, c=clause_up)

    logging.info("about to update %s"%(to_tbl))
    logging.info(upsert)

    try:
        # logging.info("upsert query")
        # logging.info(upsert)
        cur.execute(upsert)
        conn.commit()
    except IntegrityError:
        conn.rollback()
        if job.get('add_sequence', False):
            add_sequence(to_tbl, job)
            cur.execute(upsert)
            conn.commit()
            logging.info(upsert)
        else:
            raise
conn.close()
I found this in a "utils" file which could be helpful since it has "upsert"
in it:

# If there are no fields other than primary keys
    #  (such as document_type_map),
    # just do the insert
    if job.get("noupdateondupe", False):
           upsert += """
            ON CONFLICT ({uids})
            DO NOTHING
        """.format(uids=uids)
    elif len(uids) > 0 and len(live_excs) > 0:
        upsert += """
            ON CONFLICT ({uids})
            DO UPDATE SET ({lexc}) = ({exc})
            WHERE ({c})
        """.format(uids=uids, lexc=live_excs, exc = temp_excs, c=clause_up)

    logging.info("about to update %s"%(to_tbl))
    logging.info(upsert)

    try:
        # logging.info("upsert query")
        # logging.info(upsert)
        cur.execute(upsert)
        conn.commit()
    except IntegrityError:
        conn.rollback()
        if job.get('add_sequence', False):
            add_sequence(to_tbl, job)
            cur.execute(upsert)
            conn.commit()
            logging.info(upsert)
        else:
            raise
conn.close()
So in the log file, the script seems to work until here and the last bit of
text shows:

2020-04-30 10:33:18,164 about to update my_data
2020-04-30 10:33:18,164 
            INSERT INTO my_data as live (partid, id, refnum, originalnum,
catalognum, catalogpnwp, originalMFG, manufacturerid, originaldescription,
originalrevision, contentid, status_id, createddate, comment, na_id,
na_date, site_id) (
                SELECT partid, id, refnum, originalnum, catalognum,
catalogpnwp, originalMFG, manufacturerid, originaldescription,
originalrevision, contentid, status_id, createddate, comment, na_id,
na_date, site_id FROM temp_my_data
            )

                ON CONFLICT (id)
                DO UPDATE SET (partid, refnum, originalnum, catalognum,
catalogpnwp, originalMFG, manufacturerid, originaldescription,
originalrevision, contentid, status_id, createddate, comment, na_id,
na_date, site_id) = (Excluded.partid, Excluded.refnum, Excluded.originalnum,
Excluded.catalognum, Excluded.catalogpnwp, Excluded.originalMFG,
Excluded.manufacturerid, Excluded.originaldescription,
Excluded.originalrevision, Excluded.contentid, Excluded.status_id,
Excluded.createddate, Excluded.comment, Excluded.na_id, Excluded.na_date,
Excluded.site_id)
                WHERE (live.id = Excluded.id)
and the script stops working.

I'm not a database guy and certainly not a Python expert. How do I fix this
?



--
Sent from: https://www.postgresql-archive.org/PostgreSQL-novice-f2132464.html

HINT: Ensure that no rows proposed for insertion within the same command
have duplicate constrained values.

I'm not a database guy and certainly not a Python expert. How do I fix this

On Thursday, April 30, 2020, cryptodactyl <adriann.muresan@gmail.com> wrote:

HINT: Ensure that no rows proposed for insertion within the same command
have duplicate constrained values.

 

Its related to specific data yet you haven’t provided any...even if you cannot share the original data without a self-contained test case demonstrating the problem the odds of getting answers is quite low (not that i’m an on conflict expert).

I'm not a database guy and certainly not a Python expert. How do I fix this

Learn enough so you can provide good info to others who can maybe help you or turn the over the problem to someone who has more experience in this area.