Story #11908
closed
Migrate Collections.properties to JSONB
Added by Tom Morris over 7 years ago. Updated about 7 years ago.
100%
Description
Handle (deserialize) both YAML and JSON and convert column type to JSONB
Updated by Tom Morris over 7 years ago
- Description updated (diff)
- Target version set to Arvados Future Sprints
- Story points set to 2.0
Updated by Tom Morris over 7 years ago
- Target version changed from Arvados Future Sprints to 2017-07-19 sprint
Updated by Tom Clegg over 7 years ago
- Status changed from New to In Progress
Changing the column type in-place is trivial ("alter table foo alter column bar type jsonb using bar::jsonb"). Unfortunately, the fulltext index includes the old column so it gets dropped in the process, and has to be rebuilt.
The good news is that I ran into the fulltext index migration bug (existing index not detected, migration fails) and fixed it.
ActiveRecord::StatementInvalid: PG::DuplicateTable: ERROR: relation "collections_full_text_search_idx" already exists
11908-properties-column-json @ bb821d03eb10ddcc7822fac51a565d1a11082ebc
Updated by Radhika Chippada over 7 years ago
- Do we not need to drop the full text index on collection before changing column type back in down migration?
- Do we not want to update workflows -> definition column?
Updated by Tom Clegg over 7 years ago
Radhika Chippada wrote:
- Do we not need to drop the full text index on collection before changing column type back in down migration?
On my system the down-migration worked without dropping/recreating the index. I added a comment to the up-migration with the postgresql error it avoids.
- Do we not want to update workflows -> definition column?
Not here/now (this is just collections.properties, to support tags) and perhaps not ever (IIRC we decided to store literal YAML there, instead of using a serialized field, in order to preserve key order, formatting, comments).
11908-properties-column-json @ ebc65675cecdf25ca11a86f789bfb23b600875b8
Updated by Radhika Chippada over 7 years ago
On my system the down-migration worked without dropping/recreating the index. I added a comment to the up-migration with the postgresql error it avoids.
Down migration worked for me as well. I also switched to master branch after down migration and added tags to a collection and everything worked fine.
LGTM
Updated by Tom Clegg over 7 years ago
- Category set to API
- Target version changed from 2017-07-19 sprint to Arvados Future Sprints
- Story points changed from 2.0 to 0.5
Merge is blocked on PostgreSQL 9.4 dependency.
Updated by Tom Morris about 7 years ago
- Target version changed from Arvados Future Sprints to 2017-11-08 Sprint
Updated by Tom Clegg about 7 years ago
- Target version changed from 2017-11-08 Sprint to 2017-11-22 Sprint
Updated by Tom Clegg about 7 years ago
As a reminder, in July #11807 did (what we expect to be) the slowest part of the yaml-to-json migration, i.e., the jobs table, and that took 3.5 minutes on qr1hi.
This branch does a smaller part of the yaml-to-json migration. It also changes the column type of a single column (collections.properties) that is typically null in most rows, and then it has to regenerate the fulltext index. We don't having timing estimates for that yet.
Updated by Tom Clegg about 7 years ago
- Target version changed from 2017-11-22 Sprint to 2017-12-06 Sprint
Updated by Tom Clegg about 7 years ago
- Target version changed from 2017-12-06 Sprint to 2017-12-20 Sprint
Updated by Peter Amstutz about 7 years ago
- Related to deleted (Story #4019: [API] Support query of "properties" field on objects)
Updated by Peter Amstutz about 7 years ago
- Blocks Story #4019: [API] Support query of "properties" field on objects added
Updated by Tom Clegg about 7 years ago
Rebased.
11908-properties-column-json @ 2bd1ff1d4885786bf3ed838bcefb7198fcab6bc7
Updated by Tom Clegg about 7 years ago
- Status changed from In Progress to Resolved