Enterprise geodatabases use log file tables to maintain lists of selected records. Records are written to log file tables for later use by the application in the following situations:
- A client creates a selection set of a specific size (100 records in ArcMap; 1,000 records in ArcGIS Pro; 1,000 records if ObjectIDs are requested from a feature service).
- You reconcile or post to a versioned geodatabase.
- A client application checks out data for disconnected editing.
There are three log file options: shared, session-based, and pools of session-based log files. Each is described in its own section in this topic.
Geodatabases in PostgreSQL use shared log files by default, but you can change log file table settings using the Configure Geodatabase Logfile Tables geoprocessing tool.
Shared log files
Shared log files are shared by all sessions that connect as the same user. If you have multiple users connecting with the same user account, all those sessions insert records into and delete records from the same log file data table.
When to use shared log files
Use shared log files if each client and user connects with a different database user account.
When you might not want to use shared log files
You may not want to use shared log files if you have numerous connections made with the same login, such as when you publish feature services to ArcGIS Server, which will generate multiple connections with the same login. If many of your users will be running attribute queries against the same feature service, this could lead to contention and longer wait times for the SDE_LOGFILE_DATA table. In those cases, you might want to use session-based log files.
Tables created for shared log files
The log file tables used for this option are SDE_LOGFILES and SDE_LOGFILE_DATA. They are created in the schema of the connecting user the first time the user makes a selection that exceeds the selection threshold.
SDE_LOGFILES stores information about each selection set (log file) that is created. The logfile_name and logfile_id columns in this table uniquely identify the name of the log file, and the logfile_id column links the log file record to the SDE_LOGFILE_DATA table. The SDE_LOGFILE_DATA table contains the logfile_data_id and the feature identifier for the selected records.
All records are deleted as soon as the selection set is cleared to prevent the SDE_LOGFILE_DATA table from growing too large. The SDE_LOGFILES table is truncated when the session ends. Both SDE_LOGFILE_DATA and SDE_LOGFILES remain in the user's schema.
Permissions to use shared log files
Since log file tables are owned by the connecting user, that user must own a schema in which he or she can create tables. In geodatabases, the schema name must be the same as the user name. If the user does not have a matching schema in which tables can be created, an error message will be returned when the user performs an action that requires log file tables, such as starting an editing session or creating a selection set larger than the threshold size for that particular client application.
The following permissions are required to use shared log file tables in PostgreSQL:
- CONNECT
- USAGE on the user's own schema
- CREATE on the user's own schema
Note that granting AUTHORIZATION on the schema automatically confers USAGE and CREATE privileges. However, if you do not want users to create tables, you cannot grant AUTHORIZATION. Instead, grant each user USAGE and CREATE permissions on their schemas so you can revoke CREATE permissions after the shared log file tables are created. The USAGE privilege must remain so users can write to the log file tables in the future.
For example, Ian is a city planner who would only select data from the geodatabase to perform analyses related to his work. Therefore, he would be considered a read-only user. However, for Ian to create the SDE_LOGFILES and SDE_LOGFILE_DATA tables needed to store selection sets, he needs authority on his own schema.
Rather than grant Ian permissions to create tables indefinitely, the sde user creates a schema for Ian and temporarily grants him CREATE and USAGE privileges on the schema. The sde user then asks Ian to log in to the geodatabase from ArcMap and make a selection of 101 or more features. This creates the necessary tables in the schema. Once the tables are created, the sde user revokes Ian's CREATE privilege.
Optional storage control for shared log file tables and indexes
There are several parameters under the LOGFILE_DEFAULTS keyword of the DBTUNE table that control how or where log file tables are stored in the database. You do not need to set these to use shared log files, but you can set them if you want to alter how the SDE_LOGFILES and SDE_LOGFILE_DATA tables and indexes are stored in the database.
For PostgreSQL, the following parameters are used:
- LD_INDEX_ALL
- LD_STORAGE
- LF_INDEX_ID
- LF_INDEX_NAME
- LF_STORAGE
See PostgreSQL configuration parameters for details.
Session-based log files
Session-based log file data tables are dedicated to a single session and may contain multiple selection sets (log files). Each session that logs in requires a set of tables for selections.
When to use session-based log files
You might use session-based log files if you have numerous concurrent connections being made to the geodatabase with the same login. For example, if many services reference the data in your geodatabase, you might use session-based log files.
When you might not want to use session-based log files
If you have read-only users who connect to the database, you cannot use session-based log files.
The session table is dropped from the user's schema when the session ends. That means it has to be re-created when needed; therefore, users need permissions to create tables to be able to use session-based log file tables.
Tables created for session-based log files
Three tables are created: SDE_LOGFILES, SDE_LOGFILE_DATA, and SDE_SESSION<SDE_ID>. SDE_LOGFILE_DATA is not actually used in this case, but it is created automatically. The SDE_LOGFILES table stores information about the selection set plus a session tag <SDE_ID> that is appended to the name of the SDE_SESSION table. The SDE_SESSION table stores the feature identifier for the selected set and the log file ID.
The SDE_LOGFILES and SDE_LOGFILE_DATA tables remain in the geodatabase. The SDE_LOGFILES table is truncated when the connecting application disconnects. The SDE_SESSION<SDE_ID> table is truncated when the connecting application deletes the log files, and the table is dropped when the session disconnects.
Permissions to use session-based log files
Session-based log files are owned by the user who started the connecting session. This means users need privileges to create the necessary database objects.
All users require the following permissions to use session-based log files in PostgreSQL:
- CONNECT
- AUTHORIZATION on the user's own schema (AUTHORIZATION automatically confers USAGE and CREATE privileges)
Optional storage control for session-based log file tables and indexes
There are several parameters under the LOGFILE_DEFAULTS keyword of the sde_dbtune table that control how or where log file tables are stored in the database. You do not have to set these to use session-based log files, but you can set them if you want to alter how the SDE_LOGFILES, SDE_LOGFILE_DATA, and SDE_SESSION tables and indexes are stored in the database.
For PostgreSQL, the following parameters are used:
- LD_INDEX_ALL
- LD_STORAGE
- LF_INDEX_ID
- LF_INDEX_NAME
- LF_STORAGE
- SESSION_INDEX
- SESSION_STORAGE
See PostgreSQL configuration parameters for explanations of these parameters.
Pools of log files owned by the sde user
The geodatabase administrator can create a pool of session-based log files that can be checked out and used by other users. Shared log files cannot be checked out from a log file pool.
Using a pool of log files avoids having to grant users the permissions necessary to create objects in the database.
When to use log file pools
You would use a pool of log files if you cannot give users the ability to create log file tables in their own schemas. Users still need to have permissions to create a session or connect to the database, though.
When you might not want to use log file pools
Overall, using pools of log files requires slightly more maintenance because you must estimate the number of necessary log file tables, and you might find yourself adjusting the size of the pool or the number of pools used. Keep in mind that a large log file pool or a large number of log file pools can have a negative impact on performance.
Tables created for log file pools
You specify the number of SDE_LOGPOOL_<table_ID> tables that the Configure Geodatabase Logfile Tables creates in the sde user's schema. For example, if you specify 5 tables, the following tables are created in the sde schema:
- SDE_LOGPOOL_1
- SDE_LOGPOOL_2
- SDE_LOGPOOL_3
- SDE_LOGPOOL_4
- SDE_LOGPOOL_5
An additional table in the sde schema, SDE_LOGFILE_POOL, records the SDE_ID for the session and a table ID. The <table_ID> in the name of the SDE_LOGPOOL table corresponds to the value in the table_ID column of the SDE_LOGFILE_POOL table.
Each session that needs a log file table adds one record to the SDE_LOGFILE_POOL table and the session is allocated to one of the SDE_LOGPOOL_<table_ID> tables. If additional log files are created by the same session—for example, a second selection set of 300 records is created in one ArcMap session—the new log files (selection set) are added to the same SDE_LOGPOOL table.
When log files are cleared, the SDE_LOGPOOL table that is checked out to the session is truncated. For example, if the second selection set in the ArcMap session is cleared, the 300 records are removed from the SDE_LOGPOOL table but the records for the first selection set remain. When the first selection set is cleared, these records are removed from the SDE_LOGPOOL table.
As a log file (selection set) is cleared, the corresponding SDE_LOGPOOL table is truncated.
Permissions required for log file pools
To use the log file tables in the pool, users only require the ability to connect to the database and use the objects in the sde user's schema. Specifically, the following permissions are required:
- CONNECT
- USAGE on the user's own schema
Optional storage control for pools of log files
There are only a few parameters under the LOGFILE_DEFAULTS keyword of the sde_dbtune table that control how the SDE_LOGPOOL<SDE_ID> tables and their indexes are stored.
Enterprise geodatabases in PostgreSQL use the LD_STORAGE and LD_INDEX_ALL parameters in the sde_dbtune table to set storage for the SDE_LOGPOOL<SDE_ID> tables and their indexes. These two parameters also control the storage of the SDE_LOGFILE_DATA table and index. See PostgreSQL configuration parameters for explanations of these parameters.
You do not have to set these to use pools of log file tables, but you can set them if you want to alter how the SDE_LOGPOOL<SDE_ID> tables and indexes are stored in the database.