CopyFrom issue #359

New Issue

i-norden · 2023-04-05T16:01:00Z

i-norden commented

2023-04-05 16:01:00 +00:00

In v4 the CopyFrom mode uses strconv.ParseFloat in order to convert transaction.Value and account.Balance to a format that can be loaded by the binary lib/pq driver using the CopyFrom support. This doesn't overflow, but it does cause incorrect values to be inserted into the database for very large values of Balance and Value due to loss of precision during the conversion.

In v5 I foolishly switched to strconv.ParseUint, and while this doesn't have precision issues it simply overflows at very large Balance and Value.

We can't format as a string (the obvious choice) because it results in the below error:

ERROR: insufficient data left in message (SQLSTATE 08P01)

In v4 the `CopyFrom` mode uses `strconv.ParseFloat` in order to convert `transaction.Value` and `account.Balance` to a format that can be loaded by the binary lib/pq driver using the `CopyFrom` support. This doesn't overflow, but it does cause incorrect values to be inserted into the database for very large values of `Balance` and `Value` due to loss of precision during the conversion. In v5 I foolishly switched to `strconv.ParseUint`, and while this doesn't have precision issues it simply overflows at very large `Balance` and `Value`. We can't format as a string (the obvious choice) because it results in the below error: `ERROR: insufficient data left in message (SQLSTATE 08P01)`

i-norden commented

2023-04-05 16:24:53 +00:00

This is also telling of a deficiency in our unit tests which is easily addressed by using large account balances in our mock data, will fix this as this missing coverage is entirely on me.

i-norden commented

2023-04-05 16:41:25 +00:00

The direct write mode and sql/csv file write mode is unaffected in either version because the string format is used there without issue.

i-norden commented

2023-04-05 18:40:11 +00:00

Having trouble chasing down any solution so tried some shots in the dark:

Tried implementing a custom Scanner/Valuer interface for a big.Int e.g.

// Value implements the Valuer interface for BigInt
func (b *SQLBigInt) Value() (driver.Value, error) {
	if b != nil {
		return b.String(), nil
	}
	return nil, nil
}

// Scan implements the Scanner interface for BigInt
func (b *SQLBigInt) Scan(value interface{}) error {
	var i sql.NullString
	if err := i.Scan(value); err != nil {
		return err
	}
	if _, ok := b.SetString(i.String, 10); ok {
		return nil
	}
	return fmt.Errorf("Could not scan type %T into BigInt", value)
}

But this doesn't work, get SQLSTATE 57014 error e.g.

ERROR: COPY from stdin failed: cannot convert {{false [1000]}} to Numeric (SQLSTATE 57014)

Tried casting the strings to []uint8 but that throws

ERROR: COPY from stdin failed: cannot convert [49 48 48 48] to Numeric (SQLSTATE 57014)

Tried checking and ensuring no null/newline characters are present in the string (there aren't), so that didn't fix anything either.

I'm not confident there is a solution using the binary CopyFrom mode used by lib/pq.

Having trouble chasing down any solution so tried some shots in the dark: Tried implementing a custom Scanner/Valuer interface for a big.Int e.g. ``` // Value implements the Valuer interface for BigInt func (b *SQLBigInt) Value() (driver.Value, error) { if b != nil { return b.String(), nil } return nil, nil } // Scan implements the Scanner interface for BigInt func (b *SQLBigInt) Scan(value interface{}) error { var i sql.NullString if err := i.Scan(value); err != nil { return err } if _, ok := b.SetString(i.String, 10); ok { return nil } return fmt.Errorf("Could not scan type %T into BigInt", value) } ``` But this doesn't work, get `SQLSTATE 57014` error e.g. `ERROR: COPY from stdin failed: cannot convert {{false [1000]}} to Numeric (SQLSTATE 57014)` Tried casting the strings to []uint8 but that throws `ERROR: COPY from stdin failed: cannot convert [49 48 48 48] to Numeric (SQLSTATE 57014)` Tried checking and ensuring no null/newline characters are present in the string (there aren't), so that didn't fix anything either. I'm not confident there is a solution using the binary CopyFrom mode used by lib/pq.

telackey commented

2023-04-05 20:32:52 +00:00

This change uses a pgx-compatible Numeric datatype which is backed by a shopspring.Decimal.

d2bfae64eb

This change uses a pgx-compatible Numeric datatype which is backed by a shopspring.Decimal. https://github.com/cerc-io/go-ethereum/pull/360/commits/d2bfae64eb2d35523a92d27b755806a868a532c0

❤️ 1

i-norden commented

2023-04-05 23:16:14 +00:00

Thanks for the quick fix on this @telackey !

Sign in to join this conversation.

No Milestone

No project

No Assignees

2 Participants

Notifications

Due Date

The due date is invalid or out of range. Please use the format 'yyyy-mm-dd'.

No due date set.

Dependencies

No dependencies set.

Reference: cerc-io/go-ethereum#359

CopyFrom issue #359

`CopyFrom` issue #359