:id as a creatable_field and creating a resource with specific id breaks subsequent requests (Postgres)

[hidde-jan]

By default, the :id field is assumed to be a integer and is also a creatable field. Rails will happily pass on the :id supplied by the user to the database on creation, however, when using postgres subsequent creation attempts might fail.

Suppose we have two Posts in a table with ids 1 and 2. The next id rails postgres will try to create if there is no id supplied is given by SELECT nextval('posts_id_seq'), which is currently 3. If I send a POST request:

{ "data": { "type": "posts", "id": "3" } }

then I'll get a 201 response:

{ "data": { "type": "posts", "id": "3", "links": {...} } }

If I then POST a request without an id

{ "data": { "type": "posts" } }

then rails postgres will try to use the 3 supplied by nextval('posts_id_seq') and a uniqueness constraint is violated. This happens for every subsequent create request that doesn't specify a id.

There are two ways to fix this bug:

1. only allow :id to be a creatable field if it is an UUID, or
2. call Post.connection.reset_pk_sequence! Post.table_name after a record with an explicit id is saved

I'm not sure if other databases suffer from the same problem.

[hidde-jan]

Actually, the second method only fixes the sync issue for the current connection. If you have multiple processes that connect to the same database then they would all need to reset their sequence for that table. This is pretty bad.

[lgebhardt]

I'm not sure I'd consider it a rails bug. Seems like we need to protect against that happening, and the best way to stay consistent with rails is probably to remove :id from the creatable fields list by default. Agreed, it's pretty bad.

[lgebhardt]

I'm not able to reproduce this in the master or 0.8 branches, but I'm using the test framework with SQLite. I get the appropriate Param Not Allowed error. Did you by chance add id as a creatable field? I think it used to be by default.

[hidde-jan]

Did you by chance add id as a creatable field? I think it used to be by default.

I think it still is a creatable field, just not an updatable one:

https://github.com/cerebris/jsonapi-resources/blob/master/lib/jsonapi/resource.rb#L594

SQLite (and MySQL) might very well not give the error since their primary key logic is different.

[lgebhardt]

@hidde-jan, Sorry about that. The quick test I did used a resource the removed :id from the creatable list. Good news is that confirms it works (not that I had much doubt). I'll get a fix out ASAP.

[lgebhardt]

@hidde-jan Thanks for reporting this. I'm going to get this into the beta and 0.8 branches soon, though it might be a simpler fix such as just removing the id from creatable fields.

[hidde-jan]

No problem! Yeah, I've fixed it for myself by removing :id in all my resources.