Uploaded image for project: 'mod-source-record-storage'
  1. mod-source-record-storage
  2. MODSOURCE-123

Tenant init fails if another tenant is running older mod-srs

    XMLWordPrintable

    Details

    • Template:
    • Sprint:
      EPAM BatchLoader Sprint 40
    • Story Points:
      0.5
    • Development Team:
      Folijet

      Description

      This bug is a little difficult to explain...

      It appears that if you have multiple tenants sharing a database running different versions of mod-source-record-storage, the tenant init for mod-source-record-storage v3.1.3 (Fameflower) fails with this error:

      POST request for mod-source-record-storage-3.1.3 /_/tenant failed with 400: [ "          CREATE OR REPLACE FUNCTION get_highest_generation(matchedId uuid, snapshotId uuid) RETURNS integer AS $generation$ DECLARE generation integer; BEGIN SELECT MAX(records.jsonb ->> 'generation') into generation FROM records INNER JOIN snapshots ON records.jsonb ->> 'snapshotId' = snapshots.jsonb ->> 'jobExecutionId' WHERE (records.jsonb ->> 'matchedId')::uuid = matchedId AND snapshots.jsonb ->> 'status' = 'COMMITTED' AND (snapshots.jsonb -> 'metadata' ->> 'updatedDate')::timestamp with time zone < (SELECT snapshots.jsonb ->> 'processingStartedDate' FROM snapshots WHERE (snapshots.jsonb ->> 'jobExecutionId')::uuid = snapshotId)::timestamp with time zone; RETURN generation; END; $generation$ LANGUAGE plpgsql;\n          CREATE OR REPLACE FUNCTION get_record_by_external_id(externalId uuid, idFieldName text) RETURNS jsonb AS $recordDtoByExternalId$ DECLARE recordDtoByExternalId jsonb; BEGIN SELECT json_build_object('id', recordsByExternalId.jsonb->>'id', 'snapshotId', recordsByExternalId.jsonb->>'snapshotId', 'matchedProfileId', recordsByExternalId.jsonb->>'matchedProfileId', 'matchedId', recordsByExternalId.jsonb->>'matchedId', 'generation', recordsByExternalId.jsonb->>'generation', 'recordType', recordsByExternalId.jsonb->>'recordType', 'deleted', recordsByExternalId.jsonb->>'deleted', 'externalIdsHolder', recordsByExternalId.jsonb->'externalIdsHolder', 'additionalInfo', recordsByExternalId.jsonb->'additionalInfo', 'metadata', recordsByExternalId.jsonb->'metadata', 'rawRecord', raw_records.jsonb, 'parsedRecord', COALESCE(marc_records.jsonb), 'errorRecord', error_records.jsonb) INTO recordDtoByExternalId FROM (SELECT rcd.* FROM (SELECT rc.*, max(generation) over (partition by matchedId) max_generation FROM (SELECT r.*, (r.jsonb ->> 'matchedId')::text  matchedId, (r.jsonb ->> 'generation')::text generation FROM records r WHERE (jsonb -> 'externalIdsHolder' ->> idFieldName)::uuid = externalId) rc) rcd WHERE generation = max_generation) recordsByExternalId JOIN raw_records ON recordsByExternalId.jsonb ->> 'rawRecordId' = raw_records.jsonb ->> 'id' LEFT JOIN marc_records ON recordsByExternalId.jsonb ->> 'parsedRecordId' = marc_records.jsonb ->> 'id' LEFT JOIN error_records ON recordsByExternalId.jsonb ->> 'errorRecordId' = error_records.jsonb ->> 'id'; RETURN recordDtoByExternalId; END; $recordDtoByExternalId$ LANGUAGE plpgsql;\n           CREATE INDEX IF NOT EXISTS records_snapshot_id_idx_btree ON records USING BTREE ((jsonb ->> 'snapshotId')); DO $$ BEGIN IF EXISTS(SELECT * FROM information_schema.columns WHERE table_name='snapshots' AND column_name='_id') THEN ALTER TABLE sim_mod_source_record_storage.snapshots RENAME COLUMN _id TO id; END IF; END $$; DO $$ BEGIN IF EXISTS(SELECT * FROM information_schema.columns WHERE table_name='error_records' AND column_name='_id') THEN ALTER TABLE sim_mod_source_record_storage.error_records RENAME COLUMN _id TO id; END IF; END $$; DO $$ BEGIN IF EXISTS(SELECT * FROM information_schema.columns WHERE table_name='marc_records' AND column_name='_id') THEN ALTER TABLE sim_mod_source_record_storage.marc_records RENAME COLUMN _id TO id; END IF; END $$; DO $$ BEGIN IF EXISTS(SELECT * FROM information_schema.columns WHERE table_name='raw_records' AND column_name='_id') THEN ALTER TABLE sim_mod_source_record_storage.raw_records RENAME COLUMN _id TO id; END IF; END $$; DO $$ BEGIN IF EXISTS(SELECT * FROM information_schema.columns WHERE table_name='records' AND column_name='_id') THEN ALTER TABLE sim_mod_source_record_storage.records RENAME COLUMN _id TO id; END IF; END $$; UPDATE sim_mod_source_record_storage.records SET\tjsonb = jsonb_set(records.jsonb, '{externalIdsHolder}', jsonb_build_object('instanceId', marc_record_instance_id.instance_id)) FROM ( \tSELECT marc_fields._id, subfields -> 'i' as instance_id \tFROM ( \t\tSELECT (marc_records.jsonb ->> 'id')::uuid as _id, marc_records.jsonb, fields \t\tFROM sim_mod_source_record_storage.marc_records \t\tCROSS JOIN LATERAL jsonb_array_elements(jsonb -> 'content' -> 'fields') AS fields \t\tWHERE fields ? '999' \t) AS marc_fields \tCROSS JOIN LATERAL jsonb_array_elements(fields -> '999' -> 'subfields') AS subfields \tWHERE subfields ? 'i' ) AS marc_record_instance_id WHERE (records.jsonb ->> 'parsedRecordId')::uuid = marc_record_instance_id._id; DROP INDEX IF EXISTS sim_mod_source_record_storage.marc_records_id_idx; DROP INDEX IF EXISTS sim_mod_source_record_storage.raw_records_id_idx; DROP INDEX IF EXISTS sim_mod_source_record_storage.error_records_id_idx; DROP INDEX IF EXISTS sim_mod_source_record_storage.records_id_idx; GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA sim_mod_source_record_storage TO sim_mod_source_record_storage;" ]
      

      I think the relevant bit is this:

      IF EXISTS(SELECT * FROM information_schema.columns WHERE table_name='error_records' AND column_name='_id') THEN ALTER TABLE [...]
      

      The entry in information_schema of course exists for the other tenant running the older version, but the ALTER TABLE command fails because the table for the new tenant either hasn't been created yet or, if it has, of course has the new schema without the _id column.

        TestRail: Results

          Attachments

            Issue Links

              Activity

                People

                Assignee:
                ruslan_lavrov Ruslan Lavrov
                Reporter:
                wayne Wayne Schneider
                Votes:
                0 Vote for this issue
                Watchers:
                5 Start watching this issue

                  Dates

                  Created:
                  Updated:
                  Resolved:

                    TestRail: Runs

                      TestRail: Cases