Versioned tables in a geodatabase in PostgreSQL

An enterprise geodatabase must provide support for many users creating and updating large amounts of geographic information at the same time. Therefore, a geodatabase must provide an editing environment that supports multiuser concurrent editing without creating multiple copies of the data. In providing this functionality, this editing environment must also support edit sessions that typically span a number of days, the facility to undo or redo changes made to the database, the testing and development of data models and alternative application design proposals without affecting the published database, and the facility to monitor how the data and database have evolved over time.

To meet these requirements, enterprise, workgroup, and desktop geodatabases can be versioned. To start, register feature datasets, stand-alone feature classes, and tables as versioned. Then create versions of the geodatabase in which to do your editing. These versions are virtual; copies of the geodatabase are not made. The versions are represented by delta tables associated with each versioned dataset and a few system tables to track version states.

For information on versions, see A quick tour of versioning.

Versioned tables in ArcGIS for Desktop

In the Catalog window, versioned datasets appear the same as unversioned datasets. You can find out if a feature class or table is versioned by opening its Properties dialog box. On the General tab, it states whether or not the feature class or table is registered as versioned.

You can have multiple geodatabase versions. In the Catalog, you can make separate spatial database connections to each version. When you preview a versioned dataset in the DEFAULT version, it may look different and contain more or fewer records than the same feature class previewed from a version other than DEFAULT. (To learn how to make a spatial database connection to a version other than DEFAULT, see Connecting to a specific geodatabase version.)

Similarly, if you view a versioned feature class in one version in ArcMap, then view the feature class in another version in ArcMap, it may look different. That is because a table or feature class viewed in one version contains a certain number of rows, and the same feature class in another version may contain a different number of rows.

Tip:

To see which version of the data is in the map, click the List By Source button in the ArcMap table of contents.

The following example shows the Fire hydrants feature class in the DEFAULT version of the geodatabase in ArcMap:

When you switch to a different version of the geodatabase—version wo2557—the Fire hydrants feature class contains an additional hydrant and lateral. This means a hydrant was added to the Fire hydrants feature class and a lateral added to the Water laterals feature class while editing with wo2557 as the source geodatabase version.

This gives the impression that each version is a separate copy of the data. However, instead of creating a new copy of or modifying the original data, the geodatabase leaves the versioned table or feature class in its original form and stores any changes to that data in separate geodatabase system tables. The geodatabase tables that record version changes are referred to as the delta tables. For each table or feature class that has been versioned, two new delta tables—an adds (a) and a deletes (d) table—are created.

Versioned tables in a PostgreSQL database

Internally, versioning is managed by a number of database tables—the dataset tables, delta tables, and system tables—to track versions.

Delta tables

When you register a feature dataset, stand-alone feature class, or table as versioned, the delta tables—the adds and deletes tables—are created in the database. The delta tables record any inserts, updates, or deletes made to a versioned table or feature class at each state of the database. 

Note:

Only the owner of the dataset can register it as versioned.

a_<registration_id>

The a<registration_id> table (the adds table) maintains information on each inserted or updated record (feature) in a versioned business table and is queried to identify which records have been added or modified at a particular database state. The registration_id in the name of the adds table is the value in the registration_id field of the sde_table_registry table that corresponds to the versioned table. In the hydrant example, the registration_id of the hydrants feature class in the sde_table_registry table is 94; therefore, the adds table for the fire hydrants feature class is a94.

The adds table contains all the same fields as the versioned business table being edited plus a state_id.

The adds table

Field name	Field type	Description	Null?
<all the fields from the versioned business table>	<all corresponding types for the fields in the versioned business table>	All the attributes of the new feature
sde_state_id	bigint	Identifier of the state in which the new feature was added or updated	NOT NULL

For example, if you add a hydrant to the Fire hydrants feature class during a versioned edit session, a record is added to the adds table for that new hydrant.

d<registration_id>

The d<registration_id> table (or deletes table) maintains information on all rows that were deleted or updated in a versioned table and is queried to identify which rows have been deleted or modified at a particular state. When a row is deleted, the record is not physically removed: it is flagged as deleted and never returned in subsequent database queries.

The deletes table

Field name	Field type	Description	Null?
sde_state_id	bigint	Identifier of the state in which the new feature was added or updated	NOT NULL
sde_deletes_row_id	integer	The unique identifier of the deleted or updated feature	NOT NULL
deleted_at	bigint	The state in which the feature is being deleted	NOT NULL

System tables

In addition to the delta tables, system tables track versioned tables and edits. These are the sde_states, sde_state_lineages, sde_versions, and sde_mvtables_modified tables.

A versioned database typically contains a number of versions in addition to the DEFAULT version. These additional versions might represent such things as a work order, design alternative, disconnected editing session, or historical snapshot. The sde_versions table contains a descriptive list of these versions with each version identified by a unique name and ID (IDs are automatically generated by ArcGIS). In addition, each version has an owner, description, parent version, associated database state, and level of user access.

When the sde_versions table is created, the details of the DEFAULT version are automatically recorded in this table. The geodatabase administrator owns the DEFAULT version, and the initial ID of the corresponding database state is set to 0. The following is an example of the DEFAULT entry in the sde_versions table:

Name	Owner	Version_ID	Status	State_ID	Description	Parent_name	Parent_owner	Parent_version_ID	Creation_time
DEFAULT	SDE	1	1	0	Instance default version	NULL	NULL	NULL	2009-02-12 08:40:26

DEFAULT record

To manage edits made to the data, a versioned geodatabase maintains a collection of database states, or units of change to the database. A state represents a discrete snapshot of the database whenever a change is made: every edit operation creates a new database state. (An edit operation is any task or set of tasks [additions, deletions, or modifications] undertaken on features and rows.) All geodatabase versions reference one of these database states and evolve over time through a series of states.

All geodatabase states have the same schema and differ only in the number of rows that represent each modified table or feature class. To identify conflicts, which can occur when the same feature is edited in either the same or different versions, the version state lineages are compared for differences or row conflicts during version reconciling.

All the information relating to states is managed in the sde_states table. The sde_versions and sde_state_lineages are queried to identify which database state each version references.

States are maintained in a tree structure where the parent-child relationships can be derived from the state lineage. Information about the state lineage of each version is maintained in a separate table, sde_state_lineages. This table stores a multiple entry index for traversing state parent-child relationships and is used for all version queries.

To return the correct view of a version, its states lineage is queried to identify all the states that recorded each change made to that version. From this list of states, the table rows that correctly represent the version can be determined. As the geodatabase is edited and the versions change over time, the state tree becomes more complex.

Anytime a feature class or table is modified in a state, a new entry is created in the sde_mvtables_modified table. When two versions are reconciled, the first step in the process is to identify the states these two versions reference—the current edit version's state and the target version's state. From these states, a common ancestor state is identified by tracing back through the state lineage of these two versions. The sde_mvtables_modified table is then queried to identify all the tables that were modified between the common ancestor state and the target version state. From this list of modified tables, a second list of tables common to both state lineages is generated. For all common tables in this second list, a number of version difference queries are executed—INSERT, UPDATE, DELETE, UPDATE_UPDATE, and UPDATE_DELETE.

The tables involved in the versioned feature class can be seen in the following diagram:

Dashed lines indicate implicit relationships between columns.

The number in the names of the adds and deletes tables is the registration_id of the business table from the sde_table_registry table.

Versioned tables in an XML workspace document

An entry in XML documents indicates whether or not a feature class or table is versioned. It is enclosed by versioned tags. For a versioned feature class or table, the value is true.

Tip:

The versioned tag, though set set on feature datasets, does not necessarily reflect the version value for the feature classes within the feature dataset. To determine if the feature classes within a feature dataset are registered as versioned, query the individual feature classes.

<Versioned>true</Versioned>