Circle CI doesn't seem to handle Postgres serial type right

Build Environment Databases
1

Hi everyone,

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.

Thanks!

2

Do you know which version of PostgreSQL you’re running on CircleCI? Our 12.04 image uses 9.4 by default. Our 14.04 image uses 9.5.3.

You can change the build image via the menu in Project settings > Build environment.

Let us know if using 14.04 fixes this issue or not.

3

Hi Tom,

Thanks for your quick reply. I changed the build image to 14.04 and pushed a commit with a test checking the issue to Github, so it will correctly trigger with the new build. Unfortunately, it’s still failing with the same error (I sent you the relevant link in the support request).

Any further ideas on what to do about this?

Thanks for your help,
Laura