Expected unique constraint violated error but get io timeout error #698
Description
Description:
In our production environment, we are implementing a CDC feature and encountered an unexpected behavior with go-ora.
When executing a MERGE INTO statement that contains multiple records, if a primary key conflict occurs, go-ora does not immediately return the Oracle error ORA-00001: unique constraint (ADMIN.SYS_C0012447) violated.
Instead, it waits until the default timeout (10 minute) and then fails with a network error:
read tcp ... i/o timeout
This makes it look like a network instability issue at first. However, after further investigation, we confirmed that the real cause was the unique constraint violation.
We tested the same code with godror, and it correctly returns the Oracle error ORA-00001 immediately without waiting.
So the question is: Is this the expected behavior in go-ora, or is this a bug?
Reproduction steps
Create a test table:
func createTable(conn *sql.DB) error {
t := time.Now()
sqlText := `CREATE table TEMP_TABLE_343 (
ID number(10) NOT NULL,
NAME VARCHAR(20),
VAL number(10),
PRIMARY KEY(ID)
)`
_, err := conn.Exec(sqlText)
if err != nil {
return err
}
_print("finish create table: ", time.Now().Sub(t))
return nil
}
func merge(conn *sql.DB) error {
sqlText := `MERGE INTO TEMP_TABLE_343 t1
USING (select :c1 "ID",:c2 "NAME",:c3 "VAL" from dual) tmp
ON (tmp."ID"=t1."ID" and tmp."NAME"=t1."NAME")
WHEN MATCHED THEN UPDATE SET t1."VAL"=tmp."VAL"
WHEN NOT MATCHED THEN INSERT ("ID", "NAME", "VAL") VALUES (tmp."ID", tmp."NAME", tmp."VAL")`
idValues := []int64{1, 1}
nameValues := []string{"Aasdasdasdasd", "Basdasd"}
valValues := []int64{20, 25}
namedParams := []interface{}{
sql.Named("c1", idValues),
sql.Named("c2", nameValues),
sql.Named("c3", valValues),
}
_, err := conn.Exec(sqlText, namedParams...)
if err != nil {
fmt.Println("can't merge: ", err)
return err
}
return nil
}