Adding column and inverted index in transaction fails

[JorritSalverda]

Describe the problem

When adding a column and creating an inverted index for that column within the same transaction this fails.

I run the following migration script - using https://github.com/pressly/goose -

ALTER TABLE builds ADD releases JSONB;
CREATE INVERTED INDEX IF NOT EXISTS builds_releases ON builds (releases);

Which fails with the following error:

failed to commit transaction: pq: transaction committed but schema change aborted with error: (42703): verify-inverted-idx-count: column \"releases\" does not exist

As a workaround i can disable the migration script to use a transaction with -- +goose NO TRANSACTION, then it runs fine.

I wonder if this is intended behaviour or something that should actually be possible.

To Reproduce

First create a network so containers can communicate with each other by name:

docker network create test

Start cockroachdb in single node mode:

docker run --name cockroachdb --network test -d -p 8080:8080 -p 26257:26257 cockroachdb/cockroach:v19.2.0 start-single-node --insecure --advertise-addr cockroachdb --logtostderr

Run my database migration container against the cockroachdb container:

docker run --network test \
	-e COCKROACH_DATABASE=defaultdb \
	-e COCKROACH_HOST=cockroachdb \
	-e COCKROACH_INSECURE=true \
	-e COCKROACH_PORT=26257 \
	-e COCKROACH_USER=root \
	-e COCKROACH_PASSWORD='' \
	estafette/estafette-ci-db-migrator:0.1.77

Although the error doesn't result in a failing container you'll see the following error in the logs:

2019/11/21 12:11:33 OK    00012_create_releases_table.sql
{"severity":"warn","app":"estafette-ci-db-migrator","version":"0.1.77","error":"FAIL pq: transaction committed but schema change aborted with error: (42703): verify-inverted-idx-count: column \"releases\" does not exist, quitting migration","time":"2019-11-21T12:11:34Z","message":"Failed migrating database schema"}

Expected behavior

The script to execute and add both the column and the inverted index for that column; it did in the past against a prior version of cockroachdb.

Environment:

• CockroachDB version v19.2.0
• Server OS: docker container cockroachdb/cockroach:v19.2.0
• Client app golang pq library

Jira issue: CRDB-5337

Epic CRDB-104

[jordanlewis]

@lucy-zhang could you take a look at this? Guessing you might know something about it.

[thoszhang]

Hi @JorritSalverda, could you post the output of SHOW CREATE TABLE builds? The first error message is about the triggers column, and I'm not sure if that's also in the builds table.

If I understand correctly,

ALTER TABLE builds ADD releases JSONB;
CREATE INVERTED INDEX IF NOT EXISTS builds_releases ON builds (releases);

is being run inside an explicit transaction by the migration tool, right? Are there any other statements within the transaction?

The second error message you posted does reference the releases column, which leads me to think there's an issue related to referencing columns created earlier in the same transaction. But if you're getting errors for both releases and triggers then there may be something else going on.

[thoszhang]

I was able to reproduce something like this error in both 19.2.0 and 19.1.5.

19.2.0:

root@127.0.0.1:49354/movr> create table t ();
CREATE TABLE

Time: 3.445ms

root@127.0.0.1:49354/movr> 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.
                        -> alter table t add column a jsonb;
                        -> create inverted index idx on t (a);
                        -> commit;
pq: transaction committed but schema change aborted with error: (42703): verify-inverted-idx-count: column "a" does not exist
HINT: Some of the non-DDL statements may have committed successfully, but some of the DDL statement(s) failed.
Manual inspection may be required to determine the actual state of the database.
--
See: https://github.com/cockroachdb/cockroach/issues/42061

19.1.5:

root@127.0.0.1:49373/defaultdb> create table t ();                                                                                                                                                          CREATE TABLE

Time: 2.87ms

root@127.0.0.1:49373/defaultdb> 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.
                             -> alter table t add column a jsonb;
                             -> create inverted index idx on t (a);
                             -> commit;
pq: sql/sqlbase/errors.go:130 in NewUndefinedColumnError(): (42703) column "a" does not exist
verify-inverted-idx-count
github.com/cockroachdb/cockroach/pkg/sql.(*internalExecutorImpl).execInternal.func1
	/go/src/github.com/cockroachdb/cockroach/pkg/sql/internal.go:477
github.com/cockroachdb/cockroach/pkg/sql.(*internalExecutorImpl).execInternal
	/go/src/github.com/cockroachdb/cockroach/pkg/sql/internal.go:574
github.com/cockroachdb/cockroach/pkg/sql.(*internalExecutorImpl).queryInternal
	/go/src/github.com/cockroachdb/cockroach/pkg/sql/internal.go:257
github.com/cockroachdb/cockroach/pkg/sql.(*SessionBoundInternalExecutor).QueryRow
	/go/src/github.com/cockroachdb/cockroach/pkg/sql/internal.go:365
github.com/cockroachdb/cockroach/pkg/sql.(*SchemaChanger).validateInvertedIndexes.func2
	/go/src/github.com/cockroachdb/cockroach/pkg/sql/backfill.go:842
github.com/cockroachdb/cockroach/pkg/util/ctxgroup.Group.GoCtx.func1
	/go/src/github.com/cockroachdb/cockroach/pkg/util/ctxgroup/ctxgroup.go:170
github.com/cockroachdb/cockroach/vendor/golang.org/x/sync/errgroup.(*Group).Go.func1
	/go/src/github.com/cockroachdb/cockroach/vendor/golang.org/x/sync/errgroup/errgroup.go:57
runtime.goexit
	/usr/local/go/src/runtime/asm_amd64.s:1333

So I have some idea of what this error is, which is that the verify-inverted-idx-count query (which validates the index before it's added) fails to read columns created earlier in the transaction. The problem is that the TableCollection used within this transaction only has the in-progress, non-public columns in its table schema, and we have an ad-hoc way of inserting other schemas into the TableCollection to make it be able to read columns prematurely before they're actually made public, but it apparently doesn't work in this case. (See #34304, which we should really try to work on some more.)

I'm not sure why there's apparently a regression from 19.1, though, or what the regression is specifically. The problem described above is present in both versions, so there's something else going on. Maybe something changed in the migration tool?

[JorritSalverda]

Hi @lucy-zhang I updated the error message; I initially copied it from one of similar cases failing.

When running it against version 2.1.6 with the same version of the migration tool it executes fine though:

docker network create test

docker run -d --name cockroachdb --rm --network test -p 8080:8080 -p 26257:26257 cockroachdb/cockroach:v2.1.6 start --insecure --logtostderr

docker run --network test \
	-e COCKROACH_DATABASE=defaultdb \
	-e COCKROACH_HOST=cockroachdb \
	-e COCKROACH_INSECURE=true \
	-e COCKROACH_PORT=26257 \
	-e COCKROACH_USER=root \
	-e COCKROACH_PASSWORD='' \
	estafette/estafette-ci-db-migrator:0.1.77

So it must be something that regressed in the mean time.

[zepatrik]

So I was able to reproduce this with an even simpler case with v19.2.5, v19.2.0 and v19.1.0 (same output).

root@:26257/defaultdb> create table t ();
CREATE TABLE

Time: 32.001459ms

root@:26257/defaultdb> 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.
                    -> alter table t add column a text;
                    -> insert into t (a) values ('foo');
                    -> 
pq: column "a" does not exist

Basically the change made by alter table is not visible within the transaction.

[thoszhang]

@zepatrik Columns not being visible for writes in the transaction in which they're created is a limitation in how schema changes work today. There's more documentation here: https://www.cockroachlabs.com/docs/v19.2/online-schema-changes.html#limited-support-for-schema-changes-within-transactions

I think the original inverted index validation failure is a different bug and needs further investigation. It's useful to know this was a regression from 2.1 to 19.1.