Appendix F. Additional Supplied Modules and Extensions#

Appendix F. Additional Supplied Modules and Extensions
Appendix F. Additional Supplied Modules and Extensions
Prev UpPart VIII. AppendixesHome Next

Appendix F. Additional Supplied Modules and Extensions

Table of Contents

F.1. amcheck — tools to verify table and index consistency
F.1.1. Functions
F.1.2. Optional heapallindexed Verification
F.1.3. Using amcheck Effectively
F.1.4. Repairing Corruption
F.2. auth_delay — pause on authentication failure
F.2.1. Configuration Parameters
F.2.2. Author
F.3. auto_dump — extension with ProcessInterrupts hook
F.3.1. Overview
F.3.2. Requirements
F.3.3. Enabling the extension
F.3.4. Configuration (GUCs)
F.3.5. Triggering modes
F.3.6. What gets dumped
F.3.7. Output directory & file naming
F.3.8. Usage example (from real 1C-like workload)
F.3.9. Tuning & recommendations
F.3.10. Troubleshooting
F.4. auto_explain — log execution plans of slow queries
F.4.1. Configuration Parameters
F.4.2. Example
F.4.3. Author
F.5. basebackup_to_shell — example "shell" pg_basebackup module
F.5.1. Configuration Parameters
F.5.2. Author
F.6. basic_archive — an example WAL archive module
F.6.1. Configuration Parameters
F.6.2. Notes
F.6.3. Author
F.7. bloom — bloom filter index access method
F.7.1. Parameters
F.7.2. Examples
F.7.3. Operator Class Interface
F.7.4. Limitations
F.7.5. Authors
F.8. btree_gin — GIN operator classes with B-tree behavior
F.8.1. Example Usage
F.8.2. Authors
F.9. btree_gist — GiST operator classes with B-tree behavior
F.9.1. Example Usage
F.9.2. Authors
F.10. citext — a case-insensitive character string type
F.10.1. Rationale
F.10.2. How to Use It
F.10.3. String Comparison Behavior
F.10.4. Limitations
F.10.5. Author
F.11. spi — Server Programming Interface features/examples
F.11.1. refint — Functions for Implementing Referential Integrity
F.11.2. autoinc — Functions for Autoincrementing Fields
F.11.3. insert_username — Functions for Tracking Who Changed a Table
F.11.4. moddatetime — Functions for Tracking Last Modification Time
F.12. credcheck — Tantor SE username/password checks
F.12.1. Overview
F.12.2. Installation
F.12.3. Checks
F.12.4. Examples
F.12.5. Password reuse policy
F.12.6. Authentication failure ban
F.12.7. Authentication delay
F.12.8. Limitations
F.13. cube — a multi-dimensional cube data type
F.13.1. Syntax
F.13.2. Precision
F.13.3. Usage
F.13.4. Defaults
F.13.5. Notes
F.13.6. Credits
F.14. dbcopies_decoding — database copy mechanism and data accelerator
F.14.1. Usage
F.15. dblink — connect to other PostgreSQL databases
dblink_connect — opens a persistent connection to a remote database
dblink_connect_u — opens a persistent connection to a remote database, insecurely
dblink_disconnect — closes a persistent connection to a remote database
dblink — executes a query in a remote database
dblink_exec — executes a command in a remote database
dblink_open — opens a cursor in a remote database
dblink_fetch — returns rows from an open cursor in a remote database
dblink_close — closes a cursor in a remote database
dblink_get_connections — returns the names of all open named dblink connections
dblink_error_message — gets last error message on the named connection
dblink_send_query — sends an async query to a remote database
dblink_is_busy — checks if connection is busy with an async query
dblink_get_notify — retrieve async notifications on a connection
dblink_get_result — gets an async query result
dblink_cancel_query — cancels any active query on the named connection
dblink_get_pkey — returns the positions and field names of a relation's primary key fields
dblink_build_sql_insert — builds an INSERT statement using a local tuple, replacing the primary key field values with alternative supplied values
dblink_build_sql_delete — builds a DELETE statement using supplied values for primary key field values
dblink_build_sql_update — builds an UPDATE statement using a local tuple, replacing the primary key field values with alternative supplied values
F.16. dict_int — example full-text search dictionary for integers
F.16.1. Configuration
F.16.2. Usage
F.17. dict_xsyn — example synonym full-text search dictionary
F.17.1. Configuration
F.17.2. Usage
F.18. earthdistance — calculate great-circle distances
F.18.1. Cube-Based Earth Distances
F.18.2. Point-Based Earth Distances
F.19. fasttrun — function to truncate temporary tables without growing pg_class size
F.19.1. Function
F.19.2. Test example
F.20. file_fdw — access data files in the server's file system
F.21. fulleq — equivalence operator for compatibility with Microsoft SQL Server
F.21.1. Overview
F.21.2. Operator fulleq
F.22. fuzzystrmatch — determine string similarities and distance
F.22.1. Soundex
F.22.2. Daitch-Mokotoff Soundex
F.22.3. Levenshtein
F.22.4. Metaphone
F.22.5. Double Metaphone
F.23. hstore — hstore key/value datatype
F.23.1. hstore External Representation
F.23.2. hstore Operators and Functions
F.23.3. Indexes
F.23.4. Examples
F.23.5. Statistics
F.23.6. Compatibility
F.23.7. Transforms
F.23.8. Authors
F.24. hydra (columnar) — hydra optimized row columnar (ORC)
F.24.1. Overview
F.24.2. Columnar Installation
F.24.3. Basic principles and questions
F.24.4. Columnar Usage
F.24.5. Performance Microbenchmarks
F.24.6. Row vs Column Tables
F.24.7. Updates and Deletes
F.24.8. Optimizing Query Performance
F.24.9. Materialized Views
F.24.10. Vectorized Execution
F.24.11. Query Parallelization
F.24.12. Common recomendations
F.24.13. Working with Time Series Data
F.25. HypoPG — support for hypothetical indexes
F.25.1. Overview
F.25.2. Installation
F.25.3. Updating the extension
F.25.4. Usage
F.26. intagg — integer aggregator and enumerator
F.26.1. Functions
F.26.2. Sample Uses
F.27. intarray — manipulate arrays of integers
F.27.1. intarray Functions and Operators
F.27.2. Index Support
F.27.3. Example
F.27.4. Benchmark
F.27.5. Authors
F.28. isn — data types for international standard numbers (ISBN, EAN, UPC, etc.)
F.28.1. Data Types
F.28.2. Casts
F.28.3. Functions and Operators
F.28.4. Examples
F.28.5. Bibliography
F.28.6. Author
F.29. lo — manage large objects
F.29.1. Rationale
F.29.2. How to Use It
F.29.3. Limitations
F.29.4. Author
F.30. ltree — hierarchical tree-like data type
F.30.1. Definitions
F.30.2. Operators and Functions
F.30.3. Indexes
F.30.4. Example
F.30.5. Transforms
F.30.6. Authors
F.31. mchar — additional data types for compatibility with Microsoft SQL Server (MS SQL)
F.31.1. Overview
F.31.2. Additional types
F.31.3. MCHAR and MVARCHAR Features
F.32. oauth_validator — OAuth 2.0 Basic Token Validator for Tantor SE
F.32.1. Overview
F.32.2. Requirements
F.32.3. Installation
F.32.4. Configuration
F.32.5. Token Validation Logic
F.32.6. Extensibility
F.33. online_analyze — statistic collection right after INSERT/UPDATE/DELETE/SELECT
F.33.1. Overview
F.33.2. Configuration
F.34. orafce — set of functions that provide compatibility with Oracle databases
F.34.1. Overview
F.34.2. List of format strings for trunc, round functions
F.34.3. Date Functions
F.34.4. oracle.date data type
F.34.5. oracle.date functions
F.34.6. oracle.date Operators
F.34.7. Table dual
F.34.8. Package dbms_output
F.34.9. Package utl_file
F.34.10. Package dbms_sql
F.34.11. Package dbms_pipe
F.34.12. Package dbms_alert
F.34.13. Package PLVdate
F.34.14. Package PLVstr and PLVchr
F.34.15. Package PLVsubst
F.34.16. Package DBMS_utility
F.34.17. Package PLVlex
F.34.18. Package DBMS_ASSERT
F.34.19. Package PLUnit
F.34.20. Package DBMS_random
F.34.21. Others functions
F.34.22. oracle.sys_guid() function
F.34.23. VARCHAR2 and NVARCHAR2 Support
F.34.24. Triggers
F.34.25. Emulated views
F.34.26. Orafce - Oracle’s compatibility functions and packages
F.34.27. Chapter 1 Overview
F.34.28. Chapter 2 Notes on Using orafce
F.34.29. Chapter 3 Data Types
F.34.30. Chapter 4 Queries
F.34.31. Chapter 5 SQL Function Reference
F.34.32. Chapter 6 Package Reference
F.34.33. Chapter 7 Transaction behavior
F.34.34. Migration: Chapter 1 Pre-Migration Configuration
F.34.35. Migration: Chapter 2 Migrating Syntax Elements
F.34.36. Migration: Chapter 3 Migrating Functions
F.34.37. Migration: Chapter 4 Migrating SQL Statements
F.34.38. Migration: Chapter 5 Migrating PL/SQL
F.34.39. Migration: Chapter 6 Notes on Using orafce
F.34.40. Appendix A Correspondence with Oracle Databases
F.35. pageinspect — low-level inspection of database pages
F.35.1. General Functions
F.35.2. Heap Functions
F.35.3. B-Tree Functions
F.35.4. BRIN Functions
F.35.5. GIN Functions
F.35.6. GiST Functions
F.35.7. Hash Functions
F.36. page_repair — individual page repair using standby’s data
F.36.1. Overview
F.36.2. Installation
F.36.3. Usage
F.37. passwordcheck — verify password strength
F.38. pg_archive — automatically archive historical data from partitioned tables
F.38.1. Overview
F.38.2. Installation
F.38.3. Upgrade
F.38.4. Usage
F.38.5. Functions, Procedures And Tables
F.38.6. Caveats
F.39. pgAudit — detailed session and/or object audit logging
F.39.1. Overview
F.39.2. Why pgAudit?
F.39.3. Usage Considerations
F.39.4. Tantor SE Version Compatibility
F.39.5. Settings
F.39.6. Session Audit Logging
F.39.7. Object Audit Logging
F.39.8. Event Marking
F.39.9. Format
F.39.10. Caveats
F.40. pgauditlogtofile — pgAudit addon for redirect audit log lines to an independent file
F.40.1. Overview
F.40.2. Installation
F.40.3. Configuration
F.41. pg_background — run commands in background workers
F.41.1. Overview
F.41.2. Features
F.41.3. Installation
F.41.4. Usage
F.41.5. Examples
F.41.6. Privilege Management
F.41.7. Use Cases
F.41.8. More examples:
F.42. pg_buffercache — inspect Tantor SE buffer cache state
F.42.1. The pg_buffercache View
F.42.2. The pg_buffercache_summary() Function
F.42.3. The pg_buffercache_usage_counts() Function
F.42.4. The pg_buffercache_evict() Function
F.42.5. Sample Output
F.42.6. Authors
F.43. pg_cron — cron-based job scheduler working within the database
F.43.1. Overview?
F.43.2. Setting up pg_cron
F.43.3. Viewing job run details
F.44. pgcrypto — cryptographic functions
F.44.1. General Hashing Functions
F.44.2. Password Hashing Functions
F.44.3. PGP Encryption Functions
F.44.4. Raw Encryption Functions
F.44.5. Random-Data Functions
F.44.6. Notes
F.44.7. Author
F.45. pg_freespacemap — examine the free space map
F.45.1. Functions
F.45.2. Sample Output
F.45.3. Author
F.46. pg_hint_plan — configuring plans of SQL queries in SQL comments
F.46.1. Overview
F.46.2. Installation
F.46.3. Description
F.46.4. The hint table
F.46.5. Details in hinting
F.46.6. Errors
F.46.7. Functional limitations
F.46.8. Hint list
F.47. pg_ivm — Incremental View Maintenance (IVM) feature for Tantor SE
F.47.1. Overview
F.47.2. Installation
F.47.3. Objects
F.47.4. Example
F.47.5. pg_dump and pg_upgrade
F.47.6. Supported View Definitions and Restriction
F.47.7. Notes
F.48. pgl_ddl_deploy — transparent logical DDL replication
F.48.1. Overview
F.48.2. Setup and Deployment
F.48.3. Limitations and Restrictions
F.48.4. Resolving DDL Replication Issues
F.49. pg_partman — create and manage time-based and number-based table partition sets
F.49.1. Overview
F.49.2. Installation
F.49.3. Upgrade
F.49.4. Examples
F.49.5. PostgreSQL Partition Manager Extension (pg_partman)
F.49.6. Example Guide On Setting Up Native Partitioning
F.49.7. Migrating An Existing Partition Set to PG Partition Manager
F.49.8. Migrating From Trigger-based Partitioning To Native Declarative Partitioning
F.49.9. Guildelines for Upgrading to pg_partman 5.0.1
F.50. pg_prewarm — preload relation data into buffer caches
F.50.1. Functions
F.50.2. Configuration Parameters
F.50.3. Author
F.51. PGQ — generic, high-performance lockless queue with simple API based on SQL functions
F.51.1. Overview
F.51.2. What problems is PGQ a solution for?
F.51.3. Installation and setup
F.51.4. Producing Events
F.51.5. Writing a PGQ consumer
F.52. pg_qualstats — statistics on predicates found in WHERE statements and JOIN clauses
F.52.1. Overview
F.52.2. Installation
F.52.3. Configuration
F.52.4. Updating the extension
F.52.5. Usage
F.53. pg_query_id — manipulate aspects of query id computation
F.53.1. Overview
F.53.2. Getting started
F.53.3. Functionality
F.53.4. Known issues
F.54. pg_repack — remove bloat from tables and indexes
F.54.1. Overview
F.54.2. Requirements
F.54.3. Installation
F.54.4. Usage
F.54.5. Environment
F.54.6. Examples
F.54.7. Diagnostics
F.54.8. Restrictions
F.54.9. Details
F.55. pgrowlocks — show a table's row locking information
F.55.1. Overview
F.55.2. Sample Output
F.55.3. Author
F.56. pgsql-http — Tantor SE HTTP Client
F.56.1. Installation
F.56.2. Examples
F.56.3. Concepts
F.56.4. Functions
F.56.5. CURL Options
F.56.6. Keep-Alive & Timeouts
F.56.7. Why This is a Bad Idea
F.57. pg_stat_advisor — Tantor SE advisor to create extended statistics
F.57.1. Overview
F.57.2. Limitations
F.57.3. Installation
F.57.4. Usage
F.57.5. Examples
F.58. pg_stat_kcache — statistics about real reads and writes
F.58.1. Overview
F.58.2. Installation
F.58.3. Configuration
F.58.4. Usage
F.58.5. Updating the extension
F.58.6. Bugs and limitations
F.59. pg_stat_statements — track statistics of SQL planning and execution
F.59.1. The pg_stat_statements View
F.59.2. The pg_stat_statements_info View
F.59.3. Functions
F.59.4. Configuration Parameters
F.59.5. Sample Output
F.59.6. Authors
F.60. pgstattuple — obtain tuple-level statistics
F.60.1. Functions
F.60.2. Authors
F.61. pg_store_plans — means for tracking execution plan statistics of all SQL statements
F.61.1. Overview
F.61.2. The pg_store_plans View
F.61.3. The pg_store_plans View
F.61.4. Functions
F.61.5. Configuration Parameters
F.61.6. Discussion on plan_storage setting
F.61.7. Sample Output
F.62. pg_surgery — perform low-level surgery on relation data
F.62.1. Functions
F.62.2. Authors
F.63. pg_tde — Transparent Data Encryption for Tantor SE
F.63.1. Overview
F.63.2. Quickstart
F.63.3. About Transparent Data Encryption
F.63.4. Features
F.63.5. Encryption algorithms
F.63.6. Configure pg_tde
F.63.7. Key management overview
F.63.8. Global Principal Key configuration
F.63.9. pg_tde usage examples
F.63.10. Configure WAL encryption
F.63.11. Technical Reference
F.63.12. Architecture
F.63.13. GUC Variables
F.63.14. Functions
F.63.15. Streaming Replication with tde_heap
F.63.16. Overview of pg_tde CLI tools
F.63.17. How to
F.63.18. FAQ
F.63.19. Uninstall pg_tde
F.64. pg_throttle — extension which add query throttling feature
F.64.1. Overview
F.64.2. Setting up
F.64.3. Getting started
F.64.4. Cgroup v2 support
F.64.5. Configuration
F.64.6. API & examples
F.64.7. Implementation details
F.64.8. Caveats
F.65. pg_trace — Real-time filtered query tracing for Tantor SE
F.65.1. Overview
F.65.2. Getting started
F.65.3. Functionality
F.65.4. Client-Server protocol
F.65.5. Configuration
F.65.6. How it works
F.65.7. Known issues
F.66. pg_trgm — support for similarity of text using trigram matching
F.66.1. Trigram (or Trigraph) Concepts
F.66.2. Functions and Operators
F.66.3. GUC Parameters
F.66.4. Index Support
F.66.5. Text Search Integration
F.66.6. References
F.66.7. Authors
F.67. pg_uuidv7 — extension to generate UUID version 7 and some other functions
F.67.1. Usage
F.68. pg_variables — work with variables of different types
F.68.1. Overview
F.68.2. Installation
F.68.3. Module functions
F.68.4. Scalar variables functions
F.68.5. Array variables functions
F.68.6. Deprecated scalar variables functions
F.68.7. Record variables functions
F.68.8. Examples
F.69. pg_visibility — visibility map information and utilities
F.69.1. Functions
F.69.2. Author
F.70. pg_wait_profile — sample-based profiling of database activity
F.70.1. Overview
F.70.2. Usage
F.71. pg_wait_sampling — sampling based statistics of wait events
F.71.1. Overview
F.71.2. Installation
F.71.3. Usage
F.72. pg_walinspect — low-level WAL inspection
F.72.1. General Functions
F.72.2. Author
F.73. plantuner — planner hints
F.73.1. Overview
F.73.2. Motivation
F.73.3. Syntax
F.73.4. Usage
F.74. postgres_fdw — access data stored in external Tantor SE servers
F.74.1. FDW Options of postgres_fdw
F.74.2. Functions
F.74.3. Connection Management
F.74.4. Transaction Management
F.74.5. Remote Query Optimization
F.74.6. Remote Query Execution Environment
F.74.7. Cross-Version Compatibility
F.74.8. Wait Events
F.74.9. Configuration Parameters
F.74.10. Examples
F.74.11. Author
F.75. seg — a datatype for line segments or floating point intervals
F.75.1. Rationale
F.75.2. Syntax
F.75.3. Precision
F.75.4. Usage
F.75.5. Notes
F.75.6. Credits
F.76. sepgsql — SELinux-, label-based mandatory access control (MAC) security module
F.76.1. Overview
F.76.2. Installation
F.76.3. Regression Tests
F.76.4. GUC Parameters
F.76.5. Features
F.76.6. Sepgsql Functions
F.76.7. Limitations
F.76.8. External Resources
F.76.9. Author
F.77. sslinfo — obtain client SSL information
F.77.1. Functions Provided
F.77.2. Author
F.78. tablefunc — functions that return tables (crosstab and others)
F.78.1. Functions Provided
F.78.2. Author
F.79. tcn — a trigger function to notify listeners of changes to table content
F.80. test_decoding — SQL-based test/example module for WAL logical decoding
F.81. transp_anon — mask or replace personally identifiable information or commercially sensitive data
F.81.1. Overview
F.81.2. Definitions of the terms used in this project
F.81.3. Anonymization & Data Masking for Tantor SE
F.81.4. Configuration
F.81.5. Declare masking rules
F.81.6. Masking functions
F.81.7. Dynamic masking
F.81.8. Generalization
F.81.9. Custom Fake Data
F.81.10. Performances
F.81.11. Security
F.81.12. Examples
F.82. tsm_system_rows — the SYSTEM_ROWS sampling method for TABLESAMPLE
F.82.1. Examples
F.83. tsm_system_time — the SYSTEM_TIME sampling method for TABLESAMPLE
F.83.1. Examples
F.84. unaccent — a text search dictionary which removes diacritics
F.84.1. Configuration
F.84.2. Usage
F.84.3. Functions
F.85. uuid-ossp — a UUID generator
F.85.1. uuid-ossp Functions
F.85.2. Building uuid-ossp
F.85.3. Author
F.86. vector (pgvector) — search vector similarity
F.86.1. Overview
F.86.2. Getting Started
F.86.3. Storing
F.86.4. Querying
F.86.5. Indexing
F.86.6. HNSW
F.86.7. IVFFlat
F.86.8. Filtering
F.86.9. Iterative Index Scans
F.86.10. Half-Precision Vectors
F.86.11. Half-Precision Indexing
F.86.12. Binary Vectors
F.86.13. Binary Quantization
F.86.14. Sparse Vectors
F.86.15. Hybrid Search
F.86.16. Indexing Subvectors
F.86.17. Performance
F.86.18. Monitoring
F.86.19. Scaling
F.86.20. Languages
F.86.21. Frequently Asked Questions
F.86.22. Troubleshooting
F.86.23. Reference
F.86.24. Upgrading
F.87. xml2 — XPath querying and XSLT functionality
F.87.1. Deprecation Notice
F.87.2. Description of Functions
F.87.3. xpath_table
F.87.4. XSLT Functions
F.87.5. Author

This appendix and the next one contain information on the optional components found in the contrib directory of the Tantor SE distribution. These include porting tools, analysis utilities, and plug-in features that are not part of the core PostgreSQL system. They are separate mainly because they address a limited audience or are too experimental to be part of the main source tree. This does not preclude their usefulness.

This appendix covers extensions and other server plug-in module libraries found in contrib. Appendix G covers utility programs.

Many components supply new user-defined functions, operators, or types, packaged as extensions. To make use of one of these extensions, after you have installed the code you need to register the new SQL objects in the database system. This is done by executing a CREATE EXTENSION command. In a fresh database, you can simply do 

CREATE EXTENSION extension_name;

This command registers the new SQL objects in the current database only, so you need to run it in every database in which you want the extension's facilities to be available. Alternatively, run it in database template1 so that the extension will be copied into subsequently-created databases by default.

For all extensions, the CREATE EXTENSION command must be run by a database superuser, unless the extension is considered “trusted”. Trusted extensions can be run by any user who has CREATE privilege on the current database. Extensions that are trusted are identified as such in the sections that follow. Generally, trusted extensions are ones that cannot provide access to outside-the-database functionality.

The following extensions are trusted in a default installation:

btree_ginfuzzystrmatchltreetcn
btree_gisthstorepgcryptotsm_system_rows
citextintarraypg_trgmtsm_system_time
cubeisnsegunaccent
dict_intlotablefuncuuid-ossp

Many extensions allow you to install their objects in a schema of your choice. To do that, add SCHEMA schema_name to the CREATE EXTENSION command. By default, the objects will be placed in your current creation target schema, which in turn defaults to public.

Note, however, that some of these components are not “extensions” in this sense, but are loaded into the server in some other way, for instance by way of shared_preload_libraries. See the documentation of each component for details.

Prev Up Next
E.16. Prior Releases Home F.1. amcheck — tools to verify table and index consistency