Postgres autoIncrementID needs to be cast as an integer?

[danfinlay]

Was setting up autoIncrementing IDs with Postgres, wiped my DB, and re-initialized, but then I got this error:

error: operator does not exist: bigint = character varying
    at Connection.parseE (/Development/Project/node_modules/pg/lib/connection.js:561:11)
    at Connection.parseMessage (/Development/Project/node_modules/pg/lib/connection.js:390:17)

It does seem like there's a string being passed where an integer is required, not sure if this is an adapter issue or model!

[danfinlay]

Btw this was using model 0.5.14, so this feature should be working.

[mde]

Was still quote-delimiting ids in queries. This is fixed in master, a6b2ed7af96e3b29e0cb337f3f5951ca2245e56e. Can you please verify and close?

[danfinlay]

In my PG database, the ID is now listed as bigint with a modifier of not null default nextval('users_id_seq'::regclass), which all looks right, but trying to run any database commands through model throws the same error as before. :(

[danfinlay]

For thoroughness:

I dropped the old database, created it, ran

geddy jake db:init
geddy jake db:migrate
geddy jake seed

where "seed" is a custom task that populates the database, and on that task, or on a normal geddy run, on the first database contact, the original error is thrown.

My config/environment.js file looks like this:

var config = {
  generatedByVersion: '0.12.4',
  model: {
    autoIncrementId:true
  }
};
module.exports = config;

[danfinlay]

I set geddy.model.log = console.log to look at the sql queries, and I found that it isn't actually "the first database interaction" that's crashing it. In fact, a ton of simple inserts and queries work just fine! The crash comes from a much more complicated query, for example, this one:

SELECT users."id" AS "users#id", users."created_at" AT TIME ZONE 'UTC' AS "users#created_at", users."updated_at" AT TIME ZONE 'UTC' AS "users#updated_at", users."dsid" AS "users#dsid", users."email" AS "users#email", users."first_name" AS "users#first_name", users."last_name" AS "users#last_name", users."team_name" AS "users#team_name", users."is_super_admin" AS "users#is_super_admin", admin_processes."id" AS "admin_processes#id", admin_processes."created_at" AT TIME ZONE 'UTC' AS "admin_processes#created_at", admin_processes."updated_at" AT TIME ZONE 'UTC' AS "admin_processes#updated_at", admin_processes."name" AS "admin_processes#name", admin_processes."description" AS "admin_processes#description", admin_processes."default_ticket_state_id" AS "admin_processes#default_ticket_state_id" FROM users LEFT OUTER JOIN process_admins process_admins_join ON (users."id" = process_admins_join."admin_user_id") LEFT OUTER JOIN processes admin_processes ON (process_admins_join."admin_process_process_id" = admin_processes."id") WHERE (users."id" = 15);
jake aborted.
error: operator does not exist: bigint = character varying

Anyways, I'm going to sleep, but I had to get up just to check if there were clues in here, I didn't want to leave you too confused in the morning. Give me a little time and I'll dig around even more, hopefully I'll decipher more specifically the nature of that query and why it might now fail.

[danfinlay]

I tracked the issue down.

This problem comes from trying to port an old GUID-based project with a long migrations history over to sequential IDs.

My previous migrations had declared relation IDs as strings, not numbers. This caused queries like the following from the above example: process_admins_join."admin_process_process_id" = admin_processes."id", where admin_process_process_id was already set as a string instead of a number.

By going back and editing those old migrations to create the fields as numbers instead of strings, I've gotten around this problem, and confirmed that your new sequential ID feature is working! Thank you so much!

[mde]

HOORAY!

[danfinlay]

Yeah really!