ON CONFLlCT DO UPDATE command cannot affect row a second time - Mailing list pgsql-novice

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