Title
f
Francisco Correia
03/01/2023, 6:04 PMHas anyone ever encountered this error while migrating from v13 -> v14 with no down time on the
spicedb migrate add-xid-constraintsadd-xid-constraintse
ecordell
03/01/2023, 7:26 PMwhat steps did you perform so far?
I think you'll see that error if you run the migrations out of order
f
Francisco Correia
03/02/2023, 10:57 AMI ran the commands in order. I checked the state of the DB and it had the new columns before I tried adding the constraints
Also if I run it without any data it works. But if I have data it breaks
e
ecordell
03/03/2023, 2:24 PMwhat version of postgres are you running?
f
Francisco Correia
03/07/2023, 2:00 PMI think that was the issue, I've just upgrades postgres from 12 to 14 and I'm trying again
e
ecordell
03/07/2023, 2:46 PMYep - minimum supported version is 13.8
f
Francisco Correia
03/07/2023, 4:23 PMHmm still getting this error:
2023-03-07 16:21:50.218 GMT [12578] STATEMENT: ALTER TABLE relation_tuple
DROP CONSTRAINT pk_relation_tuple,
ADD CONSTRAINT pk_relation_tuple PRIMARY KEY USING INDEX ix_relation_tuple_pk,
ADD CONSTRAINT uq_relation_tuple_living_xid UNIQUE USING INDEX ix_relation_tuple_living;
2023-03-07 16:22:03.350 GMT [12609] ERROR: index "ix_relation_tuple_pk" is not valid at character 71any ideas? 😦
I am running two instances of spiceDB as part of a k8s cluster and they are running migrations in the init container phase (in the deployment file). Seems like the underlying issue I was having was a deadlock probably from the migrations trying to run simultaneously. See here:
On Postgres logs:
2023-03-07 23:16:52.770 GMT [4637] ERROR: deadlock detected
2023-03-07 23:16:52.770 GMT [4637] DETAIL: Process 4637 waits for ShareUpdateExclusiveLock on relation 16386 of database 16384; blocked by process 4640.
Process 4640 waits for ShareLock on virtual transaction 3/28975; blocked by process 4637.
Process 4637: CREATE UNIQUE INDEX CONCURRENTLY IF NOT EXISTS ix_rttx_oldpk
ON relation_tuple_transaction (id)
Process 4640: CREATE UNIQUE INDEX CONCURRENTLY IF NOT EXISTS ix_rttx_oldpk
ON relation_tuple_transaction (id)
2023-03-07 23:16:52.770 GMT [4637] HINT: See server log for query details.
2023-03-07 23:16:52.770 GMT [4637] STATEMENT: CREATE UNIQUE INDEX CONCURRENTLY IF NOT EXISTS ix_rttx_oldpk
ON relation_tuple_transaction (id)[db-migration] {"level":"error","error":"unable to migrate to `add-xid-constraints` revision: error executing migration function: ERROR: deadlock detected (SQLSTATE 40P01)","time":"2023-03-07T23:16:52Z","message":"terminated with errors"}[db-migration] {"level":"error","error":"unable to migrate to `add-xid-constraints` revision: error executing migration `add-xid-constraints`: ERROR: index \"ix_relation_tuple_pk\" is not valid (SQLSTATE 55000)","time":"2023-03-07T23:17:01Z","message":"terminated with errors"}e
ecordell
03/08/2023, 2:41 PMyeah, I wouldn't recommend running the migrations in an init container for that reason
have you checked out https://github.com/authzed/spicedb-operator? it's how we recommend running on kube. it will do all of the coordination around migrations for you too (including walking through the phases for 1.13->1.14 automatically)
f
Francisco Correia
03/10/2023, 12:12 AMYeah I have looked at that but it's a bit complicated to run that with our company infrastructure.