psycopg2.errors.UniqueViolation on Sqlalchemy's merge()

Question

I have a model with a composite primary key and i'm trying to perform an upsert using sqlalchemy's merge() method. For some reason it went fine on my other test models(with composite key consisting of 7 cols total), but struggles to go through on this one

class Offsets(db.Model, CommonMethods):
    symbol_id = db.Column(db.String(120), primary_key=True)
    account_id = db.Column(db.String(120), primary_key=True)
    settlement_counterparty = db.Column(db.String(120), primary_key=True)
    execution_counterparty = db.Column(db.String(120), primary_key=True)
    counterparty_account_id = db.Column(db.String(120))
    trade_date = db.Column(db.String(10), primary_key=True)

    daily_diff = db.Column(db.String(120))
    buy_qty = db.Column(db.String(120))
    sell_qty = db.Column(db.String(120))
    pos_before_trade = db.Column(db.String(120))
    pos_after_trade = db.Column(db.String(120))
    
    
    offset =  db.Column(db.String(120))#resulting offset

    related_orders = db.Column(db.ARRAY(db.String(360)))

    comment = db.Column(db.String(360))

    manual = db.Column(db.Boolean, default=False, primary_key=True)
    settlement = db.Column(db.Boolean, default=False, primary_key=True) #for some reason if i remove this from model, merging goes fine

below im trying to perform an upsert

app=create_app()
app.app_context().push()

entry=Offsets(
        trade_date='2020-09-29',
        symbol_id='AAPL',
        account_id='FOO',
        buy_qty='0',
        settlement_counterparty='BAR',
        execution_counterparty='BAZ',
        counterparty_account_id='FOOBARBAZ',
        sell_qty='1',
        daily_diff='1',
        related_orders=['some_uuids_here'],
        manual=False
    )

db.session.merge(entry)
<Offsets (transient 140184049284712)>
db.session.commit()#first went fine as it wasnt in db
None
db.session.merge(entry)
<Offsets (transient 140184049535072)>
db.session.commit()#second time it returns an error though
Traceback (most recent call last):
  File "<string>", line 1, in <module>
  File "/usr/local/lib/python3.6/dist-packages/sqlalchemy/orm/scoping.py", line 162, in do
    return getattr(self.registry(), name)(*args, **kwargs)
  File "/usr/local/lib/python3.6/dist-packages/sqlalchemy/orm/session.py", line 1027, in commit
    self.transaction.commit()
  File "/usr/local/lib/python3.6/dist-packages/sqlalchemy/orm/session.py", line 492, in commit
    self._assert_active(prepared_ok=True)
  File "/usr/local/lib/python3.6/dist-packages/sqlalchemy/orm/session.py", line 295, in _assert_active
    code="7s2a",
sqlalchemy.exc.InvalidRequestError: This Session's transaction has been rolled back due to a previous exception during flush. To begin a new transaction with this Session, first issue Session.rollback(). Original exception was: (psycopg2.errors.UniqueViolation) duplicate key value violates unique constraint "offsets_pkey"
DETAIL:  Key (symbol_id, account_id, settlement_counterparty, execution_counterparty, trade_date, manual, settlement)=(AAPL, FOO, BAR, BAZ, 2020-09-29, f, f) already exists.

shouldn't the above code perform an upsert(update non-primary key cols on conflict) without erorrs?

Answer 1

I had to clearly define settlement = False while creating the entry, for some reason default=False in the model wasn't enough