CREATE TABLE and INSERT fail in same Transaction

[timchunght]

I believe there is a bug in the transaction. So when I CREATE TABLE and INSERT into the same table in the same transaction, cockroach just hangs indefinitely. This bug was discovered when using the goose migration tool with Cockroach. All the code only uses the lib/pq and the default database/sql and same issue persists even in isolation with the code moved into an individual script.

Everything works perfectly in postgres. However, if I were to move the CREATE TABLE outside of the transaction, everything works. Please advise.

txn, err := db.Begin()
	if err != nil {
		log.Println("cannot start transaction", err)

	}

createVersionTableSql := `CREATE TABLE goose_db_version (
           	id serial NOT NULL,
               version_id bigint NOT NULL,
               is_applied boolean NOT NULL,
               tstamp timestamp NULL default now(),
               PRIMARY KEY(id)
           );`
if _, err := txn.Exec(createVersionTableSql); err != nil {

	log.Println("error so creatingVersionTable", err)

}

version := 0
applied := true
if _, err := db.Exec("INSERT INTO goose_db_version (version_id, is_applied) VALUES ($1, $2);", version, applied); err != nil {

	txn.Rollback()
	log.Println("error inserting goose_db_version", err)
}

txn.Commit()

[benesch]

Hi @timchunght, thanks for reporting! In case you're curious, there are a few existing issues tracking oddities with schema changes and transactions:

• sql: index creation fails if issued just after create table in same txn #13505 tracks a similar issue where index creation fails if called in the same transaction as CREATE TABLE.
• sql: Can't drop and replace a table within a transaction #12123 tracks an issue where CREATE TABLE cannot follow a DROP TABLE in the same transaction.
• sql: schema changes in SQL transactions don't make sense #14280 tracks the broader issue that, given our current schema change semantics, allowing schema change statements in transactions at all might be incorrect.

As far as I know this specific bug hasn't yet been discovered, but I'll defer triaging to the schema change experts. +cc @vivekmenezes

The workaround, as you've discovered, is to execute the CREATE TABLE statement in a separate transaction. Based on the code sample you've posted, it seems this workaround may be perfectly viable, but the fact that we're not compatible with goose out-of-the-box is unfortunate.

[petermattis]

Creating a table was not historically a schema change in the same sense as other schema changes operations and some effort was made to allow subsequent inserts to the new table within the same transaction to work. Did we regress accidentally here, or was this intentional?

[dt]

At least when there are relations between tables (fks, views), CREATE does need to be a schema change (with the committed intermediate state + waitForOneVersion dance).

[vivekmenezes]

#14368 gets rid of this kind of surprise by explicitly making this an error. It's in review.

@petermattis this used to work the way you describe but now is executed like a schema change. It's strictly not necessary, and we can consider adding back the lost behavior post 1.0

[timchunght]

@benesch Thanks and I have looked at them before filling this issue. It appears that, as you acknowledged, transaction might have slight unexpected behaviours. Nonetheless, we were able to do a 'fix' by just splitting them into two subsequent transactions. However, we also sacrificed on the transaction guarantee (though not a big deal for this case, it could potentially be useful given a lot of existing PG tools implement transactions that combine CREATE TABLE with immediate INSERT) I had to fork the library and change that specific part...

[vivekmenezes]

@timchunght thanks for pointing that out. We'll keep this issue open so that it gets fixed.

[cuongdo]

Is this fixed now that you've merged #14368?

[benesch]

Still hangs forever for me on the latest master. (Gist: https://gist.github.com/benesch/cce1a20c0981bc0d45c3a47165c47834)

[vivekmenezes]

confirmed that this hang still exists. Working on a fix.

…

On Tue, Apr 4, 2017 at 11:45 AM Nikhil Benesch ***@***.***> wrote: Still hangs forever for me on the latest master. (Gist: https://gist.github.com/benesch/cce1a20c0981bc0d45c3a47165c47834) — You are receiving this because you were assigned. Reply to this email directly, view it on GitHub <#14548 (comment)>, or mute the thread <https://github.com/notifications/unsubscribe-auth/ALOpBPr5myE0wBiDBF6rjmZz8wtg0z8nks5rsmWKgaJpZM4Mw9t2> .

[vivekmenezes]

This bug only happens when the INSERT statement used in the transaction uses placeholder variables. A workaround is to not use placeholder variables when running an INSERT following a CREATE TABLE in the same transaction. I'm going to fix this but the workaround might work for you in the interim.

[vivekmenezes]

This is a bug in sql PREPARE. It uses a different transaction when reading the table descriptor to type check the placeholder variables. For the case where a table descriptor is created in the same transaction it needs to use the transaction that created the table while type checking the PREPARE placeholder variables.

[jordanlewis]

Similar to the issue we saw in #14473, I believe the root cause of this is that Prepare statements do not run within a parent transaction if one exists: executor.go#L449