pkg/ccl/testccl/workload/schemachange/schemachange_test: TestWorkload failed [error with DROP VALUE followed by DROP SCHEMA in same txn]

[cockroach-teamcity]

pkg/ccl/testccl/workload/schemachange/schemachange_test.TestWorkload failed with artifacts on release-24.1 @ 1e1308eb82915a46d1cf2621ffd2849088c7317f:

    ],
    "result": []
   },
   {
    "query": "WITH descriptors AS (SELECT descriptor.id, \"parentSchemaID\" AS schema_id, namespace.name AS name, crdb_internal.pb_to_json('desc', descriptor) AS descriptor FROM system.descriptor JOIN (SELECT * FROM system.namespace UNION SELECT \"parentID\", \"parentSchemaID\", (json_each).@1 AS name, (json_array_elements((json_each).@2-\u003e'signatures')-\u003e'id')::INT8 AS id FROM (SELECT ns.\"parentID\", ns.id AS \"parentSchemaID\", json_each((crdb_internal.pb_to_json('desc', descriptor)-\u003e'schema')-\u003e'functions') FROM system.descriptor JOIN system.namespace AS ns ON ns.id = descriptor.id WHERE crdb_internal.pb_to_json('desc', descriptor) ? 'schema')) AS namespace ON namespace.id = descriptor.id WHERE \"parentID\" = (SELECT id FROM system.namespace WHERE (name = current_database()) AND (\"parentID\" = 0))), functions AS (SELECT id, schema_id, name, descriptor-\u003e'function' AS descriptor FROM descriptors WHERE descriptor ? 'function'), pg_depends_from_diff_schema AS (SELECT refobjid FROM pg_depend AS d, functions AS src_function, functions AS dst_function WHERE (((((src_function.schema_id != $1::REGNAMESPACE::INT8) AND (dst_function.schema_id = $1::REGNAMESPACE::INT8)) AND (d.objid = (src_function.id + 100000))) AND (d.refobjid = (dst_function.id + 100000))) AND (d.classid = 'pg_catalog.pg_proc'::REGCLASS::INT8)) AND (d.refclassid = 'pg_catalog.pg_proc'::REGCLASS::INT8)) SELECT * FROM pg_depends_from_diff_schema",
    "queryArgs": [
     "schema_w1_22"
    ],
    "result": []
   }
  ],
  "previousStatements": [
   "ALTER TYPE schema_w1_22.enum_w1_23 DROP VALUE 's'",
   "DROP SCHEMA \"schema_w1_22\" CASCADE"
  ]
 }
}
    schema_change_external_test.go:132: 
        	Error Trace:	pkg/ccl/testccl/workload/schemachange/schemachange_test_test/pkg/ccl/testccl/workload/schemachange/schema_change_external_test.go:132
        	Error:      	Received unexpected error:
        	            	***UNEXPECTED COMMIT ERROR; Received an unexpected commit error: ERROR: transaction committed but schema change aborted with error: (42704): type with ID 112 does not exist (SQLSTATE 42704)
        	            	(1) forced error mark
        	            	  | "fatal error when running txn"
        	            	  | github.com/cockroachdb/errors/withstack/*withstack.withStack::
        	            	Wraps: (2)
        	            	Wraps: (3) attached stack trace
        	            	  -- stack trace:
        	            	  | github.com/cockroachdb/cockroach/pkg/workload/schemachange.(*schemaChangeWorker).run
        	            	  | 	github.com/cockroachdb/cockroach/pkg/workload/schemachange/schemachange.go:676
        	            	  | pkg/ccl/testccl/workload/schemachange/schemachange_test_test.TestWorkload.TestWorkload.func4.func6
        	            	  | 	pkg/ccl/testccl/workload/schemachange/schemachange_test_test/pkg/ccl/testccl/workload/schemachange/schema_change_external_test.go:121
        	            	  | golang.org/x/sync/errgroup.(*Group).Go.func1
        	            	  | 	golang.org/x/sync/errgroup/external/org_golang_x_sync/errgroup/errgroup.go:78
        	            	  | runtime.goexit
        	            	  | 	src/runtime/asm_arm64.s:1222
        	            	Wraps: (4) ***UNEXPECTED COMMIT ERROR; Received an unexpected commit error
        	            	Wraps: (5) ERROR: transaction committed but schema change aborted with error: (42704): type with ID 112 does not exist (SQLSTATE 42704)
        	            	Error types: (1) *markers.withMark (2) *schemachange.ErrorState (3) *withstack.withStack (4) *errutil.withPrefix (5) *pgconn.PgError
        	Test:       	TestWorkload
Schema Workload Stats
Total Schema Statements Executed = 24
Total Schema Statements Succeeded = 24
Total Schema Statement Expected Failures = 0
Total Transactions Committed = 12
Total Transactions Rolled Back = 21
Total Transactions Executed = 33
    schema_change_external_test.go:98: backup, tracing data, and system table dumps in /artifacts/tmp/_tmp/67537770b84c3a4ea177539c1e46bd43/logTestWorkload456927949
    panic.go:626: -- test log scope end --
test logs left over in: /artifacts/tmp/_tmp/67537770b84c3a4ea177539c1e46bd43/logTestWorkload456927949
--- FAIL: TestWorkload (10.65s)

Help

See also: How To Investigate a Go Test Failure (internal)

Same failure on other branches

• pkg/ccl/testccl/workload/schemachange/schemachange_test: TestWorkload failed #122785 pkg/ccl/testccl/workload/schemachange/schemachange_test: TestWorkload failed [C-test-failure O-robot P-1 T-sql-foundations branch-release-23.2.5-rc]
• pkg/ccl/testccl/workload/schemachange/schemachange_test: TestWorkload failed [unexpected value 143] #122745 pkg/ccl/testccl/workload/schemachange/schemachange_test: TestWorkload failed [unexpected value 143] [C-test-failure O-robot P-2 T-sql-foundations branch-release-23.1]
• pkg/ccl/testccl/workload/schemachange/schemachange_test: TestWorkload failed [error with DROP VALUE followed by DROP SCHEMA in same txn] #121828 pkg/ccl/testccl/workload/schemachange/schemachange_test: TestWorkload failed [error with DROP VALUE followed by DROP SCHEMA in same txn] [C-test-failure O-robot O-schema-testing P-1 T-sql-foundations branch-release-23.2]

/cc @cockroachdb/sql-foundations

This test on roachdash | Improve this report!

Jira issue: CRDB-38375

[rafiss]

This looks like it could be similar to #121828

[rafiss]

Repro:

CREATE SCHEMA schema_w1_22;
CREATE TYPE schema_w1_22.enum_w1_23  AS ENUM ('s', 't');

begin;
ALTER TYPE schema_w1_22.enum_w1_23 DROP VALUE 's';
DROP SCHEMA "schema_w1_22" CASCADE;
commit;


ERROR: transaction committed but schema change aborted with error: (42704): type with ID 109 does not exist
SQLSTATE: XXA00
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://go.crdb.dev/issue-v/42061/v24.1

This is happening since the ALTER TYPE is executed after DROP SCHEMA. Since this is in a transaction, the legacy schema changer is used for both. DROP SCHEMA doesn't run in a job, but ALTER TYPE does, and the job only runs on commit. By that point, the type no longer exists.