...

Fragment table includes dataspace_id and anchor_id columns. Anchor table also has dataspace_id column. Since an Anchor belongs to only one Dataspace, and a Fragment belongs to only one Anchor, the dataspace_id column in Fragment is redundant (since the fragment's dataspace_id is a duplicate of the anchor's dataspace_id). It is at best redundant to have the same data duplicated in multiple database tables, and at worst could lead to inconsistencies.This task is to remove dataspace_id column from the Fragment table.

In addition to wasting space on duplicate data, the Fragment table also includes an index for dataspace_id, a foreign key constraint, and dataspace_id is included in the uniqueness constraint for Fragment. Removing all these may improve write performance (albeit a small improvement). There are no negative performance impacts expected, as the only functionality relying on dataspace_id is query-across-anchors (relevant SQL queries can be changed to use an inexpensive table JOIN on Anchor table).

This task is to remove dataspace_id column from the Fragment table.

Implementation Proposal

It is proposed to remove the dataspace_id column from the Fragment table in the Liquibase changelog. This also involves removing associated indexes and constraints.

Proposed Liquibase changes

...

...

It is proposed to:

1. drop the index for Fragment.dataspace_id
2. change the uniqueness constraint for Fragment, to exclude dataspace_id
3. drop the foreign key constraint for Fragment.dataspace_id
4. drop the Fragment.dataspace_id column
5. make Fragment.anchor_id not null

It is proposed that each of the above be implemented as a separate change in the Liquibase changelog (with a corresponding rollback for each). Notably, for dropping the column (step 4), the rollback will need to repopulate the column's data. SQL will need to be written for this.

databaseChangeLog:

  - changeSet:
        tableNameid: fragment19-1
      rollbackauthor: cps
       - createIndexchanges:
        -    columnsdropIndex:
            indexName:  - column:FKI_FRAGMENT_DATASPACE_ID_FK
            tableName: fragment
      namerollback:
dataspace_id        -     indexName: FKI_FRAGMENT_DATASPACE_ID_FKcreateIndex:
            tableNamecolumns:
 fragment    - changeSet:       id: 19-2 column:
     author: cps       changes:     name:  dataspace_id
 - dropUniqueConstraint:             constraintNameindexName: fragmentFKI_dataspaceFRAGMENT_idDATASPACE_anchor_id_xpath_keyID_FK
            tableName: fragment

  - changeSet:
   - addUniqueConstraint:  id: 19-2
      author: cps
 columnNames: anchor_id, xpath   changes:
        - constraintName: fragment_anchor_id_xpath_keydropUniqueConstraint:
            tableNameconstraintName: fragment
      rollback:
        - dropUniqueConstraint:
            constraintName: fragment_dataspace_id_anchor_id_xpath_key
            tableName: fragment
        - addUniqueConstraint:
            columnNames: dataspace_id, anchor_id, xpath
            constraintName: fragment_dataspace_id_anchor_id_xpath_key
            tableName: fragment
     - changeSetrollback:
       id: 19-3 dropUniqueConstraint:
     author: cps       constraintName: fragment_anchor_id_xpath_key
            changestableName: fragment
        - dropForeignKeyConstraintaddUniqueConstraint:
            baseTableName: fragmentcolumnNames: dataspace_id, anchor_id, xpath
            constraintName: fragment_dataspace_id_anchor_id_xpath_fkeykey
        rollback:    tableName: fragment

  - addForeignKeyConstraintchangeSet:
      id: 19-3
      author: cps
      baseColumnNames: dataspace_idchanges:
        - dropForeignKeyConstraint:
            baseTableName: fragment
            constraintName: fragment_dataspace_id_fkey
            deferrablerollback:
false        -     initiallyDeferred: falseaddForeignKeyConstraint:
            onDeletebaseColumnNames: NO ACTIONdataspace_id
            onUpdatebaseTableName: NOfragment
ACTION             referencedColumnNamesconstraintName: fragment_dataspace_id_fkey
            referencedTableNamedeferrable: dataspacefalse
            validateinitiallyDeferred: false
true    - changeSet:       idonDelete: 19-4NO ACTION
     author: cps       changesonUpdate: NO ACTION
      - dropColumn:     referencedColumnNames: id
      columnName: dataspace_id     referencedTableName:   dataspace
    tableName: fragment       rollbackvalidate:     true

  - addColumnchangeSet:
      id: 19-4
      tableNameauthor: cps
   fragment   changes:
        - columnsdropColumn:
            columnName: dataspace_id
- column:           tableName: fragment
      namerollback:
   dataspace_id     - addColumn:
            typetableName: fragment
  INTEGER         - sqlFilecolumns:
              - pathcolumn: changelog/db/changes/19-repopulate-dataspace-id-for-rollback.sql
                 - addNotNullConstraintname: dataspace_id
            tableName: fragment     type: INTEGER
      columnName: dataspace_id - sqlFile:
          columnDataType: INTEGER 

Results

Table description before update, after update, and after rollback

Fragment table before update, after update, and after rollback

...

path: changelog/db/changes/19-repopulate-dataspace-id-for-rollback.sql
        - addNotNullConstraint:
            tableName: fragment
            columnName: dataspace_id
            columnDataType: INTEGER

  - changeSet:
      id: 19-5
      author: cps
      changes:
        - addNotNullConstraint:
            tableName: fragment
            columnName: anchor_id
            columnDataType: BIGINT
      rollback:
        - dropNotNullConstraint:
            tableName: fragment
            columnName: anchor_id
            columnDataType: BIGINT
 

As part of the rollback procedure, the removed column must be re-populated with data. The following SQL is used to do this:

UPDATE
    fragment AS f
SET
    dataspace_id = a.dataspace_id
FROM
    anchor AS a
WHERE
    f.anchor_id = a.id;

Test Results

The following commands were used to test updating and rolling back liquibase changes, from within the cps-ri directory:

• To update:
  mvn compile org.liquibase:liquibase-maven-plugin:4.16.0:update -Dliquibase.url=jdbc:postgresql://localhost:5432/cpsdb -Dliquibase.username=cps -Dliquibase.password=cps -Dliquibase.changeLogFile=changelog/changelog-master.yaml
• To rollback:
  mvn compile org.liquibase:liquibase-maven-plugin:4.16.0:rollback -Dliquibase.url=jdbc:postgresql://localhost:5432/cpsdb -Dliquibase.username=cps -Dliquibase.password=cps -Dliquibase.changeLogFile=changelog/changelog-master.yaml -Dliquibase.rollbackCount=4

The reason for liquibase.rollbackCount=4 in the above, is that 4 changesets were applied, as described in previous section.

Fragment table before update, after update, and after rollback

As can be seen, the same data is restored after update and rollback (though the ordering of the columns has changed - testing has shown this to have no impact on functionality).

Table description before update, after update, and after rollback

The following data was obtained by connecting the Postgres database, and issuing a "\d public.fragment" command, to describe the table.

Before update

                              Table "public.fragment"
     Column     |  Type   | Collation | Nullable |             Default              
 id             | bigint  |           | not null | generated by default as identity
 xpath          | text    |           | not null | 
 attributes     | jsonb   |           |          | 
 anchor_id      | bigint  |           |          | 
 parent_id      | bigint  |           |          | 
 dataspace_id   | integer |           | not null | 
 schema_node_id | integer |           |          | 
Indexes:
    "fragment_pkey" PRIMARY KEY, btree (id)
    "fki_fragment_anchor_id_fk" btree (anchor_id)
    "fki_fragment_dataspace_id_fk" btree (dataspace_id)
    "fki_fragment_parent_id_fk" btree (parent_id)
    "fki_schema_node_id_to_id" btree (schema_node_id)
    "fragment_dataspace_id_anchor_id_xpath_key" UNIQUE CONSTRAINT, btree (dataspace_id, anchor_id, xpath)
Foreign-key constraints:
    "fragment_anchor_id_fkey" FOREIGN KEY (anchor_id) REFERENCES anchor(id)
    "fragment_dataspace_id_fkey" FOREIGN KEY (dataspace_id) REFERENCES dataspace(id)
    "fragment_parent_id_fkey" FOREIGN KEY (parent_id) REFERENCES fragment(id) ON DELETE CASCADE
    "fragment_schema_node_id_fkey" FOREIGN KEY (schema_node_id) REFERENCES schema_node(id)
Referenced by:
    TABLE "fragment" CONSTRAINT "fragment_parent_id_fkey" FOREIGN KEY (parent_id) REFERENCES fragment(id) ON DELETE CASCADE
    TABLE "relation" CONSTRAINT "relation_from_fragment_id_fkey" FOREIGN KEY (from_fragment_id) REFERENCES fragment(id)
    TABLE "relation" CONSTRAINT "relation_to_fragment_id_fkey" FOREIGN KEY (to_fragment_id) REFERENCES fragment(id)

After update

                              Table "public.fragment"
     Column     |  Type   | Collation | Nullable |             Default              
----------------+---------+-----------+----------+----------------------------------
 id             | bigint  |           | not null | generated by default as identity
 xpath          | text    |           | not null | 
 attributes     | jsonb   |           |          | 
 anchor_id      | bigint  |           | not null | 
 parent_id      | bigint  |           |          | 
 schema_node_id | integer |           |          | 
Indexes:
    "fragment_pkey" PRIMARY KEY, btree (id)
    "fki_fragment_anchor_id_fk" btree (anchor_id)
    "fki_fragment_parent_id_fk" btree (parent_id)
    "fki_schema_node_id_to_id" btree (schema_node_id)
    "fragment_anchor_id_xpath_key" UNIQUE CONSTRAINT, btree (anchor_id, xpath)
Foreign-key constraints:
    "fragment_anchor_id_fkey" FOREIGN KEY (anchor_id) REFERENCES anchor(id)
    "fragment_parent_id_fkey" FOREIGN KEY (parent_id) REFERENCES fragment(id) ON DELETE CASCADE
    "fragment_schema_node_id_fkey" FOREIGN KEY (schema_node_id) REFERENCES schema_node(id)
Referenced by:
    TABLE "fragment" CONSTRAINT "fragment_parent_id_fkey" FOREIGN KEY (parent_id) REFERENCES fragment(id) ON DELETE CASCADE
    TABLE "relation" CONSTRAINT "relation_from_fragment_id_fkey" FOREIGN KEY (from_fragment_id) REFERENCES fragment(id)
    TABLE "relation" CONSTRAINT "relation_to_fragment_id_fkey" FOREIGN KEY (to_fragment_id) REFERENCES fragment(id)

Observe that:

• dataspace_id column is no longer in the table
• anchor_id is not null
• fki_fragment_dataspace_id_fk index was removed
• fragment_dataspace_id_fkey foreign key constraint was removed
• fragment_dataspace_id_anchor_id_xpath_key UNIQUE CONSTRAINT was replaced with fragment_anchor_id_xpath_key.

After rollback

                              Table "public.fragment"
     Column     |  Type   | Collation | Nullable |             Default              
----------------+---------+-----------+----------+----------------------------------
 id             | bigint  |           | not null | generated by default as identity
 xpath          | text    |           | not null | 
 attributes     | jsonb   |           |          | 
 anchor_id      | bigint  |           |          | 
 parent_id      | bigint  |           |          | 
 schema_node_id | integer |           |          | 
 dataspace_id   | integer |           | not null | 
Indexes:
    "fragment_pkey" PRIMARY KEY, btree (id)
    "fki_fragment_anchor_id_fk" btree (anchor_id)
    "fki_fragment_dataspace_id_fk" btree (dataspace_id)
    "fki_fragment_parent_id_fk" btree (parent_id)
    "fki_schema_node_id_to_id" btree (schema_node_id)
    "fragment_dataspace_id_anchor_id_xpath_key" UNIQUE CONSTRAINT, btree (dataspace_id, anchor_id, xpath)
Foreign-key constraints:
    "fragment_anchor_id_fkey" FOREIGN KEY (anchor_id) REFERENCES anchor(id)
    "fragment_dataspace_id_fkey" FOREIGN KEY (dataspace_id) REFERENCES dataspace(id)
    "fragment_parent_id_fkey" FOREIGN KEY (parent_id) REFERENCES fragment(id) ON DELETE CASCADE
    "fragment_schema_node_id_fkey" FOREIGN KEY (schema_node_id) REFERENCES schema_node(id)
Referenced by:
    TABLE "fragment" CONSTRAINT "fragment_parent_id_fkey" FOREIGN KEY (parent_id) REFERENCES fragment(id) ON DELETE CASCADE
    TABLE "relation" CONSTRAINT "relation_from_fragment_id_fkey" FOREIGN KEY (from_fragment_id) REFERENCES fragment(id)
    TABLE "relation" CONSTRAINT "relation_to_fragment_id_fkey" FOREIGN KEY (to_fragment_id) REFERENCES fragment(id)

Observe that:

• dataspace_id column is added to the table again
• fki_fragment_dataspace_id_fk index is added again
• fragment_dataspace_id_fkey foreign key constraint is added again
• fragment_dataspace_id_anchor_id_xpath_key UNIQUE CONSTRAINT is added again