Versioned tables in a geodatabase in PostgreSQL

Versioned tables in a geodatabase in PostgreSQL
Release 9.3

Versioned tables in ArcCatalog and ArcMap

Versioned tables in a PostgreSQL DBMS

• Version delta tables
When the owner of a feature dataset or stand-alone feature class registers it as versioned, the delta tables—the adds and deletes tables—get created in the owner's schema in the database. The delta tables record any changes—new, modified, or deleted records—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 above, the registration_id of the hydrants feature class in the sde_table_registry table is 94; therefore, the adds table for the hydrants feature class is a94.
The adds table contains all the same fields as the versioned business table being edited plus a state_id.



Field name	Field type	Description
<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	ID of the state in which the new feature was added or updated NOT NULL

For example, if you add a hydrant to the hydrants feature class during a versioned edit session, a record will get 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 are 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 isn't physically removed: it is flagged as deleted and never returned in subsequent database queries. The following are the fields in the deletes table:



Field name	Field type	Description
sde_state_id	bigint	ID of the state in the lineage for which the feature is deleted NOT NULL
sde_deletes_row_id	integer	The unique identifier of the deleted or updated feature NOT NULL
deleted_at	bigint	ID of the state in which the new feature was deleted NOT NULL

To correctly represent each database version, the delta tables are queried, in conjunction with the sde_versions and sde_state_lineages system tables, to identify which change was made at which database state. A version then returns a seamless view of the data that takes into account the original state of the data plus any changes.
• 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_mv_tables_modified tables.
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 reconcile.
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.
In conjunction with the sde_states table, the sde_mv_tables_modified table maintains the list of all tables that have been modified at each state of the database.
Any time a feature class or table is modified in a state, a new entry is created in the sde_mv_tables_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_mv_tables_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, UPDATE_DELETE.
The tables involved in the versioned hydrants feature class can be seen in the diagram accessed from the following link:
PostgreSQL versioned feature class diagram
Dashed lines represent implicit relationships between tables; solid lines represent relationships explicitly defined in the database. You will need Adobe Reader to open the diagram.

Versioned tables in an XML workspace document

<Versioned>true</Versioned>

You can use geodatabase replication to create copies of data across two or more versioned geodatabases so that changes to the data may be synchronized. A synchronization involves one replica sending data changes and the relative replica receiving changes.

Before you create a two-way or one-way replica, you must add a global ID column to the datasets to be replicated. This gives the rows in the dataset a unique value that remains constant across geodatabases. (For details on getting a dataset ready for replication, consult Preparing data for replication.)

After changes have been made in one of the replicas, you can synchronize the geodatabases, bringing the changes made in one geodatabase into its relative geodatabase. When a geodatabase is synchronized with its relative geodatabase, a table is created in the user's schema of the replica geodatabase (the one that is sending the changes to the relative geodatabase) to track the lineages of altered datasets.

Replica tables in ArcCatalog and ArcMap

You won't see the table used for synchronization in ArcCatalog or ArcMap; it is used behind the scenes and only during synchronization.

You can, however, identify if a geodatabase is being used as a replica geodatabase by checking its properties in ArcCatalog. Right-click the geodatabase and click Properties. On the General tab, there is a Distributed Geodatabase Status section. If the geodatabase has been replicated or replicated to, the status states, This is a replica geodatabase.

In ArcMap, you know when an MXD file contains one or more layers that have been replicated because most (or even all) of the tools on the Distributed Geodatabase toolbar are active. Also, when you open the Replica Manager, one or more replicas are listed.

Replica tables in a PostgreSQL DBMS

Before datasets can be replicated, they must have a global ID column and be registered as fully versioned (not registered with the option to save edits to base). Therefore, in the database, the business tables of any datasets that will be included in the replication will have a GUID column, and delta tables exist for each of these datasets.

Replicas are tracked in the database using the following ArcSDE geodatabase system tables. Click the table names to link to their descriptions.

gdb_replicadatasets

gdb_replicalog

gdb_replicas

gdb_replicasex

The tables are related as follows:

When synchronization is performed between two geodatabases, a table is created to track dataset lineages.

Replica tables in an XML workspace document

If you choose to replicate to an XML document when you create your replica, information about the replica and replicated datasets will be enclosed in <GPReplica> tags as shown below. In this example, the name of the replica is asia, and it was replicated from geodatabase version editor9.

<GPReplica xsi:type="esri:GPReplica">
   <Name>manager.asia</Name> 
   <ID>-1</ID> 
   <ReplicaVersion>sde.editor9</ReplicaVersion> 
   <CreationDate>2007-04-23T12:13:07</CreationDate> 
   <GUID>AFC2DA1A-B751-4096-82DE-7AC9E601A563</GUID> 
   <Role>esriReplicaRoleChild</Role> 
   <AccessType>esriReplicaChildReadOnly</AccessType> 
   <MyGenerationNumber>0</MyGenerationNumber> 
   <SibGenerationNumber>0</SibGenerationNumber> 
   <SibMyGenerationNumber>0</SibMyGenerationNumber> 
   <ReplicaState>esriReplicaStateWaitingForData</ReplicaState> 
   <SibConnectionString>SERVER=boar;INSTANCE=7300;DATABASE=consult;VERSION=sde.editor9;AUTHENTICATION_MODE=DBMS;ProgID=esriDataSourcesGDB.SdeWorkspaceFactory.1</SibConnectionString> 
  <GPReplicaDescription xsi:type="esri:GPReplicaDescription">
   <ModelType>esriModelTypeFullGeodatabase</ModelType> 
   <SingleGeneration>false</SingleGeneration>
 <SpatialRelation>esriSpatialRelIntersects</SpatialRelation> 
  <QueryGeometry xsi:type="esri:EnvelopeN">
   <XMin>-5543912.2421665</XMin> 
   <YMin>3741401.908035</YMin> 
   <XMax>-5538272.6904335</XMax> 
   <YMax>3743452.058665</YMax> 
  <SpatialReference xsi:type="esri:ProjectedCoordinateSystem">
   <WKT>PROJCS["Asia_South_Albers_Equal_Area_Conic",GEOGCS["GCS_WGS_1984",DATUM["D_WGS_1984",SPHEROID["WGS_1984",6378137.0,298.257223563]],PRIMEM["Greenwich",0.0],UNIT["Degree",0.0174532925199433]],PROJECTION["Albers"],PARAMETER["False_Easting",0.0],PARAMETER["False_Northing",0.0],PARAMETER["Central_Meridian",125.0],PARAMETER["Standard_Parallel_1",7.0],PARAMETER["Standard_Parallel_2",-32.0],PARAMETER["Latitude_Of_Origin",-15.0],UNIT["Meter",1.0]],VERTCS["Ha_Tien_1960",VDATUM["Ha_Tien_1960"],PARAMETER["Vertical_Shift",0.0],PARAMETER["Direction",1.0],UNIT["Meter",1.0]]</WKT> 
   <XOrigin>-21663300</XOrigin> 
   <YOrigin>-10280500</YOrigin> 
   <XYScale>207890747.363998</XYScale> 
   <ZOrigin>0</ZOrigin> 
   <ZScale>1</ZScale> 
   <MOrigin>-100000</MOrigin> 
   <MScale>10000</MScale> 
   <XYTolerance>0.001</XYTolerance> 
   <ZTolerance>2</ZTolerance> 
   <MTolerance>0.001</MTolerance> 
   <HighPrecision>true</HighPrecision> 
   </SpatialReference>
   </QueryGeometry>
 <GPReplicaDatasets xsi:type="esri:ArrayOfGPReplicaDataset">
  <GPReplicaDataset xsi:type="esri:GPReplicaDataset">
   <DatasetName>consult.pw.roads</DatasetName> 
   <DatasetType>esriDTFeatureClass</DatasetType> 
   <RowsType>esriRowsTypeFilter</RowsType> 
   <IsPrivate>false</IsPrivate> 
   <UseGeometry>true</UseGeometry> 
  </GPReplicaDataset>

Other dataset definitions

</GPReplicaDatasets>
  <TransferRelatedObjects>true</TransferRelatedObjects> 
  </GPReplicaDescription>
  <ReconcilePolicy>esriReplicaResolveConflictsNone</ReconcilePolicy> 
</GPReplica>

You have the ability to track transaction-time history for your data using geodatabase archiving. Transaction time represents the moment in time when an event is represented in the database. It is delimited when the feature is inserted in the database, then modified or logically deleted. Tracking a dataset's history allows you to keep a record of when and how the data has changed. It also allows you to query previous versions of the data.

Archiving tables in ArcCatalog and ArcMap

To use geodatabase archiving, register the data as fully versioned, then enable it for archiving in ArcCatalog. (For details on how to perform this operation, see Geodatabase archiving and its related topics.) You can tell if a dataset has already had archiving enabled if, when you right-click the dataset in ArcCatalog and click Archiving, the context menu has Disable Archiving enabled but Enable Archiving is disabled.

Archive classes cannot be viewed in ArcCatalog, but you can save a connection to a specific historical version through ArcCatalog. See the section "Connecting to a specific version of the database" in Creating spatial database connections and the topic Working with a historical version for instructions on how to do this. To help you view the changes made at specific times, you can create historical markers that can be used by others to view the state of the data at that specific time. For details on creating historical markers, see Working with historical markers.

Archiving tables in a PostgreSQL DBMS

When a table is enabled for archiving, an archive class is created. This is a copy of the business table and contains all the same fields plus three new fields: gdb_from_date, gdb_to_date, and gdb_archive_oid. For a description of how these fields are populated, see The archive process.

The name of the archive class table is the same as the original business table name with an underscore and H appended to it. For example, if a feature class named trails is enabled for archiving, an archive class, trails_H, is created in the schema of the owner of the feature class. The archive class table is read-only, stores changes saved or posted to the DEFAULT version of the geodatabase, and is not deleted if its corresponding dataset is unregistered as versioned or deleted. If an archived dataset is unversioned or deleted, the archive class is converted to a temporal table and can still be queried. See Working with the Geodatabase History Viewer for details on viewing different historical versions.

When changes are made to the schema of a dataset that is enabled for archiving—for example, if a field is added or deleted—these changes are automatically added to the corresponding archive class.

NOTE: Never directly alter the schema of an archive class.

Also, when a table is enabled for archiving, a record gets added to the sde_archives table. This record stores the registration IDs of the table that was enabled for archiving and its associated archive class table. For further information on the sde_archives table, see System tables of a geodatabase in PostgreSQL.

The following shows a business table—lots—enabled for archiving, its corresponding archive class table, and records in the sde_archives table. The business table is related to the sde_archives table through the sde_table_registry table.

When you create historical markers to view the state of the data at a specific time, the gdb_historicalmarkers table is populated. For details on creating historical markers, see Working with historical markers.

Archiving tables in an XML workspace document

Archive classes are not exported to XML workspace documents.