sql: restricted DDL / DML inside transactions

[gpaul]

FEATURE REQUEST / BUG REPORT

I noticed the following sequences cannot be performed within a transaction. I tested using CockroachDB v1.1.8 and v2.0.2.

1. Add a field to a table, then add a constraint on that field.

create table foo ();
begin;
savepoint cockroach_restart;
ALTER TABLE foo ADD COLUMN bar VARCHAR;
ALTER TABLE foo ADD CONSTRAINT bar CHECK (foo IN ('a', 'b', 'c', 'd')); 
release savepoint cockroach_restart;
commit;

2. Add a field to a table, then SELECT that field.

create table foo ();
begin;
savepoint cockroach_restart;
ALTER TABLE foo ADD COLUMN bar VARCHAR;
SELECT bar from foo;
release savepoint cockroach_restart;
commit;

Interestingly, you can select on an existing field:

create table foo (baz varchar);
begin;
savepoint cockroach_restart;
ALTER TABLE foo ADD COLUMN bar VARCHAR;
SELECT baz from foo;
release savepoint cockroach_restart;
commit;

I figured I'd list these here as they were not mentioned in the relevant known issues section here:
https://www.cockroachlabs.com/docs/stable/known-limitations.html#schema-changes-within-transactions

Jira issue: CRDB-5005

[jordanlewis]

cc @vivekmenezes

[vivekmenezes]

@gpaul We've fixed this for 2.1 such that if you put the CREATE TABLE inside the transaction it will work.

The above issue could be fixed by placing the new column in a PUBLIC list in the uncommitted table descriptors. The trick here is we don't want the column to be writable, just readable via a SELECT. We should think this through carefully because I feel my suggestion is very hacky!

[vivekmenezes]

@rmloveland these are good examples of the known limitation you are documenting. Thanks

[leafji]

@gpaul We've fixed this for 2.1 such that if you put the CREATE TABLE inside the transaction it will work.

The above issue could be fixed by placing the new column in a PUBLIC list in the uncommitted table descriptors. The trick here is we don't want the column to be writable, just readable via a SELECT. We should think this through carefully because I feel my suggestion is very hacky!

Hi @vivekmenezes i understand you suggestion , but is there any drawback or risk in this solutions ? And is there any conflict with online schema change, especially in transication?

[ebner]

I just tried to run our integration tests (the application is built to be used with Postgres) against CockroachDB 20.2 and ran into an error that references this Github issue.

We have two statements within the same transaction that seem to trigger the error (some irrelevant data replaced with "..."):

INSERT INTO datasets (id, status, created, data_table) VALUES ('...', ..., '...', 'tablex')
CREATE TABLE IF NOT EXISTS tablex (rownr SERIAL PRIMARY KEY, data JSONB NOT NULL)

The insert is not performed against the newly created table, the insert merely contains data that refers to the table name.

According to https://www.cockroachlabs.com/docs/v20.2/online-schema-changes#run-schema-changes-inside-a-transaction-with-create-table this should actually work, the example transaction contains both a CREATE TABLE and an INSERT statement. Or must the CREATE be executed before the INSERT?

[vy-ton]

@ebner Thanks for your question. You are running into #54477. The example you linked from the documentation executes CREATE TABLE and INSERT on the same table which is allowed.

The insert is not performed against the newly created table, the insert merely contains data that refers to the table name.

By this, are you creating a foreign key to tablex?

[ebner]

@ebner Thanks for your question. You are running into #54477. The example you linked from the documentation executes CREATE TABLE and INSERT on the same table which is allowed.

Thanks for the pointer! Are there any plans for supporting this in the near future?

The insert is not performed against the newly created table, the insert merely contains data that refers to the table name.

By this, are you creating a foreign key to tablex?

Yes, it is used as foreign key, but currently not formalized using a constraint, but we'll probably change that at some point.

[ajwerner]

Commenting on an old issue. We did fix #54477 not too long after this issue. This issue more generally states the goals outlined in https://github.com/cockroachdb/cockroach/blob/c46965b4e3b01f7353213144b5a9b3fe3ff00443/docs/RFCS/20200909_transactional_schema_changes.md, which we continue to work on.