Foreign key constraint breaks transaction state

[ipoerner]

A foreign key constraint that's classified as deferred (see SQLite docs here) breaks the transaction state.

Example program:

package main

import (
	"database/sql"
	"fmt"
	_ "github.com/mattn/go-sqlite3"
)

func main() {
	db, _ := sql.Open("sqlite3", "file::memory:")
	db.Exec(`PRAGMA foreign_keys=ON`)
	db.Exec(`CREATE TABLE "pk_table" ("id" integer not null primary key autoincrement)`)
	db.Exec(`CREATE TABLE "fk_table" ("fk" integer not null default 0 REFERENCES pk_table(id) DEFERRABLE INITIALLY DEFERRED)`)
	tx, _ := db.Begin()
	tx.Exec(`INSERT INTO "fk_table" (fk) VALUES (0)`)
	tx.Commit()
	tx, err := db.Begin()
	fmt.Println(err.Error())
}

For a more profound example with proper error checking:

fk_constraint_transaction.go.txt

According to the semantics of database/sql, a transaction must be finished after a call to tx.Commit().

However, according to SQLite documentation of deferred foreign key constraints:

Deferred foreign key constraints are not checked until the transaction tries to COMMIT. For as long as the user has an open transaction, the database is allowed to exist in a state that violates any number of deferred foreign key constraints. However, COMMIT will fail as long as foreign key constraints remain in violation.

A very similar issue was reported here: #184

[ipoerner]

As you can see in the upper example, PRAGMA foreign_keys=ON is required.

[mattn]

seem to be fixed by #407

[ipoerner]

@mattn Thanks for the effort. However, I keep on getting the same error even with the patch from #407:

cannot start a transaction within a transaction

[ipoerner]

This PR here: #300 addresses a similar issue, but the fix is limited to cases where the error condition is SQLITE_BUSY.

Unfortunately, the error in the case described above seems to be a very generic SQLITE_ERROR type error, which might be anything. Otherwise I would have suggested to simply extend the error check from SQLiteTx.Commit() in a way that it checks for "transaction within a transaction", too.

EDIT: Last paragraph rendered invalid.