roachtest: sqlsmith/setup=tpcc/setting=ddl-nodrop failed

[cockroach-teamcity]

roachtest.sqlsmith/setup=tpcc/setting=ddl-nodrop failed with artifacts on master @ 2861a4ee05fac1cc13f87bf00f368a65ea8700dc:

test artifacts and logs in: /artifacts/sqlsmith/setup=tpcc/setting=ddl-nodrop/run_1
(sqlsmith.go:238).func3: error: pq: internal error: executing declarative schema change PostCommitPhase stage 2 of 7 with 1 BackfillType op (rollback=false) for ALTER TABLE: failed to construct index entries during backfill: ResolveFunctionByOID unimplemented
stmt:
ALTER TABLE defaultdb.public.item ADD COLUMN c̈ol14 REGPROCEDURE DEFAULT (9:::OID::OID);

Parameters: ROACHTEST_cloud=gce , ROACHTEST_cpu=4 , ROACHTEST_encrypted=false , ROACHTEST_ssd=0

Help

See: roachtest README

See: How To Investigate (internal)

/cc @cockroachdb/sql-queries

This test on roachdash | Improve this report!

Jira issue: CRDB-23644

[cockroach-teamcity]

roachtest.sqlsmith/setup=tpcc/setting=ddl-nodrop failed with artifacts on master @ b21379bb56dd206e6f63cc7d07ca72e85db7a4c4:

test artifacts and logs in: /artifacts/sqlsmith/setup=tpcc/setting=ddl-nodrop/run_1
(sqlsmith.go:238).func3: error: pq: internal error: executing declarative schema change PostCommitPhase stage 2 of 7 with 1 BackfillType op (rollback=false) for ALTER TABLE: failed to construct index entries during backfill: ResolveFunctionByOID unimplemented
stmt:
ALTER TABLE defaultdb.public.new_order ADD COLUMN "%vcol78" REGPROC DEFAULT (440462301:::OID) NOT NULL;

Parameters: ROACHTEST_cloud=gce , ROACHTEST_cpu=4 , ROACHTEST_encrypted=false , ROACHTEST_ssd=0

Help

See: roachtest README

See: How To Investigate (internal)

This test on roachdash | Improve this report!

[cockroach-teamcity]

roachtest.sqlsmith/setup=tpcc/setting=ddl-nodrop failed with artifacts on master @ dc974cd698364a4db5acb630162dd8b1856cfad6:

test artifacts and logs in: /artifacts/sqlsmith/setup=tpcc/setting=ddl-nodrop/run_1
(sqlsmith.go:238).func3: error: pq: internal error: executing declarative schema change PostCommitPhase stage 2 of 7 with 1 BackfillType op (rollback=false) for ALTER TABLE: failed to construct index entries during backfill: ResolveFunctionByOID unimplemented
stmt:
ALTER TABLE defaultdb.public.customer ADD COLUMN col28 REGPROCEDURE DEFAULT (5:::OID::OID) NULL CHECK (true::BOOL);

Parameters: ROACHTEST_cloud=gce , ROACHTEST_cpu=4 , ROACHTEST_encrypted=false , ROACHTEST_ssd=0

Help

See: roachtest README

See: How To Investigate (internal)

This test on roachdash | Improve this report!

[cockroach-teamcity]

roachtest.sqlsmith/setup=tpcc/setting=ddl-nodrop failed with artifacts on master @ 78fe59d0536be35fcec68280142746fb91515551:

test artifacts and logs in: /artifacts/sqlsmith/setup=tpcc/setting=ddl-nodrop/run_1
(sqlsmith.go:238).func3: error: pq: internal error: executing declarative schema change PostCommitPhase stage 2 of 7 with 1 BackfillType op (rollback=false) for ALTER TABLE: failed to construct index entries during backfill: ResolveFunctionByOID unimplemented
stmt:
ALTER TABLE defaultdb.public.warehouse
	ADD COLUMN "co% l20" REGPROCEDURE DEFAULT (1296736401:::OID) CHECK (false) CHECK (false);

Parameters: ROACHTEST_cloud=gce , ROACHTEST_cpu=4 , ROACHTEST_encrypted=false , ROACHTEST_ssd=0

Help

See: roachtest README

See: How To Investigate (internal)

Same failure on other branches

• roachtest: sqlsmith/setup=tpcc/setting=ddl-nodrop failed #96132 roachtest: sqlsmith/setup=tpcc/setting=ddl-nodrop failed [C-test-failure O-roachtest O-robot T-sql-queries branch-release-22.2]

This test on roachdash | Improve this report!

[cockroach-teamcity]

roachtest.sqlsmith/setup=tpcc/setting=ddl-nodrop failed with artifacts on master @ 69dd453d0e61e258f402c5751de310405743cd18:

test artifacts and logs in: /artifacts/sqlsmith/setup=tpcc/setting=ddl-nodrop/run_1
(sqlsmith.go:238).func3: error: pq: internal error: executing declarative schema change PostCommitPhase stage 2 of 7 with 1 BackfillType op (rollback=false) for ALTER TABLE: failed to construct index entries during backfill: ResolveFunctionByOID unimplemented
stmt:
ALTER TABLE defaultdb.public.item ADD COLUMN "col 9" REGPROCEDURE DEFAULT (3039363388:::OID) NOT NULL;

Parameters: ROACHTEST_cloud=gce , ROACHTEST_cpu=4 , ROACHTEST_encrypted=false , ROACHTEST_ssd=0

Help

See: roachtest README

See: How To Investigate (internal)

Same failure on other branches

• roachtest: sqlsmith/setup=tpcc/setting=ddl-nodrop failed #96132 roachtest: sqlsmith/setup=tpcc/setting=ddl-nodrop failed [C-test-failure O-roachtest O-robot T-sql-queries branch-release-22.2]

This test on roachdash | Improve this report!

[ajwerner]

@chengxiong-ruan maybe the answer should be that we disallow adding such a column, but then randgen would need to be told about that. For what it's worth, I think this behavior is sort of legit and the error message is terrible.

[ajwerner]

One approach is to reject adding columns of type REGPROCEDURE. Things are a little bit funny here because on some level it's the logic to do the cast we injected recently @mgartner that leads to the error.

[mgartner]

What prevents us from implementing ResolveFunctionByOID in the backfiller?

[ajwerner]

Deep down I think the actual problem here is our cast from int to regprocedure is not like postgres's. In postgres it's infallible (or so it seems).

postgres=# create table t( i int primary key);
CREATE TABLE
postgres=# insert into t values (1);
INSERT 0 1
postgres=# insert into t values (1);
INSERT 0 1
postgres=# alter table t add column j regprocedure default 12345678;
ALTER TABLE
postgres=# select * from t;
 i |    j     
---+----------
 1 | 12345678
(1 row)

postgres=# select 12345678::regprocedure;
 regprocedure 
--------------
 12345678
(1 row)

[chengxiong-ruan]

What prevents us from implementing ResolveFunctionByOID in the backfiller?

Nothing prevent us, we'll need it eventually for things like column default values.

[chengxiong-ruan]

Hmm...I guess it's not easy to plumb things through into the DummyEvalPlanner

[ajwerner]

Let's also be real, this is not something our customers actually want to do. It's an esoteric feature that maybe maybe would be useful to some far future management of schema objects by some tooling that certainly wouldn't work in cockroach today. Whatever we can do to make this test just not fail in an expedient way is what we should do here.

[mgartner]

Fair enough: #96346