Idea #11908: Migrate Collections.properties to JSONB - Arvados

Actions

Copy link

Idea #11908

closed

Migrate Collections.properties to JSONB

Added by Tom Morris over 7 years ago. Updated about 7 years ago.

Status:

Resolved

Priority:

Normal

Assigned To:

Tom Clegg

Category:

API

Target version:

2017-12-20 Sprint

Start date:

06/27/2017

Due date:

Story points:

0.5

Description

Handle (deserialize) both YAML and JSON and convert column type to JSONB

Subtasks 1 (0 open — 1 closed)

Related issues 3 (0 open — 3 closed)

Actions

Copy link

Updated by Tom Morris over 7 years ago

Description updated (diff)
Target version set to Arvados Future Sprints
Story points set to 2.0

Actions

Copy link

Updated by Tom Morris over 7 years ago

Target version changed from Arvados Future Sprints to 2017-07-19 sprint

Actions

Copy link

Updated by Tom Morris over 7 years ago

Assigned To set to Tom Clegg

Actions

Copy link

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

Actions

Copy link

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?

Actions

Copy link

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

Actions

Copy link

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

Actions

Copy link

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.

Actions

Copy link

Updated by Tom Morris about 7 years ago

Target version changed from Arvados Future Sprints to 2017-11-08 Sprint

Actions

Copy link

#10

Updated by Tom Clegg about 7 years ago

Target version changed from 2017-11-08 Sprint to 2017-11-22 Sprint

Actions

Copy link

#11

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.

Actions

Copy link

#12