Shadow Partitioning

ALTER TABLE m_shadow NO INHERIT m_object;
ALTER TABLE m_shadow RENAME TO m_shadow_default;

ALTER TABLE m_shadow_default
ALTER resourcereftargetoid TYPE uuid,
ALTER resourcereftargetoid SET NOT NULL;

DROP TRIGGER "m_shadow_oid_insert_tr" ON "m_shadow_default";
DROP TRIGGER "m_shadow_update_tr" ON "m_shadow_default";
DROP TRIGGER "m_shadow_oid_insert_tr" ON "m_shadow_default";

CREATE TABLE m_shadow (
    oid UUID NOT NULL REFERENCES m_object_oid(oid),
    objectType ObjectType
            GENERATED ALWAYS AS ('SHADOW') STORED
        CONSTRAINT m_shadow_objecttype_check
            CHECK (objectType = 'SHADOW'),
    nameOrig TEXT NOT NULL,
    nameNorm TEXT NOT NULL,
    fullObject BYTEA,
    tenantRefTargetOid UUID,
    tenantRefTargetType ObjectType,
    tenantRefRelationId INTEGER REFERENCES m_uri(id),
    lifecycleState TEXT, -- TODO what is this? how many distinct values?
    cidSeq BIGINT NOT NULL DEFAULT 1, -- sequence for container id, next free cid
    version INTEGER NOT NULL DEFAULT 1,
    -- complex DB columns, add indexes as needed per concrete table, e.g. see m_user
    -- TODO compare with [] in JSONB, check performance, indexing, etc. first
    policySituations INTEGER[], -- soft-references m_uri, only EQ filter
    subtypes TEXT[], -- only EQ filter
    fullTextInfo TEXT,

    ext JSONB,
    creatorRefTargetOid UUID,
    creatorRefTargetType ObjectType,
    creatorRefRelationId INTEGER REFERENCES m_uri(id),
    createChannelId INTEGER REFERENCES m_uri(id),
    createTimestamp TIMESTAMPTZ,
    modifierRefTargetOid UUID,
    modifierRefTargetType ObjectType,
    modifierRefRelationId INTEGER REFERENCES m_uri(id),
    modifyChannelId INTEGER REFERENCES m_uri(id),
    modifyTimestamp TIMESTAMPTZ,

    -- these are purely DB-managed metadata, not mapped to in midPoint
    db_created TIMESTAMPTZ NOT NULL DEFAULT current_timestamp,
    db_modified TIMESTAMPTZ NOT NULL DEFAULT current_timestamp, -- updated in update trigger

    objectClassId INTEGER REFERENCES m_uri(id),
    resourceRefTargetOid UUID,
    resourceRefTargetType ObjectType,
    resourceRefRelationId INTEGER REFERENCES m_uri(id),
    intent TEXT,
    tag TEXT,
    kind ShadowKindType,
    dead BOOLEAN,
    exist BOOLEAN,
    fullSynchronizationTimestamp TIMESTAMPTZ,
    pendingOperationCount INTEGER NOT NULL,
    primaryIdentifierValue TEXT,
    synchronizationSituation SynchronizationSituationType,
    synchronizationTimestamp TIMESTAMPTZ,
    attributes JSONB,
    -- correlation
    correlationStartTimestamp TIMESTAMPTZ,
    correlationEndTimestamp TIMESTAMPTZ,
    correlationCaseOpenTimestamp TIMESTAMPTZ,
    correlationCaseCloseTimestamp TIMESTAMPTZ,
    correlationSituation CorrelationSituationType
) PARTITION BY LIST (resourceRefTargetOid);
CREATE TRIGGER m_shadow_oid_insert_tr BEFORE INSERT ON m_shadow
    FOR EACH ROW EXECUTE FUNCTION insert_object_oid();
CREATE TRIGGER m_shadow_update_tr BEFORE UPDATE ON m_shadow
    FOR EACH ROW EXECUTE FUNCTION before_update_object();
CREATE TRIGGER m_shadow_oid_delete_tr AFTER DELETE ON m_shadow
    FOR EACH ROW EXECUTE FUNCTION delete_object_oid();

ALTER TABLE m_shadow ATTACH PARTITION m_shadow_default DEFAULT;

begin;
CREATE TABLE m_shadow_8558374c_0b41_4cf9_a5cd_4b3912ea6305 (like m_shadow_default INCLUDING ALL );

/* We need to remove generated from object type in order to use built-in insert/ select combo */
ALTER TABLE m_shadow_8558374c_0b41_4cf9_a5cd_4b3912ea6305 ALTER objecttype DROP EXPRESSION;

INSERT into m_shadow_8558374c_0b41_4cf9_a5cd_4b3912ea6305 SELECT * FROM m_shadow_default where resourceRefTargetOid = '8558374c-0b41-4cf9-a5cd-4b3912ea6305';
/* Migrate marks, other subtables attached to m_shadow */

ALTER TABLE m_shadow_8558374c_0b41_4cf9_a5cd_4b3912ea6305
    DROP objecttype;
ALTER TABLE m_shadow_8558374c_0b41_4cf9_a5cd_4b3912ea6305
    ADD COLUMN objecttype ObjectType
        GENERATED ALWAYS AS ('SHADOW') STORED
            CONSTRAINT m_shadow_objecttype_check
                CHECK (objectType = 'SHADOW');


/* We should skip drop triggers for m_oid table */
ALTER TABLE m_shadow_default DISABLE TRIGGER ALL;

DELETE FROM m_shadow_default where resourceRefTargetOid = '8558374c-0b41-4cf9-a5cd-4b3912ea6305';
ALTER TABLE m_shadow_default ENABLE TRIGGER ALL;



ALTER TABLE m_shadow ATTACH PARTITION m_shadow_8558374c_0b41_4cf9_a5cd_4b3912ea6305 FOR VALUES IN ('8558374c-0b41-4cf9-a5cd-4b3912ea6305');
commit;