Routines

Routines

Name Type Language Deterministic Return Type Security Restriction Comments
insert_history_item FUNCTION PLPGSQL false uuid DEFINER

Add a new history item in the lizsync.history table as the owner of the table. The SECURITY DEFINER allows the clone to update the protected table. DO NOT USE MANUALLY.
rollback_event FUNCTION PLPGSQL false void INVOKER

Rollback a logged event and returns to previous row data

Arguments:
pevent_id: The event_id of the event in lizsync.logged_actions to rollback
compare_tables FUNCTION PLPGSQL false SETOF record INVOKER
get_central_audit_logs FUNCTION PLPGSQL false SETOF record INVOKER

Get all the logs from the central database: modifications do not come from the clone, have not yet been replayed by the clone, are dated after the last synchronisation, have an event id higher than the last sync maximum event id, and concern the synchronised tables for this clone. Parameters: uid column name and excluded columns
audit_view FUNCTION SQL false void INVOKER
analyse_audit_logs FUNCTION PLPGSQL false SETOF record INVOKER

Get audit logs from the central database and the clone since the last synchronization. Compare the logs to find and resolved UPDATE conflicts (same table, feature, column): last modified object wins. This function store the resolved conflicts into the table lizsync.conflicts in the central database. Returns central server event ids, minimum event id, maximum event id, maximum action timestamp.
create_temporary_table FUNCTION PLPGSQL false boolean INVOKER

Create temporary table used during database bidirectionnal synchronization. Parameters: temporary table name, and table type (audit or conflit)
if_modified_func FUNCTION PLPGSQL false trigger DEFINER

Track changes to a table at the statement and/or row level.

Optional parameters to trigger in CREATE TRIGGER call:

param 0: boolean, whether to log the query text. Default ‘t’.

param 1: text[], columns to ignore in updates. Default [].

     Updates to ignored cols are omitted from changed_fields.

     Updates with only ignored cols changed are not inserted
     into the audit log.

     Almost all the processing work is still done for updates
     that ignored. If you need to save the load, you need to use
     WHEN clause on the trigger instead.

     No warning or error is issued if ignored_cols contains columns
     that do not exist in the target table. This lets you specify
     a standard set of ignored columns.

There is no parameter to disable logging of values. Add this trigger as
a ‘FOR EACH STATEMENT’ rather than ‘FOR EACH ROW’ trigger if you do not
want to log row values.

Note that the user name logged is the login role for the session. The audit trigger
cannot obtain the active role because it is reset by the SECURITY DEFINER invocation
of the audit trigger its self.

LizSync has added its own sync_data column to store the needed information for synchronisation purpose.

[]: ./null
[]: ./null
add_uid_columns FUNCTION PLPGSQL false boolean INVOKER
get_event_sql FUNCTION PLPGSQL false text INVOKER

Get the SQL to use for replay from a audit log event

Arguments:
pevent_id: The event_id of the event in lizsync.logged_actions to replay
puid_column: The name of the column with unique uuid values
update_synchronized_table FUNCTION PLPGSQL false boolean DEFINER

Insert or Update the table lizsync.synchronized_tables. The SECURITY DEFINER allows the clone to update the protected table. DO NOT USE MANUALLY.
create_central_server_fdw FUNCTION PLPGSQL false boolean INVOKER

Create foreign server, needed central_lizsync schema, and import all central database tables as foreign tables. This will allow the clone to connect to the central databse
get_clone_audit_logs FUNCTION PLPGSQL false SETOF record INVOKER

Get all the modifications made in the clone. Parameters: uid column name and excluded columns
synchronize FUNCTION PLPGSQL false SETOF record INVOKER

Run the bi-directionnal database synchronization between the clone and the central server
replay_clone_logs_to_central FUNCTION PLPGSQL false SETOF integer INVOKER

Replay all logs from the clone to the central database. It returns the number of actions replayed. After this, the clone audit logs are truncated.
update_history_item FUNCTION PLPGSQL false boolean DEFINER

Update the status of a history item in the lizsync.history table as the owner of the table. The SECURITY DEFINER allows the clone to update the protected table. DO NOT USE MANUALLY.
replay_event FUNCTION PLPGSQL false void INVOKER

Replay a logged event.

Arguments:
pevent_id: The event_id of the event in lizsync.logged_actions to replay
store_conflicts FUNCTION PLPGSQL false SETOF integer INVOKER

Store resolved conflicts in the central database lizsync.conflicts table.
audit_table FUNCTION SQL false void INVOKER
replay_central_logs_to_clone FUNCTION PLPGSQL false SETOF integer INVOKER

Replay the central logs in the clone database, then modifiy the corresponding audit logs in the central server to update the sync_data column. A new item is also created in the central server lizsync.history table. When running the log queries, we disable triggers in the clone to avoid adding more rows to the local audit logged_actions table
import_central_server_schemas FUNCTION PLPGSQL false SETOF ARRAY INVOKER

Import synchronised schemas from the central database foreign server into central_XXX local schemas to the clone database. This allow to edit data of the central database from the clone.
update_central_logs_add_clone_id FUNCTION PLPGSQL false boolean DEFINER

Update the central database synchronisation logs (table lizsync.logged_actions) by adding the clone ID in the “replayed_by” property of the field “sync_data”. The SECURITY DEFINER allows the clone to update the protected lizsync.logged_actions table. DO NOT USE MANUALLY.