Configuration Professional

The PostgreSQL DatabaseEngine can be configured both on the Engine and Workarea levels in various ways. The Xbase++ function DbeInfo() is used to retrieve and/or configure the current DatabaseEngine which has been defined with the function DbeSetDefault(). The function DbInfo() is used to retrieve and/or configure settings of the database object created by the DatabaseEngine to manage the tables open in the current workarea.

The #define values used by the PostgreSQL DatabaseEngine do not interfere with existing DbeInfo() settings introduced by the DBFDBE, FOXDBE, NTXDBE and CDXDBE DatabaseEngines. The idea behind this feature is to allow existing DatabaseEngine configurations to exist without changing the behavior of the PostgreSQL DatabaseEngine. In fact, the PostgreSQL DatabaseEngine saves any configuration attempts for the flat-file DatabaseEngines and will return the configured value if requested.

The configuration options of the DBE are grouped into three different areas. The settings with the PGDBE_LOG_ prefix are designed to control logging, tracing and profiling of the client/server communication between the DatabaseEngine and the PostgreSQL server. Settings with the PGDBE_ISAM_ prefix are used to control the behavior of the DatabaseEngine in the context of the ISAM emulation - its ability to behave like the DBFDBE, FOXDBE, NTXDBE or CDXDBE engines. Settings with the PGDBE_SQL_ prefix are strictly related to the behavior of the DatabaseEngine if Universal-SQL or Pass-Through-SQL is used.

PGDBE_LOG_STATEMENT_EXECUTION

The PostgreSQL DatabaseEngine allows tracing of SQL statement execution time. By default this is disabled by setting the value to -1. Using a positive number defines a threshold in milliseconds for statements to be logged, and using 0 logs all statements and shows the execution time in milliseconds. For example, the following code will force tracing of all SQL statements for which the execution time is longer than 5 milliseconds:

DbeInfo( COMPONENT_DATA, PGDBE_LOG_STATEMENT_EXECUTION, 5 ) 

Hint: When using the previous example setting with a specific threshold, always keep in mind that each SQL statement sent to the server needs a request and a reply packet, as we are here talking about a client/server architecture. Therefore the traveltime of the data-packet is a constant overhead. The easiest way to determine the traveltime is to ping your server. This will give you the network infrastructure overhead for any client/server operation.

PGDBE_LOG_PLAN

The DatabaseEngine creates various SQL Statements, some of which are simple while others can be rather complex. Examples of complex statements are navigational operations based on an index with a filter and a scope set. In these cases the PostgreSQL DatabaseEngine creates an SQL Statement based on the navigational order and rewritten Xbase++ expressions. In the context of complex expressions, to increase performance for repeated statement execution the PostgreSQL DatabaseEngine creates a plan to avoid repeated parse/compile operations on the Database Server. To log these types of SQL expressions and see what really goes on with the SQL server, this setting can be enabled. By default, logging of plan creation is disabled.

PGDBE_LOG_WRITE_OPERATION

With this setting all INSERT and UPDATE SQL statements created and sent to the server can be traced. This feature is is of particular interest if used together with logging of notices, and can help to analyze problems with your application if complex constraints have been applied to the database used.

PGDBE_LOG_SERVER_NOTICES

The PostgreSQL server generates Notice and Warning messages. Those messages are purely informational and do not indicate an immediate problem with the statement, but may inform the client about other conditions such as low disc space. It should be noted that stored procedure implementars also may have decided that they must deliver additional error information to the client by sending notices.

PGDBE_ISAM_CREATETABLE_ALWAYS

By default the function DbCreate() always creates a new table. If the table already exists the existing table is destroyed (Dropped). Because dropping tables becomes an expensive and dangerous operation, especially with a Database-Server, this setting can be used to turn off that feature. When the setting is False (.F.) the DatabaseEngine will raise an error instead of silently destroying the existing data.

PGDBE_ISAM_KEYVALUE_MAXLEN

This setting controls the maximum length of a key-value in the context of ISAM emulation. The default value is 256 which is compatible with the NTX and CDX DatabaseEngines and their behavior. However, in a situation where it is required to have longer key-values, even though still using ISAM emulation and coding style, this setting can be used to increase the length of a key-value up to a maximum of 32768 bytes.

PGDBE_ISAM_LOCKMODE

Using the PGDBE_ISAM_LOCKMODE define, the locking behavior of the ISAM emulation layer can be configured. In most cases, specifically when it comes to the migration of existing flat-file based applications, no changes are required. However, developers of new applications may consider using optimistic locking, as this will lead to increased performance while still behaving in familiar data access patterns used by the flat-file based DatabaseEngines.

ISAM_LOCKMODE_STANDARD

This is the default locking mode of the PostgreSQL DatabaseEngine when opening a table using the DbUseArea() function or the USE command. Manual locking is required when opening the table shared. If the table is opened exclusive no other workstation can access the table using the isam emulation layer. All Functions of the Xbase++ runtime such as DbRLock(), DbRUnlock() and FLock() are fully supported and behave as is the case with the flat-file isam DatabaseEngines.

ISAM_LOCKMODE_OPTIMISTIC

Enabling this mode makes use of the Multi-Version-Concurrency feature of the PostgreSQL database server. In fact, no explicit locking on the user-level is required anymore. However, lost-update situations may occur. This locking mode leads to read-commited isolation behavior for all database operations.

ISAM_LOCKMODE_BATCHING

Batching mode opens a table in shared mode, does not enforce record level locking, and does allow overriding of record ids. It is in the discipline of the programmer to achieve a consistent result. The Xbase++ Upsize Tool uses the batching mode to open the same table in different threads and perform parallel insert transactions of different chunks of data, and maintains the record id by itself using DbInfo( PGDBO_ISAM_RECORDID, nRecord ).

PGDBE_ISAM_RECCOUNT_CHECK_ALWAYS

By default the ISAM emulation engine does not verify the record count of a table with each USE or DbUseArea() operation. Instead it relies on the data provided by the meta table. However, in situations where data of a ISAM emulating table is added or removed using SQL commands if may become necessary to enable this integrity check.

PGDBE_ISAM_RECCOUNT_LIFETIME

This setting determines the timeframe for how long the number of rows is assumed to be correct. While the ISAM emulation has various strategies to ensure the correctness of its internal record-count, it is still required, in order to support high concurrency usage patterns, to perform a complete row count explicitly from time to time. Use this setting in the event your application's logic relies on the correctness of the RecCount() function in a high concurrency pattern.

PGDBE_ISAM_PAGECACHE_SIZE

To increase performance and avoid SQL Select trashing of the PostgreSQL server, the DatabaseEngine uses an intelligent cache mechanism. More details about that internal caching of the PostgreSQL DatabaseEngine are outlined in the Chapter about ISAM navigational features. The PGDBE_ISAM_PAGECACHE_SIZE define has a default value of -1, meaning the engine adapts the read ahead size dynamically. Configuring this setting explicit gives fine control about the amount of data the ISAM emulating engine does fetch with each SELECT operation. Generally, the higher the bandwidth of your network, the higher this setting should be, and vice versa. This setting shall be changed only for a specific workarea and coding pattern.

PGDBE_ISAM_PAGECACHE_PAGES

As outlined above, there is a maximum number of rows the ISAM cache can hold per page. In addition, there is a maximum number of pages allowed to be cached after that page-reuse begins. The PGDBE_ISAM_PAGECACHE_PAGES setting specifies the maximum number of cache pages per workarea. The default value is 16, which means the ISAM engine will cache a maximum of 1600 rows on the client side. Increasing this setting leads to performance increases, specifically in scenarios where Navigational commands are used to navigate forward and backward in a result-set. A typical scenario is when using a browse object. The number of cache pages has no influence on the correctness of the data, as each cache-page has a dedicated lifetime. Therefore increasing this setting only affects the performance and memory consumption of your application.

PGDBE_ISAM_PAGECACHE_LIFETIME

As outlined in the table above, each cache page of the ISAM emulation layer has a dedicated lifetime. The lifetime is the timeframe in milliseconds that the cached data is assumed to be valid. By default the lifetime is 1000 milliseconds, which is a good compromise between data correctness and performance. In the event you have data with high mutation rates and high concurrency, it may become necessary to reduce the lifetime to increase correctness of your data.

PGDBE_ISAM_ORD_REBUILD_BUFFERSIZE

When rebuilding an order with its tags, the ISAM emulation engine needs to execute UPDATE statements for all rows in a table to update the key columns. Since latency between client and server is critical for the performance of this operation, the ISAM engine sends multiple update statements to the server. This setting allows to specify the buffer size for these SQL UPDATE statements. The default value is 128 kilobyte.

PGDBE_ISAM_ORD_REBUILD_BATCHSIZE

When rebuilding an order with its tags, the first step is to fetch the current records from the server to the client to recalculate the key columns. To increase performance, the engines fetches multiple records at once. The default value is 200, meaning the engine fetches 200 records, calculates the key columns for these records, stores the result on the local disc and then fetches the next 200 records until all rows of the table have been processed.

PGDBE_ISAM_ORD_CHECKANDFIX_KEYS

By default, the ISAM emulation engine does not verify key-value versus field-value consistency. However, in situations where data of a ISAM emulating table is changed or added using SQL commands it may become necessary to enable this integrity check. If enabled, the ISAM emulation engine will check and repair index key consistency whenever an order/index is opened.

PGDIC_LICENSE

Applies a license to the client application by using an license string as shown below. The license string must at least contain a "licensekey" and "licensee" token with their corresponding values as shown below.

01:  DbeInfo( COMPONENT_DICTIONARY,; 
02:           PGDIC_LICENSE,; 
03:           "licensekey=ADADX-DBDBY-WDVBD-ADABF-FGFGT;licensee=MyCompanyName;" ) 

To get the license state, just read the PGDIC_LICENSE setting as shown below:

01:  lIsLicenseValid := DbeInfo( COMPONENT_DICTIONARY, PGDIC_LICENSE ) 

When applying a license, the developer can explicitly restrict the number of clients that can connect simultanously to any number lower than the maximum number of clients allowed by the license. This way, a 5 client-license can be restricted to 3 clients programmatically as shown below. Use the "clients" token in the license string to set a specific value.

01:  DbeInfo( COMPONENT_DICTIONARY,; 
02:           PGDIC_LICENSE,; 
03:           "licensekey=ADADX-DBDBY-WDVBD-ADABF-FGFGT;licensee=MyCompanyName;clients=3" ) 

PGDIC_ALLOWED_CLIENTS

Retrieves the maximum number of allowed clients as defined by the license or by the explicit client count defined using the "clients" token in the license string.

01:  nMaxClientCnt := DbeInfo( COMPONENT_DICTIONARY, PGDIC_ALLOWED_CLIENTS ) 

The :setProperty() method of the DacSession class allows for retrieving and/or setting the properties of the server. To retrieve or configure server properties, #define constants in the PGDBE.CH file must be passed to oDacSession:setProperty(). The DacSession object forwards the defined value to the server and the method returns the server response. The constants listed in the following table are available for use:

PGDIC_DATABASE

This define returns the database name of the connection. Database names can include spaces and are case sensitive. It is therefore good practice to use only lower-case database names. An empty string is returned if the connection is invalid.

PGDIC_USERNAME

The user name of the connection. The name returned is only used for the connection to the database server and should not be related to the user-name used to log onto the workstation. Database users can have specific rights assigned for the type of operation, with respect to a single table or the entire database. An empty string is returned if the connection is invalid.

PGDIC_SERVER_NAME

Returns the hostname of the connected PostgreSQL Database Server. An empty string is returned if the connection is invalid.

PGDIC_SERVER_VERSION

Returns the Version of the PostgreSQL server as a numeric value. Applications can use this to determine the version of the database server to which they are connected. The number is formed by converting the major, minor, and revision numbers into two-decimal-digit numbers and appending them together. For example, version 8.1.5 will be returned as 80105, and version 8.2 will be returned as 80200 (leading zeroes are not shown). Zero is returned if the connection is invalid.

PGDIC_SERVER_PID

Returns a numeric value representing the process identifier of the backend process handling this connection. The backend PID is useful for debugging or profiling purposes. For example, based on the given PID a server administrator can monitor process CPU utilization and decide which application-specific operations are creating the greatest workload.

PGDIC_CONNECTION_STATE

Returns the status of the connection. The status can be one of the states outlined in the table below. However, because the connection is established synchronously only the two states CONNECTION_STATE_OK and CONNECTION_STATE_BAD are visible to the connecting thread. In environments where the underlying TCP/IP stack is considered unreliable, it is a good idea to verify the connection status from time to time to avoid application misbehavior.

In multithreaded applications it is possible to monitor the connection status while the connection is performed. This way all intermediate connection states are visible and can be reported to the end-user.

PGDIC_CONNECTION_RESET

This setting performs the implicit action of resetting the communications channel to the server. It will first try to close the connection to the server and then attempt to reestablish a new connection using all the same parameters previously used. This can be useful for error recovery if a working connection is lost. The following code sample implements a simple monitor thread which checks the connection state and performs an automatic reset if required.

01: PROCEDURE PGMonitorConnection(oSession) 
02:    IF .NOT. oSession:isConnected() 
03:       ThreadObject():setInterval( NIL ) 
04:       RETURN 
05:    ENDIF 
06:    IF(oSession:SetProperty(PGDIC_CONNECTION_STATE)==CONNECTION_STATE_BAD) 
07:      oSession:SetProperty(PGDIC_CONNECTION_RESET, .T.) 
08:    ENDIF 
09: RETURN 
10: 
11: ... 
12: 
13: oSession:connect() 
14: oThread := Thread():New() 
15: oThread:setInterval(10) 
16: oThread:Start( "PGMonitorConnection", oSession ) 
17: 
18: // do whatever you application needs to do... 

PGDIC_TRACE_COMMUNICATION

Enables or disables tracing of the communication between the client and server. To start communication tracing, pass a filename to the property. To stop tracing, send an empty string as the filename. The following code illustrates the usage:

01:  oSession:SetProperty(PGDIC_TRACE_COMMUNICATION, "c:\\my-communication.log" ) 
02:  // perform database operations 
03:  oSession:SetProperty(PGDIC_TRACE_COMMUNICATION, "" ) 
04:  // now view the log file in notepad 

PGDBO_ISAM_RECORDID

This define is used to explicitly retrieve or set the record id of the current record. Retrieving the recordid returns the same value as the functionRecNo(). Setting the record id is only possible if the workarea was opened using ISAM_LOCKMODE_BATCHING for the PGDBE_ISAM_LOCKMODE setting. Only then is it possible to set the record id of the record. For more details see the ISAM_LOCKMODE_BATCHING documentation.

PGDBO_ISAM_CHECKANDFIX_KEYS

By default, a new workarea inherits this setting from the DatabaseEngine configuration PGDBE_ISAM_ORD_CHECKANDFIX_KEYS. To control ISAM index key-value consistency checking and fixing at workarea level, this setting can be used. If enabled, the ISAM emulation engine will check and repair index key consistency whenever an order/index is opened.

PGDBO_ISAM_SQL_INDEX

By default, the PostgreSQL ISAM emulation creates an SQL index for each ISAM index to increase data access performance. When setting the value to .F., no SQL index is created. Disabling automatic SQL index creation does not affect the ability of the ISAM emulation to perform see operations, but does affect seek performance in larger tables.

PGDBO_ISAM_FILTERMODE

Since the PostgreSQL ISAM emulation can handle filters in different ways, the define can be used to determine the current filter mode. DbInfo( PGDBO_ISAM_FILTERMODE ) returns "local-filter", "remote-filter-implicit" or "remote-filter-explicit".

More details about the different filter types can be found here RemoteFilter - LocalFilter

PGORD_ISAM_KIND

This define allows to query the order kind of the active order in the current workarea. As outlined below.

isam These orders maintain 100% backward compatibility with traditional ISAM indexes.

virtual these orders are based on a surrogate key and provide best backward compatibility and performance.

pure-virtual do not have a physical representation. Instead a OrdSetFocus( <column-name> ) does establish them dynamically. Of course, for conditions are not supported.

More details about the different order kinds can be found here SmartOrders - Index/Orders