![]() | Postgres-XL 9.2 Documentation | |||
---|---|---|---|---|
Prev | Fast Backward | Appendix E. Release Notes | Fast Forward | Next |
Release Date: 2014-05-13
Postgres-XL 9.2 is a symetric multi-headed, read and write-scalable shared-nothing massively parallel processing cluster based on PostgreSQL. This release version is based on PostgreSQL 9.2.4.
Currently the only architectures supported are 64 bit Linux operating systems.
This release of Postgres-XL is the first major release and contains the following features, characteristics and enhancements:
Support and extensions for existing features of PostgreSQL in a cluster-wide environment.
SQL extensions and functionalities for management and operations related to a cluster, which add a node-level granularity for cluster operations.
Creation of Global Transaction Manager (GTM), which is a centralized component providing cluster-wide Multi-version Concurrency Control (MVCC).
Creation of mechanisms for Postgres-XL and enhancements of existing internal mechanisms of PostgreSQL, which are related to connection pooling, global transaction management, query planning, rewriting, analyzing and execution.
Restrictions related to existing features in PostgreSQL and currently not supported by Postgres-XL.
The above items are explained in more detail in the sections below.
Postgres-XL builds off another project called Postgres-XC. The original overall architecture and design of Postgres-XC is by Koichi Suzuki, Mason Sharp, Pavan Deolasee, Andrei Martsinchyk and Michael Paquier. Koichi Suzuki is the original project lead. More recent long time contributors include Ashutosh Bapat, Abbas Butt, and Amit Khandekar.
Postgres-XL modifies the architecture to more tightly bond the components together and add massively parallel processing with direct Datanode to Datanode communication for fast performance. In addition, a plan is determined one time, serialized and sent down to the Datanodes instead of sending down SQL statements and reparsing. Also, there have been some changes made to support multi-tenancy, locking down access to pg_catalog tables and providing better pooler management. The key architects and developers are Andrei Martsinchyk, Mason Sharp, Nikhil Sontakke, and Jim Mlodgenski, with Mason Sharp the project lead.
This is an exhaustive list of all the features included in PostgreSQL and currently supported in Postgres-XL.
A list of all the CREATE/ALTER/DROP SQL commands supported appears below. All of the features listed here work like native PostgreSQL. Extensions may have been added to make them usable in a cluster environment.
CREATE USER, ALTER USER, DROP USER, CREATE AGGREGATE, ALTER AGGREGATE, DROP AGGREGATE, CREATE COLLATION, ALTER COLLATION, DROP COLLATION, CREATE CONVERSION, ALTER CONVERSION, DROP CONVERSION, CREATE DATABASE, ALTER DATABASE, DROP DATABASE, ALTER DEFAULT PRIVILEGES, CREATE DOMAIN, ALTER DOMAIN, DROP DOMAIN, CREATE FUNCTION, ALTER FUNCTION, DROP FUNCTION, CREATE GROUP, ALTER GROUP, DROP GROUP, CREATE INDEX, ALTER INDEX, DROP INDEX, CREATE LANGUAGE, ALTER LANGUAGE, DROP LANGUAGE, CREATE OPERATOR CLASS, ALTER OPERATOR CLASS, DROP OPERATOR CLASS, CREATE OPERATOR FAMILY, ALTER OPERATOR FAMILY, DROP OPERATOR FAMILY, CREATE ROLE, ALTER ROLE, DROP ROLE, CREATE SCHEMA, ALTER SCHEMA, DROP SCHEMA, CREATE SEQUENCE, ALTER SEQUENCE, DROP SEQUENCE, CREATE TABLE, ALTER TABLE, DROP TABLE, CREATE TABLESPACE, ALTER TABLESPACE, DROP TABLESPACE, CREATE TEXT SEARCH CONFIGURATION, ALTER TEXT SEARCH CONFIGURATION, DROP TEXT SEARCH CONFIGURATION, CREATE TEXT SEARCH DICTIONARY, ALTER TEXT SEARCH DICTIONARY, DROP TEXT SEARCH DICTIONARY, CREATE TEXT SEARCH PARSER, ALTER TEXT SEARCH PARSER, DROP TEXT SEARCH PARSER, CREATE TEXT SEARCH TEMPLATE, ALTER TEXT SEARCH TEMPLATE, DROP TEXT SEARCH TEMPLATE, CREATE TYPE, ALTER TYPE, DROP TYPE, CREATE USER, ALTER USER, DROP USER, CREATE VIEW, ALTER VIEW, DROP VIEW, CREATE CAST, DROP CAST, CREATE RULE, DROP RULE, CREATE TABLE AS,
List of other supported SQL
ANALYZE, BEGIN, CHECKPOINT, CLOSE, CLUSTER, COMMENT, COMMIT, COMMIT PREPARED, COPY, DEALLOCATE, DECLARE, DELETE, DISCARD, DO, END, EXECUTE, EXPLAIN, FETCH, GRANT, INSERT, LOAD, LOCK, MOVE, PREPARE, PREPARE TRANSACTION, REASSIGN OWNED, REINDEX, RESET, REVOKE, ROLLBACK, ROLLBACK PREPARED, SELECT, SELECT INTO, SET, SET CONSTRAINTS, SET ROLE, SET SESSION AUTHORIZATION, SHOW, START TRANSACTION, TRUNCATE, UPDATE, VACUUM, VALUES
HOT-Standby and streaming replication work as in native PostgreSQL, at the Coordinator and Datanode level.
New configuration parameters in postgresql.conf
pooler_port
Port opened by pooler to which backends can connect to communicate with
min_pool_size
Minimum number of connections in pool
max_pool_size
Maximum number of connections in pool
pool_maintenance_timeout
When to clean up idle connections
remote_query_cost
Cost overhead for setting up a remote query
network_byte_cost
Data shipping cost for query planning
sequence_range
For frequent requests of sequences, get up to this range
max_pool_size
Maximum number of connections in pool
max_pool_size
Maximum number of connections in pool
max_coordinators
Maximum number of Coordinators that can be defined in local node
max_datanodes
Maximum number of Datanodes that can be defined in local node
gtm_host
Host to connect to GTM
gtm_port
Port to connect to GTM
pgxc_node_name
Name of the local node. This is currently set by initdb.
This section lists all the new SQL functionalities and system functions that and can be used to manage a cluster environment.
CREATE NODE, ALTER NODE, DROP NODE
These SQL commands are used to manage cluster node information in catalog pgxc_node.
These commands run only on the local node where they are run, and running them on Datanodes make no sense as this catalog data is used only by Coordinator to identify remote nodes and by connection pooling to get necessary remote connection information.
CREATE NODE GROUP, DROP NODE GROUP
CREATE NODE GROUP and DROP NODE GROUP manage the node groups that can be used when creating a table with the extension TO GROUP of CREATE TABLE.
When specified with an ID, this command allows to register in all of the nodes of the cluster a common and consistent time point to be able to recover all the nodes consistently back to this point. Internally, a barrier is written in the WAL file of all of the nodes.
recovery_target_barrier in recovery.conf can be used to recover a node to a given barrier ID.
CLEAN CONNECTION is a connection pooling utility able to drop connections on chosen node(s) for a given database or/and user.
pgxc_pool_check()
, pgxc_pool_reload()
Those system functions can be used to check or update the data cached in pooler with
pgxc_node. pgxc_pool_check()
checks if the connection information is consistent between pooler cache and catalogs.
pgxc_pool_reload()
updates the connection information cached in pool.
EXECUTE DIRECT can be used to launch a query directly to a given node. Only a single node can be targetted at the same time.
INSERT, UPDATE and DELETE are not authorized.
Utilities are basically forbidden but some are authorized for cluster management purposes.
PAUSE CLUSTER and UNPAUSE CLUSTER can be used to halt other cluster activity except for the session that invoked it. Client sessions are not disconnected, they just are paused until brief maintenance is done, like restarting or moving a Datanode.
List of all the unsupported SQL commands
CREATE EXTENSION, ALTER EXTENSION, DROP EXTENSION, CREATE FOREIGN DATA WRAPPER, ALTER FOREIGN DATA WRAPPER, DROP FOREIGN DATA WRAPPER, CREATE FOREIGN TABLE, ALTER FOREIGN TABLE, DROP FOREIGN TABLE, ALTER LARGE OBJECT, CREATE SERVER, ALTER SERVER, DROP SERVER, CREATE TRIGGER, ALTER TRIGGER, DROP TRIGGER, CREATE USER MAPPING, ALTER USER MAPPING, DROP USER MAPPING, LISTEN, NOTIFY, RELEASE SAVEPOINT, ROLLBACK TO SAVEPOINT, SAVEPOINT, SECURITY LABEL, UNLISTEN,
Table distribution definition cannot be changed.
Distribution key of a table cannot be updated.
Triggers are not supported
Functions are always pushed down to the data nodes. This can be dangerous if a stored function tries to use data that is really on another node! Use with caution; it will only use data local to the node, so updates should not be done. The documentation in this area to make this clearer.
The CLUSTER command is currently broken if used without any arguments. CLUSTER does work for one table at a time, however.
WITH queries work, however, WITH RECURSIVE is currently blocked.
ORDER BY currently cannot be used in subqueries.
Sequences currently cannot be used in temp tables.
CREATE TABLE AS EXECUTE is not supported.
Barriers have no timeout, meaning that if a 2PC transaction is stuck forever, the barrier will be stuck too.
The regression test suite needs further refinement. Some of the listed differences are because of an unsupported feature which manipulates the data set and throws off expected results. In addition, the expected plan output for some should be udpated.