Getting DB::ColumnTypeMismatchError for Int32 columns in CockroachDB

[akadusei]

I have an app that I'm currently testing on CockroachDB, and encounter this error:

In PG::ResultSet#read the column one returned a Int64 but a Int32 was expected. (DB::ColumnTypeMismatchError)
    from lib/db/src/db/result_set.cr:89:9 in 'read'
    from lib/db/src/db/query_methods.cr:202:9 in 'query_one?:as:args'
    from lib/avram/src/avram/database.cr:88:3 in 'query_one?:as:args:queryable'
    from lib/avram/src/avram/database.cr:88:3 in 'query_one?:args:queryable:as'
    from lib/avram/src/avram/queryable.cr:224:17 in 'any?'
    # ...

The problem appears to be that in CockroachDB (v22.2), int is an alias for int8. In PostgreSQL, int refers to int4. So for an Int32 column in Avram, CockroachDB is creating an int8 column whereas crystal-pg expects to read an int4.

Not tested, but this may be solved by changing the Avram::Migrator::Columns::Int32Column#column_type to int4:

avram/src/avram/migrator/columns/int32_column.cr

Line 11 in ac949e4

"int"

While at it, we may as well change Avram::Migrator::Columns::PrimaryKeys::Int32PrimaryKey#column_type to serial4:

avram/src/avram/migrator/columns/primary_keys/int32_primary_key.cr

Line 9 in ac949e4

"serial"

These changes should make it explicit to both DB engines what the column type is. This should not be a breaking change.

[jwoertink]

Oh nice. I wasn't aware of those types. Any idea how far back of a Postgres version that works on? At least to v12?

[akadusei]

Any idea how far back of a Postgres version that works on? At least to v12?

From the PostgreSQL docs, as far back as v7.2: https://www.postgresql.org/docs/7.2/datatype.html

[jwoertink]

nice! Yeah, in that case, it should be fine, I think.