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";
Shadow Partitioning
Removing from m_object hierarchy
Shadows not being able to be found breaks lots of tasks dealing with objects in general (eg. trigger task), so it seems repository needs if searching for ObjectType emulate joined search over m_object and m_shadow
|
Migration
Upgrade Script postgres-upgrade.sql
-
Remove
m_shadow
fromm_object
inheritance -
Rename
m_shadow
tom_shadow_default
to be used as default partition -
Create
m_shadow
table with partitioning enabled-
Create
m_shadow_partition_def
to store definitions for partitions
-
-
Attach
m_shadow_default
as default partition tom_shadow
SQL Scripts
m_shadow
table to become default partitionm_shadow
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;
Creating partition for resource
Before attaching new partition we need to migrate shadows from original partition (if existed) to new partition
-
Start transaction
-
Create new table for partition
-
using
CREATE TABLE m_shadow_… (like m_shadow_default INCLUDING ALL )
-
no need to migrate partitioning scripts if new columns are added in later releases
-
-
Drop objectType generated constraint
-
creates problem during copying existing shadows in database, generated columns must not be used in inserts, if we have generated columns then we can not use
insert into … select * from
, but we will need to enumerate all columns.
-
-
-
Copy records from original partition (usually
m_shadow_default
) to new partition -
Deleting shadows in original partition
-
Disable triggers on original partition - we do not want to run delete triggers, since we are just moving data between partitions
-
Delete migrated records in old partition
-
Reenable triggers on original partition
-
-
Re-enable constraints on new partition
-
Delete
objectType
column (alter column does not support conversion from normal column to generated) -
Add
objectType
column using original partition
-
-
Attaching partition to
m_shadow
(or resource partition if it is class partition) -
Commit transaction
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;