Rolling back index creation fails with error
jvf opened this issue · 8 comments
Using Ecto migrations to create a table with an additional index and then rolling back this change leads to the following error:
Postgrex.Protocol (#PID<0.186.0>) disconnected: ** (Postgrex.Error) ERROR 40003 (statement_completion_unknown) cannot update progress on succeeded job (id 473809138654281729)
failed to update running status of job 473809138654281729
github.com/cockroachdb/cockroach/pkg/sql.(*SchemaChanger).done.func2
/go/src/github.com/cockroachdb/cockroach/pkg/sql/schema_changer.go:1030
github.com/cockroachdb/cockroach/pkg/sql.LeaseStore.Publish.func1
/go/src/github.com/cockroachdb/cockroach/pkg/sql/lease.go:438
github.com/cockroachdb/cockroach/pkg/internal/client.(*DB).Txn.func1
/go/src/github.com/cockroachdb/cockroach/pkg/internal/client/db.go:598
github.com/cockroachdb/cockroach/pkg/internal/client.(*Txn).exec
/go/src/github.com/cockroachdb/cockroach/pkg/internal/client/txn.go:688
github.com/cockroachdb/cockroach/pkg/internal/client.(*DB).Txn
/go/src/github.com/cockroachdb/cockroach/pkg/internal/client/db.go:597
github.com/cockroachdb/cockroach/pkg/sql.LeaseStore.Publish
/go/src/github.com/cockroachdb/cockroach/pkg/sql/lease.go:388
github.com/cockroachdb/cockroach/pkg/sql.(*SchemaChanger).done
/go/src/github.com/cockroachdb/cockroach/pkg/sql/schema_changer.go:974
github.com/cockroachdb/cockroach/pkg/sql.(*SchemaChanger).runStateMachineAndBackfill
/go/src/github.com/cockroachdb/cockroach/pkg/sql/schema_changer.go:1102
github.com/cockroachdb/cockroach/pkg/sql.(*SchemaChanger).exec
/go/src/github.com/cockroachdb/cockroach/pkg/sql/schema_changer.go:850
github.com/cockroachdb/cockroach/pkg/sql.(*schemaChangerCollection).execSchemaChanges
/go/src/github.com/cockroachdb/cockroach/pkg/sql/exec_util.go:1028
github.com/cockroachdb/cockroach/pkg/sql.(*connExecutor).txnStateTransitionsApplyWrapper
/go/src/github.com/cockroachdb/cockroach/pkg/sql/conn_executor.go:1993
github.com/cockroachdb/cockroach/pkg/sql.(*connExecutor).run
/go/src/github.com/cockroachdb/cockroach/pkg/sql/conn_executor.go:1315
github.com/cockroachdb/cockroach/pkg/sql.(*Server).ServeConn
/go/src/github.com/cockroachdb/cockroach/pkg/sql/conn_executor.go:436
github.com/cockroachdb/cockroach/pkg/sql/pgwire.(*conn).processCommandsAsync.func1
/go/src/github.com/cockroachdb/cockroach/pkg/sql/pgwire/conn.go:580
runtime.goexit
/usr/local/go/src/runtime/asm_amd64.s:1333
I created a minimal example to demonstrate the error (see the README for steps to reproduce): https://github.com/jvf/rollback_index_demo
Any ideas on what the problem or possible next steps for debugging would be highly appreciated.
Which version of CDB are you using ? I am creating secondary indexes all the time in the same tx as the table without any issue.
I tried it on CockroachDB 19.1.3 and 19.1.1 with the same result. Erlang/OTP 21 and Elixir 1.8.2.
Ha you are roll backing the migration, sorry missed that part...
I am kinda suspecting this is an issue with CDB itself, it has trouble in the past creating a secondary index on a table created in the same TX. Something similar is happening here I believe.
Can you try inside a TX with psql to drop the index and then the table ? I am pretty sure you will reproduce the error as well, if so, can you open an issue with them directly please ?
On a side note, the upcoming release of CDB is handling tz precision so this current fork will be archived pretty soon.
Thank you for the pointers, I am indeed able to reproduce the error directly within cockroach sql:
cockroach sql --insecure --host=localhost:26257
# Server version: CockroachDB CCL v19.1.1 (x86_64-apple-darwin18.6.0, built 2019/05/22 22:44:42, go1.12.5) (same version as client)
root@localhost:26257/defaultdb> USE friends_repo;
root@localhost:26257/friends_repo> CREATE TABLE people (id INT8 NOT NULL DEFAULT unique_rowid(), first_name VARCHAR(255), last_name VARCHAR(255), age INT8, PRIMARY KEY (id));
root@localhost:26257/friends_repo> CREATE INDEX people_first_name_index ON people (first_name);
root@localhost:26257/friends_repo> BEGIN;
Now adding input for a multi-line SQL transaction client-side (smart_prompt enabled).
Press Enter two times to send the SQL text collected so far to the server, or Ctrl+C to cancel.
You can also use \show to display the statements entered so far.
-> DROP INDEX friends_repo.public.people@people_first_name_index;
-> DROP TABLE people;
-> COMMIT; pq: cannot update progress on succeeded job (id 474023038595858433)
failed to update running status of job 474023038595858433
github.com/cockroachdb/cockroach/pkg/sql.(*SchemaChanger).done.func2
/private/tmp/cockroach-20190522-91575-1ewc686/cockroach-v19.1.1/src/github.com/cockroachdb/cockroach/pkg/sql/schema_changer.go:1030
github.com/cockroachdb/cockroach/pkg/sql.LeaseStore.Publish.func1
/private/tmp/cockroach-20190522-91575-1ewc686/cockroach-v19.1.1/src/github.com/cockroachdb/cockroach/pkg/sql/lease.go:438
github.com/cockroachdb/cockroach/pkg/internal/client.(*DB).Txn.func1
/private/tmp/cockroach-20190522-91575-1ewc686/cockroach-v19.1.1/src/github.com/cockroachdb/cockroach/pkg/internal/client/db.go:598
github.com/cockroachdb/cockroach/pkg/internal/client.(*Txn).exec
/private/tmp/cockroach-20190522-91575-1ewc686/cockroach-v19.1.1/src/github.com/cockroachdb/cockroach/pkg/internal/client/txn.go:688
github.com/cockroachdb/cockroach/pkg/internal/client.(*DB).Txn
/private/tmp/cockroach-20190522-91575-1ewc686/cockroach-v19.1.1/src/github.com/cockroachdb/cockroach/pkg/internal/client/db.go:597
github.com/cockroachdb/cockroach/pkg/sql.LeaseStore.Publish
/private/tmp/cockroach-20190522-91575-1ewc686/cockroach-v19.1.1/src/github.com/cockroachdb/cockroach/pkg/sql/lease.go:388
github.com/cockroachdb/cockroach/pkg/sql.(*SchemaChanger).done
/private/tmp/cockroach-20190522-91575-1ewc686/cockroach-v19.1.1/src/github.com/cockroachdb/cockroach/pkg/sql/schema_changer.go:974
github.com/cockroachdb/cockroach/pkg/sql.(*SchemaChanger).runStateMachineAndBackfill
/private/tmp/cockroach-20190522-91575-1ewc686/cockroach-v19.1.1/src/github.com/cockroachdb/cockroach/pkg/sql/schema_changer.go:1102
github.com/cockroachdb/cockroach/pkg/sql.(*SchemaChanger).exec
/private/tmp/cockroach-20190522-91575-1ewc686/cockroach-v19.1.1/src/github.com/cockroachdb/cockroach/pkg/sql/schema_changer.go:850
github.com/cockroachdb/cockroach/pkg/sql.(*schemaChangerCollection).execSchemaChanges
/private/tmp/cockroach-20190522-91575-1ewc686/cockroach-v19.1.1/src/github.com/cockroachdb/cockroach/pkg/sql/exec_util.go:1028
github.com/cockroachdb/cockroach/pkg/sql.(*connExecutor).txnStateTransitionsApplyWrapper
/private/tmp/cockroach-20190522-91575-1ewc686/cockroach-v19.1.1/src/github.com/cockroachdb/cockroach/pkg/sql/conn_executor.go:1993
github.com/cockroachdb/cockroach/pkg/sql.(*connExecutor).run
/private/tmp/cockroach-20190522-91575-1ewc686/cockroach-v19.1.1/src/github.com/cockroachdb/cockroach/pkg/sql/conn_executor.go:1316
github.com/cockroachdb/cockroach/pkg/sql.(*Server).ServeConn
/private/tmp/cockroach-20190522-91575-1ewc686/cockroach-v19.1.1/src/github.com/cockroachdb/cockroach/pkg/sql/conn_executor.go:436
github.com/cockroachdb/cockroach/pkg/sql/pgwire.(*conn).processCommandsAsync.func1
/private/tmp/cockroach-20190522-91575-1ewc686/cockroach-v19.1.1/src/github.com/cockroachdb/cockroach/pkg/sql/pgwire/conn.go:580
runtime.goexit
/usr/local/Cellar/go/1.12.5/libexec/src/runtime/asm_amd64.s:1337
This used to work at least in v.2.1.6, seems to be a regression. I will file this with CockroachDb directly. Again, thank you for your help!
On a side note, the upcoming release of CDB is handling tz precision so this current fork will be archived pretty soon.
I am looking forward to using pure Ecto. I tried pure Ecto with Cockroach 19.2.-alpha (which is supposed to have cockroachdb/cockroach#32098 solved) but so far the precision issue is still persisting. I will try again when new versions are released.
I checked the public alpha release and the last commit inside is from July 3th:
The support for TZ precision was merged on July 5th:
If you try any alpha after that date, it should work i believe.
If you try any alpha after that date, it should work i believe.
There is hope than :D
I filed a bug report with CockroachDB: cockroachdb/cockroach#39260
As this is not an issue with ecto_cockroach I am closing the issue.