CB Response: Postgres Error on Upgrade - "watchlist_action" violates foreign key constraint "watchlist_action_watchlist_id_fkey"
search cancel

CB Response: Postgres Error on Upgrade - "watchlist_action" violates foreign key constraint "watchlist_action_watchlist_id_fkey"


Article ID: 287404


Updated On:


Carbon Black EDR (formerly Cb Response)


  • Error on upgrade :
sqlalchemy.exc.IntegrityError: (psycopg2.IntegrityError) insert or update on table "watchlist_action" violates foreign key constraint "watchlist_action_watchlist_id_fkey" 
DETAIL: Key (watchlist_id)=(<watchlist_id>) is not present in table "watchlist_entries". 
[SQL: 'INSERT INTO watchlist_action (watchlist_id, alliance_feed_id, action_type) VALUES (%(watchlist_id)s, %(alliance_feed_id)s, %(action_type)s) RETURNING watchlist_action.id'] [parameters: {'action_type': 1, 'alliance_feed_id': None, 'watchlist_id': <watchlist_id>}] (Background on this error at: http://sqlalche.me/e/gkpj)



  • CB Response Server: 6.3.0 and Higher


During upgrade the Postgres service fails checks due to a previously deleted watchlist having a reference in the watchlist_action_settings table.


1. Ensure all cb-enterprise services are stopped. 
service cb-enterprise status 
service cb-enterprise stop
ps -ef | grep cb
  • If anything is returned, kill those processes.
killall -KILL -u cb
 2. Start the cb-pgsql service : 
service cb-pgsql start
3. Login to the postgresql shell : 
sudo psql cb -p 5002
4. Verify there are one or more rows in the watchlist_action_settings table with the <watchlist_id> from the error: 
SELECT * FROM watchlist_action_settings WHERE watchlist_id='<watchlist_id>';
5. Verify there is NO result for the following query: 
SELECT * FROM watchlist_entries WHERE id=<watchlist_id>
6. Delete the entry once confirmed: 
DELETE FROM watchlist_action_settings WHERE watchlist_id='<watchlist_id>';
7. Stop the cb-pgsql service 
service cb-pgsql stop
8. Restart the upgrade

Additional Information

  • Step 6 will permanently delete values from the database. Consider taking a backup before deleting the rows. 
    • pg_dump -C -Fp -f psqldump_full.sql cb -p 5002
      pg_dumpall -p 5002 --roles-only -f psqlroles.sql
  • The <watchlist_id> will vary by environments. This value is tied to the specific ID of a watchlist that was deleted in the past. This value can be obtained from the error provided in they Symptoms section