ActiveRecord::InvalidForeignKey: PG::ForeignKeyViolation: ERROR: insert or update on table "index_statuses" violates foreign key ...

This error isn't happening often on GitLab.com but there seems to be some issues with indexing projects that are not in the projects table anymore (maybe deleted?)

https://sentry.gitlab.net/gitlab/gitlabcom/issues/3197057/?referrer=gitlab_plugin

PG::ForeignKeyViolation: ERROR:  insert or update on table "index_statuses" violates foreign key constraint "fk_74b2492545"
DETAIL:  Key (project_id)=(35536406) is not present in table "projects".

  lib/gitlab/database/load_balancing/connection_proxy.rb:119:in `block in write_using_load_balancer'
    connection.send(...)
  lib/gitlab/database/load_balancing/load_balancer.rb:112:in `block in read_write'
    yield connection
  lib/gitlab/database/load_balancing/load_balancer.rb:179:in `retry_with_backoff'
    return yield
  lib/gitlab/database/load_balancing/load_balancer.rb:110:in `read_write'
    retry_with_backoff do
  lib/gitlab/database/load_balancing/connection_proxy.rb:118:in `write_using_load_balancer'
    @load_balancer.read_write do |connection|
...
(184 additional frame(s) were not displayed)

ActiveRecord::InvalidForeignKey: PG::ForeignKeyViolation: ERROR:  insert or update on table "index_statuses" violates foreign key constraint "fk_74b2492545"
DETAIL:  Key (project_id)=(35536406) is not present in table "projects".

PG::ForeignKeyViolation: ERROR:  insert or update on table "index_statuses" violates foreign key constraint "fk_74b2492545"
DETAIL:  Key (project_id)=(35536406) is not present in table "projects".

Possible Fixes

There is already a check for Project.exists? in the update_index_status method within the indexer.rb file. The check could be done differently or wrapped in a try/catch statement.

Another nice option is to use DELETE CASCADE for the foreign key. If the project is deleted, we don't need its index status.

Click to expand process progress

Next Steps for this issue

Validation track

• checkboxes are required steps

• Bugs can skip Design and Solution Validation

• workflowvalidation backlog Problem Validation Backlog -@JohnMcGuire

  • Issue is Up-to-date
  • Prioritized in the backlog

• workflowproblem validation Problem validation - @JohnMcGuire

  • Thorough understanding of the problem
    • Problem Validation Template
    • Opportunity Canvas
      • Review of the opportunity canvas
    • UX Research
      • Validate your problem with users
    • Update issue/epic description
    • Ensure your issue is up-to-date with the latest understanding of the problem.
    • Understand and document (in the issue) the goals that people want to accomplish using the Jobs to be Done (JTBD) framework.
    • communicate the findings to Product and UX leadership.
    • PMs are strongly encouraged to open Dogfooding issues

• workflowready for design - @nickbrandt

• workflowdesign Validation phase 3: Design - @nickbrandt

  • Proposed solution(s) identified and documented
    • Diverge: explore multiple different approaches as a team
    • Think Big session.
      • document findings in Dovetail
    • Converge: identify a small set of options to validate
      • Think Small session with the team.
      • Design reviews with team
      • Low fidelity design ideas
      • Update issue/epic description with proposed solution
      • Add Figma design file link or attach design to GitLab's Design Management
      • Validate approach with help from stakeholders.
        • Run user validation using any of the proposed methods
        • Document your findings in Dovetail and appropriate GitLab issue.
      • Draw inspiration from competitive and adjacent offerings.
  • Shared understanding in the team of the proposed solution
    • Review the proposed solution as a team so that everyone has a chance to contribute
      • ask questions
      • raise concerns
      • suggest alternatives
    • Review the proposed solution with leadership.
  • Confidence in the technical feasibility
    • Discuss the technical implications with Engineering to ensure that what is being proposed is possible within the desired timeframe.
    • Engage engineering peers early and often
    • If the solution is large and complex, consider scheduling a spike to mitigate risks and uncover the optimal iteration path.
  • Updated issues/epic descriptions - @JohnMcGuire
    • Ensure issues and epics are up-to-date, so we can continue our work efficiently and asynchronously.
    • Experiment definition
  • Continue Dogfooding process

• workflowsolution validation - Validation phase 4: Solution Validation - @nickbrandt

  • High confidence in the proposed solution
    • Gather feedback from relevant stakeholders.
      • Follow solution validation guidance to gather feedback.
  • Documented Solution validation Learnings
    • Document solution validation findings as insights in Dovetail.
    • Update the opportunity canvas (if used) with relevant insights.
    • Update the issue or epic description to contain or link to the findings.

Build track

• workflowplanning breakdown - @JohnMcGuire
  • Well-scoped MVC issues
    • Issues are the SSOT for all feature development.
    • Refine issues into something that can be delivered within a single milestone
    • Open follow on issues to track work that is de-prioritized
    • Promote existing issues to Epics and open implementation issues for the upcoming milestone
    • Review feature issues with contributors
    • Consider scheduling a POC or engineering investigation issue
    • Make scope tradeoffs to reach for a right-sized MVC
    • Request an issue review to ensure communication is clear and have proposed the right iteration plan to execute on the solution.
• Prioritized in Milestone
  • The team should understand what issues should be delivered during the next milestone
• workflowready for development - @JohnMcGuire
• typebug typefeature typemaintenance - @JohnMcGuire
• Deliverable - @changzhengliu and @nickbrandt
• Add to Planning Issue - @JohnMcGuire
• Defined Quality Plan -@ebanks
  • Quad Planning
  • Test planning
• workflowrefinement - @changzhengliu
  • as needed, refine the aspects of the original feature
• workflowin dev - @changzhengliu
  • Applied by the engineer after work (including documentation) has begun on the issue. An MR is typically linked to the issue at this point.
• workflowin review - Engineering
  • Applied by an engineer indicating that all MRs required to close an issue are in review.
• workflowblocked - Engineering
  • Applied if at any time during development the issue is blocked. For example: technical issue, open question to PM or PD, cross-group dependency.
• workflowverification - Engineering
  • After the MRs in the issue have been merged, this label is applied signaling the issue needs to be verified in staging or production.
• workflowawaiting security release -Engineering
  • Applied by an engineer after the security issue has passed verification, this label signals that it is ready but awaiting the next monthly security release.
• Close the Issue - Once available in production
• Feature is available to GitLab.com hosted customers - Developer
  • Code is deployed to production.
  • Feature flag(s) enabled.
• Feature is available to self-managed customers - Developer
  • Code is included in the self-managed release (depending upon the cut-off).
• Stakeholders of a feature will know it's available in production - Developer
  • After the feature is deployed to production and any needed verification in production is completed, the development team will close the issue.
  • Prior to the issue being closed, the development team may set the workflow label to workflow::verification or workflow::production for tracking purposes.
  • Product Manager may follow up with individual stakeholders to let them know the feature is available.
• Customers will be informed about major changes - @JohnMcGuire
  • Product Manager follows the instructions in the template, which will then cause it to appear on the GitLab.com releases page and be part of the release post.