Gecko [ tanishkshah.com ]

Name	Size	Permission	Date
README.PSQL_stack_trace.txt	1.96 KB	-rw-r--r--	2026-04-17 16:36
README.aggregate_filter.md	941 B	-rw-r--r--	2026-04-17 16:36
README.aggregate_tracking	3.88 KB	-rw-r--r--	2026-04-17 16:36
README.alternate_string_quoting.txt	846 B	-rw-r--r--	2026-04-17 16:36
README.autonomous_transactions.txt	1.31 KB	-rw-r--r--	2026-04-17 16:36
README.blob_append.md	4.99 KB	-rw-r--r--	2026-04-17 16:36
README.builtin_functions.txt	29.49 KB	-rw-r--r--	2026-04-17 16:36
README.case	1.71 KB	-rw-r--r--	2026-04-17 16:36
README.coalesce	1.04 KB	-rw-r--r--	2026-04-17 16:36
README.column_type_psql.txt	871 B	-rw-r--r--	2026-04-17 16:36
README.common_table_expressions	4.78 KB	-rw-r--r--	2026-04-17 16:36
README.context_variables	4.66 KB	-rw-r--r--	2026-04-17 16:36
README.context_variables2	8.47 KB	-rw-r--r--	2026-04-17 16:36
README.cumulative_roles.txt	2.27 KB	-rw-r--r--	2026-04-17 16:36
README.current_time	814 B	-rw-r--r--	2026-04-17 16:36
README.cursor_variables.txt	2.75 KB	-rw-r--r--	2026-04-17 16:36
README.cursors	2.43 KB	-rw-r--r--	2026-04-17 16:36
README.data_type_results_of_aggregations.txt	1.56 KB	-rw-r--r--	2026-04-17 16:36
README.data_types	8.31 KB	-rw-r--r--	2026-04-17 16:36
README.db_triggers.txt	1.8 KB	-rw-r--r--	2026-04-17 16:36
README.ddl.txt	22.52 KB	-rw-r--r--	2026-04-17 16:36
README.ddl_access.txt	1.53 KB	-rw-r--r--	2026-04-17 16:36
README.ddl_triggers.txt	11.67 KB	-rw-r--r--	2026-04-17 16:36
README.default_parameters	1.91 KB	-rw-r--r--	2026-04-17 16:36
README.derived_tables.txt	2.69 KB	-rw-r--r--	2026-04-17 16:36
README.distinct	813 B	-rw-r--r--	2026-04-17 16:36
README.domains_psql.txt	933 B	-rw-r--r--	2026-04-17 16:36
README.exception_handling	2.44 KB	-rw-r--r--	2026-04-17 16:36
README.execute_block	1.45 KB	-rw-r--r--	2026-04-17 16:36
README.execute_statement	4.24 KB	-rw-r--r--	2026-04-17 16:36
README.execute_statement2	9.54 KB	-rw-r--r--	2026-04-17 16:36
README.explicit_locks	5.91 KB	-rw-r--r--	2026-04-17 16:36
README.expression_indices	1.3 KB	-rw-r--r--	2026-04-17 16:36
README.external_connections_pool	4.31 KB	-rw-r--r--	2026-04-17 16:36
README.floating_point_types.md	2.33 KB	-rw-r--r--	2026-04-17 16:36
README.global_temporary_tables	3.43 KB	-rw-r--r--	2026-04-17 16:36
README.hex_literals.txt	1.13 KB	-rw-r--r--	2026-04-17 16:36
README.identity_columns.txt	3.24 KB	-rw-r--r--	2026-04-17 16:36
README.iif	541 B	-rw-r--r--	2026-04-17 16:36
README.isc_info_xxx	3.61 KB	-rw-r--r--	2026-04-17 16:36
README.joins.txt	1.23 KB	-rw-r--r--	2026-04-17 16:36
README.keywords	5.26 KB	-rw-r--r--	2026-04-17 16:36
README.leave_labels	1.68 KB	-rw-r--r--	2026-04-17 16:36
README.length	486 B	-rw-r--r--	2026-04-17 16:36
README.linger	1.34 KB	-rw-r--r--	2026-04-17 16:36
README.list	1.04 KB	-rw-r--r--	2026-04-17 16:36
README.management_statements_psql.md	925 B	-rw-r--r--	2026-04-17 16:36
README.mapping.html	14.91 KB	-rw-r--r--	2026-04-17 16:36
README.merge.txt	1.77 KB	-rw-r--r--	2026-04-17 16:36
README.null_value	23 B	-rw-r--r--	2026-04-17 16:36
README.nullif	611 B	-rw-r--r--	2026-04-17 16:36
README.offset_fetch.txt	1.69 KB	-rw-r--r--	2026-04-17 16:36
README.order_by_expressions_nulls	1.67 KB	-rw-r--r--	2026-04-17 16:36
README.packages.txt	5.12 KB	-rw-r--r--	2026-04-17 16:36
README.plan	3.49 KB	-rw-r--r--	2026-04-17 16:36
README.regr_functions.txt	1.14 KB	-rw-r--r--	2026-04-17 16:36
README.returning	2.91 KB	-rw-r--r--	2026-04-17 16:36
README.rows	1.34 KB	-rw-r--r--	2026-04-17 16:36
README.savepoints	4.08 KB	-rw-r--r--	2026-04-17 16:36
README.scrollable_cursors.txt	3.06 KB	-rw-r--r--	2026-04-17 16:36
README.select_expressions	2.18 KB	-rw-r--r--	2026-04-17 16:36
README.sequence_generators	1.68 KB	-rw-r--r--	2026-04-17 16:36
README.set_bind.md	4.49 KB	-rw-r--r--	2026-04-17 16:36
README.set_role	768 B	-rw-r--r--	2026-04-17 16:36
README.set_transaction.txt	1.63 KB	-rw-r--r--	2026-04-17 16:36
README.similar_to.txt	9.53 KB	-rw-r--r--	2026-04-17 16:36
README.sql_security.txt	5.51 KB	-rw-r--r--	2026-04-17 16:36
README.statistical_functions.txt	1.6 KB	-rw-r--r--	2026-04-17 16:36
README.subroutines.txt	4.23 KB	-rw-r--r--	2026-04-17 16:36
README.substring_similar.txt	1.2 KB	-rw-r--r--	2026-04-17 16:36
README.time_zone.md	14.34 KB	-rw-r--r--	2026-04-17 16:36
README.trim	997 B	-rw-r--r--	2026-04-17 16:36
README.universal_triggers	2.77 KB	-rw-r--r--	2026-04-17 16:36
README.update_or_insert	1.38 KB	-rw-r--r--	2026-04-17 16:36
README.user_management	4.43 KB	-rw-r--r--	2026-04-17 16:36
README.view_updates	23 B	-rw-r--r--	2026-04-17 16:36
README.window_functions.md	14.92 KB	-rw-r--r--	2026-04-17 16:36

Rename

The pool of external connections.

To avoid delays when external connections established frequently, external 
data source (EDS) subsystem is supplemented by the pool of external connections.
The pool keeps unused external connections for some time and allows to avoid the 
cost of connecting / disconnecting for frequently used connection strings.

Author:
   Vlad Khorsun <hvlad@users.sourceforge.net>

How pool works:
- every external connection is associated with a pool when created
- pool maintains two lists: idle connections and active connections
- when some connection become unused (i.e. it have no active requests and no 
  active transactions), it is reset and placed into idle list (on successful 
  reset) or closed (if reset failed).
  Connection is reset using ALTER SESSION RESET statement. It is considered 
  successful if no error occurs. Note, if external data source not supported
  ALTER SESSION RESET statement - it is not considered as error and such
  connection will be placed into pool.
- if the pool has reached max. size, the oldest idle connection is closed
- when engine ask to create a new external connection, the pool first looks 
  for candidate at the idle list. The search is based on 4 parameters:
  - connection string,
  - username,
  - password,
  - role.
  The search is case sensitive.
- if suitable connection is found, then it tested if it is still alive
  - if it did not pass the check, it is deleted and the search is repeated
    (the error is not reported to the user)
- found (and alive) connection is moved from idle list to active list and 
  returned to the caller
- if there are several suitable connections, the most recently used is chosen
- if there is no suitable connection, the new one is created (and put into 
  active list)
- when idle connection gets expired (exceeded the lifetime), it is deleted from 
  the pool and closed.

Key characteristics:
- absence of "eternal" external connections
- limited number of inactive (idle) external connections at the pool
- support a quick search among the connections (using 4 parameters above)
- the pool is common for all external databases
- the pool is common for all local connections handled by the given Firebird 
  process

Pool parameters:
- connection life time: the time interval from the moment of the last usage of 
  connection after which it will be forcibly closed
- pool size: the maximum allowed number of idle connections in the pool

Pool management:
  New SQL statement is introduced to manage the pool:

ALTER EXTERNAL CONNECTIONS POOL.

When prepared it is described as DDL statement but have immediate effect: i.e. 
it is executed immediately and completely, not waiting for transaction commit.
Changes applied to the in-memory instance of the pool in current Firebird 
process. Therefore change in one Classic process doesn't affect other Classic
processes. Changes is not persistent and after restart Firebird will use pool 
settings at firebird.conf (see below).

New system privilege "MODIFY_EXT_CONN_POOL" is required to run the statement.

The full syntax is:

- ALTER EXTERNAL CONNECTIONS POOL SET SIZE <int>
  set maximum number of idle connections.

Valid values are from 0 to 1000.
  Value of zero means that pool is disabled. 
  Default value is set in firebird.conf (see below).

- ALTER EXTERNAL CONNECTIONS POOL SET LIFETIME <int> <time_part>,
    where <time_part> is SECOND | MINUTE | HOUR

Set idle connection lifetime, in seconds. 
  Valid values are from 1 SECOND to 24 HOUR.
  Default value is set in firebird.conf (see below).

- ALTER EXTERNAL CONNECTIONS POOL CLEAR ALL
  Closes all idle connections. 
  Disassociates all active connections off the pool (such connections will be 
  closed immediately when gets unused).

- ALTER EXTERNAL CONNECTIONS POOL CLEAR OLDEST
  Closes expired idle connections.

The state of external connections pool could be queried using new context
variables in 'SYSTEM' namespace:
- EXT_CONN_POOL_SIZE			pool size
- EXT_CONN_POOL_LIFETIME		idle connection lifetime, in seconds
- EXT_CONN_POOL_IDLE_COUNT		count of currently inactive connections
- EXT_CONN_POOL_ACTIVE_COUNT	count of active connections, associated with pool

Firebird configuration (firebird.conf) got two new settings related with pool:

- ExtConnPoolSize = 0, pool size, and
- ExtConnPoolLifeTime = 7200, idle connection lifetime, seconds