Enabling Table Versioning

To take part in versioning, a table needs to be version enabled (sometimes referred to as "activated"), which is an operation performed using the SQL API.

When a table is activated, it is replaced by a view with the same name and columns as the table so that it can continue to be read from and written to as before. The table itself is renamed and has columns added to record information about the versions for each row.

The renamed table should not be accessed directly; only the view should be used to read and write data.

Metadata tables

Metadata tables are used to record which tables are activated for versioning. Note that there will be some restrictions as to what type of constraints, triggers, indexes or other database objects can exist on a table to be activated.

The following metadata tables can be found in the sms_1transact schema:

  • activated_tables - The set of tables that have been activated for versioning.
  • checkpoint - The set of checkpoints available in this database.
  • version - The set of versions available in this database.

Naming tables and columns

Note: The following guidelines apply for PostgreSQL. For Oracle, see the Oracle Workspace Manager documentation.

Postgres uses a naming convention of lowercase names for objects such as tables and their columns. You can force objects to be in uppercase by using quotes around the names of these objects, however 1Transact only supports standard lowercase names for objects.

When activating a table, a view is created with the name of the table appended with _vm. Ensure that when you activate a table, there is no other table with a name of the activated table with _vm at the end.

Column names

An activated table cannot contain any column called ckpt, end-ckpt, or transact_status.

Editing columns in activated tables

You can add, remove, or rename columns on an activated table.

Note: It is recommended that you back up your database before adding, removing or renaming a column.

  1. Make the required changes to the columns in the table containing your features.

    When the table was activated it was renamed [original_name]_vm; make the changes to this table.

    Note: Do not alter the 1Transact-specific columns ckpt, end-ckpt, or transact_status.

  2. Drop the two views which will be called [original_name] and [original_name]_vc.

    For example, for a table called features:

    Drop view features;

    Drop view features_vc;

  3. Call the SQL function to regenerate the view for the base table:

    sms_1transact.create_view('table_name');

Restrictions for activated tables

Triggers

AFTER and INSTEAD of triggers are not supported as they can invalidate the data or cause infinite loops.

BEFORE triggers are supported; these can exist on the table before activation or can also be added to the underlying *_vm table after activation.

Constraints

A feature table must have a non-compound primary key defined on it in order to be activated.

Because each feature can have more than one version, no unique constraint or index can be applied to the activated table unless they are removed before editing and replaced with composite constraints across both the original column and the ckpt column. Primary keys will automatically be replaced in this way when activating the table.

Foreign keys and join tables

Foreign keys to rows in an activated table (such as from a join table) are not allowed.

An activated table can contain multiple versions of the same feature and no longer has a single-column primary key, so a table cannot be activated if there are foreign keys pointing to it . A workaround is to remove the foreign key constraint but continue to rely on the feature ID being unique when accessed from the feature view (rather than the underlying feature table). This means that the database will not enforce the foreign key; however, typically this relationship is set and managed and validated by application software.

While compound foreign keys could be defined (so that they reference the feature ID and the 1Transact version of the feature) these would be risky as the behaviour of "cascading deletes" may cause the referencing data to become accidentally changed or deleted.

Support for partitioned tables

Partitioned tables can be activated and take part in versioning.

Note: When activating the table, all the child partition tables will have their primary key replaced with compound indexes on the original column and the ckpt column as for unpartitioned tables.

Support for pgpool

1Transact has been tested on a database that has been configured to use pgpool as a load balancing mechanism but has shown that pgpool cannot cope with some of the standard SQL that 1Transact is generating, such as using a single SQL command to add a column to a table with a cascading foreign key constraint and a default value.

This is also an issue when doing a simple restore of data using pgAdmin that contains a cascading foreign key constraint and a default value; this appears to be a bug in pgpool. Another problem is that accessing a specific version or checkpoint requires calling a function which creates a temporary view. This means that read-only pgpool nodes cannot be used to access versions or checkpoints because this requires creating a view which is not a read-only operation.