Customized options v16

custom_variable_classes

The custom_variable_classes parameter was deprecated in EDB Postgres Advanced Server 9.2. Parameters that previously depended on this parameter no longer require its support. In previous releases of EDB Postgres Advanced Server, custom_variable_classes was required by parameters not normally known to be added by add-on modules, such as procedural languages.

dbms_alert.max_alerts

Parameter type: Integer

Default value: 100

Range: 0 to 500

Minimum scope of effect: Cluster

When value changes take effect: Restart

Required authorization to activate: EDB Postgres Advanced Server service account

Specifies the maximum number of concurrent alerts allowed on a system using the DBMS_ALERTS package.

dbms_pipe.total_message_buffer

Parameter type: Integer

Default value: 30 Kb

Range: 30 Kb to 256 Kb

Minimum scope of effect: Cluster

When value changes take effect: Restart

Required authorization to activate: EDB Postgres Advanced Server service account

Specifies the total size of the buffer used for the DBMS_PIPE package.

index_advisor.enabled

Parameter type: Boolean

Default value: true

Range: {true | false}

Minimum scope of effect: Per session

When value changes take effect: Immediate

Required authorization to activate: Session user

Temporarily suspends Index Advisor in an EDB-PSQL or PSQL session. To use this configuration parameter, the Index Advisor plugin index_advisor must be loaded in the EDB-PSQL or PSQL session.

You can load the Index Advisor plugin as follows:

$ psql -d edb -U enterprisedb
Password for user enterprisedb:
psql (14.0.0)
Type "help" for help.

edb=# LOAD 'index_advisor';
LOAD

Use the SET command to change the parameter setting to control whether Index Advisor generates an alternative query plan:

edb=# SET index_advisor.enabled TO off;
SET
edb=# EXPLAIN SELECT * FROM t WHERE a < 10000;
Output
                     QUERY PLAN

-------------------------------------------------------
 Seq Scan on t (cost=0.00..1693.00 rows=9864 width=8)
   Filter: (a < 10000)
(2 rows)
edb=# SET index_advisor.enabled TO on;
SET
edb=# EXPLAIN SELECT * FROM t WHERE a < 10000;
Output
                      QUERY PLAN
-----------------------------------------------------------------------------
 Seq Scan on t (cost=0.00..1693.00 rows=9864 width=8)
   Filter: (a < 10000)
 Result (cost=0.00..327.88 rows=9864 width=8)
   One-Time Filter: '===[ HYPOTHETICAL PLAN ]==='::text
   -> Index Scan using "<hypothetical-index>:1" on t (cost=0.00..327.88
 rows=9864 width=8)
          Index Cond: (a < 10000)
(6 rows)

edb_sql_protect.enabled

Parameter type: Boolean

Default value: false

Range: {true | false}

Minimum scope of effect: Cluster

When value changes take effect: Reload

Required authorization to activate: EDB Postgres Advanced Server service account

Controls whether SQL/Protect is actively monitoring protected roles by analyzing SQL statements issued by those roles and reacting according to the setting of edb_sql_protect.level. When you're ready to begin monitoring with SQL/Protect, set this parameter to on.

edb_sql_protect.level

Parameter type: Enum

Default value: passive

Range: {learn | passive | active}

Minimum scope of effect: Cluster

When value changes take effect: Reload

Required authorization to activate: EDB Postgres Advanced Server service account

Sets the action taken by SQL/Protect when a SQL statement is issued by a protected role.

You can set this parameter to one of the following values to use learn mode, passive mode, or active mode:

  • learn. Tracks the activities of protected roles and records the relations used by the roles. Use this value when initially configuring SQL/Protect so the expected behaviors of the protected applications are learned.
  • passive. Issues warnings if protected roles are breaking the defined rules but doesn't stop any SQL statements from executing. This is the next step after SQL/Protect learns the expected behavior of the protected roles. This essentially behaves in intrusion detection mode. You can run it in production if you monitor it.
  • active. Stops all invalid statements for a protected role. This behavior acts as a SQL firewall that prevents dangerous queries from running. This is particularly effective against early penetration testing when the attacker is trying to determine the vulnerability point and the type of database behind the application. Not only does SQL/Protect close those vulnerability points, it tracks the blocked queries. This behavior can alert administrators before the attacker finds an alternative method of penetrating the system.

If you're using SQL/Protect for the first time, set edb_sql_protect.level to learn.

edb_sql_protect.max_protected_relations

Parameter type: Integer

Default value: 1024

Range: 1 to 2147483647

Minimum scope of effect: Cluster

When value changes take effect: Restart

Required authorization to activate: EDB Postgres Advanced Server service account

Sets the maximum number of relations that can be protected per role. The total number of protected relations for the server is the number of protected relations times the number of protected roles. Every protected relation consumes space in shared memory. The space for the maximum possible protected relations is reserved during database server startup.

If the server is started when edb_sql_protect.max_protected_relations is set to a value outside of the valid range (for example, a value of 2,147,483,648), then a warning message is logged in the database server log file:

2014-07-18 16:04:12 EDT WARNING: invalid value for parameter
"edb_sql_protect.max_protected_relations": "2147483648"
2014-07-18 16:04:12 EDT HINT: Value exceeds integer range.

The database server starts successfully but with edb_sql_protect.max_protected_relations set to the default value of 1024.

Although the upper range for the parameter is listed as the maximum value for an integer data type, the practical setting depends on how much shared memory is available and the parameter value used during database server startup.

As long as the space required can be reserved in shared memory, the value is acceptable. If the value is such that the space in shared memory can't be reserved, the database server startup fails with an error message like the following:

2014-07-18 15:22:17 EDT FATAL: could not map anonymous shared memory:
Cannot allocate memory
2014-07-18 15:22:17 EDT HINT: This error usually means that PostgreSQL's
request for a shared memory segment exceeded available memory, swap
space or huge pages. To reduce the request size (currently 2070118400
bytes), reduce PostgreSQL's shared memory usage, perhaps by reducing
shared_buffers or max_connections.

In this case, reduce the parameter value until you can start the database server successfully.

edb_sql_protect.max_protected_roles

Parameter type: Integer

Default value: 64

Range: 1 to 2147483647

Minimum scope of effect: Cluster

When value changes take effect: Restart

Required authorization to activate: EDB Postgres Advanced Server service account

Sets the maximum number of roles that can be protected.

Every protected role consumes space in shared memory. The server reserves space for the number of protected roles times the number of protected relations (edb_sql_protect.max_protected_relations). The space for the maximum possible protected roles is reserved during database server startup.

If the database server is started when edb_sql_protect.max_protected_roles is set to a value outside of the valid range (for example, a value of 2,147,483,648), then a warning message is logged in the database server log file:

2014-07-18 16:04:12 EDT WARNING: invalid value for parameter
"edb_sql_protect.max_protected_roles": "2147483648"
2014-07-18 16:04:12 EDT HINT: Value exceeds integer range.

The database server starts successfully but with edb_sql_protect.max_protected_roles set to the default value of 64.

Although the upper range for the parameter is listed as the maximum value for an integer data type, the practical setting depends on how much shared memory is available and the parameter value used during database server startup.

As long as the space required can be reserved in shared memory, the value is acceptable. If the value is such that the space in shared memory can't be reserved, the database server startup fails with an error message such as the following:

2014-07-18 15:22:17 EDT FATAL: could not map anonymous shared memory:
Cannot allocate memory
2014-07-18 15:22:17 EDT HINT: This error usually means that PostgreSQL's
request for a shared memory segment exceeded available memory, swap
space or huge pages. To reduce the request size (currently 2070118400
bytes), reduce PostgreSQL's shared memory usage, perhaps by reducing
shared_buffers or max_connections.

In this cases, reduce the parameter value until you can start the database server successfully.

edb_sql_protect.max_queries_to_save

Parameter type: Integer

Default value: 5000

Range: 100 to 2147483647

Minimum scope of effect: Cluster

When value changes take effect: Restart

Required authorization to activate: EDB Postgres Advanced Server service account

Sets the maximum number of offending queries to save in the view edb_sql_protect_queries.

Every saved query consumes space in shared memory. The space for the maximum possible queries that can be saved is reserved during database server startup.

If the database server is started when edb_sql_protect.max_queries_to_save is set to a value outside of the valid range (for example, a value of 10), then a warning message is logged in the database server log file:

2014-07-18 13:05:31 EDT WARNING:  10 is outside the valid range for
parameter "edb_sql_protect.max_queries_to_save" (100 .. 2147483647)

The database server starts successfully but with edb_sql_protect.max_queries_to_save set to the default value of 5000.

Although the upper range for the parameter is listed as the maximum value for an integer data type, the practical setting depends on how much shared memory is available and the parameter value used during database server startup.

As long as the space required can be reserved in shared memory, the value is acceptable. If the value is such that the space in shared memory can't be reserved, the database server startup fails with an error message like the following:

2014-07-18 15:22:17 EDT FATAL: could not map anonymous shared memory:
Cannot allocate memory
2014-07-18 15:22:17 EDT HINT: This error usually means that PostgreSQL's
request for a shared memory segment exceeded available memory, swap
space or huge pages. To reduce the request size (currently 2070118400
bytes), reduce PostgreSQL's shared memory usage, perhaps by reducing
shared_buffers or max_connections.

In this case, reduce the parameter value until you can start the database server successfully.

edb_wait_states.directory

Parameter type: String

Default value: edb_wait_states

Range: n/a

Minimum scope of effect: Cluster

When value changes take effect: Restart

Required authorization to activate: EDB Postgres Advanced Server service account

Sets the directory path where the EDB wait states log files are stored. Use a full, absolute path, not a relative path. However, the default setting is edb_wait_states, which makes $PGDATA/edb_wait_states the default directory location. See EDB Wait States for more information.

edb_wait_states.retention_period

Parameter type: Integer

Default value: 604800s

Range: 86400s to 2147483647s

Minimum scope of effect: Cluster

When value changes take effect: Reload

Required authorization to activate: EDB Postgres Advanced Server service account

Sets the time to wait before deleting the log files for EDB wait states. The default is 604,800 seconds, which is 7 days. See EDB Wait States for more information.

edb_wait_states.sampling_interval

Parameter type: Integer

Default value: 1s

Range: 1s to 2147483647s

Minimum scope of effect: Cluster

When value changes take effect: Reload

Required authorization to activate: EDB Postgres Advanced Server service account

Sets the timing interval between two sampling cycles for EDB wait states. The default setting is 1 second. See EDB Wait States for more information.

edbldr.empty_csv_field

Parameter type: Enum

Default value: NULL

Range: {NULL | empty_string | pgsql}

Minimum scope of effect: Per session

When value changes take effect: Immediate

Required authorization to activate: Session user

Use the edbldr.empty_csv_field parameter to specify how EDB*Loader treats an empty string. The table shows the valid values for the edbldr.empty_csv_field parameter.

Parameter settingEDB*Loader behavior
NULLAn empty field is treated as NULL.
empty_stringAn empty field is treated as a string of length zero.
pgsqlAn empty field is treated as a NULL if it doesn't contain quotes and as an empty string if it contains quotes.

For more information about the edbldr.empty_csv_field parameter in EDB*Loader, see Tools, utilities, and components.

utl_encode.uudecode_redwood

Parameter type: Boolean

Default value: false

Range: {true | false}

Minimum scope of effect: Per session

When value changes take effect: Immediate

Required authorization to activate: Session user

When set to TRUE, EDB Postgres Advanced Server’s UTL_ENCODE.UUDECODE function can decode uuencoded data that was created by the Oracle implementation of the UTL_ENCODE.UUENCODE function.

When set to FALSE, EDB Postgres Advanced Server’s UTL_ENCODE.UUDECODE function can decode uuencoded data created by EDB Postgres Advanced Server’s UTL_ENCODE.UUENCODE function.

utl_file.umask

Parameter type: String

Default value: 0077

Range: Octal digits for umask settings

Minimum scope of effect: Per session

When value changes take effect: Immediate

Required authorization to activate: Session user

The utl_file.umask parameter sets the file mode creation mask in a manner similar to the Linux umask command. This is for use only within the EDB Postgres Advanced Server UTL_FILE package.

Note

The utl_file.umask parameter isn't supported on Windows systems.

The value specified for utl_file.umask is a three- or four-character octal string that's valid for the Linux umask command. The setting determines the permissions on files created by the UTL_FILE functions and procedures.

The following shows the results of the default utl_file.umask setting of 0077. All permissions are denied on users belonging to the enterprisedb group as well as all other users. Only the user enterprisedb has read and write permissions on the file.

-rw------- 1 enterprisedb enterprisedb 21 Jul 24 16:08 utlfile