Skip to content

Commit

Permalink
DPP-406 split append only migration (#9777)
Browse files Browse the repository at this point in the history 
* Rename migrations

* Split the append-only migration

changelog_begin
changelog_end
  • Loading branch information
@rautenrieth-da
rautenrieth-da authored May 21, 2021
1 parent 45bca6e commit 66b8f19
Show file tree
Hide file tree
Showing 10 changed files with 320 additions and 286 deletions.
1 change: 1 addition & 0 deletions ...api/src/main/resources/db/migration/postgres-appendonly/V100_0__Append_only_schema.sha256
View file
Original file line number Diff line number Diff line change
@@ -0,0 +1 @@
99f4517fe3eee4f62a80ba3c80c34d39ccb4930d2c496bde2657ee96a5812e25
283 changes: 0 additions & 283 deletions ...s-appendonly/V100__Append_only_schema.sql → ...appendonly/V100_0__Append_only_schema.sql
View file
Original file line number Diff line number Diff line change
Expand Up @@ -66,26 +66,6 @@ CREATE TABLE participant_events_divulgence (
create_argument_compression SMALLINT
);

-- offset index: used to translate to sequential_id
CREATE INDEX participant_events_divulgence_event_offset ON participant_events_divulgence USING btree (event_offset);

-- sequential_id index for paging
CREATE INDEX participant_events_divulgence_event_sequential_id ON participant_events_divulgence USING btree (event_sequential_id);

-- filtering by template
CREATE INDEX participant_events_divulgence_template_id_idx ON participant_events_divulgence USING btree (template_id);

-- filtering by witnesses (visibility) for some queries used in the implementation of
-- GetActiveContracts (flat), GetTransactions (flat) and GetTransactionTrees.
-- Note that Potsgres has trouble using these indices effectively with our paged access.
-- We might decide to drop them.
CREATE INDEX participant_events_divulgence_tree_event_witnesses_idx ON participant_events_divulgence USING gin (tree_event_witnesses);

-- lookup divulgance events, in order of ingestion
CREATE INDEX participant_events_divulgence_contract_id_idx ON participant_events_divulgence USING btree (contract_id, event_sequential_id);




---------------------------------------------------------------------------------------------------
-- Events table: create
Expand Down Expand Up @@ -135,36 +115,6 @@ CREATE TABLE participant_events_create (
-- text and bytea values are compressed by default, "STORAGE EXTERNAL" disables the compression
ALTER TABLE participant_events_create ALTER COLUMN create_key_hash SET STORAGE EXTERNAL;

-- offset index: used to translate to sequential_id
CREATE INDEX participant_events_create_event_offset ON participant_events_create USING btree (event_offset);

-- sequential_id index for paging
CREATE INDEX participant_events_create_event_sequential_id ON participant_events_create USING btree (event_sequential_id);

-- lookup by event-id
CREATE INDEX participant_events_create_event_id_idx ON participant_events_create USING btree (event_id);

-- lookup by transaction id
CREATE INDEX participant_events_create_transaction_id_idx ON participant_events_create USING btree (transaction_id);

-- filtering by template
CREATE INDEX participant_events_create_template_id_idx ON participant_events_create USING btree (template_id);

-- filtering by witnesses (visibility) for some queries used in the implementation of
-- GetActiveContracts (flat), GetTransactions (flat) and GetTransactionTrees.
-- Note that Potsgres has trouble using these indices effectively with our paged access.
-- We might decide to drop them.
CREATE INDEX participant_events_create_flat_event_witnesses_idx ON participant_events_create USING gin (flat_event_witnesses);
CREATE INDEX participant_events_create_tree_event_witnesses_idx ON participant_events_create USING gin (tree_event_witnesses);

-- lookup by contract id
CREATE INDEX participant_events_create_contract_id_idx ON participant_events_create USING hash (contract_id);

-- lookup by contract_key
CREATE INDEX participant_events_create_create_key_hash_idx ON participant_events_create USING btree (create_key_hash, event_sequential_id);




---------------------------------------------------------------------------------------------------
-- Events table: consuming exercise
Expand Down Expand Up @@ -212,32 +162,6 @@ CREATE TABLE participant_events_consuming_exercise (
exercise_result_compression SMALLINT
);

-- offset index: used to translate to sequential_id
CREATE INDEX participant_events_consuming_exercise_event_offset ON participant_events_consuming_exercise USING btree (event_offset);

-- sequential_id index for paging
CREATE INDEX participant_events_consuming_exercise_event_sequential_id ON participant_events_consuming_exercise USING btree (event_sequential_id);

-- lookup by event-id
CREATE INDEX participant_events_consuming_exercise_event_id_idx ON participant_events_consuming_exercise USING btree (event_id);

-- lookup by transaction id
CREATE INDEX participant_events_consuming_exercise_transaction_id_idx ON participant_events_consuming_exercise USING btree (transaction_id);

-- filtering by template
CREATE INDEX participant_events_consuming_exercise_template_id_idx ON participant_events_consuming_exercise USING btree (template_id);

-- filtering by witnesses (visibility) for some queries used in the implementation of
-- GetActiveContracts (flat), GetTransactions (flat) and GetTransactionTrees.
-- Note that Potsgres has trouble using these indices effectively with our paged access.
-- We might decide to drop them.
CREATE INDEX participant_events_consuming_exercise_flat_event_witnesses_idx ON participant_events_consuming_exercise USING gin (flat_event_witnesses);
CREATE INDEX participant_events_consuming_exercise_tree_event_witnesses_idx ON participant_events_consuming_exercise USING gin (tree_event_witnesses);

-- lookup by contract id
CREATE INDEX participant_events_consuming_exercise_contract_id_idx ON participant_events_consuming_exercise USING hash (contract_id);



---------------------------------------------------------------------------------------------------
-- Events table: non-consuming exercise
Expand Down Expand Up @@ -285,30 +209,6 @@ CREATE TABLE participant_events_non_consuming_exercise (
exercise_result_compression SMALLINT
);

-- offset index: used to translate to sequential_id
CREATE INDEX participant_events_non_consuming_exercise_event_offset ON participant_events_non_consuming_exercise USING btree (event_offset);

-- sequential_id index for paging
CREATE INDEX participant_events_non_consuming_exercise_event_sequential_id ON participant_events_non_consuming_exercise USING btree (event_sequential_id);

-- lookup by event-id
CREATE INDEX participant_events_non_consuming_exercise_event_id_idx ON participant_events_non_consuming_exercise USING btree (event_id);

-- lookup by transaction id
CREATE INDEX participant_events_non_consuming_exercise_transaction_id_idx ON participant_events_non_consuming_exercise USING btree (transaction_id);

-- filtering by template
CREATE INDEX participant_events_non_consuming_exercise_template_id_idx ON participant_events_non_consuming_exercise USING btree (template_id);

-- filtering by witnesses (visibility) for some queries used in the implementation of
-- GetActiveContracts (flat), GetTransactions (flat) and GetTransactionTrees.
-- Note that Potsgres has trouble using these indices effectively with our paged access.
-- We might decide to drop them.
-- NOTE: index name truncated because the full name exceeds the 63 characters length limit
CREATE INDEX participant_events_non_consuming_exercise_flat_event_witnes_idx ON participant_events_non_consuming_exercise USING gin (flat_event_witnesses);
CREATE INDEX participant_events_non_consuming_exercise_tree_event_witnes_idx ON participant_events_non_consuming_exercise USING gin (tree_event_witnesses);



---------------------------------------------------------------------------------------------------
-- Data migration
Expand Down Expand Up @@ -530,186 +430,3 @@ FROM divulged_contracts INNER JOIN participant_contracts USING (contract_id);

-- Drop temporary objects
DROP SEQUENCE temp_divulgence_sequential_id;


---------------------------------------------------------------------------------------------------
-- Drop old tables, at this point all data has been copied to the new tables
---------------------------------------------------------------------------------------------------

DROP TABLE participant_contracts CASCADE;
DROP TABLE participant_contract_witnesses CASCADE;
DROP TABLE participant_events CASCADE;


---------------------------------------------------------------------------------------------------
-- Events table: view of all events
---------------------------------------------------------------------------------------------------

-- This view is used to drive the transaction and transaction tree streams,
-- which will in the future also contain divulgence events.
-- The event_kind field defines the type of event (numbers allocated to leave some space for future additions):
-- 0: divulgence event
-- 10: create event
-- 20: consuming exercise event
-- 25: non-consuming exercise event
-- TODO append-only: EITHER only include columns that are used in queries that use this view OR verify that the query planning
-- is not negatively affected by a long list of columns that are never used.
CREATE VIEW participant_events
AS
SELECT
0::smallint as event_kind,
event_sequential_id,
NULL::text as event_offset,
NULL::text as transaction_id,
NULL::timestamp without time zone as ledger_effective_time,
command_id,
workflow_id,
application_id,
submitters,
NULL::integer as node_index,
NULL::text as event_id,
contract_id,
template_id,
NULL::text[] as flat_event_witnesses,
tree_event_witnesses,
create_argument,
NULL::text[] as create_signatories,
NULL::text[] as create_observers,
NULL::text as create_agreement_text,
NULL::bytea as create_key_value,
NULL::text as create_key_hash,
NULL::text as exercise_choice,
NULL::bytea as exercise_argument,
NULL::bytea as exercise_result,
NULL::text[] as exercise_actors,
NULL::text[] as exercise_child_event_ids,
create_argument_compression,
NULL::smallint as create_key_value_compression,
NULL::smallint as exercise_argument_compression,
NULL::smallint as exercise_result_compression
FROM participant_events_divulgence
UNION ALL
SELECT
10::smallint as event_kind,
event_sequential_id,
event_offset,
transaction_id,
ledger_effective_time,
command_id,
workflow_id,
application_id,
submitters,
node_index,
event_id,
contract_id,
template_id,
flat_event_witnesses,
tree_event_witnesses,
create_argument,
create_signatories,
create_observers,
create_agreement_text,
create_key_value,
create_key_hash,
NULL::text as exercise_choice,
NULL::bytea as exercise_argument,
NULL::bytea as exercise_result,
NULL::text[] as exercise_actors,
NULL::text[] as exercise_child_event_ids,
create_argument_compression,
create_key_value_compression,
NULL::smallint as exercise_argument_compression,
NULL::smallint as exercise_result_compression
FROM participant_events_create
UNION ALL
SELECT
20::smallint as event_kind,
event_sequential_id,
event_offset,
transaction_id,
ledger_effective_time,
command_id,
workflow_id,
application_id,
submitters,
node_index,
event_id,
contract_id,
template_id,
flat_event_witnesses,
tree_event_witnesses,
NULL::bytea as create_argument,
NULL::text[] as create_signatories,
NULL::text[] as create_observers,
NULL::text as create_agreement_text,
create_key_value,
NULL::text as create_key_hash,
exercise_choice,
exercise_argument,
exercise_result,
exercise_actors,
exercise_child_event_ids,
NULL::smallint as create_argument_compression,
create_key_value_compression,
exercise_argument_compression,
exercise_result_compression
FROM participant_events_consuming_exercise
UNION ALL
SELECT
25::smallint as event_kind,
event_sequential_id,
event_offset,
transaction_id,
ledger_effective_time,
command_id,
workflow_id,
application_id,
submitters,
node_index,
event_id,
contract_id,
template_id,
flat_event_witnesses,
tree_event_witnesses,
NULL::bytea as create_argument,
NULL::text[] as create_signatories,
NULL::text[] as create_observers,
NULL::text as create_agreement_text,
create_key_value,
NULL::text as create_key_hash,
exercise_choice,
exercise_argument,
exercise_result,
exercise_actors,
exercise_child_event_ids,
NULL::smallint as create_argument_compression,
create_key_value_compression,
exercise_argument_compression,
exercise_result_compression
FROM participant_events_non_consuming_exercise
;



---------------------------------------------------------------------------------------------------
-- Parameters table
---------------------------------------------------------------------------------------------------

-- new field: the sequential_event_id up to which all events have been ingested
ALTER TABLE parameters ADD COLUMN ledger_end_sequential_id bigint;
UPDATE parameters SET ledger_end_sequential_id = (
SELECT max(event_sequential_id) FROM participant_events
);

-- Note that ledger_end_sequential_id_before will not be equal to ledger_end_sequential_id_after,
-- as this migration creates divulgence events.
UPDATE participant_migration_history_v100
SET ledger_end_sequential_id_after = (
SELECT max(ledger_end_sequential_id) FROM parameters
);

---------------------------------------------------------------------------------------------------
-- Completions table
---------------------------------------------------------------------------------------------------

CREATE INDEX participant_command_completion_offset_application_idx ON participant_command_completions USING btree (completion_offset, application_id);
1 change: 1 addition & 0 deletions ...pi/src/main/resources/db/migration/postgres-appendonly/V100_1__Append_only_cleanup.sha256
View file
Original file line number Diff line number Diff line change
@@ -0,0 +1 @@
3f1cf409fcfeaa7ec851a92e3ac25f5666bd504f9347577295db231b178b6c89
Loading

0 comments on commit 66b8f19

Please sign in to comment.