PG::CheckViolation: ERROR: new row for relation "members" violates check constraint "check_508774aac0"

Job #3148328135 failed for f331e75a:

MR !99476 (diffs)

RunPipelineScheduleWorker
  #perform
    when a schedule project is missing
      does not call the Service (FAILED - 1)
        
1st Try error in ./spec/workers/run_pipeline_schedule_worker_spec.rb:27:
PG::CheckViolation: ERROR:  new row for relation "members" violates check constraint "check_508774aac0"
DETAIL:  Failing row contains (2541, 50, 1655, Project, 1701, 3, ProjectMember, 2022-10-10 05:45:51.925046, 2022-10-10 05:45:51.925046, null, null, null, null, null, null, f, f, 0, t, null, null).
CONTEXT:  SQL statement "UPDATE ONLY "public"."members" SET "member_namespace_id" = NULL WHERE $1 OPERATOR(pg_catalog.=) "member_namespace_id""
SQL statement "DELETE FROM namespaces
WHERE namespaces.id = OLD.project_namespace_id AND
namespaces.type = 'Project'"
PL/pgSQL function delete_associated_project_namespace() line 3 at SQL statement
        
RSpec::Retry: 2nd try ./spec/workers/run_pipeline_schedule_worker_spec.rb:27
Failures:
  1) RunPipelineScheduleWorker#perform when a schedule project is missing does not call the Service
     Failure/Error: connection.send(...)
     ActiveRecord::StatementInvalid:
       PG::CheckViolation: ERROR:  new row for relation "members" violates check constraint "check_508774aac0"
       DETAIL:  Failing row contains (2541, 50, 1655, Project, 1701, 3, ProjectMember, 2022-10-10 05:45:51.925046, 2022-10-10 05:45:51.925046, null, null, null, null, null, null, f, f, 0, t, null, null).
       CONTEXT:  SQL statement "UPDATE ONLY "public"."members" SET "member_namespace_id" = NULL WHERE $1 OPERATOR(pg_catalog.=) "member_namespace_id""
       SQL statement "DELETE FROM namespaces
       WHERE namespaces.id = OLD.project_namespace_id AND
       namespaces.type = 'Project'"
       PL/pgSQL function delete_associated_project_namespace() line 3 at SQL statement
     # ./lib/gitlab/database/load_balancing/connection_proxy.rb:120:in `block in write_using_load_balancer'
     # ./lib/gitlab/database/load_balancing/load_balancer.rb:122:in `block in read_write'
     # ./lib/gitlab/database/load_balancing/load_balancer.rb:193:in `retry_with_backoff'
     # ./lib/gitlab/database/load_balancing/load_balancer.rb:118:in `read_write'
     # ./lib/gitlab/database/load_balancing/connection_proxy.rb:119:in `write_using_load_balancer'
     # ./lib/gitlab/database/load_balancing/connection_proxy.rb:61:in `block (2 levels) in <class:ConnectionProxy>'
     # ./spec/workers/run_pipeline_schedule_worker_spec.rb:24:in `block (4 levels) in <main>'
     # ./spec/spec_helper.rb:417:in `block (3 levels) in <top (required)>'
     # ./spec/support/sidekiq_middleware.rb:9:in `with_sidekiq_server_middleware'
     # ./spec/spec_helper.rb:409:in `block (2 levels) in <top (required)>'
     # ./spec/spec_helper.rb:405:in `block (3 levels) in <top (required)>'
     # ./lib/gitlab/application_context.rb:58:in `with_raw_context'
     # ./spec/spec_helper.rb:405:in `block (2 levels) in <top (required)>'
     # ./spec/spec_helper.rb:242:in `block (2 levels) in <top (required)>'
     # ./spec/support/system_exit_detected.rb:7:in `block (2 levels) in <main>'
     # ./spec/support/flaky_tests.rb:27:in `block (2 levels) in <main>'
     # ./spec/support/database/prevent_cross_joins.rb:106:in `block (3 levels) in <main>'
     # ./spec/support/database/prevent_cross_joins.rb:60:in `with_cross_joins_prevented'
     # ./spec/support/database/prevent_cross_joins.rb:106:in `block (2 levels) in <main>'
     # ------------------
     # --- Caused by: ---
     # PG::CheckViolation:
     #   ERROR:  new row for relation "members" violates check constraint "check_508774aac0"
     #   DETAIL:  Failing row contains (2541, 50, 1655, Project, 1701, 3, ProjectMember, 2022-10-10 05:45:51.925046, 2022-10-10 05:45:51.925046, null, null, null, null, null, null, f, f, 0, t, null, null).
     #   CONTEXT:  SQL statement "UPDATE ONLY "public"."members" SET "member_namespace_id" = NULL WHERE $1 OPERATOR(pg_catalog.=) "member_namespace_id""
     #   SQL statement "DELETE FROM namespaces
     #   WHERE namespaces.id = OLD.project_namespace_id AND
     #   namespaces.type = 'Project'"
     #   PL/pgSQL function delete_associated_project_namespace() line 3 at SQL statement
     #   ./lib/gitlab/database/load_balancing/connection_proxy.rb:120:in `block in write_using_load_balancer'
Finished in 4.01 seconds (files took 34.8 seconds to load)
1 example, 1 failure
Failed examples:
rspec ./spec/workers/run_pipeline_schedule_worker_spec.rb:27 # RunPipelineScheduleWorker#perform when a schedule project is missing does not call the Service

Solution approach

fk_aa82dcc1c6 has been made redundant and conflicts with check_508774aac0

!93240 (diffs) checking if it relates to the current issue and thinking of the above scenario #377433 (comment 1130273630)

Check constraints:
    "check_508774aac0" CHECK (member_namespace_id IS NOT NULL) NOT VALID
Foreign-key constraints:
    "fk_2f85abf8f1" FOREIGN KEY (member_namespace_id) REFERENCES namespaces(id) ON DELETE CASCADE NOT VALID
    "fk_aa82dcc1c6" FOREIGN KEY (member_namespace_id) REFERENCES namespaces(id) ON DELETE SET NULL

How do you think fk_aa82dcc1c6 and fk_aa82dcc1c6 get executed, whenever a namespace is deleted?

Do you think we need fk_aa82dcc1c6?

Edited by Alper Akgun