Thread: 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
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.
David J.
On Thursday, April 30, 2020, David G. Johnston <david.g.johnston@gmail.com> wrote:
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 thisLearn 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.
Oh removing python from the problem space is a solid next step, the server is raising the error so it should be doable to use a psql script to duplicate the problem.
David J.