I am using replace into in a transaction, executing it every 10 seconds with 100,000 entries each time. The execution is very unstable and frequently throws errors.

Create Table: CREATE TABLE interfaceocts (
  unix_ts BIGINT NULL,
  metric_time TIMESTAMP NULL,
  metric_name VARCHAR NULL,
  aiyama VARCHAR NULL,
  instance VARCHAR NULL,
  ifname VARCHAR NULL,
  ipifdescr VARCHAR NULL,
  ifindex INT NULL,
  metric_value DOUBLE NULL
) ENGINE=FUSE CLUSTER BY linear(unix_ts, metric_name, instance, ipifdescr, to_date(metric_time))

               tx, err :=db.BeginTx(ctx, nil)
		if err != nil {
			return err
		}
		// 准备插入语句
		// Build batch insert query
		query := "REPLACE INTO interfaceocts (unix_ts, metric_time, metric_name, aiyama, instance, ifname, ipifdescr, ifindex, metric_value) on (unix_ts,metric_name,aiyama,ipifdescr) VALUES "
		values := make([]interface{}, 0, len(buffer)*9)
		placeholders := make([]string, 0, len(buffer))
		for _, o := range buffer {
			placeholders = append(placeholders, "(?,?,?,?,?,?,?,?,?)")
			values = append(values, o.unix_ts, o.metric_time, o.metric_name, o.aiyama, o.instance, o.ifname, o.ipifdescr, o.ifindex, o.metric_value)
		}
		query += strings.Join(placeholders, ", ")
		fmt.Println(query, values, len(values))
		result, err := tx.ExecContext(ctx, query, values...)
		if err != nil {
			return err
		}
		cnt, _ := result.RowsAffected()
		// 提交事务
		if err := tx.Commit(); err != nil {
			return err
		}

when use
placeholders = append(placeholders, fmt.Sprintf("($%d, $%d, $%d, $%d, $%d, $%d, $%d, $%d, $%d)",i9+1, i9+2, i9+3, i9+4, i9+5, i9+6, i9+7, i9+8, i*9+9)) return err buildRequest: failed to interpolate params: databend: wrong placeholder count"
so change to placeholders = append(placeholders, "(?,?,?,?,?,?,?,?,?)") then done.
but frequently throws errors: query error: code: 4001, message: Unresolvable conflict detected for table 804368612",cnt, _ := result.RowsAffected() cnt is 0

can use "replace into table " on Prepare? #142

Description

Activity

Metadata

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

Issue actions