PDF page 44
#### 3.5.1 Added Catalogs {#pg10-3-5-1-added-catalogs} With the additional features, the following system catalogs have been added. **Table 8.** Added system catalog list | Catalog name | Description | | --- | --- | | pg_hba_file_rules | Referencing pg_hba.conf file | | pg_partitioned_table | Partitioning table information | | pg_publication | PUBLICATION object for Logical Replication | | pg_publication_rel | Target table list for Logical Replication | | pg_publication_tables | Target table list for Logical Replication | | pg_sequence | SEQUENCE object list | | pg_sequences | SEQUENCE object list | | pg_stat_subscription | Status for Logical Replication | | pg_statistic_ext | Extended statistics view | | pg_subscription | SUBSCRIPTION object for Logical Replication | | pg_subscription_rel | Target table list for Logical Replication | ##### pg_hba_file_rules catalog {#pg10-p44-pg-hba-file-rules-catalog} The pg_hba_file_rules catalog can refer to the contents of the pg_hba.conf file. When changing the file, the contents of the view are reflected immediately. Comment only lines are not included.PDF page 45
**Table 9.** pg\_hba\_file\_rules catalog | Column name | Data type | Description | | --- | --- | --- | | line_number | integer | Line number in the file | | type | text | Connection type of local, host, etc | | database | text[] | Target database or all, replication | | user_name | text[] | Username or all | | address | text | TCP/IP address | | netmask | text | Net mask | | auth_method | text | Authentication method | | options | text[] | Options | | error | text | Error messages | ##### pg_partitioned_table catalog {#pg10-p45-pg-partitioned-table-catalog} The pg_partitioned_table catalog stores information on the parent table on partitioning table. **Table 10.** pg\_partitioned\_table catalog | Column name | Data type | Description | | --- | --- | --- | | partrelid | oid | OID for the table | | partstrat | char | Partitioning method (list = 'l', range = 'r') | | partnatts | smallint | Number of attached partitions | | partattrs | int2vector | Array of partition column values | | partclass | oidvector | The data type of the partition key. | | partcollation | oidvector | Collation information of partition-key columns | | partexprs | pg_node_tree | Information on partitioning columns | ##### pg_publication catalog {#pg10-p45-pg-publication-catalog} The pg_publication catalog stores information on PUBLICATION objects used in Logical Replication.PDF page 46
**Table 11.** pg\_publication catalog | Column name | Data type | Description | | --- | --- | --- | | pubname | name | Name of the PUBLICATION | | pubowner | oid | Owner of the PUBLICATION object | | puballtables | boolean | If true, this publication automatically includes all tables in the database | | pubinsert | boolean | If true, INSERT operations are replicated for tables | | pubupdate | boolean | If true, UPDATE operations are replicated for tables | | pubdelete | boolean | If true, UPDATE operations are replicated for tables | ##### pg_publication_rel catalog {#pg10-p46-pg-publication-rel-catalog} The pg_publication_rel catalog stores the information of the replication target table contained in the PUBLICATION object. **Table 12.** pg\_publication\_rel catalog | Column name | Data type | Description | | --- | --- | --- | | prpubidprrelid | oid | OID of the PUBLICATION object OID of the target table | ##### pg_publication_tables catalog {#pg10-p46-pg-publication-tables-catalog} The pg_publication_tables catalog stores the information of the replication target table contained in the PUBLICATION object. **Table 13.** pg\_publication\_tables catalog | Column name | Data type | Description | | --- | --- | --- | | pubname | name | Name of the PUBLICATION object | | schemaname | name | Name of the schema | | tablename | name | Name of the target table | ##### pg_sequence catalog {#pg10-p46-pg-sequence-catalog} The pg_sequence catalog that provides a list of SEQUENCE objects has been added. This catalog can be searched by general users.PDF page 47
**Table 14.** pg\_sequence catalog | Column name | Data type | Description | | --- | --- | --- | | seqrelid | oid | OID of the object | | seqtypid | oid | Data type of the SEQUENCE | | seqstart | bigint | Start value | | seqincrement | bigint | Incremental value | | seqmax | bigint | Maximum sequence value | | seqmin | bigint | Minimum sequence value | | seqcache | bigint | Number of caches | | seqcycle | boolean | Indicate whether to cyclic | ##### pg_sequences catalog {#pg10-p47-pg-sequences-catalog} The pg_sequences catalog that provides a list of SEQUENCE object has been added. Although this catalog can be searched by general users, the last_value column is NULL if the nextval function has not been executed yet or if the search user does not have USAGE or SELECT privilege on that SEQUENCE. **Table 15.** pg\_sequences catalog | Column name | Data type | Description | | --- | --- | --- | | schemaname | name | Name of the schema | | sequencename | name | Name of the SEQUENCE object | | sequenceowner | name | Name of the owner | | data_type | regtype | Data type of the SEQUENCE | | start_value | bigint | Start value | | min_value | bigint | Minimum value | | max_value | bigint | Maximum value | | increment_by | bigint | Incremental value | | cycle | boolean | Indicate whether to cyclic | | cache_size | bigint | Number of caches | | last_value | bigint | Last sequence value or NULL |PDF page 48
**Example 50.** Reference pg\_sequences catalog ```text postgres=> \x Expanded display is on. postgres=> SELECT * FROM pg_sequences ; -[ RECORD 1 ]-+-------------------- schemaname | public sequencename | seq1 sequenceowner | postgres data_type | bigint start_value | 1 min_value | 1 max_value | 9223372036854775807 increment_by | 1 cycle | f cache_size | 1 last_value | ``` ```sql The result of the SELECT statement for the sequence has changed due to the addition of the pg_sequences catalog. ``` **Example 51.** Search for a sequence \(PostgreSQL 9\.6\) ```text postgres=> CREATE SEQUENCE seq1 ; CREATE SEQUENCE postgres=> SELECT * FROM seq1 ; -[ RECORD 1 ]-+-------------------- sequence_name | seq1 last_value | 1 start_value | 1 increment_by | 1 max_value | 9223372036854775807 min_value | 1 cache_value | 1 log_cnt | 0 is_cycled | f is_called | f ```PDF page 49
**Example 52.** Search for a sequence \(PostgreSQL 10\) ```text postgres=> CREATE SEQUENCE seq1 ; CREATE SEQUENCE postgres=> SELECT * FROM seq1 ; -[ RECORD 1 ]----- last_value | 1 log_cnt | 0 is_called | f ``` ##### pg_stat_subscription catalog {#pg10-p49-pg-stat-subscription-catalog} The pg_stat_subscription catalog stores WAL information received by the SUBSCRIPTION object. This catalog can refer to data only while the replication process is running. **Table 16.** pg\_stat\_subscription catalog | Column name | Data type | Description | | --- | --- | --- | | subid | oid | OID of the SUBSCRIPTION | | subname | name | Name of the SUBSCRIPTION | | pid | integer | Process id of the logical replication worker | | relid | oid | OID of the table | | received_lsn | pg_lsn | Received LSN | | last_msg_send_time | timestamp with time zone | Message send time | | last_msg_receipt_time | timestamp with time zone | Message receive time | | latest_end_lsn | pg_lsn | Latest end LSN | | latest_end_time | timestamp with time zone | Latest end timestamp | ##### pg_statistic_ext catalog {#pg10-p49-pg-statistic-ext-catalog} The pg_statistic_ext catalog stores information on extended statistics created with the CREATE STATISTICS statement.PDF page 50
**Table 17.** pg\_statistic\_ext catalog | Column name | Data type | Description | | --- | --- | --- | | stxrelid | oid | OID of the statistics acquisition table | | stxname | name | Name of the extended statistics | | stxnamespace | oid | OID of the namespace | | stxowner | oid | Owner of extended statistics | | stxkeys | int2vector | Array of column numbers from which extended statistics were obtained | | stxkind | "char"[] | Types of the statistics activated | | stxndistinct | pg_ndistinct | Serialized N-distinct value | | stxdependencies | pg_dependencies | Column dependencies | ##### pg_subscription catalog {#pg10-p50-pg-subscription-catalog} The pg_subscription catalog stores information on the SUBSCRIPTION object used by Logical Replication. This catalog can only be viewed by users with SUPERUSER privilege. **Table 18.** pg\_subscription catalog | Column name | Data type | Description | | --- | --- | --- | | subdbid | oid | OID of database constituting SUBSCRIPTION | | subname | name | Name of the SUBSCRIPTION object | | subowner | oid | Owner’s OID | | subenabled | boolean | Is the object valid? | | subconninfo | text | Connection information to PUBLICATION instance | | subslotname | name | Name of the replication slot | | subsynccommit | text | Synchronous COMMIT setting value | | subpublications | text[] | Array of PUBLICATION names | ##### pg_subscription_rel catalog {#pg10-p50-pg-subscription-rel-catalog} The pg_subscription_rel catalog stores the information on table targeted by the SUBSCRIPTION object used in Logical Replication.PDF page 51
**Table 19.** pg\_subscription\_rel catalog | Column name | Data type | Description | | --- | --- | --- | | srsubid | oid | OID of SUBSCRIPTION object | | srrelid | oid | OID of target table | | srsubstate | "char" | Status i = initializing, d = data transferring, s = synchronizing, r = normal | | srsublsn | pg_lsn | The last LSN of the s or r state of srsubstate column | #### 3.5.2 Modified catalogs {#pg10-3-5-2-modified-catalogs} The following catalogs have been changed. **Table 20.** System catalog with columns added | Catalog name | Added column | Data Type | Description | | --- | --- | --- | --- | | pg_class | relispartition | boolean | Partition parent table | | | relpartbound | pg_node_tree | Partitioning information | | pg_replication_slots | temporary | boolean | Indicate a temporary slot | | pg_policy | polpermissive | boolean | PERMISSIVE mode | | pg_policies | permissive | text | PERMISSIVE mode | | pg_stat_replication | write_lag | interval | Write lag | | | flush_lag | interval | Flush lag | | | replay_lag | interval | Replay lag | | pg_collation | collprovider | char | Provider information | | | collversion | text | Version information | | pg_stat_activity | backend_type | text | Type of process | | pg_attribute | attidentity | char | GENERATED column | ##### pg_stat_activity catalog {#pg10-p51-pg-stat-activity-catalog} The statuses of all backend processes except the postmaster process are now displayed in the pg_stat_activity catalog. The type of backend process can be confirmed by backend_type column.PDF page 52
**Example 53.** Referencing pg\_stat\_activity catalog ```text postgres=# SELECT pid,wait_event, backend_type FROM pg_stat_activity ; pid | wait_event | backend_type -------+---------------------+--------------------- 12251 | AutoVacuumMain | autovacuum launcher 12253 | LogicalLauncherMain | background worker 12269 | | client backend 12249 | BgWriterHibernate | background writer 12248 | CheckpointerMain | checkpointer 12250 | WalWriterMain | walwriter (6 rows) ``` #### 3.5.3 Enhancement of libpq library {#pg10-3-5-3-enhancement-of-libpq-library} The following enhancements have been added to the PostgreSQL Client library libpq. ##### Multi-instance specification {#pg10-p52-multi-instance-specification} Settings for connecting to multiple instances which are already supported in the JDBC Driver are also implemented in the libpq library. As described below, multiple host names and port numbers can be described in comma-separated form. #### Syntax 7 - Multi-instance specification {#pg10-syntax-7} ```sql host=host1,host2 host=host1,host2 port=port1,port2 postgresql://host1,host2/ postgresql://host1:port2,host2:port2/ ``` Multiple values can be specified with the comma (,) separator in the environment variables PGHOST and PGPORT. Along with this, it is now possible to specify multiple values for the --host and --port parameters of the psql and pg_basebackup commands ##### Added target_session_attrs attribute {#pg10-p52-added-target-session-attrs-attribute} Target_session_attrs has been added as a new connection attribute. This parameter can be specified as "any" if the instance to be connected can be hot standby, or "read-write" if the instance is writable. A similar specification can be specified for the environment variable PGTARGETSESSIONATTRS. Internally, it seems to be use the SHOW transaction_read_only statement to determine the connectionPDF page 53
destination. ##### Added passfile attribute {#pg10-p53-added-passfile-attribute} "passfile" has been added as a new connection attribute. In the past, it was specified with the environment variable PGPASSFILE etc. #### 3.5.4 Change from XLOG to WAL {#pg10-3-5-4-change-from-xlog-to-wal} The name of XLOG used in the function, directory name, and utility was unified in WAL. Also, the pg_clog directory has been changed to the pg_xact directory. The default output directory name of the log file has been changed due to the effect of changing the default value of the parameter log_directory. The name "location" indicating the location of WAL has been changed to "lsn". **Table 21.** Changed name | Category | change | After changing | | --- | --- | --- | | Directories | pg_xlog | pg_wal | | | pg_clog | pg_xact | | | pg_log | log | | Utilities | pg_receivexlog | pg_receivewal | | | pg_resetxlog | pg_resetwal | | | pg_xlogdump | pg_waldump | | | pg_basebackup --xlog-method | pg_basebackup --wal-method | | | pg_basebackup --xlogdir | pg_basebackup --waldir | | | initdb --xlogdir | initdb --waldir | | Functions | pg xlog location diff___ | pg_wal_location_diff | | | pg_switch_xlog | pg_switch_wal | | | pg_current_xlog_* | pg_current_wal_* | | | pg xlogfile*_ | pg_walfile* | | | pg_is_xlog_replay_replay_paused | pg_is_wal_replay_replay_paused | | | pg_last_xlog_* | pg_last_wal_* | | | pg *location*_ | pg_*lsn* | | Catalog | pg_stat_replication Catalog | pg_stat_replication Catalog | | | - sent location_ | sent_lsn | | | - write location_ | write_lsn | | | - flush location_ | flush_lsn | | | - replay location_ | replay_lsn |PDF page 54
At the same time, the string XLOG included in the error message has also been changed to WAL. It has been changed to the following message. - Failed while allocating a WAL reading processor. - could not read two-phase state from WAL at … - expected two-phase state data is not present in WAL at … - Failed while allocating a WAL reading processor. - WAL redo at %X/%X for %s - Forces a switch to the next WAL file if a new file has not been started within N seconds. The description of the parameter archive_timeout has been changed as follows. - Forces a switch to the next WAL file if a new file has not been started within N seconds. #### 3.5.5 Temporary replication slot {#pg10-3-5-5-temporary-replication-slot} Replication slots can be used for building a streaming replication environment or for the pg_basebackup command. In PostgreSQL 10, temporary replication slots can now be created. A temporary replication slot is the same as a normal replication slot except that it is automatically deleted by session termination. To create a temporary replication slot, specify true for the third parameter of the pg_create_physical_replication_slot function or pg_create_logical_replication_slot function. Along with this, "temporary" column has been added to the pg_replication_slots catalog. **Example 54.** Create temporary replication slot ```text postgres=# SELECT pg_create_physical_replication_slot('temp1', true, true) ; pg_create_physical_replication_slot ------------------------------------- (temp1,0/30000370) (1 row) postgres=# SELECT slot_name, temporary FROM pg_replication_slots ; slot_name | temporary -----------+----------- temp1 | t (1 row) ``` #### 3.5.6 Change instance startup log {#pg10-3-5-6-change-instance-startup-log} The listen address and port number are now output to the instance startup log.PDF page 55
**Example 55.** Instance start log \(partially omitted\) ```bash $ pg_ctl -D data start waiting for server to start.... LOG: listening on IPv4 address "0.0.0.0", port 5432 LOG: listening on IPv6 address "::", port 5432 LOG: listening on Unix socket "/tmp/.s.PGSQL.5432" LOG: redirecting log output to logging collector process HINT: Future log output will appear in directory "log". done server started ``` #### 3.5.7 WAL of hash index {#pg10-3-5-7-wal-of-hash-index} Hash index of previous versions did not generate WAL on update. In PostgreSQL 10, it now generate WAL, so it can now be used in streaming replication environments. The warnings outputted in the CREATE INDEX USING HASH statement are no longer output. **Example 56.** Create hash index \(PostgreSQL 10\) ```text postgres=> CREATE INDEX idx1_hash1 ON hash1 USING hash (c1) ; CREATE INDEX ``` **Example 57.** Create hash index \(PostgreSQL 9\.6\) ```text postgres=> CREATE INDEX Idx1_hash1 ON hash1 USING hash (c1) ; WARNING: hash indexes are not WAL-logged and their use is discouraged CREATE INDEX ``` #### 3.5.8 Added roles {#pg10-3-5-8-added-roles} The following roles have been added. All roles do not have "login" privilege. **Table 22.** Added role | Role | Description | | --- | --- | | pg_read_all_settings | All configuration parameters can be referred. | | pg_read_all_stats | All pg_stat_ * views can be referred. | | pg_stat_scan_tables | Execute the monitoring function to take AccessShareLock lock | | pg_monitor | All of the above 3 roles have authority |PDF page 56
Registering the following Contrib modules, execute permissions for function are automatically granted to the roles above. - pg_buffercache - pg_freespacemap - pg_stat_statements - pg_visibility - pgstattuple #### 3.5.9 Custom Scan Callback {#pg10-3-5-9-custom-scan-callback} A new callback called at the end of the parallel query has been added. It is explained in the manual "58.3. Executing Custom Scans" as follows. **Example 58.** Custom Scan Callback ```text Initialize a parallel worker's custom state based on the shared state set up in the leader by InitializeDSMCustomScan. This callback is optional, and needs only be supplied if this custom path supports parallel execution. void (*ShutdownCustomScan) (CustomScanState *node); ``` #### 3.5.10 Size of WAL file {#pg10-3-5-10-size-of-wal-file} The choice of WAL file size determined by the --with-wal-segsize option of the "configure" command has increased. 128, 256, 512, 1024 can be used in addition to the conventional 1 to 64. #### 3.5.11 ICU {#pg10-3-5-11-icu} ICU can be used for locale function. Specify --with-icu when executing "configure" command. When building in Linux environment, installation of libicu package and libicu-devel package is necessary. #### 3.5.12 EUI-64 data type {#pg10-3-5-12-eui-64-data-type} The data type macaddr8 indicating the EUI-64 address is now available. #### 3.5.13 Unique Join {#pg10-3-5-13-unique-join} ```text When joining tables, the execution plan that performs a join using a unique index can be planned. ```PDF page 57
In the execution plan displayed by EXPLAIN VERBOSE statement, it will appear as "Inner Unique: true". **Example 59.** Inner Unique Join ```text postgres=> CREATE TABLE unique1(c1 INTEGER PRIMARY KEY, c2 VARCHAR(10)) ; CREATE TABLE postgres=> CREATE TABLE unique2(c1 INTEGER PRIMARY KEY, c2 VARCHAR(10)) ; CREATE TABLE … postgres=> EXPLAIN VERBOSE SELECT * FROM unique1 u1 INNER JOIN unique2 u2 ON u1.c1 = u2.c1 ; QUERY PLAN ----------------------------------------------------------------------------------- Hash Join (cost=280.00..561.24 rows=10000 width=18) Output: u1.c1, u1.c2, u2.c1, u2.c2 Inner Unique: true Hash Cond: (u1.c1 = u2.c1) -> Seq Scan on public.unique1 u1 (cost=0.00..155.00 rows=10000 width=9) Output: u1.c1, u1.c2 -> Hash (cost=155.00..155.00 rows=10000 width=9) Output: u2.c1, u2.c2 -> Seq Scan on public.unique2 u2 (cost=0.00..155.00 rows=10000 width=9) Output: u2.c1, u2.c2 (10 rows) ``` #### 3.5.14 Shared Memory Address {#pg10-3-5-14-shared-memory-address} When the EXEC_BACKEND macro is defined and installed, the environment variable PG_SHMEM_ADDR can be used. Specify the start address of System V shared memory used as part of the cache. Internally it is digitized with the strtoul function and used as the second parameter of the shmat system call.