Documentation Index

Fetch the complete documentation index at: https://docs.canton.network/llms.txt

Use this file to discover all available pages before exploring further.

​

SQL API

​

Ledger time model

​

Offset

​

Ledger time

​

Transaction ID

• Not every offset has a transaction ID. For example, the completion event of a rejected transaction does not have a transaction ID because it was unsuccessful.
• There is, at most, one transaction ID at a given offset.
• Each transaction ID is unique and always has a single offset.
• While offsets are allocated by, and are specific to, a Participant Node; transaction ID values are common to all Participant Nodes.
• Transaction ordering (as represented by associated offset) can vary between Participant Nodes.
• A transaction ID is entirely opaque and does not communicate any information, other than identification.

​

Which should I use?

• Causal: Offset provides an understanding of events in causal order, consistent with the participant-determined ledger commit ordering.
• Systematic: Transaction ID is required for correlating over multiple Participant Nodes, serving as a common identifier for individual transactions.
• Temporal: Ledger Time provides an ordering of events in wall-clock time, with bounded skew. This can be useful depending on your need for precision.

​

PQS time model

​

Offset management

• Ignore; The latest available state.
• Pin; The state of the ledger at a specific time.
• Span; The ledger events across a time range, such as for an audit trail.
• Consistency; The ledger in a way that maintains consistency with other interactions you have had with the ledger (read or write).

• set_latest(offset): nominates the offset of the latest data to include in observing the ledger. If NULL it uses the latest available. The actual offset to be used is returned. If the supplied offset is beyond what is available, an error occurs.
• validate_offset_exists(offset): validates that the datastore has a complete history up to and including the offset provided. Returns an error if the nominated offset is not available (too old, or too new).
• set_oldest(offset): nominates the offset of the oldest events to include in query scope. If NULL then it uses the oldest available. Function returns the actual offset used. If the supplied offset is beyond what is available, an error occurs.
• nearest_offset(time): a helper function to determine the offset of a given time (or interval prior to now).
• pruned_offset(): returns the offset up to which the database has been pruned, or NULL if no pruning has occurred.

​

Accessing contracts and exercises

• active(name, [at_offset]): active instances of the target template/interface views that existed at the time of the latest offset
• creates(name, [from_offset], [to_offset]): create events of the target template/interface views that occurred between the oldest and latest offset
• archives(name, [from_offset], [to_offset]): archive events of the target template/interface views that occurred between the oldest and latest offset
• exercises(name, [from_offset], [to_offset]): exercise events of the target choice that occurred between the oldest and latest offset

• Fully qualified: <package>:<module>:<template|interface|choice>
• Partially qualified: <module>:<template|interface|choice> or <template|interface|choice> (if unambiguous)

   select set_oldest('from_offset');
   select set_latest('to_offset');
   select * from creates('package:My.Module:Template');

   select *
   from creates('package:My.Module:Template', 'from_offset', 'to_offset');

​

Accessing transactions metadata

   select c.contract_id, t.transaction_id, (t.trace_context).trace_parent
   from creates('package:My.Module:Template') c
   inner join transactions t on c.created_at_ix = t.ix
   where c.create_event_id = (42::bigint,0);

​

Summary functions

• summary_transients(from_offset, to_offset): the number of transients per Daml fully qualified name within the offset range.
• summary_updates(from_offset, to_offset): summary of create and archive counts per Daml fully qualified name within the offset range.

• summary_active(at_offset)
• summary_creates(from_offset, to_offset)
• summary_archives(from_offset, to_offset)
• summary_exercises(from_offset, to_offset)

​

Lookup functions

• lookup_contract(contract_id) is a mechanism to retrieve contract data without needing to know its Daml qualified name. The function returns both contract and all associated interface view projections, distinguishable by the payload_type column.
• lookup_exercises(contract_id) is a mechanism to retrieve choice exercise data without needing to know the Daml qualified name; knowing the contract ID is sufficient.

​

Types of returned data

​

Package ID and Daml upgrades

• package_id is the representative package ID used for decoding, guaranteed to be present on the connected Participant Node.
• creation_package_id is the original package ID from the creation transaction.
• package_version reflects the version of the representative package.

​

JSONB encoding

​

Primitive types

​

User-defined types

​

Offset model

​

Offset management

​

Table functions for contracts and exercises

-- Implicit: set scope first, then query
select set_oldest('from_offset');
select set_latest('to_offset');
select * from creates('package:My.Module:Template');

-- Explicit: inline the entire context in a single statement
select *
from creates('package:My.Module:Template', 'from_offset', 'to_offset');

​

Lookup functions

​

Transactions view

select c.contract_id, t.transaction_id, (t.trace_context).trace_parent
from creates('package:My.Module:Template') c
inner join transactions t on c.created_at_ix = t.ix
where c.create_event_id = (42::bigint, 0);

​

Summary functions

​

Contract columns

​

Exercise columns

​

JSONB indexing

call create_index_for_contract(
  'token_quantity',         -- index name suffix
  'Token',                  -- template name
  '((payload->>''quantity'')::decimal)',  -- expression
  'btree'                   -- index type (btree, hash, gin, gist)
);

-- Determine the internal table number for your template
select __contract_tpe4name('Token');  -- returns e.g. 14

-- Then analyze
vacuum analyze __contracts_14;

call create_index_for_contract(
  'token_wallet_holder',
  'Token',
  '(payload->''wallet''->>''holder'')',
  'hash'
);

​

Maintenance functions

​

Pruning

select * from prune_to_offset('<offset>');

select * from prune_to_offset(nearest_offset('2024-01-30T00:00:00Z'::timestamptz));
select * from prune_to_offset(nearest_offset(interval '30 days'));

​

Redaction

• redact_contract(contract_id, redaction_id) — nullifies payload and contract_key on an archived contract (and its interface views). Returns the number of affected entries.
• redact_exercise(event_id, redaction_id) — nullifies argument and result on an exercise event.

select redact_contract('<contract_id>', '<redaction_id>');
select redact_exercise('<event_id>', '<redaction_id>');

​

History Slicing

• The requested offset range falls outside the participant’s available ledger history
• The start offset refers to a pruned region or genesis on a pruned ledger
• The requested range would create a gap in the PQS datastore’s existing history (you cannot skip over offsets that the datastore has not yet seen)

​

Resetting

-- Dry run: inspect scope of the proposed reset
select * from validate_reset_offset('<offset>');

-- Destructive: execute the reset
select * from reset_to_offset('<offset>');