Improved CREATE TABLE AS

[jseldess]

Background: https://airtable.com/tblD3oZPLJgGhCmch/viwchkdQkRUw6hZ3z/recUoBdXbVnkbBmtl?blocks=hide
PM: @rolandcrosby
Eng:

[rolandcrosby]

Important note to call out in the docs/release notes: CREATE TABLE AS statements will run as jobs in 19.2. This means that they will complete asynchronously and their results will not be immediately available. That change means that transactions like this one, which work in CockroachDB 19.1, will not work in 19.2:

BEGIN TRANSACTION;
CREATE TABLE foo AS (SELECT 1 AS col1);
UPDATE foo SET col1 = 2 * col1 WHERE col1 = 1;
COMMIT TRANSACTION;

I'm not concerned that this will break anyone's workflow -- usage of CREATE TABLE AS is very low, according to our telemetry data, and CREATE TABLE AS currently fails with tables of any meaningful size -- but we do need to call this out as a breaking change.

[lnhsingh]

cockroachdb/cockroach#38904
cockroachdb/cockroach#38884
cockroachdb/cockroach#38589

[lnhsingh]

Important note to call out in the docs/release notes: CREATE TABLE AS statements will run as jobs in 19.2. This means that they will complete asynchronously and their results will not be immediately available. That change means that transactions like this one, which work in CockroachDB 19.1, will not work in 19.2:

BEGIN TRANSACTION;
CREATE TABLE foo AS (SELECT 1 AS col1);
UPDATE foo SET col1 = 2 * col1 WHERE col1 = 1;
COMMIT TRANSACTION;

I'm not concerned that this will break anyone's workflow -- usage of CREATE TABLE AS is very low, according to our telemetry data, and CREATE TABLE AS currently fails with tables of any meaningful size -- but we do need to call this out as a breaking change.

Is this currently true? I ran a CTAS statement and I don't see it in SHOW JOBS. Does the CTAS need to be a certain size for it to be a job?

[lnhsingh]

Note: @ericharmeling made most of the necessary edits about a month ago.

[lnhsingh]

Chatted with @rolandcrosby about this today. No additional docs changes need to be added.