concourse: Don't log "ERROR: update or delete on table "volumes" violates foreign key constraint" during happy path of GC

Bug Report

Concourse version: 3.0.1
Deployment type (BOSH/Docker/binary): BOSH
Infrastructure/IaaS: GCP (postgresql is GCP postgresql BETA)

We are seeing a lot of log messages on postgresql similar to

E  ERROR:  update or delete on table "volumes" violates foreign key constraint "volumes_parent_id_fkey" on table "volumes" 
E  DETAIL:  Key (id, state)=(3716002, created) is still referenced from table "volumes". 
E  STATEMENT:  UPDATE volumes SET state = $1 WHERE (id = $2 AND (state = $3 OR state = $4)) 
E  ERROR:  update or delete on table "volumes" violates foreign key constraint "volumes_parent_id_fkey" on table "volumes" 
E  DETAIL:  Key (id, state)=(3716099, created) is still referenced from table "volumes". 
E  STATEMENT:  UPDATE volumes SET state = $1 WHERE (id = $2 AND (state = $3 OR state = $4)) 
E  ERROR:  update or delete on table "volumes" violates foreign key constraint "volumes_parent_id_fkey" on table "volumes" 
E  DETAIL:  Key (id, state)=(3716100, created) is still referenced from table "volumes". 
E  STATEMENT:  UPDATE volumes SET state = $1 WHERE (id = $2 AND (state = $3 OR state = $4)) 
E  ERROR:  update or delete on table "volumes" violates foreign key constraint "volumes_parent_id_fkey" on table "volumes" 
E  DETAIL:  Key (id, state)=(3716114, created) is still referenced from table "volumes". 
E  STATEMENT:  UPDATE volumes SET state = $1 WHERE (id = $2 AND (state = $3 OR state = $4)) 
E  ERROR:  update or delete on table "volumes" violates foreign key constraint "volumes_parent_id_fkey" on table "volumes" 
E  DETAIL:  Key (id, state)=(3716113, created) is still referenced from table "volumes". 
E  STATEMENT:  UPDATE volumes SET state = $1 WHERE (id = $2 AND (state = $3 OR state = $4)) 
E  ERROR:  update or delete on table "volumes" violates foreign key constraint "volumes_parent_id_fkey" on table "volumes" 
E  DETAIL:  Key (id, state)=(3715531, created) is still referenced from table "volumes". 
E  STATEMENT:  UPDATE volumes SET state = $1 WHERE (id = $2 AND (state = $3 OR state = $4))

About this issue

Original URL
State: closed
Created 7 years ago
Reactions: 5
Comments: 16 (7 by maintainers)

Most upvoted comments

I’m getting this too, on 3.14.1. I can help to diagnose/troubleshoot this, if needed. It is happening few times per minute now on my cluster.

Nopik on Jun 24, 2018

Any word on how this is going?

keithkroeger on Oct 17, 2018

I’m experiencing the same issue on Concourse 5.2.0 installed to Kubernetes with Helm. Postgres logs full of

ERROR:  update or delete on table "volumes" violates foreign key constraint "volumes_parent_id_fkey" on table "volumes"
DETAIL:  Key (id, state)=(101038, created) is still referenced from table "volumes".
STATEMENT:  UPDATE volumes SET state = $1 WHERE (id = $2 AND (state = $3 OR state = $4))

Jobs error out with either dial tcp: lookup concourse-05212019-postgresql on 10.96.0.42:53: read udp 10.38.0.4:42714->10.96.0.42:53: i/o timeout or failed to create volume

Did anyone come up with a workaround or recovery protocol yet?

prein on May 31, 2019

Seeing the same here running concourse v4.2.1 w/PostgresDB v10.5. Our DBAs are getting angry as we start to run concourse at scale with persistent PostgresDBs and their logs are getting flooded with errors. Previously we’ve been running our concourse instances with an ephemeral postgresDB (via docker-compose) but are transitioning to permanent persistent postgresDBs

From our DBA:

I am still noticing this issue in the PostgreSQL QA logs.  Here are number of errors in for this issue as noted in the logs:
 
2018-12-08                          2882
2018-12-09                          2880
2018-12-10                          3069
2018-12-11                          1852
2018-12-12                          3461
2018-12-13                          9268
2018-12-14                          18999
2018-12-15                          20160
2018-12-16                          20160

This is with just about 6-8 concourse installations, and we could theoretically scale up to around 600 which will be a very big problem.

billimek on Dec 17, 2018

We have just faced this problem on 4.2.1.

ghost on Dec 12, 2018