7 Architecture

PDF page 44

3.5.1 Added Catalogs

With the additional features, the following system catalogs have been added.

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

pg_partitioned_table catalog

The pg_partitioned_table catalog stores information on the parent table on partitioning table.

pg_publication catalog

The pg_publication catalog stores information on PUBLICATION objects used in Logical Replication.

PDF page 46

pg_publication_rel catalog

The pg_publication_rel catalog stores the information of the replication target table contained in the PUBLICATION object.

pg_publication_tables catalog

The pg_publication_tables catalog stores the information of the replication target table contained in the PUBLICATION object.

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

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.

PDF page 48

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    |

The result of the SELECT statement for the sequence has changed due to the addition of the
pg_sequences catalog.

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

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

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.

pg_statistic_ext catalog

The pg_statistic_ext catalog stores information on extended statistics created with the CREATE STATISTICS statement.

PDF page 50

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.

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

3.5.2 Modified catalogs

The following catalogs have been changed.

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

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

The following enhancements have been added to the PostgreSQL Client library libpq.

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

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

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 connection

PDF page 53

destination.

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

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”.

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

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.

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

The listen address and port number are now output to the instance startup log.

PDF page 55

$ 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

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.

postgres=> CREATE INDEX idx1_hash1 ON hash1 USING hash (c1) ;
CREATE INDEX

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

The following roles have been added. All roles do not have “login” privilege.

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

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.

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

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

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

The data type macaddr8 indicating the EUI-64 address is now available.

3.5.13 Unique Join

  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”.

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

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.