Postgres-XC Wiki


Enters in this spec the following SQL commands:


Session parameters are necessary in PostgreSQL to be able to configure the session user is on. For example, search_patch helps to define on which schema to refer when looking for relations. Other parameters permit to customize the server time-line or connection timeout.

Spec details

Postgres-XC, being a shared nothing cluster, makes its connections interact with each other thanks to a connection pooling located at Coordinator. This connection pooling is in charge of Coordinator <-> Coordinator and Coordinator <-> Datanode connections.

Once a transaction is finished, connections are sent back to pool and not released to improve cluster performance. This is available if the parameter persistent_connections is set at off.

PostgreSQL supports roughly two types of connection parameters:

  • SET LOCAL, which have no effect if launched outside a transaction block and are reset at the end of a transaction block
  • SET GLOBAL, which are parameters alive as long as user session is on. Once user disconnects from the PostgreSQL instance, those parameters are reset.

In Postgres-XC, management of global and local parameters is made through the pooler, a unique process on each coordinator (having a unique process is important in this spec). In order to do that, two strings have been added to the pooler manager. A pooler manager holds all the information of 1 postmaster child that connects to pooler on coordinator (backend PID, pointers reporing to connections to datanodes and coordinators, database pool of where to get connections).

 typedef struct
     /* Process ID of postmaster child process associated to pool agent */
     int         pid;
     /* communication channel */
     PoolPort    port;
     DatabasePool *pool;
     PGXCNodePoolSlot **dn_connections; /* one for each Datanode */
     PGXCNodePoolSlot **coord_connections; /* one for each Coordinator */
     char       *session_params;
     char       *local_params;
 } PoolAgent;

Basically, when a SET LOCAL command is run from a remote Coordinator, the command string is saved in local_params of pooler agent. When a SET GLOBAL (global parameter) is run on remote Coordinator, the command string is saved in session_params of pooler agent.

If several commands are launched in a same session (or a same transaction block), the list of commands is saved in the string session_params or local_params respecting this format: "SET $command1;SET $command2;...;SET $commandN".

At the moment a SET command is launched, the SET command is not only saved, but also run on all the backends whose connection with remote coordinator is already open. If during a session new connections to some backends are opened, the session parameters saved in session_params and local_params are launched on the newly-opened connection to the backend node.

At the end of a transaction, when postmaster child requests to release the handles to nodes back to pool, the string local_params is reset. At commit phase as remote coordinator was in charge to send the commit command to each opened backend, local parameters have been automatically reset thanks to the COMMIT message, so it is not necessary to send a command "RESET ALL" to each backend. In case only local parameters have been used in a transaction without global parameters, connections are safely sent back to pool.

When a session is over on remote coordinator, pooler is in charge to send to each open backend a "RESET ALL" command to clean up connections of session parameters. Then connections are sent back to pool.

Pooler is always in charge of sending SET commands to open backends. This means that when a remote coordinator receives a SET command, it is sent to pooler, and pooler uses the connection pointer it holds to send the command with PQquery. Then pooler consumes all the messages related to SET. Coordinator waits for a confirmation response when transmitting the SET command to pooler, so as pooler is a unique process SET is launched consistently in the cluster.