[FOLIO-2254] Remove outdated indexes on jsonb->>'foreignKeyId' field Created: 11/Sep/19  Updated: 03/Jun/20  Resolved: 04/Dec/19

Status: Closed
Project: FOLIO
Components: None
Affects versions: None
Fix versions: None

Type: Task Priority: P2
Reporter: Hongwei Ji Assignee: Hongwei Ji
Resolution: Done Votes: 0
Labels: platform-backlog
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original estimate: Not Specified

Issue links:
Blocks
is blocked by RMB-468 Foreign key field index Closed
Relates
relates to MODINVOSTO-42 Remove outdated indexes on jsonb->>'f... Closed
relates to MODSOURMAN-196 Remove outdated indexes on jsonb->>'f... Closed
relates to MODINVSTOR-352 Remove outdated indexes on jsonb->>'f... Closed
relates to MODORDSTOR-111 Remove outdated indexes on jsonb->>'f... Closed
Sprint: CP: sprint 78
Story Points: 0
Development Team: Core: Platform

 Description   

Once RMB-468 Closed is implemented, we should consider to clean up existing indexes on jsonb->>'foreignKeyId' field
For example: https://github.com/folio-org/mod-inventory-storage/blob/v17.0.0/src/main/resources/templates/db_scripts/schema.json#L688

      "tableName": "holdings_record",
      "ginIndex": [
        {
          "fieldName": "instanceId",
          "tOps": "ADD",
          "caseSensitive": false,
          "removeAccents": true
        },
        {
          "fieldName": "permanentLocationId",
          "tOps": "ADD",
          "caseSensitive": false,
          "removeAccents": true
        },
        {
          "fieldName": "temporaryLocationId",
          "tOps": "ADD",
          "caseSensitive": false,
          "removeAccents": true
        }
      ]

We have tickets for individual modules, so this becomes an umbrella ticket. We should skip scoring this one and close it once all module tickets are completed.



 Comments   
Comment by Jakub Skoczen [ 19/Sep/19 ]

Hongwei Ji can you please create tickets for individual modules (modinvstor, modcircstor, etc)?

Comment by Hongwei Ji [ 19/Sep/19 ]

Jakub Skoczen I did a quick GitHub search and created four user store
https://folio-org.atlassian.net/browse/MODINVSTOR-352
https://folio-org.atlassian.net/browse/MODINVOSTO-42
https://folio-org.atlassian.net/browse/MODORDSTOR-111
https://folio-org.atlassian.net/browse/MODSOURMAN-196

BTW, mod-users has one place but it does not specify foreign keys in schema.json
https://github.com/folio-org/mod-users/blob/master/src/main/resources/templates/db_scripts/schema.json#L152-L168

Comment by Julian Ladisch [ 20/Sep/19 ]

This should be part of bumping the RMB version that contains RMB-468 Closed .

Comment by Julian Ladisch [ 20/Sep/19 ]

We should extend this issue to also remove indexes on the primary key "id". Postgresql automatically creates an index for the primary key therefore this additional index is not needed.
Example: https://github.com/folio-org/mod-inventory-storage/blob/v17.0.0/src/main/resources/templates/db_scripts/schema.json#L708

Comment by Julian Ladisch [ 02/Dec/19 ]

https://github.com/folio-org/raml-module-builder/blob/master/doc/upgrading.md#version-271 contains this upgrading instructions for 27.1:
"Remove all foreign keys fields and the primary key field id from the all index sections of schema.json. These btree indexes are created automatically."

Comment by Hongwei Ji [ 04/Dec/19 ]

Completed all four children tickets. Close this one as well now.

Generated at Thu Feb 08 23:19:20 UTC 2024 using Jira 1001.0.0-SNAPSHOT#100246-sha1:7a5c50119eb0633d306e14180817ddef5e80c75d.