How do I create a new composite after adding indexes to a model?

Devtools ComposeDB
1

I created the composite with one of the models as following:

type BountyCategory
  @createModel(
    accountRelation: LIST
    description: "Join table for Bounty and Category"
  )
{
  bountyId: StreamID! @documentReference(model: "Bounty")
  categoryId: StreamID! @documentReference(model: "Category")
  bounty: Profile! @relationDocument(property: "bountyId")
  category: Category! @relationDocument(property: "categoryId")
}

I tried to create indexes and re-create the composite

type BountyCategory
  @createModel(
    accountRelation: LIST
    description: "Join table for Bounty and Category"
  )
  @createIndex(fields: [{ path: "bountyId" }])
  @createIndex(fields: [{ path: "categoryId" }]) {
  bountyId: StreamID! @documentReference(model: "Bounty")
  categoryId: StreamID! @documentReference(model: "Category")
  bounty: Profile! @relationDocument(property: "bountyId")
  category: Category! @relationDocument(property: "categoryId")
}

The error below is thrown

{"error":"Schema verification failed for index: kjzl6hvfrbw6c7tyadfa1s7sbh6ac4671b5mz0le170e8djpvmc46oo7dkt2evn. Please make sure latest migrations have been applied.\n          Missing Indices=[\"idx_oo7dkt2evn_bount\",\"idx_oo7dkt2evn_categ\"]\n          Actual=[\"idx_idx_oo7dkt2evn_pkey\",\"constr_idx_oo7dkt2evn_unique\",\"idx_oo7dkt2evn_stream_id\",\"idx_oo7dkt2evn_last_anchored_at\",\"idx_oo7dkt2evn_first_anchored_at\",\"idx_oo7dkt2evn_created_at\",\"idx_oo7dkt2evn_updated_at\",\"idx_oo7dkt2evn_last_anchored_at_created_at\",\"idx_idx_oo7dkt2evn_custom_bountyId\",\"idx_idx_oo7dkt2evn_custom_categoryId\"]"}
    at RemoteAdminApi.fetchJson [as _fetchJson]
2

You can only add custom indices via composedb tooling when creating a new Model for the first time. Once the Model has already been deployed to your node, the tooling won’t allow you to build new indices against that Model. This is because if the Model has existing data, building an index against existing data can take a long time and could cause application downtime while the index builds.

You should, however, be able to build the index manually against the Postgres database directly using the standard mechanisms for building a JSONB index against a JSONB column. I don’t have an example of what that would look like handy, maybe @mzk or @paul have an example they can point you at.

1 Like
3

Thanks for providing explanation to me. Is it possible to create the updated model as a new model instead?

@mzk @paul Appreciate if you have any example for the suggested solution of building JSONB index against a JSONB column!

4

Hi, I guess it depends on the tools you’re using to interact with your DB, but here is the logic we use to create the index: js-ceramic/packages/indexing/src/migrations/1-create-model-table.ts at d335257846d6d53fbac554cd5638e60a65027276 · ceramicnetwork/js-ceramic · GitHub

1 Like