I have an issue with the Postgres datatype ‘serial’ that does not seem to be working with Circle CI.
The stack is Ruby (not Rails) using ActiveRecord with PostgreSQL.
One of my tables (called beacons) contains a field with a serial, which is supposed to be an auto-incrementing integer value as specified here: https://www.postgresql.org/docs/9.5/static/datatype-numeric.html
It is defined in the migration like this
[...] t.column(:example_field_name, 'serial') [...] add_index :beacons, :example_field_name, :unique => true [...]
which turns into
[...] t.integer "example_field_name", default: "nextval('beacons_example_field_name_seq'::regclass)", null: false [...] add_index "beacons", ["example_field_name"], name: "index_beacons_example_field_name", unique: true, using: :btree [...]
in the schema.rb.
This works fine in my local tests, and on creation of a record the value auto-increments as it’s supposed to. On Circle CI, however, this doesn’t seem to work and therefore, when I create more than one beacon in one spec, I get test failures like this:
Failure/Error: create(:beacon) ActiveRecord::RecordNotUnique: PG::UniqueViolation: ERROR: duplicate key value violates unique constraint "index_beacons_on_example_field_name" DETAIL: Key (example_field_name)=(0) already exists. : INSERT INTO "beacons" ("serial_number", "uuid", "public_key", "created_at", "updated_at") VALUES ($1, $2, $3, $4, $5) RETURNING "id"
I SSH’d into the Circle CI build and it turns out that, as opposed to creating a record locally, right when building the object and before it is saved, the value ‘0’ is assigned to the field in question, instead of a correctly auto-incremented value that only is created on actual creation of the record in the database.
The Postgres version I’m running locally is 9.5.1. Initially I thought that the problem might be Circle CI running an older version that doesn’t support serials, but I went backwards a couple of versions in the Postgres docs and it seems that it should be working just fine with the version Circle is running.
Any ideas on how to solve this?
I’d also much appreciate a word by the Circle CI team to let me know whether the issue is actually on their side or if there’s anything I can do on my side right now.