PDF page 8
#### 3.1.1 New WAL format {#pg9-5-3-1-1-new-wal-format} XLogRecordBlockHeader structure is newly defined in src/include/access/xlogrecord.h. It is used in the output of the WAL file. #### 3.1.2 WAL compression {#pg9-5-3-1-2-wal-compression} Specifying the parameter wal_compression to "on", the WAL of Full Page Write (during the first update after CHECKPOINT completed) will be compressed and written. WAL pages are compressed by a PGLZ method, which is implemented in pglz_compress functions in the source code src/common/pg_lzcompress.c. Since the default value for the parameter wal_compression is "off", this feature does not work under normal condition. #### 3.1.3 Added System Catalogs {#pg9-5-3-1-3-added-system-catalogs} With the addition of new features, the following system catalogs have been added. **Table 4.** Added system Catalogs | Catalog Name | Description | | --- | --- | | pg_file_settings | Parameter file and setting information | | pg_policy | Row level security policies for tables | | pg_policies | Row level security applied table information | | pg_replication_origin | Unverified | | pg_replication_origin_status | Unverified | | pg_stat_ssl | Connected user information which use SSL | | pg_transform | Unverified | ##### pg_file_settings Catalog {#pg9-5-p8-pg-file-settings-catalog} Information of the loaded parameter file name (postgresql.conf, postgresql.auto.conf, etc) and parameters are stored in this catalog. The entity of this catalog is pg_show_all_file_settings function.PDF page 9
**Table 5.** pg\_file\_settings Catalog | Column Name | Data Type | Description | | --- | --- | --- | | sourcefile | text | Parameter filename (Full-path) | | sourceline | integer | Line number in the parameter file | | seqno | integer | Serial number through the multiple files | | name | text | Parameter name | | setting | text | Parameter value | | applied | boolean | True if the value can be applied successfully | | error | text | If not null, an error message indicating why this entry could not be applied | This catalog has the following features: ##### Every time search is performed for pg_file_settings catalogs, the parameter file is parsed. {#pg9-5-p9-every-time-search-is-performed-for-pg-file-settings-catalogs-the-parameter-file-is-parsed} ##### Requires SUPERUSER privileges to search. {#pg9-5-p9-requires-superuser-privileges-to-search} ##### This catalog also supports 'include' syntax in postgresql.conf file. {#pg9-5-p9-this-catalog-also-supports-include-syntax-in-postgresql-conf-file} **Example 1.** Reload parameter file ```text postgres=# ALTER SYSTEM SET max_connections = 1000 ; ALTER SYSTEM postgres=# SELECT sourcefile, name, setting FROM pg_file_settings WHERE name = 'max_connections' ; sourcefile | name | setting ------------------------------------------+-----------------+--------- /usr/local/pgsql/data/postgresql.auto.conf | max_connections | 1000 (1 row) ``` ##### pg_policy Catalog {#pg9-5-p9-pg-policy-catalog} This catalog provide the information about POLICY objects to be used in the Row Level Security feature. This catalog is created for each database.PDF page 10
**Table 6.** pg\_policy Catalog | Column Name | Data Type | Description | | --- | --- | --- | polname name The name of the policy polrelid oid OID of the table to which the policy is applies polcmd char The command type to which the policy is applied: ##### SELECT = r {#pg9-5-p10-select-r} ##### INSERT = a {#pg9-5-p10-insert-a} ##### UPDATE = w {#pg9-5-p10-update-w} ##### DELETE = d {#pg9-5-p10-delete-d} ##### ALL = * {#pg9-5-p10-all} polroles oid[] Array of role OID to which the policy is applied polqual pg_node_tree Conditions checked in the USING clause polwithcheck pg_node_tree Conditions checked in the WITH CHECK clause ##### pg_policies Catalog {#pg9-5-p10-pg-policies-catalog} In this catalog, information on the table, to which the policy has been applied, is stored. This catalog created for each database. **Table 7.** pg\_policies Catalog | Column Name | Data Type | Description | | --- | --- | --- | | schemaname | name | Schema name of the policy applied table | | tablename | name | Policy applied table name | | policyname | name | POLICY object name | | roles | name[] | Policy target roll | | cmd | text | Policy target command | | qual | text | WHERE clause to check the policy | | with_check | text | WITH check clause | ##### pg_stat_ssl Catalog {#pg9-5-p10-pg-stat-ssl-catalog} This catalog provides the SSL information about sessions that are connected to the instance.PDF page 11
**Table 8.** pg\_stat\_ssl Catalog | Column Name | Data Type | Description | Note | | --- | --- | --- | --- | | pid | integer | Backend process ID | Same as pg_stat_activity.pid | | ssl | boolean | Is SSL connection? | | | version | text | SSL version | | | cipher | text | SSL cipher | | | bits | integer | SSL bit | | | compressionclientdn | boolean | Is compressed? | | | | text | Client DN | | ##### pg_transform Catalog {#pg9-5-p11-pg-transform-catalog} This catalog stores transform information that you created in the CREATE TRANSFORM statement. Details of the CREATE TRANSFORM statement and this catalog are unverified. **Table 9.** pg\_transform Catalog | Column Name | Data Type | Description | | --- | --- | --- | | trftype | oid | OID of the data type to which this transform is applied | | trflang | oid | OID of the language to which this transform is applied | | trffromsql | regproc | OID of the input function which converts the data type | | trftosql | regproc | OID of the output function which converts the data type | #### 3.1.4 Modified Catalogs {#pg9-5-3-1-4-modified-catalogs} The following System catalogs have been changed. **Table 10.** Modified catalogs | pg_settings | Add pending_restart column | | --- | --- | | pg_tables | Add rowsecurity column | | pg_stat_statements | Add some statistic columns (pg_stat_statements Contrib module) | | pg_authid | Add rolbypassrls column, remove rolcatupdate column | Catalog Name Changed pg_replication_slots Add active_pid column ##### pg_replication_slots Catalog {#pg9-5-p11-pg-replication-slots-catalog} Process ID column of wal sender process has been added. This makes it possible of binding to pg_stat_replication catalog.PDF page 12
**Table 11.** Added column to pg\_replication\_slots Catalog | Column | Data Type | Description | | --- | --- | --- | | active_pid | integer | Process ID of wal sender process | Following example shows join of pg_stat_replication and pg_replication_slots catalog, and display the correspondence of host name and the slot name of the slave instance. Since in some cases the slot is not used, LEFT OUTER JOIN clause is used. **Example 2.** Join pg\_stat\_replication Catalog and pg\_replication\_slots Catalog ```text postgres=> SELECT r.client_hostname, r.pid, s.slot_name FROM pg_stat_replication r LEFT OUTER JOIN pg_replication_slots s ON r.pid = s.active_pid ; client_hostname | pid | slot_name -----------------+-------+----------- dbslv1 | 12915 | slot_1 dbslv2 | 12934 | (2 rows) ``` ##### pg_settings Catalog {#pg9-5-p12-pg-settings-catalog} Pending_restart column has been added. In spite of this change, the parameter which wait the restart can be checked. The value of this column becomes true when the parameter file is reloaded after changing the file, or after executing ALTER SYSTEM statement. This value is not changed by executing ALTER SYSTEM statement alone. Once pending_restart column becomes true, it will not be changed if the parameter value will be set to the original value. **Table 12.** Added column to pg\_settings Catalog | Column Name | Data Type | Description | | --- | --- | --- | | pending_restart | boolean | Changed in the configuration file and waiting a restart | ##### pg_stat_statements Catalog {#pg9-5-p12-pg-stat-statements-catalog} In the pg_stat_statements catalog, that is created on registering a Contrib module pg_stat_statements, the columns which contain the execution time of maximum / minimum / average / standard derivation of the SQL statements.PDF page 13
**Table 13.** Added columns to pg\_stat\_statements Catalog | Column Name | Data Type | Description | | --- | --- | --- | | min_time | doubleprecision | Minimum time spent in the statement | | max_time | doubleprecision | Maximum time spent in the statement | | mean_time | doubleprecision | Average time spent in the statement | | stddev_time | doubleprecision | Population standard deviation of time spent in the statement | ##### pg_tables Catalog {#pg9-5-p13-pg-tables-catalog} Into the pg_tables catalog, which provides table information, rowsecurity column has been added. This column indicates whether Row Level Security feature is used or not. This column is true for the table of which has a setting of ENABLE ROW LEVEL SECURITY. It is false in the case of policy settings only. **Table 14.** Added columns to pg\_tables Catalog | Column Name | Data Type | Description | | --- | --- | --- | | rowsecurity | boolean | True if Row Level Security is enabled on the table | #### 3.1.5 Modified Contrib modules {#pg9-5-3-1-5-modified-contrib-modules} In PostgreSQL 9.5, some Contrib module have been changed. Some commands widely used before have been moved from the Contrib module to PostgreSQL bin.PDF page 14
**Table 15.** Changed in Contrib modules | Module Name | Changed | Note | | --- | --- | --- | | hstore_plperlhstore_plpythonltree_plpythontsm_system_rowstsm_system_time | Newly added | | | pg_archivecleanup | Move to PostgreSQL bin | | | pg_test_fsync | Move to PostgreSQL bin | | | pg_test_timing | Move to PostgreSQL bin | | | pg_upgrade | Move to PostgreSQL bin | | | pg_xlogdumppgbench | Move to PostgreSQL bin | | | test_parser | Move to test/modules | Change directory | | test_shm_mq | Move to test/modules | Change directory | | worker_spi | Move to test/modules | Change directory | | pg_stat_statements | Added some features | Add some columns | | pageinspectpgcryptopg_buffercache | Added some features Add function Added some features | Add functions for BRIN index | #### 3.1.6 Interface, API, Hook {#pg9-5-3-1-6-interface-api-hook} Infrastructure for expanding the PostgreSQL has been developed. ##### Parallel processing Infrastructure {#pg9-5-p14-parallel-processing-infrastructure} APIs for performing the parallel processing has been implemented. An explanation of the way to use dynamic shared memory and worker process has been described in the "src/backend/access/transam/README.parallel" file. ##### Custom Scan/Join Interface {#pg9-5-p14-custom-scan-join-interface} Features to call hook functions to replace the execution plan have been provided. The following hooks have been added.PDF page 15
**Table 16.** Added Hook | Hook | Description | Definition | | --- | --- | --- | | set_rel_pathlist_hookset_join_pathlist_hook | Hook function for Custom Scan Hook function for Custom Join | src/include/optimizer/paths.h | #### 3.1.7 OOM Killer support {#pg9-5-3-1-7-oom-killer-support} Environment variables to control Linux OOM killer have been added. However, in Linux, it needs root user privileges to write /proc/self/oom_score_adj file. **Table 17.** Environment variables for OOM Killer support | Variable name | Description | | --- | --- | | PG_OOM_ADJUST_FILEPG_OOM_ADJUST_VALUE | File to set the weighting Value to set the weighting | #### 3.1.8 Archiving of last WAL segment {#pg9-5-3-1-8-archiving-of-last-wal-segment} WAL file the slave instance is written partway will be output as ".partial" extension file after standby promotion. **Example 3.** Archiving of last segment ```bash $ pg_ctl -D data.slave1 promote server promoting $ $ psql -c 'SELECT pg_switch_xlog()' pg_switch_xlog ---------------- 0/146D2FA8 (1 row) $ ls -1 arch.slave1 000000010000000000000014.partial 000000020000000000000014 00000002.history $ ```