Log file table configuration options for geodatabases in PostgreSQL

There are four different ArcSDE log file options: shared, session-based, stand-alone, and pools of session-based or stand-alone log files. Each is described in its own section in this topic.

In most cases, the default ArcSDE log file configuration for your database management system (DBMS) should be sufficient and is the recommended setting.

For PostgreSQL, the default log file configuration is shared ArcSDE log files.

Log file options are set using specific parameters in the sde_server_config and sde_dbtune tables.

Parameters in these tables are altered using the sdeconfig and sdedbtune commands, respectively. Syntax for these parameters can be found in the ArcSDE Administration Command Reference provided with the ArcSDE component of ArcGIS Server at the Enterprise level.

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 you have a large number of concurrent sessions and each session connects using an individual DBMS user account (which is the recommended way for making connections to the geodatabase).

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 using an ArcIMS service that generates multiple connections with the same login. This can lead to contention and 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. For ArcGIS Desktop, this threshold is 100 records.

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 user's session ends. Both SDE_LOGFILE_DATA and SDE_LOGFILES remain in the user's schema.

Settings to use shared log files

The following settings are needed in the SERVER_CONFIG table to create shared log file tables:

• MAXSTANDALONELOGS = 0
• ALLOWSESSIONLOGFILE = FALSE
• LOGFILEPOOLSIZE = 0

Settings to control storage of 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 have 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 DBTUNE configuration parameters for details.

Required user permissions to use shared log files

Since the log file tables are owned by the connecting user, users must be granted privileges that allow them to create the required data objects, such as tables. This is required even if the user has read-only access to the geodatabase or ArcSDE is set to be read-only. If these privileges are not granted, users receive an error message the first time they create a selection set larger than the threshold size for that particular client application. However, once the SDE_LOGFILES and SDE_LOGFILE_DATA tables are created for a user, the ArcSDE administrator can revoke the privileges.

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 in the city's ArcSDE geodatabase, he needs to be able to create tables in the geodatabase.

Rather than grant Ian permission to create tables indefinitely, the ArcSDE administrator decides to log in to the geodatabase as Ian, make a selection that exceeds the selection threshold, then revoke Ian's privilege to create tables.

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

You create a schema for most PostgreSQL users and grant the user AUTHORIZATION on the schema, which automatically confers USAGE and CREATE permissions on the schema. However, for users to truly have read-only permissions, you cannot grant AUTHORIZATION to them on their schemas. Instead, you need to grant each read-only user USAGE and CREATE permissions on the schema. You can revoke the user's CREATE permission from the schema once the log file tables have been created in the user's schema. The USAGE privilege must remain so the user can utilize the log file tables in the future.

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.

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.

Settings to use session-based log files (nonpooled)

In the sde_server_config table, the following settings are needed to create session-based log file tables that are not owned by the ArcSDE administrator:

• ALLOWSESSIONLOGFILE = TRUE
• MAXSTANDALONELOGS = 0
• LOGFILEPOOLSIZE = 0

Settings to control storage of 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 DBTUNE configuration parameters for explanations of these parameters.

Required user 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
• USAGE on the user's own schema
• CREATE on the user's own schema

You create a schema for most PostgreSQL users and grant the user AUTHORIZATION on the schema, which automatically confers USAGE and CREATE permissions on the schema. However, for users to truly have read-only permissions, you cannot grant AUTHORIZATION to them on their schemas. Instead, you need to grant each read-only user USAGE and CREATE permissions on the schema. You can revoke the user's CREATE permission from the schema once the log file tables have been created in the user's schema. The USAGE privilege must remain so the user can utilize the log file tables in the future.

Stand-alone log files

Stand-alone log files are created by a session for each selection set the application needs to store.

Stand-alone log files generate the largest number of tables of all the log file options. Keep in mind that you need to configure enough space to store all these log file tables. The DBTUNE parameters SESSION_STORAGE and SESSION_INDEX allocate space for the tables and indexes of stand-alone log files in the majority of DBMSs.

When to use stand-alone log files

If you are not concerned with granting permissions to all database users to create tables in the geodatabase, you can use stand-alone log file tables.

One advantage of stand-alone log file tables is that when a selection set is cleared, the SDE_LOGDATA table that held that selection is truncated rather than deleted. Truncating can be performed more rapidly than a delete operation because no internal Structured Query Language (SQL) statement has to be issued. However, there are not many instances for which this gain in performance would outweigh the cost of creating and storing individual log file tables for each layer.

When you might not want to use stand-alone log files

If you have read-only users who connect to the database, you cannot use stand-alone log files. The SDE_LOGDATA<SDE_ID> tables are dropped from the user's schema when the connection is terminated. They have to be re-created each time the selection threshold for a layer is passed, so you cannot remove CREATE permissions from users if they are going to connect to the geodatabase.

Tables created for stand-alone log files

For each selection set above the selection threshold made by a session, a new SDE_LOGDATA<SDE_ID> table is created for each layer. This eliminates contention for the SDE_LOGDATA table. However, since a new table is created for each selection set and dropped when the session disconnects, a large number of CREATE TABLE and DROP TABLE SQL statements are generated.

The SDE_LOGFILES and SDE_LOGFILE_DATA tables are created per connection in the user's schema. The SDE_LOGFILES table stores the selection set properties, but the SDE_LOGFILE_DATA table is not used.

When the selection set is no longer needed for the layers, the SDE_LOGDATA<SDE_ID> tables are truncated. The SDE_LOGDATA<SDE_ID> tables are dropped when the session disconnects. The SDE_LOGFILES and SDE_LOGFILE_DATA tables remain in the user's schema even after the user disconnects; however, the SDE_LOGFILES table is truncated.

Settings required to use stand-alone log files (nonpooled)

The parameter in the sde_server_config table that specifies the number of stand-alone log files that can be created is MAXSTANDALONELOGS. The default setting for MAXSTANDALONELOGS is 0, so if you want to use stand-alone log files, you must set the number of MAXSTANDALONELOGS to the number of stand-alone log files you want each user to be able to create.

The following settings are needed to create stand-alone log file tables that are not owned by the ArcSDE administrator:

• MAXSTANDALONELOGS = <Maximum number of SDE_LOGDATA<SDE_ID> tables any user can create>
• LOGFILEPOOLSIZE = 0

Stand-alone log files are used until the session's quota—defined by the MAXSTANDALONELOGS server configuration parameter—is exhausted. When the user runs out of stand-alone log files—in other words, if the application needs to simultaneously create more selection sets (log files) than MAXSTANDALONELOGS allows—ArcSDE will attempt to create session-based log files but only if ALLOWSESSIONLOGFILE is set to TRUE. If it can't create a session-based log file, it tries to create a shared log file. If a shared log file can't be created and the stand-alone log files are used up, ArcSDE returns an error.

See In what order do ArcSDE log files get used? for examples of the order in which log file types are used.

Settings to control storage of stand-alone 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 stand-alone log files, but you can set them if you want to alter how the SDE_LOGFILES, SDE_LOGFILE_DATA, and SDE_LOGDATA 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 DBTUNE configuration parameters for explanations of these parameters.

Required user permissions to use stand-alone log files (nonpooled)

User accounts using stand-alone log file tables have to be able to create the necessary database objects. Unlike with shared log files, you cannot revoke a user's privileges after the log file tables are created because a new user-owned table per layer is created each time a session creates a large enough selection.

All users require the following permissions to use stand-alone log file tables in PostgreSQL:

• CONNECT
• USAGE on the user's own schema
• CREATE on the user's own schema

You create a schema for most PostgreSQL users and grant the user AUTHORIZATION on the schema, which automatically confers USAGE and CREATE permissions on the schema. However, for users to truly have read-only permissions, you cannot grant AUTHORIZATION to them on their schemas. Instead, you need to grant each read-only user USAGE and CREATE permissions on the schema. You can revoke the user's CREATE permission from the schema once the log file tables have been created in the user's schema. The USAGE privilege must remain so the user can utilize the log file tables in the future.

The CONNECT privilege is granted by default to all users; therefore, you only need to explicitly grant this permission if you have revoked it from PUBLIC.

Pools of log files owned by the ArcSDE administrator

The ArcSDE administrator can create a pool of log files that can be checked out and used by other users. These can be either session-based or stand-alone log files. Shared log files cannot be checked out from an ArcSDE log file pool.

Using a pool of ArcSDE log files avoids having to grant users the permissions necessary to create objects in the database.

When to use pools of ArcSDE administrator-owned log files

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.

It is a more efficient use of pool resources to use session-based log files in the pool because session-based log files write multiple selection sets to a single table; whereas, stand-alone log files use one table for each eligible selection set.

When you might not want to use pools of ArcSDE administrator-owned log files

Overall, using pools of log files requires slightly more maintenance because you must estimate the number of necessary log file tables, and you may 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 pools of ArcSDE administrator-owned log files

The value set for the LOGFILEPOOLSIZE parameter in the sde_server_config table determines the number of SDE_LOGPOOL_<table_ID> tables created in the ArcSDE administrative user's schema. For example, if you set LOGFILEPOOLSIZE to 5, the following tables are created in the schema of the ArcSDE administrator:

• SDE_LOGPOOL_1
• SDE_LOGPOOL_2
• SDE_LOGPOOL_3
• SDE_LOGPOOL_4
• SDE_LOGPOOL_5

An additional table in the ArcSDE administrator's schema, SDE_LOGFILE_POOL, records the SDE_ID for the ArcSDE 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.

If you use a pool of session-based log files, each session that creates a selection exceeding the selection threshold 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.

If you use a pool of stand-alone log files, each log file (selection set of the required size) creates a new record in the SDE_LOGFILE_POOL table and uses one of the SDE_LOGPOOL tables. For example, if in a single ArcMap session, you selected (1) from a feature class that stored information about businesses, all the businesses licensed to serve food and (2) from a feature class that stored storm drain information, all catch basins located within a kilometer of a business that served food, there would be two records added to the SDE_LOGFILE_POOL table: one for the selection set of businesses and one for the selection set of catch basins. Each selection set would be assigned its own SDE_LOGPOOL table.

As a log file (selection set) is cleared, the corresponding SDE_LOGPOOL table is truncated.

Settings required to use ArcSDE administrator-owned pools of log files

The settings in the sde_server_config table that specifically affect pools of log files are LOGFILEPOOLSIZE and HOLDLOGPOOLTABLES.

As mentioned in the previous section, to create a pool of log files, set the configuration parameter LOGFILEPOOLSIZE to the number of log files (in other words, the number of SDE_LOGPOOL tables) that you determine need to be created. This number should reflect the number of sessions that will connect to your server in addition to the stand-alone log files, if allowed.

To calculate the total number of log files you should set for the log file pool, use the following formulas:

• If session log files are allowed but not stand-alone log files

  LOGFILEPOOLSIZE = total sessions expected

  For example, if MAXSTANDALONELOGS is set to 0, ALLOWSESSIONLOGFILE is set to TRUE, and you expect no more than 30 connections to the geodatabase at any one time, set LOGFILEPOOLSIZE to 30.

• If stand-alone log files are allowed but not session log files

  LOGFILEPOOLSIZE = MAXSTANDALONELOGS * total sessions expected

  For instance, if MAXSTANDALONELOGS is set to 5, ALLOWSESSIONLOGFILE is set to FALSE, and you estimate no more than 10 connections will be made to the geodatabase at any one time, set LOGFILEPOOLSIZE to 50.LOGFILEPOOLSIZE = 5 * 10.

• If both stand-alone log files and session log files are allowed

  LOGFILEPOOLSIZE = (MAXSTANDALONELOGS + 1) * total sessions expected

  For instance, if MAXSTANDALONELOGS is set to 8, ALLOWSESSIONLOGFILE is set to true, and you estimate there will be no more than 20 connections to the database at any one time, you would set LOGFILEPOOLSIZE to 180.LOGFILEPOOLSIZE = (8 + 1) * 20.

If the pool is exhausted and another log file table is needed, ArcSDE will attempt to create it in the user's schema. If the log file table cannot be created in the user's schema, an error is returned.

The SDE_LOGPOOL_<table_ID> tables are created or dropped whenever the LOGFILEPOOLSIZE parameter is changed. In the previous example, when LOGFILEPOOLSIZE is set to 180, 180 SDE_LOGPOOL_<table_ID> tables are created. If you change the LOGFILEPOOLSIZE parameter to 100, 80 of those tables will be dropped.

The other log file pool parameter, HOLDLOGPOOLTABLES, determines when an SDE_LOGPOOL table gets returned to the pool and can be used by other users. If HOLDLOGPOOLTABLES is set to TRUE (the default value), records remain in the SDE_LOGFILE_POOL table and SDE_LOGPOOL tables stay locked until the connecting session is terminated. If HOLDLOGPOOLTABLES is set to FALSE, the log file tables are released and the SDE_LOGFILE_POOL table is truncated whenever the selection set is no longer needed. This behavior is the same for pools of stand-alone and session-based log files.

Settings to control storage of ArcSDE administrator-owned pools of log file tables and indexes

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.

ArcSDE for PostgreSQL uses 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 DBTUNE 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.

Required user permissions to use ArcSDE administrator-owned pools of log files

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 ArcSDE administrator's schema.

To use pools of log files in PostgreSQL, the following permissions are required:

• CONNECT
• USAGE on the user's own schema

Note:

If your PostgreSQL database is used exclusively for your geodatabase, you will usually grant USAGE permission on the sde schema to PUBLIC to avoid having to grant it to every individual role or group. USAGE on the sde schema is necessary for users to access the geodatabase.

Note:

The CONNECT privilege is granted by default to all users; therefore, you only need to explicitly grant this permission if you have revoked it from PUBLIC.