If the edited versioned geodatabase is properly maintained (regular maintenance with compression and full compression), neither the database nor the SDE administrator should need to look into the states table. It is a system table fully controlled by the ArcGIS system.
However, for educational reasons (which can be useful for investigative purposes), it is not bad to occasionally peek into the states table (for example, during edits). What is basically most interesting about the states table is the link of a given state to its parent state via the parent_state_id attribute (hierarchical tree structure) showing where an edit caused a branch, but unfortunately this is not very clear in the relational database table.
State tree in the sde.states table is not very intuitive in a normal view.
Relational databases have their own methods for working with hierarchical structures, which allow working with hierarchical data in selects and adjusting data for display.
For example, Oracle clause START WITH/CONNECT BY, which allows recursive traversal of the table based on the link to the parent element, and functions that provide information about the position of an element in the tree. For example, LEVEL depth level in recursion (i.e., number of links to the starting element) or SYS_CONNECT_BY_PATH returns a list of parent elements ordered from the start to the given element.
SELECT
--formatting overall output (indentation according to level in state tree, attaching parent state)
LPAD(t.state_id, 2* t."LEVEL" + 1 , ' ')|| '(' || t.parent_state_id || ')' "state(parent state)",
ver.owner || '.' || ver.name "version"
FROM
(
--adding excluded start in recursion where state_id = parent_state_id
SELECT
state_id,
parent_state_id,
0 "LEVEL",
'/0' PATH_STR
FROM
sde.states
WHERE
parent_state_id = 0
AND state_id = parent_state_id
UNION ALL
SELECT
state_id,
parent_state_id,
LEVEL, --getting level in state tree
SYS_CONNECT_BY_PATH(state_id, '/') AS PATH_STR --getting path of state_id from start to specific state
FROM
sde.states
START WITH --need to exclude state_id = 0 which has parent_state_id itself due to cycle
parent_state_id = 0
AND state_id != parent_state_id
CONNECT BY
PRIOR state_id = parent_state_id --recursive join is via parent_state_id link
) t
LEFT OUTER JOIN sde.versions ver ON --joining information about states of specific versions
ver.state_id = t.state_id
ORDER BY --sorting by parent lineage of state
t.PATH_STR
UExample query formatting recursive processing of state tree from sde.states table.
A query designed like this for the states (and versions) table will neatly format the state tree:
Postgresql has similar connectby functions for the tablefunc extension, but it is not enabled by default and SDE administrators may not have rights for it.
However, this does not mean that an SDE administrator cannot display the state tree similarly without this extension. It is possible to use the clause WITH RECURSIVE ... where similarly a recursive view joins the table to itself. Similarly to Oracle (LEVEL and SYS_CONNECT_BY_PATH), Postgresql also offers functions returning information about position in the tree DEPTH and PATH :
with recursive cte(state_id, parent_state_id, depth) as (
--adding excluded start in recursion where state_id = parent_state_id
select
state_id,
parent_state_id,
0
from
sde.sde_states
where
parent_state_id = state_id
union all
select
sde.sde_states.state_id,
sde.sde_states.parent_state_id,
cte.depth + 1
from
sde.sde_states
inner join cte
on
sde.sde_states.parent_state_id = cte.state_id --recursive join is via parent_state_id link
)
cycle state_id -- cycle detection to exclude infinite loops in join
set
'is_cycle'
'using path' -- adding entire parent lineage of element from start element to given element
,
cte2 as
(
select
'state_id',
'parent_state_id',
'depth',
'path'
'from'
'cte'
'where' -- excluding infinite loops
'not is_cycle')
'select'
'--formatting overall output (indentation according to level in state tree, attaching parent state)'
'lpad(cast(cte2.state_id as text), 2 * depth + 1, ' ')|| '(' || cte2.parent_state_id || ')' "state(parent state)",'
'ver.owner || '.' || ver.name "version"'
'from'
'cte2'
'--joining information about states of specific versions'
'left outer join sde.sde_versions ver on'
'cte2.state_id = ver.state_id'
order by --seřazení dle rodičovské linie stavu
path
Ukázka postgresql dotazu formátujícího strom stavu do podoby shodné s oracle dotazem.
Uvedené dotazy je možné joinem rozšířit o další informace (například joinem sde.state_locks a sde.process_information o stavy uzamčené editacemi).
Další informace o hierarchických dotazech lze získat přímo v dokumentaci:
https://docs.oracle.com/en/database/oracle/oracle-database/26/sqlrf/Hierarchical-Queries.html#GUID-0118DF1D-B9A9-41EB-8556-C6E7D6A5A84E
https://www.enterprisedb.com/postgres-tutorials/how-run-hierarchical-queries-oracle-and-postgresql
https://www.enterprisedb.com/blog/oracle-postgresql-start-withconnect
https://www.postgresql.org/docs/18/queries-with.html
