PostgreSQL 正體中文使用手冊
PostgreSQL.TW官方使用手冊小島故事加入社團
15
15
  • 簡介
  • 前言
    • 1. 什麼是 PostgreSQL?
    • 2. PostgreSQL 沿革
    • 3. 慣例
    • 4. 其他參考資訊
    • 5. 問題回報指南
  • I. 新手教學
    • 1. 入門指南
      • 1.1. 安裝
      • 1.2. 基礎架構
      • 1.3. 建立一個資料庫
      • 1.4. 存取一個資料庫
    • 2. SQL 查詢語言
      • 2.1. 簡介
      • 2.2. 概念
      • 2.3. 創建一個新的資料表
      • 2.4. 資料列是資料表的組成單位
      • 2.5. 資料表的查詢
      • 2.6. 交叉查詢
      • 2.7. 彙總查詢
      • 2.8. 更新資料
      • 2.9. 刪除資料
    • 3. 先進功能
      • 3.1. 簡介
      • 3.2. 檢視表(View)
      • 3.3. 外部索引鍵
      • 3.4. 交易安全
      • 3.5. 窗函數
      • 3.6. 繼承
      • 3.7. 結論
  • II. SQL 查詢語言
    • 4. SQL 語法
      • 4.1. 語法結構
      • 4.2. 參數表示式
      • 4.3. 函數呼叫
    • 5. 定義資料結構
      • 5.1. 認識資料表
      • 5.2. 預設值
      • 5.3. Generated Columns
      • 5.4. 限制條件
      • 5.5. 系統欄位
      • 5.6. 表格變更
      • 5.7. 權限
      • 5.8. 資料列安全原則
      • 5.9. Schemas
      • 5.10. 繼承
      • 5.11. 分割資料表
      • 5.12. 外部資料
      • 5.13. 其他資料庫物件
      • 5.14. 相依性追蹤
    • 6. 資料處理
      • 6.1. 新增資料
      • 6.2. 更新資料
      • 6.3. 刪除資料
      • 6.4. 修改並回傳資料
    • 7. 資料查詢
      • 7.1. 概觀
      • 7.2. 資料表表示式
      • 7.3. 取得資料列表
      • 7.4. 合併查詢結果
      • 7.5. 資料排序
      • 7.6. LIMIT 和 OFFSET
      • 7.7. VALUES 列舉資料
      • 7.8. WITH Querys(Common Table Expressions)
    • 8. 資料型別
      • 8.1. 數字型別
      • 8.2. 貨幣型別
      • 8.3. 字串型別
      • 8.4. 位元組型別(bytea)
      • 8.5. 日期時間型別
      • 8.6. 布林型別
      • 8.7. 列舉型別
      • 8.8. 地理資訊型別
      • 8.9. 網路資訊型別
      • 8.10. 位元字串型別
      • 8.11. 全文檢索型別
      • 8.12. UUID 型別
      • 8.13. XML 型別
      • 8.14. JSON 型別
      • 8.15. 陣列
      • 8.16. 複合型別
      • 8.17. 範圍型別
      • 8.18. Domain Types
      • 8.19. 物件指標型別
      • 8.20. pg_lsn 型別
      • 8.21. 概念型別
    • 9. 函式及運算子
      • 9.1. 邏輯運算子
      • 9.2. 比較函式及運算子
      • 9.3. 數學函式及運算子
      • 9.4. 字串函式及運算子
      • 9.5. 位元字串函式及運算子
      • 9.6. 二元字串函式及運算子
      • 9.7. 特徵比對
      • 9.8. 型別轉換函式
      • 9.9 日期時間函式及運算子
      • 9.10. 列舉型別函式
      • 9.11. 地理資訊函式及運算子
      • 9.12. 網路位址函式及運算子
      • 9.13. 文字檢索函式及運算子
      • 9.14. UUID Functions
      • 9.15. XML 函式
      • 9.16. JSON 函式及運算子
      • 9.17. 序列函式
      • 9.18. 條件表示式
      • 9.19. 陣列函式及運算子
      • 9.20. 範圍函式及運算子
      • 9.21. 彙總函數
      • 9.22. Window 函式
      • 9.23. 子查詢
      • 9.24. 資料列與陣列的比較運算
      • 9.25. 集合回傳函數
      • 9.26. 系統資訊函數
      • 9.27. 系統管理函式
      • 9.28. 觸發函式
      • 9.29. 事件觸發函式
      • 9.30. Statistics Information Functions
    • 10. 型別轉換
      • 10.1. 概觀
      • 10.2. 運算子
      • 10.3. 函式
      • 10.4. 資料儲存轉換規則
      • 10.5. UNION、CASE 等相關結構
      • 10.6. SELECT 輸出規則
    • 11. 索引(Index)
      • 11.1. 簡介
      • 11.2. 索引型別
      • 11.3. 多欄位索引
      • 11.4. 索引與 ORDER BY
      • 11.5. 善用多個索引
      • 11.6. 唯一值索引
      • 11.7. 表示式索引
      • 11.8. 部份索引(partial index)
      • 11.9. Index-Only Scans and Covering Indexes
      • 11.10. 運算子物件及家族
      • 11.11. 索引與排序規則
      • 11.12. 檢查索引運用
    • 12. 全文檢索
      • 12.1. 簡介
      • 12.2. 查詢與索引
      • 12.3. 細部控制
      • 12.4. 延伸功能
      • 12.5. 斷詞
      • 12.6. 字典
      • 12.7. 組態範例
      • 12.8. 測試與除錯
      • 12.9. GIN 及 GiST 索引型別
      • 12.10. psql支援
      • 12.11. 功能限制
    • 13. 一致性管理(Concurrency Control)
      • 13.1. 簡介
      • 13.2. 交易隔離
      • 13.3. 鎖定模式
      • 13.4. 在應用端檢視資料一致性
      • 13.5. Serialization Failure Handling
      • 13.6. 特別提醒
      • 13.7. 鎖定與索引
    • 14. 效能技巧
      • 14.1. 善用 EXPLAIN
      • 14.2. 統計資訊
      • 14.3. 使用確切的 JOIN 方式
      • 14.4. 快速建立資料庫內容
      • 14.5. 風險性彈性設定
    • 15. 平行查詢
      • 15.1. 如何運作?
      • 15.2. 啓用時機?
      • 15.3. 平行查詢計畫
      • 15.4. 平行查詢的安全性
  • III. 系統管理
    • 16. 以預編譯套件安裝
    • 17. 以原始碼安裝
      • 17.1. 簡要步驟
      • 17.2. 環境需求
      • 17.3. Getting The Source
      • 17.4. 安裝流程
      • 17.5. Post-Installation Setup
      • 17.6. Supported Platforms
      • 17.7. 平台相關的注意事項
    • 18. 以原始碼在 Windows 上安裝
      • 18.1. Building with Visual C++ or the Microsoft Windows SDK
    • 19. 服務配置與維運
      • 19.1. PostgreSQL 使用者帳號
      • 19.2. Creating a Database Cluster
      • 19.3. Starting the Database Server
      • 19.4. 核心資源管理
      • 19.5. Shutting Down the Server
      • 19.6. Upgrading a PostgreSQL Cluster
      • 19.7. Preventing Server Spoofing
      • 19.8. Encryption Options
      • 19.9. Secure TCP/IP Connections with SSL
      • 19.10. Secure TCP/IP Connections with GSSAPI Encryption
      • 19.11. Secure TCP/IP Connections with SSH Tunnels
      • 19.12. 在 Windows 註冊事件日誌
    • 20. 服務組態設定
      • 20.1. Setting Parameters
      • 20.2. File Locations
      • 20.3. 連線與認證
      • 20.4. 資源配置
      • 20.5. Write Ahead Log
      • 20.6. 複寫(Replication)
      • 20.7. 查詢規畫
      • 20.8. 錯誤回報與日誌記錄
      • 20.9. 執行階段統計資訊
      • 20.10. 自動資料庫清理
      • 20.11. 用戶端連線預設參數
      • 20.12. 交易鎖定管理
      • 20.13. 版本與平台的相容性
      • 20.14. Error Handling
      • 20.15. 預先配置的參數
      • 20.16. Customized Options
      • 20.17. Developer Options
      • 20.18. Short Options
    • 21. 使用者認證
      • 21.1. 設定檔:pg_hba.conf
      • 21.2. User Name Maps
      • 21.3. Authentication Methods
      • 21.4. Trust Authentication
      • 21.5. Password Authentication
      • 21.6. GSSAPI Authentication
      • 21.7. SSPI Authentication
      • 21.8. Ident Authentication
      • 21.9. Peer Authentication
      • 21.10. LDAP Authentication
      • 21.11. RADIUS Authentication
      • 21.12. Certificate Authentication
      • 21.13. PAM Authentication
      • 21.14. BSD Authentication
      • 21.15. Authentication Problems
    • 22. 資料庫角色
      • 22.1. Database Roles
      • 22.2. Role Attributes
      • 22.3. Role Membership
      • 22.4. 移除角色
      • 22.5. Default Roles
      • 22.6. Function Security
    • 23. 管理資料庫
      • 23.1. Overview
      • 23.2. Creating a Database
      • 23.3. 樣版資料庫
      • 23.4. Database Configuration
      • 23.5. Destroying a Database
      • 23.6. Tablespaces
    • 24. 語系
      • 24.1. 語系支援
      • 24.2. Collation Support
      • 24.3. 字元集支援
    • 25. 例行性資料庫維護工作
      • 25.1. 例行性資料清理
      • 25.2. 定期重建索引
      • 25.3. Log 檔案維護
    • 26. 備份及還原
      • 26.1. SQL Dump
      • 26.2. 檔案系統層級備份
      • 26.3. 持續封存及 Point-in-Time Recovery (PITR)
    • 27. High Availability, Load Balancing, and Replication
      • 27.1. 比較不同的解決方案
      • 27.2. 日誌轉送備用伺服器 Log-Shipping Standby Servers
      • 27.3. Failover
      • 27.4. Hot Standby
    • 28. 監控資料庫活動
      • 28.1. 標準的 Unix 工具
      • 28.2. 統計資訊收集器
      • 28.3. Viewing Locks
      • 28.4. Progress Reporting
      • 28.5. Dynamic Tracing
    • 29. 監控磁碟使用情況
      • 29.1. 瞭解磁碟使用情形
      • 29.2. 磁碟空間不足錯誤
    • 30. 高可靠度及預寫日誌
      • 30.1. 可靠度
      • 30.2. Data Checksums
      • 30.3. Write-Ahead Logging(WAL)
      • 30.4. Asynchronous Commit
      • 30.5. WAL Configuration
      • 30.6. WAL Internals
    • 31. 邏輯複寫(Logical Replication)
      • 31.1. 發佈(Publication)
      • 31.2. 訂閱(Subscription)
      • 31.3. Row Filters
      • 31.4. Column Lists
      • 31.5. 衝突處理
      • 31.6. 限制
      • 31.7. 架構
      • 31.8. 監控
      • 31.9. 安全性
      • 31.10. 系統設定
      • 31.11. 快速設定
    • 32. Just-in-Time Compilation(JIT)
      • 32.1. What is JIT compilation?
      • 32.2. When to JIT?
      • 32.3. Configuration
      • 32.4. Extensibility
    • 33. 迴歸測試
      • 33.1. Running the Tests
      • 33.2. Test Evaluation
      • 33.3. Variant Comparison Files
      • 33.4. TAP Tests
      • 33.5. Test Coverage Examination
  • IV. 用戶端介面
    • 34. libpq - C Library
      • 33.1. 資料庫連線控制函數
      • 33.2. 連線狀態函數
      • 33.3. Command Execution Functions
      • 33.4. Asynchronous Command Processing
      • 33.5. Retrieving Query Results Row-By-Row
      • 33.6. Canceling Queries in Progress
      • 33.7. The Fast-Path Interface
      • 33.8. Asynchronous Notification
      • 33.9. Functions Associated with the COPY Command
      • 33.10. Control Functions
      • 33.11. Miscellaneous Functions
      • 33.12. Notice Processing
      • 33.13. Event System
      • 33.14. 環境變數
      • 34.16. 密碼檔
      • 33.16. The Connection Service File
      • 33.17. LDAP Lookup of Connection Parameters
      • 33.18. SSL Support
      • 33.19. Behavior in Threaded Programs
      • 33.20. Building libpq Programs
      • 33.21. Example Programs
    • 35. Large Objects
      • 35.1. Introduction
      • 35.2. Implementation Features
      • 35.3. Client Interfaces
      • 35.4. Server-side Functions
      • 35.5. Example Program
    • 36. ECPG - Embedded SQL in C
      • 35.1. The Concept
      • 35.2. Managing Database Connections
      • 35.3. Running SQL Commands
      • 35.4. Using Host Variables
      • 35.5. Dynamic SQL
      • 35.6. pgtypes Library
      • 35.7. Using Descriptor Areas
      • 35.8. Error Handling
      • 35.9. Preprocessor Directives
      • 35.10. Processing Embedded SQL Programs
      • 35.11. Library Functions
      • 35.12. Large Objects
      • 35.13. C++ Applications
      • 35.14. Embedded SQL Commands
      • 35.15. Informix Compatibility Mode
      • 35.16. Internals
    • 37. The Information Schema
      • 37.1. The Schema
      • 37.2. Data Types
      • 37.3. information_schema_catalog_name
      • 37.4. administrable_role_authorizations
      • 37.5. applicable_roles
      • 37.7. attributes
      • 37.7. character_sets
      • 37.8. check_constraint_routine_usage
      • 37.9. check_constraints
      • 37.10. collations
      • 37.11. collation_character_set_applicability
      • 37.12. column_column_usage
      • 37.13. column_domain_usage
      • 37.14. column_options
      • 37.15. column_privileges
      • 37.16. column_udt_usage
      • 37.17. columns
      • 37.18. constraint_column_usage
      • 37.19. constraint_table_usage
      • 37.20. data_type_privileges
      • 37.21. domain_constraints
      • 37.21. domain_udt_usage
      • 37.22. domains
      • 37.23. element_types
      • 37.24. enabled_roles
      • 37.25. foreign_data_wrapper_options
      • 37.26. foreign_data_wrappers
      • 37.27. foreign_server_options
      • 37.28. foreign_servers
      • 37.29. foreign_table_options
      • 37.30. foreign_tables
      • 36.32. key_column_usage
      • 37.33. parameters
      • 36.34. referential_constraints
      • 37.34. role_column_grants
      • 37.35. role_routine_grants
      • 37.37. role_table_grants
      • 37.38. role_udt_grants
      • 37.39. role_usage_grants
      • 37.40. routine_column_usage
      • 37.41. routine_privileges
      • 37.45. routines
      • 37.46. schemata
      • 37.47. sequences
      • 37.48. sql_features
      • 37.49. sql_implementation_info
      • 37.50. sql_parts
      • 37.51. sql_sizing
      • 36.51. table_constraints
      • 36.49. table_privileges
      • 37.52. tables
      • 37.53. transforms
      • 37.54. triggered_update_columns
      • 37.55. triggers
      • 37.56. udt_privileges
      • 37.57. usage_privileges
      • 37.58. user_defined_types
      • 37.59. user_mapping_options
      • 37.60. user_mappings
      • 37.63. view_column_usage
      • 37.64. view_routine_usage
      • 37.65. view_table_usage
      • 37.66. views
  • V. 資料庫程式設計
    • 38. SQL 延伸功能
      • 38.1. How Extensibility Works
      • 38.2. The PostgreSQL Type System
      • 38.3. 使用者自訂函數
      • 38.4. User-defined Procedures
      • 38.5. Query Language (SQL) Functions
      • 38.6. Function Overloading
      • 38.7. 函數易變性類別
      • 38.8. Procedural Language Functions
      • 38.9. Internal Functions
      • 38.10. C-Language Functions
      • 38.11. Function Optimization Information
      • 38.12. User-defined Aggregates
      • 38.13. User-defined Types
      • 38.14. User-defined Operators
      • 38.15. Operator Optimization Information
      • 38.16. Interfacing Extensions To Indexes
      • 38.17. 封裝相關物件到延伸功能中
      • 38.18. Extension Building Infrastructure
    • 39. Triggers
      • 39.1. Overview of Trigger Behavior
      • 39.2. Visibility of Data Changes
      • 39.3. Writing Trigger Functions in C
      • 39.4. A Complete Trigger Example
    • 40. Event Triggers (事件觸發)
      • 40.1. Overview of Event Trigger Behavior
      • 40.2. Event Trigger Firing Matrix
      • 40.3. Writing Event Trigger Functions in C
      • 40.4. A Complete Event Trigger Example
    • 41. 規則系統
      • 41.1. The Query Tree
      • 41.2. Views and the Rule System
      • 41.3. Materialized Views
      • 41.4. Rules on INSERT, UPDATE, and DELETE
      • 41.5. 規則及權限
      • 41.6. Rules and Command Status
      • 41.7. Rules Versus Triggers
    • 42. Procedural Languages(程序語言)
      • 42.1. Installing Procedural Languages
    • 43. PL/pgSQL - SQL Procedural Language
      • 43.1. Overview
      • 43.2. Structure of PL/pgSQL
      • 43.3. Declarations
      • 43.4. Expressions
      • 43.5. 基本語法
      • 43.6. Control Structures
      • 43.7. Cursors
      • 43.8. Transaction Management
      • 43.9. Errors and Messages
      • 43.10. Trigger Functions
      • 43.11. PL/pgSQL under the Hood
      • 43.12. Tips for Developing in PL/pgSQL
      • 43.13. Porting from Oracle PL/SQL
    • 44. PL/Tcl - Tcl Procedural Language
    • 45. PL/Perl — Perl Procedural Language
    • 46. PL/Python - Python Procedural Language
      • 46.1. PL/Python Functions
      • 46.2. Data Values
      • 46.3. Sharing Data
      • 46.4. Anonymous Code Blocks
      • 46.5. Trigger Functions
      • 46.6. Database Access
      • 46.7. Explicit Subtransactions
      • 46.8. Transaction Management
      • 46.9. Utility Functions
      • 46.10. Python 2 vs. Python 3
      • 46.11. Environment Variables
    • 47. Server Programming Interface
    • 48. Background Worker Processes
    • 49. Logical Decoding
      • 48.1. Logical Decoding Examples
      • 48.2. Logical Decoding Concepts
      • 48.3. Streaming Replication Protocol Interface
      • 48.4. Logical Decoding SQL Interface
      • 48.5. System Catalogs Related to Logical Decoding
      • 48.6. Logical Decoding Output Plugins
      • 48.7. Logical Decoding Output Writers
      • 48.8. Synchronous Replication Support for Logical Decoding
    • 50. Replication Progress Tracking
    • 51. Archive Modules
      • 51.1. Initialization Functions
      • 51.2. Archive Module Callbacks
  • VI. 參考資訊
    • I. SQL 指令
      • ALTER DATABASE
      • ALTER DEFAULT PRIVILEGES
      • ALTER EXTENSION
      • ALTER FUNCTION
      • ALTER INDEX
      • ALTER LANGUAGE
      • ALTER MATERIALIZED VIEW
      • ALTER POLICY
      • ALTER PUBLICATION
      • ALTER ROLE
      • ALTER RULE
      • ALTER SCHEMA
      • ALTER SEQUENCE
      • ALTER STATISTICS
      • ALTER SUBSCRIPTION
      • ALTER SYSTEM
      • ALTER TABLE
      • ALTER TABLESPACE
      • ALTER TRIGGER
      • ALTER TYPE
      • ALTER USER
      • ALTER VIEW
      • ANALYZE
      • CLUSTER
      • COMMENT
      • COMMIT PREPARED
      • COPY
      • CREATE ACCESS METHOD
      • CREATE CAST
      • CREATE DATABASE
      • CREATE EVENT TRIGGER
      • CREATE EXTENSION
      • CREATE FOREIGN TABLE
      • CREATE FOREIGN DATA WRAPPER
      • CREATE FUNCTION
      • CREATE INDEX
      • CREATE LANGUAGE
      • CREATE MATERIALIZED VIEW
      • CREATE DOMAIN
      • CREATE POLICY
      • CREATE PROCEDURE
      • CREATE PUBLICATION
      • CREATE ROLE
      • CREATE RULE
      • CREATE SCHEMA
      • CREATE SEQUENCE
      • CREATE SERVER
      • CREATE STATISTICS
      • CREATE SUBSCRIPTION
      • CREATE TABLE
      • CREATE TABLE AS
      • CREATE TABLESPACE
      • CREATE TRANSFORM
      • CREATE TRIGGER
      • CREATE TYPE
      • CREATE USER
      • CREATE USER MAPPING
      • CREATE VIEW
      • DEALLOCATE
      • DELETE
      • DO
      • DROP ACCESS METHOD
      • DROP DATABASE
      • DROP EXTENSION
      • DROP FUNCTION
      • DROP INDEX
      • DROP LANGUAGE
      • DROP MATERIALIZED VIEW
      • DROP OWNED
      • DROP POLICY
      • DROP PUBLICATION
      • DROP ROLE
      • DROP RULE
      • DROP SCHEMA
      • DROP SEQUENCE
      • DROP STATISTICS
      • DROP SUBSCRIPTION
      • DROP TABLE
      • DROP TABLESPACE
      • DROP TRANSFORM
      • DROP TRIGGER
      • DROP TYPE
      • DROP USER
      • DROP VIEW
      • EXECUTE
      • EXPLAIN
      • GRANT
      • IMPORT FOREIGN SCHEMA
      • INSERT
      • LISTEN
      • LOAD
      • MERGE
      • NOTIFY
      • PREPARE
      • PREPARE TRANSACTION
      • REASSIGN OWNED
      • REFRESH MATERIALIZED VIEW
      • REINDEX
      • RESET
      • REVOKE
      • ROLLBACK PREPARED
      • SECURITY LABEL
      • SELECT
      • SELECT INTO
      • SET
      • SET CONSTRAINTS
      • SET ROLE
      • SET SESSION AUTHORIZATION
      • SET TRANSACTION
      • SHOW
      • TRUNCATE
      • UNLISTEN
      • UPDATE
      • VACUUM
      • VALUES
    • II. PostgreSQL 用戶端工具
      • createdb
      • createuser
      • dropdb
      • dropuser
      • oid2name
      • pgbench
      • pg_basebackup
      • pg_dump
      • pg_dumpall
      • pg_isready
      • pg_receivewal
      • pg_recvlogical
      • pg_restore
      • pg_verifybackup
      • psql
      • vacuumdb
    • III. PostgreSQL 伺服器應用程式
      • initdb
      • pg_archivecleanup
      • pg_ctl
      • pg_standby
      • pg_test_fsync
      • pg_test_timing
      • pg_upgrade
      • postgres
  • VII. 資料庫進階
    • 52. PostgreSQL 的內部架構
      • 52.1. 處理查詢語句的流程
      • 52.2. How Connections Are Established
      • 52.3. The Parser Stage
      • 52.4. The PostgreSQL Rule System
      • 52.5. Planner/Optimizer
      • 52.6. Executor
    • 53. 系統資訊目錄
      • 51.3. pg_am
      • 51.7. pg_attribute
      • 51.8. pg_authid
      • 51.9. pg_auth_members
      • 51.10. pg_cast
      • 51.11 pg_class
      • 51.12. pg_collation
      • 51.13. pg_constraint
      • 51.15 pg_database
      • 51.21. pg_event_trigger
      • 51.22. pg_extension
      • 51.26 pg_index
      • 51.29. pg_language
      • 51.32. pg_namespace
      • 51.33. pg_opclass
      • 51.38. pg_policy
      • 51.39. pg_proc
      • 51.44. pg_rewrite
      • 51.49. pg_statistic
      • 51.50. pg_statistic_ext
      • 51.52. pg_subscription
      • 51.53. pg_subscription_rel
      • 51.54. pg_tablespace
      • 51.56. pg_trigger
      • 51.62. pg_type
      • 51.66. pg_available_extensions
      • 51.67. pg_available_extension_versions
      • 51.71. pg_hba_file_rules
      • 51.72. pg_indexes
      • 51.73. pg_locks
      • 51.77. pg_prepared_xacts
      • 51.79. pg_replication_origin_status
    • 54. System Views
      • 54.1. Overview
      • 54.19. pg_replication_slots
      • 54.20 pg_roles
      • 54.24. pg_settings
      • 54.25. pg_shadow
      • 54.26. pg_shmem_allocations
      • 54.27. pg_stats
      • 54.30. pg_tables
      • 54.31. pg_timezone_abbrevs
      • 54.32. pg_timezone_names
      • 54.33. pg_user
      • 54.35. pg_views
    • 55. Frontend/Backend Protocol
      • 52.1. Overview
      • 52.2. Message Flow
      • 52.3. SASL Authentication
      • 52.4. Streaming Replication Protocol
      • 52.5. Logical Streaming Replication Protocol
      • 52.6. Message Data Types
      • 52.7. Message Formats
      • 52.8. Error and Notice Message Fields
      • 52.9. Logical Replication Message Formats
      • 52.10. Summary of Changes since Protocol 2.0
    • 56. PostgreSQL 程式撰寫慣例
      • 53.1. Formatting
      • 53.2. Reporting Errors Within the Server
      • 53.3. Error Message Style Guide
      • 53.4. Miscellaneous Coding Conventions
    • 57. Native Language Support
      • 54.1. For the Translator
      • 54.2. For the Programmer
    • 58. 撰寫程序語言的處理程序
    • 59. Writing a Foreign Data Wrapper
      • 56.1. Foreign Data Wrapper Functions
      • 56.2. Foreign Data Wrapper Callback Routines
      • 56.3. Foreign Data Wrapper Helper Functions
      • 56.4. Foreign Data Wrapper Query Planning
      • 56.5. Row Locking in Foreign Data Wrappers
    • 60. Writing a Table Sampling Method
    • 61. Writing a Custom Scan Provider
    • 62. Genetic Query Optimizer
      • 59.1. Query Handling as a Complex Optimization Problem
      • 59.2. Genetic Algorithms
      • 59.3. Genetic Query Optimization (GEQO) in PostgreSQL
      • 59.4. Further Reading
    • 63. Table Access Method Interface Definition
    • 64. Index Access Method Interface Definition
    • 65. Generic WAL Records
    • 66. Custom WAL Resource Managers
    • 67. B-Tree Indexes
      • 67.1. Introduction
      • 67.2. Behavior of B-Tree Operator Classes
      • 67.3. B-Tree Support Functions
      • 67.4. Implementation
    • 68. GiST Indexes
      • 64.1. Introduction
      • 64.2. Built-in Operator Classes
      • 64.3. Extensibility
      • 64.4. Implementation
      • 64.5. Examples
    • 69. SP-GiST Indexes
      • 65.1. Introduction
      • 65.2. Built-in Operator Classes
      • 65.3. Extensibility
      • 65.4. Implementation
      • 65.5. Examples
    • 70. GIN 索引
      • 70.1. 簡介
      • 70.2. 內建運算子類
      • 70.3. 延伸介面
      • 70.4. 實作說明
      • 70.5. GIN 小技巧
      • 70.6. 限制
      • 70.7. 範例
    • 71. BRIN Indexes
      • 67.1. Introduction
      • 67.2. Built-in Operator Classes
      • 67.3. Extensibility
    • 72. Hash Indexes
    • 73. 資料庫實體儲存格式
      • 73.1. Database File Layout
      • 73.3. TOAST
      • 68.3. Free Space Map
      • 68.4 可視性映射表(Visibility Map)
      • 68.5. The Initialization Fork
      • 68.6. Database Page Layout
    • 74. System Catalog Declarations and Initial Contents
    • 75. 查詢計畫如何使用統計資訊
      • 70.1. Row Estimation Examples
      • 70.2. 多元統計資訊範例
      • 70.3. Planner Statistics and Security
    • 76. Backup Manifest Format
  • VIII. 附錄
    • A. PostgreSQL 錯誤代碼
    • B. 日期時間格式支援
      • B.1. 日期時間解譯流程
      • B.2. Handling of Invalid or Ambiguous Timestamps
      • B.3. 日期時間慣用字
      • B.4. 日期時間設定檔
      • B.5. POSIX Time Zone Specifications
      • B.6. 日期時間的沿革
      • B.7. Julian Dates
    • C. SQL 關鍵字
    • D. SQL 相容性
      • D.1. Supported Features
      • D.2. Unsupported Features
      • D.3. XML Limits and Conformance to SQL/XML
    • E. 版本資訊
      • E.1. Release 15.2
      • E.2. Release 15.1
      • E.3. Release 15
      • E.4. Prior Releases
    • F. 延伸支援模組
      • F.1. adminpack
      • F.2. amcheck
      • F.3. auth_delay
      • F.4. auto_explain
      • F.5. bloom
      • F.6. btree_gin
      • F.10. dblink
        • dblink_connect
        • dblink_connect_u
        • dblink_disconnect
        • dblink
        • dblink_exec
        • dblink_open
        • dblink_fetch
        • dblink_close
        • dblink_get_connections
        • dblink_error_message
        • dblink_send_query
        • dblink_is_busy
        • dblink_get_notify
        • dblink_get_result
        • dblink_cancel_query
        • dblink_get_pkey
        • dblink_build_sql_insert
        • dblink_build_sql_delete
        • dblink_build_sql_update
      • F.13. earthdistance
      • F.14. file_fdw
      • F.16. hstore
      • F.24. pg_buffercache
      • F.26. passwordcheck
      • F.29. pg_stat_statements
      • F.30. pgstattuple
      • F.31. pg_trgm
      • F.32. pg_visibility
      • F.38. postgres_fdw
      • F.35. sepgsql
      • F.43. tablefunc
      • F.45. test_decoding
      • F.46. tsm_system_rows
      • F.47. tsm_system_time
      • F.49. uuid-ossp
    • G. Additional Supplied Programs
      • G.1. Client Applications
        • oid2name
        • vacuumlo
      • G.2. Server Applications
        • pg_standby
    • H. 外部專案
      • H.1. 用戶端介面
      • H.2. Administration Tools
      • H.3. Procedural Languages
      • H.4. Extensions
    • I. The Source Code Repository
      • I.1. Getting The Source via Git
    • J. 文件取得
      • J.1. DocBook
      • J.2. Tool Sets
      • J.3. Building the Documentation
      • J.4. Documentation Authoring
      • J.5. Style Guide
    • K. PostgreSQL Limits
    • L. 縮寫字
    • M. Glossary
    • N. 色彩支援
      • N.1. When Color is Used
      • N.2. Configuring the Colors
    • O. Obsolete or Renamed Features
  • 參考書目
Powered by GitBook
On this page
  • E.3.1. Overview
  • E.3.2. Migration to Version 15
  • E.3.3. Changes
  • E.3.3.1. Server
  • E.3.3.2. Streaming Replication And Recovery
  • E.3.3.3. Utility Commands
  • E.3.3.4. Data Types
  • E.3.3.5. Functions
  • E.3.3.6. PL/PgSQL
  • E.3.3.7. Libpq
  • E.3.3.8. Client Applications
  • E.3.3.9. Server Applications
  • E.3.3.10. Documentation
  • E.3.3.11. Source Code
  • E.3.3.12. Additional Modules
  • E.3.4. Acknowledgments

Was this helpful?

Edit on GitHub
Export as PDF
  1. VIII. 附錄
  2. E. 版本資訊

E.3. Release 15

PreviousE.2. Release 15.1NextE.4. Prior Releases

Last updated 2 years ago

Was this helpful?

Release date: 2022-10-13

E.3.1. Overview

PostgreSQL 15 contains many new features and enhancements, including:

  • Support for the SQL command.

  • Selective publication of tables' contents within publications, through the ability to specify column lists and row filter conditions.

  • More options for compression, including support for Zstandard (zstd) compression. This includes support for performing compression on the server side during .

  • Support for structured using the JSON format.

  • Performance improvements, particularly for in-memory and on-disk sorting.

The above items and other new features of PostgreSQL 15 are explained in more detail in the sections below.

E.3.2. Migration to Version 15

A dump/restore using or use of or logical replication is required for those wishing to migrate data from any previous release. See for general information on migrating to new major releases.

Version 15 contains a number of changes that may affect compatibility with previous releases. Observe the following incompatibilities:

  • Remove PUBLIC creation permission on the (Noah Misch)

    The new default is one of the secure schema usage patterns that has recommended since the security release for CVE-2018-1058. The change applies to new database clusters and to newly-created databases in existing clusters. Upgrading a cluster or restoring a database dump will preserve public's existing permissions.

    For existing databases, especially those having multiple users, consider revoking CREATE permission on the public schema to adopt this new default. For new databases having no need to defend against insider threats, granting CREATE permission will yield the behavior of prior releases.

  • Change the owner of the public schema to be the new pg_database_owner role (Noah Misch)

    This allows each database's owner to have ownership privileges on the public schema within their database. Previously it was owned by the bootstrap superuser, so that non-superuser database owners could not do anything with it.

    This change applies to new database clusters and to newly-created databases in existing clusters. Upgrading a cluster or restoring a database dump will preserve public's existing ownership specification.

  • Remove long-deprecated (David Steele, Nathan Bossart)

    If the database server stops abruptly while in this mode, the server could fail to start. The non-exclusive backup mode is considered superior for all purposes. Functions pg_start_backup()/pg_stop_backup() have been renamed to pg_backup_start()/pg_backup_stop(), and the functions pg_backup_start_time() and pg_is_in_backup() have been removed.

  • Increase default to 2.0 (Peter Geoghegan)

    This allows query hash operations to use more memory than other operations.

  • Remove server-side language and generic Python language plpythonu (Andres Freund)

    Python 2.x is no longer supported. While the original intent of plpythonu was that it could eventually refer to plpython3u, changing it now seems more likely to cause problems than solve them, so it's just been removed.

  • Generate an error if is passed an empty-string array element (Jean-Christophe Arnu)

    This is prohibited because lexemes should never be empty. Users of previous Postgres releases should verify that no empty lexemes are stored because they can lead to dump/restore failures and inconsistent results.

  • Generate an error when is supplied with a negative argument (Peter Eisentraut)

  • Prevent from changing the collation of an output column (Tom Lane)

  • Disallow zero-length , e.g., U&"" (Peter Eisentraut)

    Non-Unicode zero-length identifiers were already disallowed.

  • Prevent from having non-numeric trailing characters (Peter Eisentraut)

    Previously, query text like 123abc would be interpreted as 123 followed by a separate token abc.

  • Adjust numeric literal processing to match the SQL/JSON-standard (Peter Eisentraut)

    This accepts numeric formats like .1 and 1., and disallows trailing junk after numeric literals, like 1.type().

  • When input provides a fractional value for a unit greater than months, round to the nearest month (Bruce Momjian)

    For example, convert 1.99 years to 2 years, not 1 year 11 months as before.

  • Improve consistency of interval parsing with trailing periods (Tom Lane)

    Numbers with trailing periods were rejected on some platforms.

  • Mark the interval output function as stable, not immutable, since it depends on (Tom Lane)

    This will, for example, cause creation of indexes relying on the text output of interval values to fail.

  • Detect integer overflow in (Joe Koshakow)

    The affected functions are justify_interval(), justify_hours(), and justify_days().

  • Change the I/O format of type "char" for non-ASCII characters (Tom Lane)

    Bytes with the high bit set are now output as a backslash and three octal digits, to avoid encoding issues.

  • Remove the default privilege a login role has on its own role membership (Robert Haas)

    Previously, a login role could add/remove members of its own role, even without ADMIN OPTION privilege.

  • Allow to run as the owner of the subscription (Mark Dilger)

    Because row-level security policies are not checked, only superusers, roles with bypassrls, and table owners can replicate into tables with row-level security policies.

  • Prevent UPDATE and DELETE operations on tables where the subscription owner does not have SELECT permission on the table (Jeff Davis)

    UPDATE and DELETE commands typically involve reading the table as well, so require the subscription owner to have table SELECT permission.

  • When references the session's temporary object schema, refer to it as pg_temp (Amul Sul)

    Previously the actual schema name was reported, leading to inconsistencies across sessions.

  • Fix to sum values for the rare case of TOAST tables with multiple indexes (Andrei Zubkov)

    Previously such cases would show one row for each index.

  • Disallow setting that match the name of an installed extension, but are not one of the extension's declared variables (Florin Irion, Tom Lane)

    This change causes any such pre-existing variables to be deleted during extension load, and then prevents new ones from being created later in the session. The intent is to prevent confusion about whether a variable is associated with an extension or not.

  • Remove obsolete server variable stats_temp_directory (Andres Freund, Kyotaro Horiguchi)

  • Improve the algorithm used to compute (Fabien Coelho)

    This will cause random()'s results to differ from what was emitted by prior versions, even for the same seed value.

  • libpq's function is no longer supported in pipeline mode (Álvaro Herrera)

    Applications that are using that combination will need to be modified to use PQsendQueryParams() instead.

  • On non-Windows platforms, consult the HOME environment variable to find the user's home directory (Anders Kaseorg)

    If HOME is empty or unset, fall back to the previous method of checking the <pwd.h> database. This change affects libpq (for example, while looking up ~/.pgpass) as well as various client application programs.

  • Remove 's --no-synchronized-snapshots option (Tom Lane)

    All still-supported server versions support synchronized snapshots, so there's no longer a need for this option.

  • After an error is detected in 's --single-transaction mode, change the final COMMIT command to ROLLBACK only if ON_ERROR_STOP is set (Michael Paquier)

  • Avoid unnecessary casting of constants in queries sent by (Dian Fay)

    When column types are intentionally different between local and remote databases, such casts could cause errors.

  • Remove 's xml_is_well_formed() function (Tom Lane)

    This function has been implemented in the core backend since Postgres 9.1.

  • Allow to indicate if they support projections (Sven Klemm)

    The default is now that custom scan providers are assumed to not support projections; those that do will need to be updated for this release.

E.3.3. Changes

Below you will find a detailed account of the changes between PostgreSQL 15 and the previous major release.

E.3.3.1. Server

  • This feature is designed to detect collation version changes to avoid index corruption. Function pg_database_collation_actual_version() reports the underlying operating system collation version, and ALTER DATABASE ... REFRESH sets the recorded database collation version to match the operating system collation version.

  • Previously, only libc-based collations could be selected at the cluster and database levels. ICU collations could only be used via explicit COLLATE clauses.

  • Improve planning time for queries referencing partitioned tables (David Rowley)

    This change helps when only a few of many partitions are relevant.

  • Allow ordered scans of partitions to avoid sorting in more cases (David Rowley)

    Previously, a partitioned table with a DEFAULT partition or a LIST partition containing multiple values could not be used for ordered partition scans. Now they can be used if such partitions are pruned during planning.

  • Improve foreign key behavior of updates on partitioned tables that move rows between partitions (Amit Langote)

    Previously, such updates ran a delete action on the source partition and an insert action on the target partition. PostgreSQL will now run an update action on the partition root, providing cleaner semantics.

  • Also prohibit cloned triggers from being renamed.

E.3.3.1.2. Indexes

  • Previously de-duplication was disabled for these types of indexes.

  • Allow unique constraints and indexes to treat NULL values as not distinct (Peter Eisentraut)

    Previously NULL entries were always treated as distinct values, but this can now be changed by creating constraints and indexes using UNIQUE NULLS NOT DISTINCT.

E.3.3.1.3. Optimizer

  • Regular statistics already tracked parent and parent-plus-all-children statistics separately.

E.3.3.1.4. General Performance

  • Previously the code always sequentially scanned the list of values.

  • Allow SELECT DISTINCT to be parallelized (David Rowley)

  • Speed up encoding validation of UTF-8 text by processing 16 bytes at a time (John Naylor, Heikki Linnakangas)

  • When the sort data no longer fits in work_mem, switch to a batch sorting algorithm that uses more output streams than before.

  • Improve performance and reduce memory consumption of in-memory sorts (Ronan Dunklau, David Rowley, Thomas Munro, John Naylor)

  • This only works if max_wal_senders = 0 and wal_level = minimal.

  • Improve the performance of spinlocks on high-core-count ARM64 systems (Geoffrey Blake)

E.3.3.1.5. Monitoring

  • Enable default logging of checkpoints and slow autovacuum operations (Bharath Rupireddy)

  • Generate progress messages in the server log during slow server starts (Nitin Jadhav, Robert Haas)

  • Previously this data was sent to a statistics collector process via UDP packets, and could only be read by sessions after transferring it via the file system. There is no longer a separate statistics collector process.

  • Add additional information to VACUUM VERBOSE and autovacuum logging messages (Peter Geoghegan)

  • The new setting is log_destination = jsonlog.

  • The new wait events are used when calling archive_command, archive_cleanup_command, restore_command and recovery_end_command.

E.3.3.1.6. Privileges

  • Previously, view accesses were always treated as being done by the view's owner. That's still the default.

  • Previously only superusers could perform such backups.

  • The new function has_parameter_privilege() reports on this privilege.

  • Previously checkpoints could only be run by superusers.

  • Previously these views could only be accessed by superusers.

  • Previously this function could only be run by superusers.

E.3.3.1.7. Server Configuration

  • This is only supported on Linux.

  • This change supports use of shared_preload_libraries to load custom access methods and WAL resource managers, which would be essential for database access even in single-user mode.

  • The previous default choice, posix, can result in spurious failures on this platform.

E.3.3.2. Streaming Replication And Recovery

  • Run the checkpointer and bgwriter processes during crash recovery (Thomas Munro)

    This helps to speed up long crash recoveries.

  • Allow WAL processing to pre-fetch needed file contents (Thomas Munro)

  • Allow archiving via loadable modules (Nathan Bossart)

  • For example, this syntax is now supported: CREATE PUBLICATION pub1 FOR TABLES IN SCHEMA s1,s2. ALTER PUBLICATION supports a similar syntax. Tables added later to the listed schemas will also be replicated.

  • Allow publication content to be filtered using a WHERE clause (Hou Zhijie, Euler Taveira, Peter Smith, Ajin Cherian, Tomas Vondra, Amit Kapila)

    Rows not satisfying the WHERE clause are not published.

  • Allow publication content to be restricted to specific columns (Tomas Vondra, Álvaro Herrera, Rahila Syed)

  • Add support for prepared (two-phase) transactions to logical replication (Peter Smith, Ajin Cherian, Amit Kapila, Nikhil Sontakke, Stas Kelvich)

  • Prevent logical replication of empty transactions (Ajin Cherian, Hou Zhijie, Euler Taveira)

    Previously, publishers would send empty transactions to subscribers if subscribed tables were not modified.

  • Add SQL functions to monitor the directory contents of logical replication slots (Bharath Rupireddy)

  • Allow subscribers to stop the application of logical replication changes on error (Osumi Takamichi, Mark Dilger)

  • Adjust subscriber server variables to match the publisher so datetime and float8 values are interpreted consistently (Japin Li)

    Some publishers might be relying on inconsistent behavior.

  • In some cases a partition could appear more than once.

E.3.3.3. Utility Commands

  • This is similar to INSERT ... ON CONFLICT but more batch-oriented.

  • The new option causes the column names to be output, and optionally verified on input.

  • This is the new default method for copying the template database, as it avoids the need for checkpoints during database creation. However, it might be slow if the template database is large, so the old method is still available.

  • Previously, all of the columns in the foreign key were always affected.

  • Track dependencies on individual columns in the results of functions returning composite types (Tom Lane)

    Previously, if a view or rule contained a reference to a specific column within the result of a composite-returning function, that was not noted as a dependency; the view or rule was only considered to depend on the composite type as a whole. This meant that dropping the individual column would be allowed, causing problems in later use of the view or rule. The column-level dependency is now also noted, so that dropping such a column will be rejected unless the view is changed or dropped.

E.3.3.4. Data Types

  • This allows rounding of values to the left of the decimal point, e.g., '1234'::numeric(4, -2) returns 1200.

  • Change the I/O format of type "char" for non-ASCII characters (Tom Lane)

  • Update the display width information of modern Unicode characters, like emojis (Jacob Champion)

    Also update from Unicode 5.0 to 14.0.0. There is now an automated way to keep Postgres updated with Unicode releases.

E.3.3.5. Functions

  • Add regular expression functions for compatibility with other relational systems (Gilles Darold, Tom Lane)

  • The upper-case equivalents of these were already supported.

  • This allows the conversion to be considered stable rather than volatile, and it saves a kernel call per invocation.

  • These functions effectively ignore empty-string array elements (since those could never match a valid lexeme). It seems consistent to let them ignore NULL elements too, instead of failing.

  • Previously this function reported all temporary schemas as pg_temp, but it's misleading to use that for any but the current session's temporary schema.

  • Fix enforcement of PL/pgSQL variable CONSTANT markings (Tom Lane)

  • Allow IP address matching against a server certificate's Subject Alternative Name (Jacob Champion)

  • Allow PQsslAttribute() to report the SSL library type without requiring a libpq connection (Jacob Champion)

  • Change query cancellations sent by the client to use the same TCP settings as normal client connections (Jelte Fennema)

    This allows configured TCP timeouts to apply to query cancel connections.

  • Prevent libpq event callback failures from forcing an error result (Tom Lane)

E.3.3.8. Client Applications

  • Improve performance of psql's \copy command, by sending data in larger chunks (Heikki Linnakangas)

  • Add \dconfig command to report server variables (Mark Dilger, Tom Lane)

    This is similar to the server-side SHOW command, but it can process patterns to show multiple variables conveniently.

  • Add \getenv command to assign the value of an environment variable to a psql variable (Tom Lane)

  • Add + option to the \lo_list and \dl commands to show large-object privileges (Pavel Luzanov)

  • Add a pager option for the \watch command (Pavel Stehule, Thomas Munro)

    This is only supported on Unix and is controlled by the PSQL_WATCH_PAGER environment variable.

  • Make psql include intra-query double-hyphen comments in queries sent to the server (Tom Lane, Greg Nancarrow)

    Previously such comments were removed from the query before being sent. Double-hyphen comments that are before any query text are not sent, and are not recorded as separate psql history entries.

  • Adjust psql so that Readline's meta-# command will insert a double-hyphen comment marker (Tom Lane)

    Previously a pound marker was inserted, unless the user had taken the trouble to configure a non-default comment marker.

  • Make psql output all results when multiple queries are passed to the server at once (Fabien Coelho)

    Previously, only the last query result was displayed. The old behavior can be restored by setting the SHOW_ALL_RESULTS psql variable to off.

  • After an error is detected in --single-transaction mode, change the final COMMIT command to ROLLBACK only if ON_ERROR_STOP is set (Michael Paquier)

    Previously, detection of an error in a -c command or -f script file would lead to issuing ROLLBACK at the end, regardless of the value of ON_ERROR_STOP.

  • Improve psql's tab completion (Shinya Kato, Dagfinn Ilmari Mannsåker, Peter Smith, Koyu Tanigawa, Ken Kato, David Fetter, Haiying Tang, Peter Eisentraut, Álvaro Herrera, Tom Lane, Masahiko Sawada)

  • Limit support of psql's backslash commands to servers running PostgreSQL 9.2 or later (Tom Lane)

    Remove code that was only used when running with an older server. Commands that do not require any version-specific adjustments compared to 9.2 will still work.

  • Make pg_dump dump public schema ownership changes and security labels (Noah Misch)

  • Improve performance of dumping databases with many objects (Tom Lane)

  • Improve parallel pg_dump's performance for tables with large TOAST tables (Tom Lane)

  • Add dump/restore option --no-table-access-method to force restore to only use the default table access method (Justin Pryzby)

E.3.3.9. Server Applications

  • The new options are server to write the backup locally and blackhole to discard the backup (for testing).

  • Allow pg_basebackup to do server-side gzip, LZ4, and Zstandard compression and client-side LZ4 and Zstandard compression of base backup files (Dipesh Pandit, Jeevan Ladhe)

    Client-side gzip compression was already supported.

  • Allow pg_basebackup to compress on the server side and decompress on the client side before storage (Dipesh Pandit)

    This is accomplished by specifying compression on the server side and plain output format.

  • Allow pg_basebackup's --compress option to control the compression location (server or client), compression method, and compression options (Michael Paquier, Robert Haas)

  • This is enabled via --compress=lz4 and requires binaries to be built using --with-lz4.

  • Add additional capabilities to pg_receivewal's --compress option (Georgios Kokolatos)

  • Improve pg_receivewal's ability to restart at the proper WAL location (Ronan Dunklau)

    Previously, pg_receivewal would start based on the WAL file stored in the local archive directory, or at the sending server's current WAL flush location. With this change, if the sending server is running Postgres 15 or later, the local archive directory is empty, and a replication slot is specified, the replication slot's restart point will be used.

  • Store pg_upgrade's log and temporary files in a subdirectory of the new cluster called pg_upgrade_output.d (Justin Pryzby)

    Previously such files were left in the current directory, requiring manual cleanup. Now they are automatically removed on successful completion of pg_upgrade.

  • Disable default status reporting during pg_upgrade operation if the output is not a terminal (Andres Freund)

    The status reporting output can be enabled for non-tty usage by using --verbose.

  • Make pg_upgrade report all databases with invalid connection settings (Jeevan Ladhe)

    Previously only the first database with an invalid connection setting was reported.

  • Make pg_upgrade preserve tablespace and database OIDs, as well as relation relfilenode numbers (Shruthi Gowda, Antonin Houska)

  • Add a --no-sync option to pg_upgrade (Michael Paquier)

    This is recommended only for testing.

  • Limit support of pg_upgrade to old servers running PostgreSQL 9.2 or later (Tom Lane)

  • Allow pg_waldump output to be filtered by relation file node, block number, fork number, and full page images (David Christensen, Thomas Munro)

  • Make pg_waldump report statistics before an interrupted exit (Bharath Rupireddy)

    For example, issuing a control-C in a terminal running pg_waldump --stats --follow will report the current statistics before exiting. This does not work on Windows.

  • Improve descriptions of some transaction WAL records reported by pg_waldump (Masahiko Sawada, Michael Paquier)

  • Allow pg_waldump to dump information about multiple resource managers (Heikki Linnakangas)

    This is enabled by specifying the --rmgr option multiple times.

E.3.3.10. Documentation

E.3.3.11. Source Code

  • Add support for continuous integration testing using cirrus-ci (Andres Freund, Thomas Munro, Melanie Plageman)

  • Add an ABI identifier field to the magic block in loadable libraries, allowing non-community PostgreSQL distributions to identify libraries that are not compatible with other builds (Peter Eisentraut)

    An ABI field mismatch will generate an error at load time.

  • Some other internal-use-only types have also been assigned to this category.

  • Remove server support for old BASE_BACKUP command syntax and base backup protocol (Robert Haas)

  • Add support for extensions to set custom backup targets (Robert Haas)

  • Allow extensions to define custom WAL resource managers (Jeff Davis)

  • On Windows, export all the server's global variables using PGDLLIMPORT markers (Robert Haas)

    Previously, only specific variables were accessible to extensions on Windows.

  • Require GNU make version 3.81 or later to build PostgreSQL (Tom Lane)

  • Require Perl version 5.8.3 or later (Dagfinn Ilmari Mannsåker)

  • Require Python version 3.2 or later (Andres Freund)

E.3.3.12. Additional Modules

  • Improve amcheck sanity checks for TOAST tables (Mark Dilger)

  • These can be used for exclusion constraints.

  • Previously, improper negative values could be returned in certain cases.

  • Add JIT counters to pg_stat_statements (Magnus Hagander)

  • Allow postgres_fdw to push down CASE expressions (Alexander Pyhalov)

  • Add server variable postgres_fdw.application_name to control the application name of postgres_fdw connections (Hayato Kuroda)

  • Allow parallel commit on postgres_fdw servers (Etsuro Fujita)

    This is enabled with the CREATE SERVER option parallel_commit.

E.3.4. Acknowledgments

The following individuals (in alphabetical order) have contributed to this release as patch authors, committers, reviewers, testers, or reporters of issues.

Abhijit Menon-Sen

Adam Brusselback

Adam Mackler

Adrian Ho

Ahsan Hadi

Ajin Cherian

Alastair McKinley

Aleksander Alekseev

Ales Zeleny

Alex Kingsborough

Alex Kozhemyakin

Alexander Korotkov

Alexander Kukushkin

Alexander Lakhin

Alexander Nawratil

Alexander Pyhalov

Alexey Borzov

Alexey Ermakov

Aliaksandr Kalenik

Álvaro Herrera

Amit Kapila

Amit Khandekar

Amit Langote

Amul Sul

Anastasia Lubennikova

Anders Kaseorg

Andreas Dijkman

Andreas Grob

Andreas Seltenreich

Andrei Zubkov

Andres Freund

Andrew Alsup

Andrew Bille

Andrew Dunstan

Andrew Gierth

Andrew Kesper

Andrey Borodin

Andrey Lepikhov

Andrey Sokolov

Andy Fan

Anton Melnikov

Anton Voloshin

Antonin Houska

Arjan van de Ven

Arne Roland

Arthur Zakirov

Ashutosh Bapat

Ashutosh Sharma

Ashwin Agrawal

Asif Rehman

Asim Praveen

Atsushi Torikoshi

Aya Iwata

Bauyrzhan Sakhariyev

Benoit Lobréau

Bernd Dorn

Bertrand Drouvot

Bharath Rupireddy

Björn Harrtell

Boris Kolpackov

Boris Korzun

Brad Nicholson

Brar Piening

Bruce Momjian

Bruno da Silva

Bryn Llewellyn

Carl Sopchak

Cary Huang

Chapman Flack

Chen Jiaoqian

Chris Bandy

Chris Lowder

Christian Quest

Christoph Berg

Christoph Heiss

Christophe Pettus

Christopher Painter-Wakefield

Claudio Freire

Clemens Zeidler

Corey Huinker

Dag Lem

Dagfinn Ilmari Mannsåker

Dan Kubb

Daniel Cherniy

Daniel Gustafsson

Daniel Polski

Daniel Vérité

Daniel Westermann

Daniele Varrazzo

Daniil Anisimov

Danny Shemesh

Darafei Praliaskouski

Daria Lepikhova

Dave Cramer

Dave Page

David Christensen

David Fetter

David G. Johnston

David Rowley

David Steele

David Zhang

Dean Rasheed

Dian Fay

Dilip Kumar

Dipesh Pandit

Dmitry Dolgov

Dmitry Koval

Dmitry Marakasov

Dominique Devienne

Dong Wook

Drew DeVault

Eduard Català

Egor Chindyaskin

Egor Rogov

Ekaterina Kiryanova

Elena Indrupskaya

Elvis Pranskevichus

Emmanuel Quincerot

Emre Hasegeli

Eric Mutta

Erica Zhang

Erik Rijkers

Erki Eessaar

Etsuro Fujita

Euler Taveira

Fabien Coelho

Fabrice Chapuis

Fabrice Fontaine

Fabrízio de Royes Mello

Feike Steenbergen

Filip Gospodinov

Florin Irion

Floris Van Nee

Frédéric Yhuel

Gabriela Serventi

Gaurab Dey

Geoff Winkless

Geoffrey Blake

Georgios Kokolatos

Gilles Darold

Greg Nancarrow

Greg Rychlewski

Greg Sabino Mullane

Greg Stark

Gregory Smith

Guillaume Lelarge

Gunnar Bluth

Gurjeet Singh

Haiyang Wang

Haiying Tang

Hannu Krosing

Hans Buschmann

Hayato Kuroda

Heath Lord

Heikki Linnakangas

Herwig Goemans

Himanshu Upadhyaya

Holly Roberts

Hou Zhijie

Hubert Lubaczewski

Ian Barwick

Ian Campbell

Ibrar Ahmed

Ildus Kurbangaliev

Ilya Anfimov

Itamar Gafni

Jacob Champion

Jaime Casanova

Jakub Wartak

James Coleman

James Hilliard

James Inform

Jan Piotrowski

Japin Li

Jason Harvey

Jason Kim

Jean-Christophe Arnu

Jeevan Ladhe

Jeff Davis

Jeff Janes

Jehan-Guillaume de Rorthais

Jelte Fennema

Jeremy Evans

Jeremy Schneider

Jian Guo

Jian He

Jimmy Yih

Jiri Fejfar

Jitka Plesníková

Joe Conway

Joe Wildish

Joel Jacobson

Joey Bodoia

John Naylor

Jonathan Katz

Josef Simanek

Joseph Koshakow

Josh Soref

Joshua Brindle

Juan José Santamaría Flecha

Julien Rouhaud

Julien Roze

Junwang Zhao

Jürgen Purtz

Justin Pryzby

Ken Kato

Kevin Burke

Kevin Grittner

Kevin Humphreys

Kevin McKibbin

Kevin Sweet

Kevin Zheng

Klaudie Willis

Konstantin Knizhnik

Konstantina Skovola

Kosei Masumura

Kotaro Kawamoto

Koyu Tanigawa

Kuntal Ghosh

Kyotaro Horiguchi

Lars Kanis

Lauren Fliksteen

Laurent Hasson

Laurenz Albe

Leslie Lemaire

Liam Bowen

Lingjie Qiang

Liu Huailing

Louis Jachiet

Lukas Fittl

Ma Liangzhu

Maciek Sakrejda

Magnus Hagander

Mahendra Singh Thalor

Maksim Milyutin

Marc Bachmann

Marcin Krupowicz

Marcus Gartner

Marek Szuba

Marina Polyakova

Mario Emmenlauer

Mark Dilger

Mark Murawski

Mark Wong

Markus Wanner

Markus Winand

Martijn van Oosterhout

Martin Jurca

Martin Kalcher

Martín Marqués

Masahiko Sawada

Masahiro Ikeda

Masao Fujii

Masaya Kawamoto

Masayuki Hirose

Matthias van de Meent

Matthijs van der Vleuten

Maxim Orlov

Maxim Yablokov

Melanie Plageman

Michael Banck

Michael Harris

Michael J. Sullivan

Michael Meskes

Michael Mühlbeyer

Michael Paquier

Michael Powers

Mike Fiedler

Mike Oh

Mikhail Kulagin

Miles Delahunty

Naoki Okano

Nathan Bossart

Nathan Long

Nazir Bilal Yavuz

Neha Sharma

Neil Chen

Nicola Contu

Nicolas Lutic

Nikhil Benesch

Nikhil Shetty

Nikhil Sontakke

Nikita Glukhov

Nikolai Berkoff

Nikolay Samokhvalov

Nikolay Shaplov

Nitin Jadhav

Noah Misch

Noboru Saito

Noriyoshi Shinoda

Olaf Bohlen

Olly Betts

Onder Kalaci

Oskar Stenberg

Otto Kekalainen

Paul Guo

Paul Jungwirth

Paul Martinez

Pavan Deolasee

Pavel Borisov

Pavel Luzanov

Pavel Stehule

Peter Eisentraut

Peter Geoghegan

Peter Slavov

Peter Smith

Petr Jelínek

Phil Florent

Phil Krylov

Pierre-Aurélien Georges

Prabhat Sahu

Quan Zongliang

Rachel Heaton

Rahila Syed

Rajakavitha Kodhandapani

Rajkumar Raghuwanshi

Ranier Vilela

Rei Kamigishi

Reid Thompson

Rémi Lapeyre

Renan Soares Lopes

Richard Guo

Richard Wesley

RKN Sai Krishna

Robert Haas

Robert Treat

Roberto Mello

Robins Tharakan

Roger Mason

Roman Zharkov

Ronan Dunklau

Rui Zhao

Ryan Kelly

Ryo Matsumura

Ryohei Takahashi

Sadhuprasad Patro

Sait Talha Nisanci

Sami Imseih

Sandeep Thakkar

Sebastian Kemper

Sehrope Sarkuni

Sergei Kornilov

Sergei Shoulbakov

Sergey Shinderuk

Shay Rojansky

Shenhao Wang

Shi Yu

Shinya Kato

Shruthi Gowda

Simon Perepelitsa

Simon Riggs

Sirisha Chamarthi

Soumyadeep Chakraborty

Stan Hu

Stas Kelvich

Stefen Hillman

Stephen Frost

Steve Chavez

Sumanta Mukherjee

Suraj Khamkar

Suraj Kharage

Sven Klemm

Takamichi Osumi

Takayuki Tsunakawa

Takeshi Ideriha

Tatsuhiro Nakamori

Tatsuhito Kasahara

Tatsuo Ishii

Tatsuro Yamada

Teja Mupparti

Teodor Sigaev

Thibaud Walkowiak

Thom Brown

Thomas McKay

Thomas Munro

Tim McNamara

Timo Stolz

Timur Khanjanov

Tom Lane

Tomas Barton

Tomas Vondra

Tony Reix

Troy Frericks

Tushar Ahuja

Victor Wagner

Victor Yegorov

Vignesh C

Vik Fearing

Vincas Dargis

Vitaly Burovoy

Vitaly Voronov

Vladimir Sitnikov

Wang Ke

Wei Sun

Wei Wang

Whale Song

Will Mortensen

Wolfgang Walther

Yanliang Lei

Yaoguang Chen

Yogendra Suralkar

YoungHwan Joo

Yugo Nagata

Yukun Wang

Yura Sokolov

Yusuke Egashira

Yuzuko Hosoya

Zhang Mingli

Zhang Wenjie

Zhihong Yu

Zhiyong Wu

Record and check the collation version of each (Peter Eisentraut)

Allow collations to be set as the default for clusters and databases (Peter Eisentraut)

Add system view to report pg_ident.conf information (Julien Rouhaud)

E.3.3.1.1.

Allow on partitioned tables (Justin Pryzby)

Fix on partitioned tables to properly rename triggers on all partitions (Arne Roland, Álvaro Herrera)

Allow btree indexes on system and tables to efficiently store duplicates (Peter Geoghegan)

Improve lookup performance of indexes that were built using sorting (Aliaksandr Kalenik, Sergei Shoulbakov, Andrey Borodin)

Allow the starts-with operator and the starts_with() function to use btree indexes if using the C collation (Tom Lane)

Previously these could only use indexes.

Allow to record statistics for a parent with all its children (Tomas Vondra, Justin Pryzby)

Add server variable to allow the user to specify the expected size of the working table of a (Simon Riggs)

Allow hash lookup for clauses with many constants (David Rowley, James Coleman)

This will improve text-heavy operations like .

Improve performance for sorts that exceed (Heikki Linnakangas)

Allow WAL to use LZ4 and Zstandard compression (Andrey Borodin, Justin Pryzby)

This is controlled by the server setting.

Add support for writing WAL using on macOS (Thomas Munro)

Allow to be more aggressive in setting the oldest frozen and multi transaction id (Peter Geoghegan)

Allow a query referencing multiple to perform parallel foreign table scans in more cases (Andrey Lepikhov, Etsuro Fujita)

Improve the performance of that use row_number(), rank(), dense_rank() and count() (David Rowley)

This changes the default of to on and that of to 10 minutes. This will cause even an idle server to generate some log output, which might cause problems on resource-constrained servers without log file rotation. These defaults should be changed in such cases.

The messages report the cause of the delay. The time interval for notification is controlled by the new server variable .

Store data in shared memory (Kyotaro Horiguchi, Andres Freund, Melanie Plageman)

Add output for temporary file block I/O (Masahiko Sawada)

Allow in JSON format (Sehrope Sarkuni, Michael Paquier)

Allow to reset the counters of relations shared across all databases (Sadhuprasad Patro)

Add for local shell commands (Fujii Masao)

Allow table accesses done by a to optionally be controlled by privileges of the view's caller (Christoph Heiss)

Allow members of the predefined role to perform server-side base backups (Dagfinn Ilmari Mannsåker)

Allow to grant permissions to change individual server variables via SET and ALTER SYSTEM (Mark Dilger)

Add predefined role that allows members to run CHECKPOINT (Jeff Davis)

Allow members of the predefined role to access the views and (Bharath Rupireddy)

Allow to grant permissions on (Jeff Davis)

Add server variable to report the size of allocated shared memory (Nathan Bossart)

Add server variable to report the number of huge memory pages required (Nathan Bossart)

Honor server variable in single-user mode (Jeff Davis)

On Solaris, make the default setting of be sysv (Thomas Munro)

Allow to properly report runtime-computed values (Nathan Bossart)

Previously runtime-computed values , , and would report values that would not be accurate on the running server. However, this does not work on a running server.

Add support for LZ4 and Zstandard compression of server-side (Jeevan Ladhe, Robert Haas)

This is controlled by the server variable .

Previously, archiving was only done by calling shell commands. The new server variable can be set to specify a library to be called for archiving.

No longer require to be run before START_REPLICATION (Jeff Davis)

E.3.3.2.1.

Allow of all tables in a schema (Vignesh C, Hou Zhijie, Amit Kapila)

Allow skipping of transactions on a subscriber using (Masahiko Sawada)

The new option is called TWO_PHASE. pg_recvlogical now supports a new --two-phase option during slot creation.

The new functions are , pg_ls_logicalmapdir(), and pg_ls_replslotdir(). They can be run by members of the predefined pg_monitor role.

This is enabled with the subscriber option and avoids possible infinite error loops during stream application.

Add system view to report on subscriber activity (Masahiko Sawada)

The new function allows resetting these statistics counters.

Suppress duplicate entries in the system view (Hou Zhijie)

Add SQL command to adjust one table to match another (Simon Riggs, Pavan Deolasee, Álvaro Herrera, Amit Langote)

Add support for HEADER option in text format (Rémi Lapeyre)

Add new WAL-logged method for (Dilip Kumar)

Allow to set the database OID (Shruthi Gowda, Antonin Houska)

Prevent , , and from occasionally failing during concurrent use on Windows (Thomas Munro)

Allow foreign key actions to affect only specified columns (Paul Martinez)

Allow to modify a table's ACCESS METHOD (Justin Pryzby, Jeff Davis)

Properly call object access hooks when causes table rewrites (Michael Paquier)

Allow creation of unlogged (Peter Eisentraut)

Allow the scale of a value to be negative, or greater than its precision (Dean Rasheed, Tom Lane)

Improve overflow detection when casting values to (Joe Koshakow)

Add multirange input to (Paul Jungwirth)

Add and MAX() aggregates for the data type (Ken Kato)

The new functions are , regexp_instr(), regexp_like(), and regexp_substr(). Some new optional arguments were also added to regexp_replace().

Add the ability to compute the distance between (Tom Lane)

Add format codes of, tzh, and tzm (Nitin Jadhav)

When applying to a time with time zone value, use the transaction start time rather than wall clock time to determine whether DST applies (Aleksander Alekseev, Tom Lane)

Ignore NULL array elements in and setweight() functions with array arguments (Jean-Christophe Arnu)

Add support for petabyte units to and pg_size_bytes() (David Christensen)

Change to output references to other sessions' temporary schemas using the actual schema name (Tom Lane)

E.3.3.6.

Previously, a variable could be used as a output parameter or refcursor OPEN variable despite being marked CONSTANT.

E.3.3.7.

Allow to retry after serialization and deadlock failures (Yugo Nagata, Marina Polyakova)

E.3.3.8.1.

E.3.3.8.2.

This will also improve the performance of .

Limit support of pg_dump and to servers running PostgreSQL 9.2 or later (Tom Lane)

Add new option --target to control the base backup location (Robert Haas)

Add the LZ4 compression method to (Georgios Kokolatos)

Add option --config-file to simplify use when server configuration files are stored outside the data directory (Gunnar Bluth)

E.3.3.9.1.

E.3.3.9.2.

Add documentation for and pg_char_to_encoding() (Ian Lawrence Barwick)

Document the starts-with operator (Tom Lane)

Add configure option to enable Zstandard builds (Jeevan Ladhe, Robert Haas, Michael Paquier)

Create a new value for "char" (Tom Lane)

Add new protocol message to specify a new COPY method to be used for base backups (Robert Haas)

now uses this method.

Add new protocol message and COMPRESSION_DETAIL to specify the compression method and options (Robert Haas)

Add function to get the flags of server variables (Justin Pryzby)

Require OpenSSL to build the extension (Peter Eisentraut)

Allow to check sequences (Mark Dilger)

Add new module as an example of a custom backup target (Robert Haas)

Add new module as an example of performing archiving via a library (Nathan Bossart)

Allow indexes on boolean columns (Emre Hasegeli)

Fix 's page_header() to handle 32-kilobyte page sizes (Quan Zongliang)

Add counters for temporary file block I/O to (Masahiko Sawada)

Add new module (Bharath Rupireddy)

This gives SQL-level output similar to .

Indicate the permissive/enforcing state in log messages (Dave Page)

E.3.3.12.1.

Previously the remote session's could only be set on the remote server or via a postgres_fdw connection specification. postgres_fdw.application_name supports some escape sequences for customization, making it easier to tell such connections apart on the remote server.

MERGE
logical replication
pg_basebackup
server log output
pg_dumpall
pg_upgrade
Section 19.6
public schema
Section 5.9.6
exclusive backup mode
hash_mem_multiplier
work_mem
plpython2u
array_to_tsvector()
chr()
CREATE OR REPLACE VIEW
Unicode identifiers
numeric literals
JSON
interval
IntervalStyle
interval justification functions
ADMIN OPTION
logical replication
logical replication
EXPLAIN
pg_statio_all_tables
custom options
random()
PQsendQuery()
pg_dump
psql
postgres_fdw
xml2
custom scan providers
database
ICU
pg_ident_file_mappings
Partitioning
CLUSTER
ALTER TRIGGER RENAME
TOAST
GiST
^@
SP-GiST
extended statistics
recursive_worktable_factor
recursive query
NOT IN
COPY FROM
work_mem
full page writes
wal_compression
direct I/O
vacuum
foreign tables
window functions
log_checkpoints
log_autovacuum_min_duration
log_startup_progress_interval
cumulative statistics system
EXPLAIN (BUFFERS)
log output
pg_stat_reset_single_table_counters()
wait events
view
pg_write_server_files
GRANT
pg_checkpoint
pg_read_all_stats
pg_backend_memory_contexts
pg_shmem_allocations
GRANT
pg_log_backend_memory_contexts()
shared_memory_size
shared_memory_size_in_huge_pages
shared_preload_libraries
dynamic_shared_memory_type
postgres -C
data_checksums
wal_segment_size
data_directory_mode
base backups
recovery_prefetch
archive_library
IDENTIFY_SYSTEM
Logical Replication
publication
ALTER SUBSCRIPTION ... SKIP
CREATE_REPLICATION_SLOT
pg_ls_logicalsnapdir()
disable_on_error
pg_stat_subscription_stats
pg_stat_reset_subscription_stats()
pg_publication_tables
MERGE
COPY
database creation
CREATE DATABASE
DROP DATABASE
DROP TABLESPACE
ALTER DATABASE SET TABLESPACE
ON DELETE SET
ALTER TABLE
ALTER TABLE
sequences
numeric
interval
range_agg()
MIN()
xid8
regexp_count()
polygons
to_char()
AT TIME ZONE
ts_delete()
pg_size_pretty()
pg_event_trigger_ddl_commands()
PL/PgSQL
CALL
Libpq
pgbench
psql
pg_dump
pg_upgrade
pg_dumpall
pg_basebackup
pg_receivewal
pg_rewind
pg_upgrade
pg_waldump
pg_encoding_to_char()
^@
--with-zstd
pg_type.typcategory
TARGET
pg_basebackup
COMPRESSION
pg_settings_get_flags()
pgcrypto
amcheck
basebackup_to_shell
basic_archive
btree_gist
pageinspect
pg_stat_statements
pg_walinspect
pg_waldump
sepgsql
postgres_fdw
application_name