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
  • 13.2.1. Read Committed 隔離等級
  • 13.2.2. Repeatable Read 隔離等級
  • 13.2.3. Serializable 隔離等級

Was this helpful?

Edit on GitHub
Export as PDF
  1. II. SQL 查詢語言
  2. 13. 一致性管理(Concurrency Control)

13.2. 交易隔離

Previous13.1. 簡介Next13.3. 鎖定模式

Last updated 1 year ago

Was this helpful?

SQL 標準中定義了四個等級的交易隔離,其中最嚴格的隔離是「Serializable(序列化)」。序列化在標準的描述中,被定義為任意序列化交易的並行操作,都會保證產出與依照某種任意順序一個一個執行它們的效果相同。其他三個等級則是經由現象來定義的,即在不同的並行交易間的互動,這些現象在各個等級中必不能發生。標準中也注意到,基於 Serializable 的定義,這些現象都不可能發生在 Serializable 等級。(這並不難理解 -- 如果交易的影響必須與一個一個執行的結果一致,你怎麼會看到這些因為互動而產生的現象呢?)

在不同等級中被禁止的現象是:

dirty read(髒讀) 交易會讀取到尚未交易完成的資料。

nonrepeatable read(無法重複的讀取) 交易重新讀取時,會讀取到它之前得到過的資料,會發現資料被其他交易修改了(在最初讀取之後提交的)。

phantom read(幻讀) 交易重新執行相同搜尋條件的資料查詢,會得到最近剛提交的資料變更結果。

serialization anomaly(序列化異常) 在成功提交一群交易後,結果與以所有可能的順序依序執行交易的結果都不一致。

SQL 標準以及 PostgreSQL 實作的交易隔離等級,可參閱 。

Table 13.1. 交易隔離等級

隔離等級
Dirty Read
Nonrepeatable Read
Phantom Read
Serialization Anomaly

Read uncommitted

允許,但 PG 中不會

可能

可能

可能

Read committed

不可能

可能

可能

可能

Repeatable read

不可能

不可能

允許,但 PG 中不會

可能

Serializable

不可能

不可能

不可能

不可能

在 PostgreSQL 當中,你可以要求上述四種交易隔離等級的任何一種,但在 PostgreSQL 內部實際上實作的只有三種隔離等級。舉例來說,PostgreSQL 的 Read Uncommitted 模式跟 Read Committed 的行為很相像,這是因為這是能夠把標準的隔離等級對應到 PostgreSQL 的 MVCC 架構的明智方法。

表格中也顯示了 PostgreSQL 的 Repeatable Read 的實作並不允許 phantom read。關於這點,在 SQL 標準中,更嚴格的行為是被容許的:標準中定義的四個隔離等級,只定義了哪些現象必須不會發生,而沒有定義哪些現象 必須 發生。可用的隔離等級的行為,在接下來的小節中會詳細描述。

若要設定交易的隔離等級,可使用指令 。

有些 PostgreSQL 的資料型態和函式具有特殊的交易行為的規則。具體來說,對序列(以及以 serial 宣告的計算器欄位)造成的變更將會立刻能夠被所有其他交易所看見,並且即使造成變更的交易取消了也不會被還原。請參考 及 。

13.2.1. Read Committed 隔離等級

Read Committed(提交讀) 是 PostgreSQL 預設的隔離等級。當交易使用這個隔離等級時,一個 SELECT 的查詢(沒有 FOR UPDATE/SHARE 的宣告)只會看見在查詢開始前已經被提交的資料;它不會看見尚未提交的資料或者是在查詢執行過程中被並行的交易提交的變更。實際上,SELECT 查詢是看見了在查詢開始執行的那一瞬間的資料庫快照。然而,SELECT 會看見在它自己的交易中比它先執行的更新,即使那些更新尚未被提交。同時需要注意的是,對於兩個成功的 SELECT 指令,如果其他交易在第一個 SELECT 開始後、第二個 SELECT 開始前提交變更,則即使這兩個 SELECT 在同一個交易中也可能會看見不同的資料,

UPDATE、DELETE、SELECT FOR UPDATE、和 SELECT FOR SHARE 在搜尋目標資料列的行為與 SELECT 一樣:它們只會找出在指令開始時已經被提交的目標資料列。然而,這些資料列在被找到的時候,有可能已經被其他並行交易更新(或者刪除、鎖定),在這個情況下,這個更新者會等待第一個更新的交易提交或者還原(如果那個交易還正在進行中)。如果第一個更新者還原了,那麼它的影響就無效了,第二個更新者會以原本找到的資料列做更新。而當第一個更新者提交了的狀況,若它把資料列刪除,則第二個更新者會忽略這個資料列,否則其他狀況下第二個更新者會嘗試在更新後的資料列上套用它要做的操作。此時指令中的搜尋條件(WHERE 宣告)會被重新評估,看看更新後的資料列是否依然符合搜尋條件,如果符合的話,第二個更新者就會對更新後的資料列套用它的操作。在 SELECT FOR UPDATE 和 SELECT FOR SHARE 的案例中,這代表會對更新後的資料列上鎖並回傳給客戶端。

包含 ON CONFLICT DO UPDATE 宣告的 INSERT 的行為也很相似。在 Read Committed 模式中,每個插入的資料列會被插入或者更新,除非有其他不相關的錯誤,否則會保證兩種結果的其中一種發生。如果衝突來自於其影響尚未能被 INSERT 所看見的其他交易,即使指令可能 沒有 該資料列在慣例上可見的版本,UPDATE 宣告也將會影響那個資料列。

包含 ON CONFLICT DO NOTHING 宣告的 INSERT 在其他交易的影響尚未在 INSERT 的快照中可見的狀況,有可能不會執行插入。再次強調,這是只有在 Read Committed 模式的案例。

因為上述的規則,對更新的指令來說有可能會看見不一致的快照:它能夠看見其他並行的更新指令對它嘗試要更新的資料列的影響,但它不會看見這些其他指令對資料庫裡的其他資料列的影響。這個行為使得 Read Committed 模式並不適合牽涉到複雜的搜尋條件的指令;然而,它對於比較簡單的案例卻是剛剛好。例如,考慮以下更新銀行餘額的交易:

BEGIN;
UPDATE accounts SET balance = balance + 100.00 WHERE acctnum = 12345;
UPDATE accounts SET balance = balance - 100.00 WHERE acctnum = 7534;
COMMIT;

如果兩個這樣的交易並行嘗試更新帳戶 12345 的餘額,我們顯然會希望第二個交易以帳戶資料列的更新後版本開始。因為每個指令都只會影響可預先決定的資料列,使得讓它看到更新後的資料列版本並不會造成任何有問題的不一致。

其他更複雜的使用狀況,在 Read Committed 模式中可以產生出並非期望的結果。例如考慮一個 DELETE 指令,要操作正好從它的限制條件中加入和移除的資料,像是假設 website 是一張有兩個資料列的表格,其中 website.hits 分別為 9 和 10:

BEGIN;
UPDATE website SET hits = hits + 1;
-- run from another session:  DELETE FROM website WHERE hits = 10;
COMMIT;

即使在 UPDATE 之前與之後,都存在 website.hits = 10 的資料列,但 DELETE 指令不會產生任何影響。這是因為更新前值為 9 的資料列已經被忽略了,並且當 UPDATE 執行完成且 DELETE 取得鎖,新的資料列的值已經不是 10 而是 11,不再滿足限制條件了。

因為 Read Committed 模式在每個指令開始時會取得包含在那個瞬間已經提交的所有交易的快照,其後在同個交易中的指令無論如何都會看到並行交易提交的影響。上述的爭論點在於 單一 指令是否看見資料庫完全一致的視野。

Read Committed 提供的部份交易隔離對很多應用程式來說已經足夠,並且這個模式很快且容易使用;然而,它沒辦法滿足所有的案例。對於做複雜查詢和更新的應用程式,可能會需要比 Read Committed 更嚴格的資料庫一致性視野。

13.2.2. Repeatable Read 隔離等級

這個等級跟 Read Committed 不同的地方在於 Repeatable Read 的交易看見的是在 交易 的第一個非交易控制指令開始時的快照,而不是當前指令開始時的。因此,一個成功的 SELECT 指令在 單一 交易之內都會看見相同的資料,例如它們不會看見在它們的交易開始以後其他交易所提交的變更。

使用這個等級的應用程式必須要準備好因為序列化失敗造成的交易重試。

UPDATE、DELETE、SELECT FOR UPDATE、和 SELECT FOR SHARE 指令的行為在搜尋目標資料列時與 SELECT 相同:它們將只會找在交易開始之前已經被提交的目標資料列。然而,這些目標資料列有可能在它被搜尋到時,已經被其他並行交易更新(或者刪除、上鎖),此時,Repeatable Read 的交易會等待第一個更新的交易提交或者還原(如果它正在進行中)。如果第一個更新者還原了,那麼它的影響就無效了,Repeatable Read 的交易就可以對原本找到的資料列做更新。但如果第一個更新者提交了(且確實更新或者刪除這個資料列,而非只是鎖定它而已),那麼 Repeatable Read 的交易將會還原並回應以下的訊息。

ERROR:  could not serialize access due to concurrent update

這是因為 Repeatable Read 的交易無法更新或者鎖定在 Repeatable Read 的交易開始後被其他交易變更過的資料列。

當應用程式收到這個錯誤訊息,它應該要放棄現在的交易並且從頭開始重試整個交易。在第二次的期間,交易會看見之前提交的變更作為它對於資料庫的初始視野,因此在新的交易的更新中,不會有因為使用資料列的新版本為起始點而產生的邏輯衝突。

需注意的是,只有更新的交易可能需要重試;只有讀取的交易永遠不會發生序列化衝突。

Repeatable Read 模式提供了嚴格的保證,每個交易會看見完全穩定的資料庫視野。然而,這個視野並不需要跟並行交易的某些依序(一次一個)執行的結果總是維持一致。舉例來說,在這個等級中即使是一個只有讀取的交易,也可能只看見反應一個批次完成的控制紀錄的更新,但卻 沒 看見邏輯上是批次的一部分的一個細節紀錄變更,因為它讀取到的是比較早的控制紀錄的版本。嘗試想要在這個隔離等級的交易下執行商業邏輯時,若沒有謹慎地使用明確的鎖去阻止並行交易的話,可能不會正確地運作。

在 PostgreSQL 9.1 以前,一個採用 Serializable 交易隔離等級的要求,會提供跟這裡描述的完全一樣的行為。若想要保持過去的 Serializable 的行為,在現在應該要使用 Repeatable Read。

13.2.3. Serializable 隔離等級

Serializable(序列化) 隔離等級提供了最嚴格的交易隔離。這個等級模擬了對所有提交的交易的一系列交易執行;如同交易們被一個接著一個地執行,連續地、而不是並行地。然而,如同 Repeatable Read,應用程式使用這個等級時必須要準備好因為序列化失敗而重試交易。事實上,這個隔離等級運作地完全與 Repeatable Read 相同,除了它會監視可能造成並行的序列化交易的執行結果跟一個一個執行這些交易產生些微不一致的症狀。這個監視行為並不會比 Repeatable Read 產生額外任何的阻塞(blocking),但監視的確會有一些額外的負擔,並且偵測到會造成 serialization anomaly 的症狀將會觸發 serialization failure 。

舉例來說,考慮有一個表格 mytab,一開始有:

 class | value
-------+-------
     1 |    10
     1 |    20
     2 |   100
     2 |   200

假設有個序列化交易 A 要計算:

SELECT SUM(value) FROM mytab WHERE class = 1;

然後將結果(30)作為 value 插入為 class = 2 的新資料列。同時序列化交易 B 並行地計算:

SELECT SUM(value) FROM mytab WHERE class = 2;

並且得到結果 300,將它插入為 class = 1 的新資料列。接著兩個交易嘗試要提交。如果其中任何一個交易是以 Repeatable Read 隔離等級執行的,兩個交易都會被允許提交;但因為並沒有序列的執行順序與這個結果一致,使用 Serializable 的交易將會導致一個交易提交、另一個被還原並回覆這個訊息:

ERROR:  could not serialize access due to read/write dependencies among transactions

這是因為如果 A 在 B 之前執行的話,B 會得到結果 330 而非 300;同樣的,其他順序會導致 A 得到不同的結果。

When relying on Serializable transactions to prevent anomalies, it is important that any data read from a permanent user table not be considered valid until the transaction which read it has successfully committed. This is true even for read-only transactions, except that data read within a deferrable read-only transaction is known to be valid as soon as it is read, because such a transaction waits until it can acquire a snapshot guaranteed to be free from such problems before starting to read any data. In all other cases applications must not depend on results read during a transaction that later aborted; instead, they should retry the transaction until it succeeds.

To guarantee true serializability PostgreSQL uses predicate locking, which means that it keeps locks which allow it to determine when a write would have had an impact on the result of a previous read from a concurrent transaction, had it run first. In PostgreSQL these locks do not cause any blocking and therefore can not play any part in causing a deadlock. They are used to identify and flag dependencies among concurrent Serializable transactions which in certain combinations can lead to serialization anomalies. In contrast, a Read Committed or Repeatable Read transaction which wants to ensure data consistency may need to take out a lock on an entire table, which could block other users attempting to use that table, or it may use SELECT FOR UPDATE or SELECT FOR SHARE which not only can block other transactions but cause disk access.

Consistent use of Serializable transactions can simplify development. The guarantee that any set of successfully committed concurrent Serializable transactions will have the same effect as if they were run one at a time means that if you can demonstrate that a single transaction, as written, will do the right thing when run by itself, you can have confidence that it will do the right thing in any mix of Serializable transactions, even without any information about what those other transactions might do, or it will not successfully commit. It is important that an environment which uses this technique have a generalized way of handling serialization failures (which always return with a SQLSTATE value of '40001'), because it will be very hard to predict exactly which transactions might contribute to the read/write dependencies and need to be rolled back to prevent serialization anomalies. The monitoring of read/write dependencies has a cost, as does the restart of transactions which are terminated with a serialization failure, but balanced against the cost and blocking involved in use of explicit locks and SELECT FOR UPDATE or SELECT FOR SHARE, Serializable transactions are the best performance choice for some environments.

While PostgreSQL's Serializable transaction isolation level only allows concurrent transactions to commit if it can prove there is a serial order of execution that would produce the same effect, it doesn't always prevent errors from being raised that would not occur in true serial execution. In particular, it is possible to see unique constraint violations caused by conflicts with overlapping Serializable transactions even after explicitly checking that the key isn't present before attempting to insert it. This can be avoided by making sure that all Serializable transactions that insert potentially conflicting keys explicitly check if they can do so first. For example, imagine an application that asks the user for a new key and then checks that it doesn't exist already by trying to select it first, or generates a new key by selecting the maximum existing key and adding one. If some Serializable transactions insert new keys directly without following this protocol, unique constraints violations might be reported even in cases where they could not occur in a serial execution of the concurrent transactions.

For optimal performance when relying on Serializable transactions for concurrency control, these issues should be considered:

  • Declare transactions as READ ONLY when possible.

  • Control the number of active connections, using a connection pool if needed. This is always an important performance consideration, but it can be particularly important in a busy system using Serializable transactions.

  • Don't put more into a single transaction than needed for integrity purposes.

  • Eliminate explicit locks, SELECT FOR UPDATE, and SELECT FOR SHARE where no longer needed due to the protections automatically provided by Serializable transactions.

Repeatable Read(重複讀取) 隔離等級只會看到在交易開始前已經被提交的資料;它永遠不會看見尚未提交的資料或者在交易期間被並行交易提交的變更。(然而,查詢會看見在它自己的交易中前面的更新所造成的影響,即使那些影響尚未被提交。)這是比 SQL 標準對這個隔離等級要求的還要更強的保證,並且能夠預防除了 serialization anomalies 以外所有在描述的現象。如前所述,這是標準所允許的,因為標準只描述了每個隔離等級一定要提供的 最小 保護。

Predicate locks in PostgreSQL, like in most other database systems, are based on data actually accessed by a transaction. These will show up in the system view with a mode of SIReadLock. The particular locks acquired during execution of a query will depend on the plan used by the query, and multiple finer-grained locks (e.g., tuple locks) may be combined into fewer coarser-grained locks (e.g., page locks) during the course of the transaction to prevent exhaustion of the memory used to track the locks. A READ ONLYtransaction may be able to release its SIRead locks before completion, if it detects that no conflicts can still occur which could lead to a serialization anomaly. In fact, READ ONLYtransactions will often be able to establish that fact at startup and avoid taking any predicate locks. If you explicitly request a SERIALIZABLE READ ONLY DEFERRABLE transaction, it will block until it can establish this fact. (This is the only case where Serializable transactions block but Repeatable Read transactions don't.) On the other hand, SIRead locks often need to be kept past transaction commit, until overlapping read write transactions complete.

Don't leave connections dangling “idle in transaction” longer than necessary. The configuration parameter may be used to automatically disconnect lingering sessions.

When the system is forced to combine multiple page-level predicate locks into a single relation-level predicate lock because the predicate lock table is short of memory, an increase in the rate of serialization failures may occur. You can avoid this by increasing , , and/or .

A sequential scan will always necessitate a relation-level predicate lock. This can result in an increased rate of serialization failures. It may be helpful to encourage the use of index scans by reducing and/or increasing . Be sure to weigh any decrease in transaction rollbacks and restarts against any overall change in query execution time.

The Serializable isolation level is implemented using a technique known in academic database literature as Serializable Snapshot Isolation, which builds on Snapshot Isolation by adding checks for serialization anomalies. Some differences in behavior and performance may be observed when compared with other systems that use a traditional locking technique. Please see for detailed information.

pg_locks
idle_in_transaction_session_timeout
max_pred_locks_per_transaction
max_pred_locks_per_relation
max_pred_locks_per_page
random_page_cost
cpu_tuple_cost
[ports12]
SET TRANSACTION
9.16 小節
8.1.4 小節
Table 13.1
表格 13.1