Server configuration parameters (GUCs)

Sets the application name for a client session. For example, if connecting via psql, this will be set to psql. Setting an application name allows it to be reported in log messages and statistics views.

A shell command used to archive write-ahead log (WAL) files if archive_mode is on or always. The command must return zero exit status if and only if it succeeds.

The command can include placeholders:

Defines whether to send completed WAL file segments to an archive storage by executing archive_command.

Possible values:

Controls whether the array input parser recognizes unquoted NULL as specifying a null array element. By default, this is on, allowing array values containing null values to be entered. When off, treats NULL as specifying a normal array element with the string value 'NULL'.

Maximum time to complete client authentication. This prevents hung clients from occupying a connection indefinitely.

Controls whether a quote mark can be represented by \' in a string literal. The preferred SQL-standard way to represent a quote mark is doubling it (''), but PostgreSQL has historically also accepted \'. However, use of \' creates security risks because in some client character set encodings, there are multibyte characters in which the last byte is numerically equivalent to ASCII \.

Possible values:

Reports the size of a disk block in bytes.

Specifies the Bonjour broadcast name. By default, the host name is used, specified as an empty string. This option is ignored if the server was not compiled with Bonjour support.

When set to off, deactivates validation of the function body string during CREATE FUNCTION. Deactivating validation is occasionally useful to avoid problems such as forward references when restoring function definitions from a dump.

Sets the time interval between optional checks that the client is still connected while running queries, in milliseconds. 0 deactivates connection checks.

Sets the client-side encoding (character set). The default is to use the same encoding as the database. See Supported Character Sets in the PostgreSQL documentation.

Controls which message levels are sent to the client. Each level includes all the levels that follow it. The later the level, the fewer messages are sent. INFO level messages are always sent to the client.

For the Postgres planner, sets the estimate of the cost of processing each index row during an index scan. This is measured as a fraction of the cost of a sequential page fetch.

For the Postgres planner, sets the estimate of the cost of processing each operator in a WHERE clause. This is measured as a fraction of the cost of a sequential page fetch.

For the Postgres planner, sets the estimate of the cost of processing each row during a query. This is measured as a fraction of the cost of a sequential page fetch.

Tells the Postgres planner how many rows are expected to be fetched in a cursor query. This allows the Postgres planner to use this information to optimize the query plan. The default of 1 means all rows will be fetched.

Reports whether checksums are enabled for heap data storage in the database system. Checksums for heap data are enabled or deactivated when the database system is initialized and cannot be changed.

Heap data pages store heap tables, catalog tables, indexes, and database metadata. Append-optimized storage has built-in checksum support that is unrelated to this parameter.

Greengage DB uses checksums to prevent loading data corrupted in the file system into memory managed by database processes. When heap data checksums are enabled, Greengage DB computes and stores checksums on heap data pages when they are written to disk. When a page is retrieved from disk, the checksum is verified. If the verification fails, an error is generated, and the page is not permitted to load into managed memory.

If the ignore_checksum_failure configuration parameter has been set to on, a failed checksum verification generates a warning, but the page is allowed to be loaded into managed memory. If the page is then updated, it is flushed to disk and replicated to the mirror. This can cause data corruption to propagate to the mirror and prevent a complete recovery. Because of the potential for data loss, the ignore_checksum_failure parameter should only be enabled when needed to recover data.

Sets the Greengage DB’s data directories for master and each segment on their respective hosts. Initial values are read from the database configuration file during the cluster initialization. Learn more in Create the database configuration file.

Sets the display format for date and time values, as well as the rules for interpreting ambiguous date input values. This variable contains two independent components: the output format specification and the input/output specification for year/month/day ordering.

Enables per-database user names. If on, you should create users as username@dbname. To create ordinary global users, simply append @ when specifying the user name in the client.

The time to wait on a lock before checking to see if there is a deadlock condition. On a heavily loaded server, you might want to increase this value. Ideally, the setting should exceed your typical transaction time to improve the odds that a lock will be released before the waiter decides to check for deadlock.

Turns on various assertion checks. The default value is off. You can change the default value to on when building Greengage DB from sources by adding the --enable-cassert option.

Indents debug output to produce a more readable but much longer output format. client_min_messages or log_min_messages must be DEBUG1 or lower.

For each query run, prints the resulting parse tree. client_min_messages or log_min_messages must be DEBUG1 or lower.

For each query run, prints the parallel query execution plan. client_min_messages or log_min_messages must be DEBUG1 or lower.

For each query run, prints the preliminary query plan. client_min_messages or log_min_messages must be DEBUG1 or lower.

For each query run, prints the query rewriter output. client_min_messages or log_min_messages must be DEBUG1 or lower.

For each query run, prints the query slice plan. client_min_messages or log_min_messages must be DEBUG1 or lower.

Sets the default statistics sampling target (the number of values that are stored in the list of common values) for table columns that have not had a column-specific target set via ALTER TABLE SET STATISTICS. Larger values may improve the quality of the Postgres planner estimates.

The default tablespace in which to create objects (tables and indexes) when a CREATE command does not explicitly specify a tablespace.

See also Configure default tablespaces.

Selects the text search configuration that is used by those variants of the text search functions that do not have an explicit argument specifying the configuration. The built-in default is pg_catalog.simple, but initdb will initialize the configuration file with a setting that corresponds to the chosen lc_ctype locale, if a configuration matching that locale can be identified.

When running at the SERIALIZABLE isolation level, a deferrable read-only SQL transaction may be delayed before it is allowed to proceed. However, once it begins running, it does not incur any of the overhead required to ensure serializability; so serialization code will have no reason to force it to abort because of concurrent updates, making this option suitable for long-running read-only transactions.

This parameter controls the default deferrable status of each new transaction. It currently has no effect on read-write transactions or those operating at isolation levels lower than SERIALIZABLE. The default is off.

Controls the default isolation level of each new transaction. Greengage DB treats read uncommitted the same way as read committed, and treats serializable the same way as repeatable read.

Controls the default read-only status of each new transaction. A read-only SQL transaction cannot alter non-temporary tables.

If a dynamically loadable module needs to be opened and the file name specified in the CREATE FUNCTION or LOAD command does not have a directory component (the name does not contain a slash), the system will search this path for the required file. The compiled-in PostgreSQL package library directory is substituted for $libdir. This is where the modules provided by the standard PostgreSQL distribution are installed.

Sets the assumption about the effective size of the disk cache that is available to a single query for the Postgres planner. This is factored into estimates of the cost of using an index; a higher value makes it more likely index scans will be used, a lower value makes it more likely sequential scans will be used. When setting this parameter, you should consider both Greengage DB’s shared buffers and the portion of the kernel’s disk cache that will be used for data files (though some data might exist in both places). Take also into account the expected number of concurrent queries on different tables, since they will have to share the available space. This parameter has no effect on the size of shared memory allocated by a Greengage DB server instance, nor does it reserve kernel disk cache; it is used only for estimation purposes.

Set this parameter to a number of block_size blocks (default 32K) with no units; for example, 262144 for 8 GB. You can also directly specify the size of the effective cache; for example, 1GB specifies a size of 32768 blocks. The gpconfig utility and SHOW command display the effective cache size value in units such as GB, MB, or kB.

Activates or deactivates the use of bitmap-scan plan types by the Postgres planner. Note that this is different from a Bitmap Index Scan. A Bitmap Scan means that indexes will be dynamically converted to bitmaps in memory when appropriate, giving faster index performance on complex queries against very large tables. It is used when there are multiple predicates on different indexed columns. Each bitmap per column can be compared to create a final list of selected tuples.

Activates or deactivates the use of group aggregation plan types by the Postgres planner.

Activates or deactivates the use of hash aggregation plan types by the Postgres planner.

Activates or deactivates the use of hash-join plan types by the Postgres planner.

Activates or deactivates the use of index-only scan plans by the Postgres planner.

Activates or deactivates the use of index-scan plan types by the Postgres planner.

Activates or deactivates the use of merge-join plan types by the Postgres planner. Merge join is based on the idea of sorting the left- and right-hand tables into order and then scanning them in parallel. Both data types must be capable of being fully ordered, and the join operator must be one that can only succeed for pairs of values that fall at the "same place" in the sort order. In practice, this means that the join operator must behave like equality.

Activates or deactivates the use of nested-loop join plans by the Postgres planner. Turning this parameter off discourages the Postgres planner from using nested-loop joins by adding 10000000000 (1.0e10) to each Nested Loop node’s cost. Note that this does not suppress nested-loop joins entirely but reduces their use to the minimum if there are other methods available.

Activates or deactivates the use of sequential scan plan types by the Postgres planner. Turning this parameter off discourages the Postgres planner from using sequential scans by adding 10000000000 (1.0e10) to each Seq Scan node’s cost. Note that this does not suppress sequential scans entirely but reduces their use to the minimum if there are other methods available.

Activates or deactivates the use of explicit sort steps by the Postgres planner. Turning this parameter off discourages the Postgres planner from using explicit sorts by adding 10000000000 (1.0e10) to each Sort node’s cost. Note that this does not suppress explicit sorts entirely but reduces their use to the minimum if there are other methods available.

Activates or deactivates the use of tuple identifier (TID) scan plan types by the Postgres planner.

When on, a warning is issued if a backslash (\) appears in an ordinary string literal ('…​' syntax). Escape string syntax (E'…​') should be used for escapes.

Sets the level of memory usage details in EXPLAIN ANALYZE. Possible values (from the lowest to the highest level):

Adjusts the number of digits displayed for floating-point values, including float4, float8, and geometric data types. The parameter value is added to the standard number of digits. The value can be set as high as 3, to include partially-significant digits; this is especially useful for dumping float data that needs to be restored exactly. Can be set to a negative value to suppress unwanted digits.

The Postgres planner will merge subqueries into upper queries if the resulting FROM list would have no more than this many items. Smaller values reduce planning time but may yield inferior query plans.

Enables the selectivity of NULL tests over outer joins.

Specifies the threshold ratio (as a percentage) of hidden rows to total rows that triggers compaction of the segment file when VACUUM is run without the FULL option (a lazy vacuum). If the ratio of hidden rows in a segment file on a segment is less than this threshold, the segment file is not compacted, and a log message is issued.

Determines whether to allow Greengage DB to trigger automatic statistics collection when a table is modified by a non-owner.

The default value is false: Greengage DB does not trigger automatic statistics collection on a table that is updated by a non-owner.

When set to true, Greengage DB will also trigger automatic statistics collection on a table when:

Can only be changed by a superuser.

Specifies the mode for triggering automatic statistics collection with ANALYZE. The on_no_stats value triggers statistics collection for CREATE TABLE AS SELECT, INSERT, or COPY operations on any table that has no existing statistics.

The on_change value triggers statistics collection only when the number of rows affected exceeds the threshold defined by gp_autostats_on_change_threshold. Operations that can trigger automatic statistics collection with on_change are CREATE TABLE AS SELECT, UPDATE, DELETE, INSERT, and COPY.

Default is on_no_stats.

Specifies the mode for triggering automatic statistics collection with ANALYZE for statements in procedural language functions. The none value deactivates statistics collection. The on_no_stats value triggers statistics collection for CREATE TABLE AS SELECT, INSERT, or COPY operations that are run in functions on any table that has no existing statistics.

The on_change value triggers statistics collection only when the number of rows affected exceeds the threshold defined by gp_autostats_on_change_threshold. Operations that can trigger automatic statistics collection with on_change are CREATE TABLE AS SELECT, UPDATE, DELETE, INSERT, and COPY.

Specifies the threshold for automatic statistics collection when gp_autostats_mode is set to on_change. When a triggering table operation affects a number of rows exceeding this threshold, ANALYZE is added and statistics are collected for the table.

When a user starts a session with Greengage DB and issues a query, the system creates groups (or gangs) of worker processes on each segment to do the work. After the work is done, the segment worker processes are destroyed except for a cached number, which is set by this parameter. A lower setting conserves system resources on the segment hosts, but a higher setting may improve performance for users who run intensive queries.

Shows how many commands the master has received from the client. Note that a single SQL command might actually involve more than one command internally, so the counter may increment by more than one for a single query. This counter is shared by all the segment processes working on the command.

Timeout for sending data to unresponsive Greengage DB user clients during query processing. A value of 0 deactivates the timeout, Greengage DB waits indefinitely for a client. When the timeout is reached, the query is canceled with this message:

Local content ID of a segment:

By default (off), Greengage DB calculates segment memory allocation for resource groups using gp_segment_configuration.hostname. When set to on, segment memory is calculated using gp_segment_configuration.address.

Controls table creation when a Greengage DB table is created with a CREATE TABLE or CREATE TABLE AS command that does not contain a DISTRIBUTED BY clause.

For CREATE TABLE, if the value of the parameter is off (the default), and the table creation command does not contain a DISTRIBUTED BY clause, Greengage DB chooses the table distribution key based on the command:

If the value of the parameter is set to on, Greengage DB follows these rules to create a table when the DISTRIBUTED BY clause is not specified:

For a CREATE TABLE AS command that does not contain a distribution clause:

Local content DBID of a segment.

Number of seconds for a Greengage DB process to linger after a fatal internal error.

Sets the default values for table storage options when a table is created with the CREATE TABLE command:

Specify multiple storage option values as a comma-separated list.

You can set the storage options with this parameter instead of specifying the table storage options in the WITH of the CREATE TABLE command. The table storage options that are specified with the CREATE TABLE command override the values specified by this parameter.

Not all combinations of storage option values are valid. If the specified storage options are not valid, an error is returned. See the CREATE TABLE command reference for information about table storage options.

The defaults can be set for a database and a user. If the server configuration parameter is set at different levels, this is the order of precedence, from highest to lowest, of the table storage values when a user logs into a database and creates a table:

The parameter value is not cumulative. For example, if the parameter specifies the appendoptimized and compresstype options for a database, and a user logs in and sets the parameter to specify the value for the orientation option, the appendoptimized and compresstype values set at the database level are ignored.

This example ALTER DATABASE command sets the default orientation and compresstype table storage options for the database mytest:

To create an append-optimized table in the mytest database with column-oriented table and RLE compression, the user needs to specify only appendoptimized=TRUE in the WITH clause.

This example of the gpconfig utility command sets the default storage options for a Greengage DB system. If you set the defaults for multiple table storage options, the value must be enclosed in single quotes.

This example of the gpconfig utility command shows the value of the parameter. The parameter value must be consistent across the Greengage DB master and all segments.

Maximum number of TCP keepalive retransmits from a Query Dispatcher to its Query Executors. It controls the number of consecutive keepalive retransmits that can be lost before a connection between a Query Dispatcher and a Query Executor is considered dead.

Time in seconds between issuing TCP keepalives from a Query Dispatcher to its Query Executors.

Time in seconds between TCP keepalive retransmits from a Query Dispatcher to its Query Executors.

Interval in seconds at which the Distributed Transaction (DTX) recovery process checks for prepared transactions that are not committed or aborted yet. Used to clean up orphaned transactions.

Time threshold (in seconds) for detecting potentially orphaned transactions for the Distributed Transaction (DTX) recovery process. If a prepared transaction is not committed or aborted within this number of seconds, it is subject to recovery actions.

Enables plans that can dynamically eliminate the scanning of partitions.

Activates or deactivates two-phase aggregation to compute a single distinct-qualified aggregate. This applies only to subqueries that include a single distinct-qualified aggregate function.

Activates or deactivates three-phase aggregation and joins to compute distinct-qualified aggregates. This applies only to subqueries that include one or more distinct-qualified aggregate functions.

Activates or deactivates the dispatching of targeted query plans for queries that access data on a single segment. When on, queries that target rows on a single segment will only have their query plan dispatched to that segment (rather than to all segments). This significantly reduces the response time of qualifying queries as there is no interconnect setup involved. Direct dispatch does require more CPU utilization on the master.

Controls the availability of the EXCHANGE DEFAULT PARTITION clause for ALTER TABLE. The default value for the parameter is off. The clause is not available, and Greengage DB returns an error if the clause is specified in an ALTER TABLE command.

If the value is on, Greengage DB returns a warning stating that exchanging the default partition might result in incorrect results due to invalid data in the default partition.

When set to on, the Postgres planner plans single-row inserts so that they are sent directly to the correct segment instance (no motion operation required). This significantly improves the performance of single-row-insert statements.

Controls whether the Greengage DB Global Deadlock Detector is enabled to manage concurrent UPDATE and DELETE operations on heap tables to improve performance. The default is off, the Global Deadlock Detector is deactivated. In this case, Greengage DB runs concurrent update and delete operations on a heap table serially.

If the Global Deadlock Detector is enabled, concurrent updates are permitted, and the Global Deadlock Detector determines when a deadlock exists, and breaks the deadlock by cancelling one or more backend processes associated with the youngest transaction(s) involved.

Activates or deactivates the data collection agents that populate the gpperfmon database.

Activates or deactivates gathering data to a single node to compute distinct-qualified aggregates on grouping extension queries. When this parameter and gp_enable_groupext_distinct_pruning are both enabled, the Postgres planner uses the cheaper plan.

Activates or deactivates three-phase aggregation and join to compute distinct-qualified aggregates on grouping extension queries. Usually, enabling this parameter generates a cheaper query plan that the Postgres planner will use in preference to the existing plan.

Enables the planner’s use of index scans with a limit to optimize the execution of MIN() and MAX() functions on indexed columns.

Activates or deactivates the use of two- or three-stage parallel aggregation plans Postgres planner. This approach applies to any subquery with aggregation. If gp_enable_multiphase_agg is off, then gp_enable_agg_distinct and gp_enable_agg_distinct_pruning are deactivated.

When enabled, the Postgres planner applies query predicates to both table expressions in cases where the tables are joined on their distribution key column(s). Filtering both tables prior to doing the join (when possible) is more efficient.

Enables two-phase duplicate removal for SELECT DISTINCT queries (not SELECT COUNT(DISTINCT)). When enabled, it adds an extra SORT DISTINCT set of plan nodes before motioning. In cases where the distinct operation greatly reduces the number of rows, this extra SORT DISTINCT is much cheaper than the cost of sending the rows across the interconnect.

Enables collection of query metrics. When query metrics collection is enabled, Greengage DB collects metrics during query execution. The default is off.

Enables GPORCA and the Postgres planner to use the estimated size of a table (pg_relation_size() function) if there are no statistics for the table. By default, GPORCA and the planner use a default value to estimate the number of rows if statistics are not available. The default behavior improves query optimization time and reduces resource queue usage in heavy workloads, but can lead to suboptimal plans.

This parameter is ignored for a root partition of a partitioned table. When GPORCA is enabled and the root partition does not have statistics, GPORCA always uses the default value. You can use ANALYZE ROOTPARTITION to collect statistics on the root partition. See Collect statistics via ANALYZE for details.

Enables duplicates removal during sorting.

Enables the LIMIT operation to be performed while sorting. Sorts more efficiently when the plan requires the first limit_number of rows at most.

Activates or deactivates the use of external tables that run OS commands or scripts on the segment hosts (CREATE EXTERNAL TABLE EXECUTE syntax). Must be enabled if using gpperfmon or MapReduce.

Enables filter pushdown when reading data from external tables. If pushdown fails, a query is run without pushing filters to the external data source. Instead, Greengage DB applies the same constraints to the result. See External tables overview for more information about external tables.

Sets the number of segments that will scan external table data during an external table operation. The purpose is to avoid overloading the system with scanning data and taking away resources from other concurrent operations. This only applies to external tables that use the gpfdist:// protocol to access external table data.

Time interval (in seconds) allowed for a disconnected mirror to reconnect before being marked as down in configuration by the fault detection process (ftsprobe).

Specifies the polling interval for the fault detection process (ftsprobe). The ftsprobe process will take approximately this amount of time to detect a segment failure.

Specifies the number of times the fault detection process (ftsprobe) attempts to connect to a segment before reporting segment failure.

Specifies the allowed timeout for the fault detection process (ftsprobe) to establish a connection to a segment before declaring it down.

Specifies the maximum number of times that Greengage DB attempts to establish a primary-mirror replication connection. When this count is exceeded, the fault detection process (ftsprobe) stops retrying and marks the mirror down.

Specifies the executing interval of the global deadlock detector background worker process.

Sets the frequency that the Greengage DB server processes send query execution updates to the data collection agent processes used to populate the gpperfmon database. Query operations executed during this interval are sent through UDP to the segment monitor agents. If you find that an excessive number of UDP packets are dropped during long-running, complex queries, you may consider increasing this value.

Sets the target density of the hash table used by hash join operations. A smaller value will tend to produce larger hash tables, which can increase join performance.

For the parameter value n, Greengage DB stops processing input rows when you import data with the COPY command or from an external table if the first n rows processed contain formatting errors. If a valid row is processed within the first n rows, Greengage DB continues processing input rows.

Setting the value to 0 deactivates this limit.

The SEGMENT REJECT LIMIT clause can also be specified for the COPY command or the external table definition to limit the number of rejected rows.

The amount of shared memory allocated for query metrics, in kilobytes. The default is 5120 and the maximum is 131072. At startup, if gp_enable_query_metrics is set to on, Greengage DB allocates space in shared memory to save query metrics. This memory is organized as a header and a list of slots. The number of slots needed depends on the number of concurrent queries and the number of execution plan nodes per query. The default value, 5120, is based on a Greengage DB system that runs a maximum of about 250 concurrent queries with 120 nodes per query. If the gp_enable_query_metrics configuration parameter is off, or if the slots are exhausted, the metrics are maintained in local memory instead of in shared memory.

Specifies the type of address binding strategy Greengage DB uses for communication between segment host sockets. There are two types: unicast and wildcard (default).

Specifies the size of the Cursor History Table for UDP interconnect. Although it is not usually necessary, you may increase it if running a user-defined function that contains many concurrent cursor queries hangs. The default value is 128.

Specifies the interval, in seconds, to log Greengage DB interconnect debugging messages when the server configuration parameter gp_log_interconnect is set to DEBUG. The default is 10 seconds.

The log messages contain information about the interconnect communication between Greengage DB segment instance worker processes. The information can be helpful when debugging network issues between segment instances.

Specifies the flow control method used for the default Greengage DB UDPIFC interconnect:

Sets the minimum number of retries before reporting a transmit timeout in the interconnect.

Sets the minimum retransmission timeout (RTO) for UDP-based interconnect, in milliseconds.

Sets the proxy ports that Greengage DB uses when the server configuration parameter gp_interconnect_type is set to proxy. Otherwise, this parameter is ignored. The default value is an empty string ("").

When the gp_interconnect_type parameter is set to proxy, you must specify a proxy port for the master, standby master, and all primary and mirror segment instances in this format:

For the master, standby master, and segment instance, the first three fields, db_id, cont_id, and seg_address can be found in the gp_segment_configuration catalog table. The fourth field, port, is the proxy port for the Greengage DB master or a segment instance.

You must specify the value as a single-quoted string. This gpconfig command sets the value for gp_interconnect_proxy_addresses for a cluster of a master and a single segment instance.

For an example of setting gp_interconnect_proxy_addresses, see Configure proxies for interconnect.

Sets the amount of data per-peer to be queued by the Greengage DB interconnect on receivers (when data is received but no space is available to receive it, the data will be dropped, and the transmitter will need to resend it) for the default UDPIFC interconnect. Increasing the depth from its default value will cause the system to use more memory, but may increase performance. It is reasonable to set this value between 1 and 10. Queries with data skew potentially perform better with an increased queue depth. Increasing this may radically increase the amount of memory used by the system.

Specifies the amount of time, in seconds, that Greengage DB waits for the interconnect to complete setup before it times out.

Sets the amount of data per-peer to be queued by the default UDPIFC interconnect on senders. Increasing the depth from its default value will cause the system to use more memory, but may increase performance. Reasonable values for this parameter are between 1 and 4. Increasing the value might radically increase the amount of memory used by the system.

Size of the listening queue for each TCP interconnect socket. Use this value together with the net.core.somaxconn and net.ipv4.tcp_max_syn_backlog kernel parameters to tune network performance.

Sets the timer checking period for UDP interconnect, in milliseconds.

Sets the timer period for UDP interconnect, in milliseconds.

Specifies the amount of time, in seconds, that Greengage DB waits for network transmission of interconnect traffic to complete before it times out.

Sets the networking protocol used for Greengage DB interconnect traffic.

UDPIFC (the default) specifies using UDP with flow control for interconnect traffic. Specify the interconnect flow control method with gp_interconnect_fc_method.

With TCP as the interconnect protocol, Greengage DB has an upper limit of 1000 segment instances — less than that if the query workload involves complex, multi-slice queries.

The PROXY value specifies using the TCP protocol, and when running queries, using a proxy for Greengage DB interconnect communication between the master instance and segment instances and between two segment instances. When this parameter is set to PROXY, you must specify the proxy ports for the master and segment instances with the server configuration parameter gp_interconnect_proxy_addresses. For information about configuring and using proxies with the Greengage DB interconnect, see Configure proxies for interconnect.

When on, maintains the relation locks on all append-optimized leaf partitions involved in a query until the end of a transaction. Turning this parameter on can help avoid relatively rare visibility issues in queries, such as read beyond eof when running concurrently with lazy VACUUM(s) directly on the leaves.

Specifies the format of the server log files. If using the gp_toolkit administrative schema, the log files must be in CSV format.

Controls whether Greengage DB logs statements that cause subtransaction overflow.

Sets the maximum number of distributed transaction log entries to cache in the backend process memory of a segment instance.

The log entries contain information about the state of rows that are being accessed by an SQL statement. The information is used to determine which rows are visible to an SQL transaction when running multiple simultaneous SQL statements in an MVCC environment. Caching distributed transaction log entries locally improves transaction processing speed by improving the performance of the row visibility determination process.

The default value is optimal for a wide variety of SQL processing environments.

Sets the tuple-serialization chunk size for the Greengage DB interconnect in bytes.

Limits the maximum number of levels allowed in a partition hierarchy. By default, the value is 0, which indicates no limit.

Specifies the total maximum uncompressed size of a query execution plan multiplied by the number of Motion operators (slices) in the plan. If the size of the query plan exceeds the value, the query is canceled and an error is returned. A value of 0 means that the size of the plan is not monitored.

You can specify a value in kB (default), MB, or GB. For example, a value of 200 is 200kB. A value of 1GB is the same as 1024MB or 1048576kB.

Sets the limit of shared memory slots used by scan nodes for each backend.

Specifies the maximum number of slices (portions of a query plan that are run on segment instances) that can be generated by a query. If the query generates more than the specified number of slices, Greengage DB returns an error and does not run the query. The default value is 0 — no maximum value.

Running a query that generates a large number of slices might affect Greengage DB performance. For example, a query that contains UNION or UNION ALL operators over several complex views can generate a large number of slices. You can run EXPLAIN ANALYZE` on the query to view slice statistics for the query.

Specifies the maximum number of slices (portions of a query plan that are run on segment instances) that can be generated by a query. If the query generates more than the specified number of slices, Greengage DB returns an error and does not run the query. The default value is 0, no maximum value.

Running a query that generates a large number of slices might affect Greengage DB performance. For example, a query that contains UNION or UNION ALL operators over several complex views can generate a large number of slices. You can run EXPLAIN ANALYZE` on the query to view slice statistics for the query.

Sets the Postgres Planner cost estimate for a Motion operator to transfer a row from one segment to another, measured as a fraction of the cost of a sequential page fetch. If 0, then the value used is two times the value of cpu_tuple_cost.

When a user starts a session with Greengage DB and issues a query, the system creates groups or "gangs" of worker processes on each segment to do the work. gp_print_create_gang_time controls the display of additional information about gang creation, including gang reuse status and the shortest and longest connection establishment time to the segment.

The default value is false, Greengage DB does not display the additional gang creation information.

For single row error handling on COPY and external table SELECT queries, sets the number of rows processed before SEGMENT REJECT LIMIT <n> PERCENT starts calculating.

If enabled, will attempt to dump core if a fatal server error occurs.

Used by a resource group to manage memory allocation to query operators.

When set to auto, Greengage DB uses resource group memory limits to distribute memory across query operators, allocating a fixed size of memory to non-memory-intensive operators and the rest to memory-intensive operators.

When you specify eager_free, Greengage DB distributes memory among operators more optimally by re-allocating memory released by operators that have completed their processing to operators in a later query stage.

Activates or deactivates the enforcement of resource group concurrent transaction limits on Greengage DB resources. The default value is false, which enforces resource group transaction limits.

You can set this parameter to true to bypass resource group concurrent transaction limitations so that a query can run immediately. For example, you can set the parameter to true for a session to run a system catalog query or a similar query that requires a minimal amount of resources.

When you set this parameter to true and run a query, the query runs in this environment:

This parameter can be set for a session. The parameter cannot be set within a transaction or a function.

The default value for this configuration parameter is false, Greengage DB’s resource group scheduler enforces resource group limits on catalog queries. Note that when false and the database has reached the maximum amount of concurrent transactions, the scheduler can block queries that exclusively read from system catalogs.

When set to true, Greengage DB’s resource group scheduler bypasses all queries that fulfill both of the following criteria:

Enables the ceiling enforcement mode when assigning CPU resources to resource groups by percentage. When deactivated, the elastic mode will be used. See CPU percentage: CPU_RATE_LIMIT for details.

Identifies the maximum percentage of system CPU resources to allocate to resource groups on each Greengage DB segment node.

Sets the CPU priority for Greengage DB processes relative to non-Greengage processes when resource groups are enabled. For example, setting this parameter to 10 sets the ratio of allotted CPU resources for Greengage DB processes to non-Greengage processes to 10:1.

Specifies whether Greengage DB recalculates the maximum amount of memory to allocate on a segment host per query running in a resource group. The default value is false, Greengage DB calculates the maximum per-query memory on a segment host based on the memory configuration and the number of primary segments on the master host. When set to true, Greengage DB recalculates the maximum per-query memory on a segment host based on the memory and the number of primary segments configured for that segment host.

Identifies the maximum percentage of system memory resources to allocate to resource groups on each Greengage DB segment node.

For example, suppose your Greengage DB cluster is utilizing the default gp_resource_group_memory_limit of 0.7 and a segment host named sdw1 has 4 primary segments and 4 mirror segments. Greengage DB assigns each primary segment on sdw1 (0.7 / 4 = 0.175) of overall system memory. If failover occurs, and two mirrors on sdw1 fail over to become primary segments, each of the original 4 primaries retains their memory allotment of 0.175, and the two new primary segments are each allotted (0.7 / 6 = 0.116) of system memory. Overall memory allocation on sdw1 in this scenario is 0.7 + (0.116 * 2) = 0.932, which is above the percentage configured in the gp_resource_group_memory_limit setting.

Cancels the pg_resgroup_move_query() function, which moves a running query from one resource group to another, if it waits longer than the specified number of milliseconds.

Cancel a transaction queued in a resource group that waits longer than the specified number of milliseconds. The time limit applies separately to each transaction. The default value is zero; transactions are queued indefinitely and never time out.

Identifies the resource management scheme currently enabled in the Greengage DB cluster: resource groups or resource queues. The default scheme is to use resource queues.

Enables Greengage DB memory management features. The distribution algorithm eager_free takes advantage of the fact that not all operators run at the same time. The query plan is divided into stages and Greengage DB eagerly frees memory allocated to a previous stage at the end of that stage’s execution, then allocates the eagerly freed memory to the new stage.

When set to auto, query memory usage is controlled by statement_mem and resource queue memory limits. When set to none, none of these features are enabled.

Activates or deactivates query prioritization. When this parameter is deactivated, existing priority settings are not evaluated at query run time.

Specifies the number of CPU units allocated to each segment instance on a segment host. If the segment is configured with primary-mirror segment instance pairs, use the number of primary segment instances on the host in the calculation. Include any CPU core that is available to the operating system, including virtual CPU cores, in the total number of available cores.

For example, if a Greengage DB cluster has 10-core segment hosts that are configured with four primary segments, set the value to 2.5 on each segment host (10 divided by 4). A master host typically has only a single running master instance, so set the value on the master and standby master hosts to reflect the usage of all available CPU cores, in this case 10.

Incorrect settings can result in CPU under-utilization or query prioritization not working as designed.

Specifies the interval at which the sweeper process evaluates current CPU usage, in milliseconds. When a new statement becomes active, its priority is evaluated and its CPU share is determined when the next interval is reached.

The role of this server process is set to DISPATCH for master and EXECUTE for segments.

Specifies the minimum size for safe write operations to append-optimized tables in a non-mature file system. When a number of bytes greater than zero is specified, the append-optimized writer adds padding data up to that number in order to prevent data corruption due to file system errors. Each non-mature file system has a known safe write size that must be specified here when using Greengage DB with that type of file system. This is commonly set to a multiple of the extent size of the file system; for example, Linux ext3 is 4096 bytes, so a value of 32768 is commonly used.

Time that the Greengage DB interconnect will try to connect to a segment instance over the network before timing out. Controls the network connection timeout between master and primary segments, and primary to mirror segment replication processes.

Sets the number of primary segment instances for the Postgres Planner to assume in its cost and size estimates. If 0, then the value used is the actual number of primary segments. This variable affects the Postgres Planner’s estimates of the number of rows handled by each sending and receiving process in Motion operators.

Reports the version number of the server as a string. A version modifier argument, such as beta, might be appended to the numeric portion of the version string.

Reports the version number of the server as an integer. The number is guaranteed to always be increasing for each version and can be used for numeric comparisons. The major version is represented as is, the minor and patch versions are zero-padded to always be double-digit wide.

A system assigned ID number for a client session. Starts counting from 1 when the master instance is first started.

If enabled, when a Greengage DB server process (postmaster) is started, it will bind to a CPU.

Enables the use of statistics from child tables when planning queries on the parent table by the Postgres Planner.

When enabled, the Postgres Planner will use the statistics of the referenced column in the parent table when a column is a foreign key reference to another table instead of the statistics of the column itself.

Enable extended pending deletion tracking to avoid accumulation of orphaned files. Disabling this turns off storing relation nodes in shared memory, dumping them to WAL, and removing of files during recovery.

If a database session is idle for longer than the time specified, the session will free system resources (such as shared memory), but remain connected to the database. This allows more concurrent connections to the database at one time.

Sets the amount of memory (in megabytes) that all postgres processes of an active segment instance can consume. If a query causes this limit to be exceeded, memory will not be allocated and the query will fail. Note that this is a local parameter and must be set for every segment in the system (primary and mirrors). When setting the parameter value, specify only the numeric value, for example, 4096 for 4096 MB.

To prevent over-allocation of memory, these calculations can estimate a safe gp_vmem_protect_limit value.

First calculate the value of gp_vmem — the Greengage DB memory available on a host:

where SWAP is the host swap space and RAM is the RAM on the host in GB.

Next, calculate the max_acting_primary_segments value. This is the maximum number of primary segments that can be running on a host when mirror segments are activated due to a failure. For example, with mirrors arranged in a 4-host block with 8 primary segments per host, a failure of a single segment host would activate two or three mirror segments on each remaining host in the failed host’s block. The max_acting_primary_segments value for this configuration is 11 (8 primary segments plus 3 mirrors activated on failure).

This is the calculation for gp_vmem_protect_limit. The value should be converted to MB.

For scenarios where a large number of workfiles are generated, this is the calculation for gp_vmem that accounts for the workfiles.

For information about monitoring and managing workfile usage, see Manage spill files.

Based on the gp_vmem value, you can calculate the value for the vm.overcommit_ratio operating system kernel parameter. This parameter is set when you configure each Greengage DB host.

If a query executor process consumes more than this configured amount (in megabytes), then the process will not be cached for use in subsequent queries after the process completes. Systems with lots of connections or idle processes may want to reduce this number to free more memory on the segments. Note that this is a local parameter and must be set for every segment.

Specifies whether spill files created by hash aggregation or hash join operations on disk are compressed.

If your Greengage DB installation uses Serial ATA (SATA) disk drives, enabling compression might help to avoid overloading the disk subsystem with IO operations.

Learn more about spill files compression in Compression.

The overhead memory limit in kilobytes for all compressed spill files of a single workfile set. Once the limit is reached, the following files will not be compressed. 0 deactivates the limit.

See also Compression.

Sets the maximum number of temporary spill files (also known as workfiles) allowed per query per segment. Spill files are created when running a query that requires more memory than it is allocated. The current query is terminated when the limit is exceeded.

Set the value to 0 to allow an unlimited number of spill files.

Sets the maximum disk size (in kilobytes) an individual query is allowed to use for creating temporary spill files at each segment. The default value is 0, which means a limit is not enforced.

Sets the maximum total disk size (in kilobytes) that all running queries are allowed to use for creating temporary spill files at each segment. The default value is 0, which means a limit is not enforced.

Controls the time (in seconds) that Greengage DB waits before returning an error when Greengage DB is attempting to connect or write to a gpfdist server and gpfdist does not respond. The default value is 300 (5 minutes). A value of 0 deactivates the timeout.

Sets the port number of gpperfmon.

Only has effect if data_checksums is enabled.

Greengage DB uses checksums to prevent loading data that has been corrupted in the file system into memory managed by database processes.

By default, if a checksum verification error occurs when reading a heap data page, Greengage DB generates an error and prevents the page from being loaded into managed memory. When ignore_checksum_failure is set to on and a checksum verify failure occurs, Greengage DB generates a warning and allows the page to be read into managed memory. If the page is then updated, it is saved to disk and replicated to the mirror. If the page header is corrupt, an error is reported even if this option is enabled.

The default setting is off, and it can only be changed by a superuser.

Reports whether PostgreSQL was built with support for 64-bit integer dates and times.

Sets the display format for interval values:

See Date/Time Types in the PostgreSQL documentation for more information.

The Postgres Planner will rewrite explicit JOIN constructs into lists of FROM items whenever a list of no more than this many items in total would result. By default, this variable is set to the same as from_collapse_limit, which is appropriate for most uses. Setting it to 1 prevents any reordering of inner joins. Setting this variable to a value between 1 and from_collapse_limit might be useful to trade off planning time against the quality of the chosen plan (higher values produce better plans).

Sets whether Kerberos user names should be treated case-insensitively. The default is case-sensitive (off).

Sets the location of the Kerberos server key file.

See also GSSAPI authentication based on FreeIPA.

Reports the locale in which sorting of textual data is done. The value is determined when the Greengage DB cluster is initialized.

Reports the locale that determines character classifications. The value is determined when the Greengage DB cluster is initialized.

Sets the language in which messages are displayed. The locales available depends on what was installed with your operating system — use locale -a to list available locales. The default value is inherited from the execution environment of the server. On some systems, this locale category does not exist. Setting this variable will still work, but there will be no effect. Also, there is a chance that no translated messages for the desired language exist. In that case, you will continue to see the English messages.

Sets the locale to use for formatting monetary amounts, for example, with the to_char family of functions. The locales available depend on what was installed with your operating system — use locale -a to list available locales. The default value is inherited from the execution environment of the server.

Sets the locale to use for formatting numbers, for example, with the to_char family of functions. The locales available depends on what was installed with your operating system — use locale -a to list available locales. The default value is inherited from the execution environment of the server.

Sets the locale for formatting date and time values.

Specifies the TCP/IP address(es) on which the server is to listen for connections from client applications — a comma-separated list of host names and/or numeric IP addresses. The special entry * corresponds to all available IP interfaces. If the list is empty, only Unix domain sockets can connect.

Comma-separated list of shared library files to preload at the start of a client session.

Abort any statement that waits longer than the specified number of milliseconds while attempting to acquire a lock on a table, index, row, or other database object. The time limit applies separately to each lock acquisition attempt. The limit applies both to explicit locking requests (such as LOCK TABLE or SELECT FOR UPDATE and to implicitly-acquired locks. If log_min_error_statement is set to ERROR or lower, Greengage DB logs the statement that timed out. A value of zero (the default) turns off this lock wait monitoring.

Unlike statement_timeout, this timeout can only occur while waiting for locks. Note that if statement_timeout is nonzero, it is rather pointless to set lock_timeout to the same or larger value, since the statement timeout would always trigger first.

Greengage DB uses the deadlock_timeout and gp_global_deadlock_detector_period to trigger local and global deadlock detection. Note that if lock_timeout is turned on and set to a value smaller than these deadlock detection timeouts, Greengage DB will abort a statement before it would ever trigger a deadlock check in that session.

Logs information about automatic ANALYZE operations related to gp_autostats_mode and gp_autostats_on_change_threshold.

Sets the minimum execution time (in milliseconds) above which autovacuum actions will be logged. Zero prints all actions. -1 turns autovacuum logging off.

Causes checkpoints and restart points to be logged in the server log. Some statistics are included in the log messages, including the number of buffers written and the time spent writing them.

This outputs a line to the server log detailing each successful connection. Some client programs, like psql, attempt to connect twice while determining if a password is required, so duplicate connection received messages do not always indicate a problem.

This outputs a line in the server log at termination of a client session, and includes the duration of the session.

When set to on, this parameter adds a log message with verbose information about the dispatch of the statement.

Causes the duration of every completed statement that satisfies log_statement to be logged.

Controls the amount of detail written in the server log for each message that is logged.