Editing PostgreSQL (section)

== Control and connectivity ==

=== Foreign data wrappers ===
PostgreSQL can link to other systems to retrieve data via foreign data wrappers (FDWs).<ref>{{cite book
 |last1=Obe
 |first1=Regina
 |last2=Hsu
 |first2=Leo S.
 |chapter=10: Replication and External Data
 |title=PostgreSQL: Up and Running
 |url=https://books.google.com/books?id=Q8jkIZkMTPcC
 |edition=1
 |location=Sebastopol, CA
 |publisher=[[O'Reilly Media|O'Reilly Media, Inc.]]
 |date=2012
 |page=129
 |isbn=978-1-4493-2633-3
 |access-date=October 17, 2016
 |quote=Foreign Data Wrappers (FDW) [...] are mechanisms of querying external datasources. PostgreSQL 9.1 introduced this [[SQL/MED]] standards compliant feature.
}}</ref>
These can take the form of any data source, such as a file system, another [[relational database]] management system (RDBMS), or a web service. This means that regular database queries can use these data sources like regular tables, and even join multiple data-sources together.

=== Interfaces ===
PostgreSQL supports a binary [[communication protocol]] that allows applications to connect to the database server. The protocol is versioned (currently 3.0, as of PostgreSQL 7.4) and has a detailed specification.<ref>{{cite web
	| url = https://www.postgresql.org/docs/current/protocol.html
	| title = Frontend/Backend Protocol
    | author = <!--Not stated-->
	| website = postgresql.org
	| date = November 9, 2023
 | access-date = 2023-12-17
	| quote = This document describes version 3.0 of the protocol, implemented in PostgreSQL 7.4 and later.
}}</ref>

The official client implementation of this communication protocol is a [[C (programming language)|C]] [[API]], libpq.<ref>{{cite web
	| url = https://www.postgresql.org/docs/16/libpq.html
	| title = libpq
    | author = <!--Not stated-->
	| website = postgresql.org
	| date = November 9, 2023
 | access-date = 2023-12-17
}}</ref> In addition, the officially supported [[ECPG]] tool allows SQL commands to be embedded in C code.<ref>{{cite web
	| url = https://www.postgresql.org/docs/16/ecpg.html
	| title = Embedded SQL in C
    | author = <!--Not stated-->
	| website = postgresql.org
	| date = November 9, 2023
 | access-date = 2023-12-17
}}</ref> Both are part of the standard PostgreSQL distribution.<ref>{{cite web
	| url = https://www.postgresql.org/docs/16/client-interfaces.html
	| title = Client Interfaces
    | author = <!--Not stated-->
	| website = postgresql.org
	| date = November 9, 2023
 | access-date = 2023-12-17
}}</ref>

Third-party libraries for connecting to PostgreSQL are available for many [[programming language]]s, including [[C++]],<ref>{{cite web|title=libpqxx|url=https://pqxx.org/development/libpqxx/|access-date=April 4, 2020}}</ref> [[Java (programming language)|Java]],<ref>{{cite web|title=PostgreSQL JDBC Driver|url=https://jdbc.postgresql.org/|access-date=April 4, 2020}}</ref> [[Julia (programming language)|Julia]],<ref>{{Cite web|date=2021-06-30|title=[ANN] PostgresORM.jl: Object Relational Mapping for PostgreSQL|url=https://discourse.julialang.org/t/ann-postgresorm-jl-object-relational-mapping-for-postgresql/63847|access-date=2021-08-26|website=JuliaLang|language=en}}</ref><ref>{{Cite web|title=GitHub - invenia/LibPQ.jl: A Julia wrapper for libpq|url=https://github.com/invenia/LibPQ.jl|access-date=2021-08-26|website=GitHub|language=en}}</ref><ref name="PL/Julia" /> [[Python (programming language)|Python]],<ref name="psycopg2" /> [[Node.js]],<ref>{{cite web|title=node-postgres|url=https://node-postgres.com/|access-date=April 4, 2020}}</ref> [[Go (programming language)|Go]],<ref>{{cite web |title=SQL database drivers |url=https://github.com/golang/go/wiki/SQLDrivers#drivers |website=Go wiki |publisher=golang.org |access-date=June 22, 2015}}</ref> and [[Rust (programming language)|Rust]].<ref>{{cite web|title=Rust-Postgres|url=https://crates.io/crates/postgres|access-date=April 4, 2020}}</ref>

=== Procedural languages ===
Procedural languages allow developers to extend the database with custom [[subroutine]]s (functions), often called ''[[stored procedure]]s''. These functions can be used to build [[database trigger]]s (functions invoked on modification of certain data) and custom data types and [[aggregate function]]s.<ref>{{cite web|url=https://www.postgresql.org/docs/current/server-programming.html|title=Server Programming|access-date=19 May 2019|website=PostgreSQL documentation}}</ref> Procedural languages can also be invoked without defining a function, using a DO command at SQL level.<ref>{{cite web|url=https://www.postgresql.org/docs/current/sql-do.html|title=DO|access-date=19 May 2019|website=PostgreSQL documentation}}</ref>

Languages are divided into two groups: Procedures written in ''safe'' languages are [[Sandbox (computer security)|sandboxed]] and can be safely created and used by any user. Procedures written in ''unsafe'' languages can only be created by [[superuser]]s, because they allow bypassing a database's security restrictions, but can also access sources external to the database. Some languages like Perl provide both safe and unsafe versions.

PostgreSQL has built-in support for three procedural languages:

* Plain SQL (safe). Simpler SQL functions can get [[inline expansion|expanded inline]] into the calling (SQL) query, which saves function call overhead and allows the query optimizer to "see inside" the function.
* Procedural Language/PostgreSQL ([[PL/pgSQL]]) (safe), which resembles Oracle's Procedural Language for SQL ([[PL/SQL]]) procedural language and SQL/Persistent Stored Modules ([[SQL/PSM]]).
* [[C (programming language)|C]] (unsafe), which allows loading one or more custom [[shared library]] into the database. Functions written in C offer the best performance, but bugs in code can crash and potentially corrupt the database. Most built-in functions are written in C.

In addition, PostgreSQL allows procedural languages to be loaded into the database through extensions. Three language extensions are included with PostgreSQL to support [[Perl]], [[Tcl]], and [[Python (programming language)|Python]]. For Python, the current {{nowrap|Python 3}} is used, and the discontinued {{nowrap|Python 2}} is no longer supported as of PostgreSQL 15. Both were supported previously, defaulting to {{nowrap|Python 2}}, while old and new versions couldn't be used in the same session.<ref>{{cite web|url=https://www.postgresql.org/docs/current/plpython.html|title=PL/Python - Python Procedural Language|access-date=October 23, 2022|website=PostgreSQL documentation}}</ref> External projects provide support for many other languages,<ref>{{cite web |date=March 31, 2016 |title=Procedural Languages |publisher=postgresql.org |url=https://www.postgresql.org/docs/current/static/external-pl.html |access-date=April 7, 2016}}</ref> including PL/[[Java (programming language)|Java]], [[JavaScript]] (PL/V8), PL/[[Julia (programming language)|Julia]],<ref name="PL/Julia">{{Cite web|date=2020-03-08|title=PL/Julia extension ( minimal )|url=https://discourse.julialang.org/t/pl-julia-extension-minimal/34232/2|access-date=2021-08-26|website=JuliaLang|language=en}}</ref> PL/[[R (programming language)|R]],<ref>{{Cite web|url=https://github.com/postgres-plr/plr|title=postgres-plr/plr|date=June 17, 2021|via=GitHub}}</ref> PL/[[Ruby (programming language)|Ruby]], and others.

=== Triggers ===
Triggers are events triggered by the action of SQL [[data manipulation language]] (DML) statements. For example, an [[Insert (SQL)|INSERT]] statement might activate a trigger that checks if the values of the statement are valid. Most triggers are only activated by either INSERT or [[Update (SQL)|UPDATE]] statements.

Triggers are fully supported and can be attached to tables. Triggers can be per-column and conditional, in that UPDATE triggers can target specific columns of a table, and triggers can be told to execute under a set of conditions as specified in the trigger's WHERE clause. Triggers can be attached to [[View (SQL)|views]] by using the INSTEAD OF condition. Multiple triggers are fired in alphabetical order. In addition to calling functions written in the native PL/pgSQL, triggers can also invoke functions written in other languages like PL/Python or PL/Perl.

=== Asynchronous notifications ===
PostgreSQL provides an asynchronous messaging system that is accessed through the NOTIFY, LISTEN and UNLISTEN commands. A session can issue a NOTIFY command, along with the user-specified channel and an optional payload, to mark a particular event occurring. Other sessions are able to detect these events by issuing a LISTEN command, which can listen to a particular channel. This functionality can be used for a wide variety of purposes, such as letting other sessions know when a table has updated or for separate applications to detect when a particular action has been performed. Such a system prevents the need for continuous [[polling (computer science)|polling]] by applications to see if anything has yet changed, and reducing unnecessary overhead. Notifications are fully transactional, in that messages are not sent until the transaction they were sent from is committed. This eliminates the problem of messages being sent for an action being performed which is then rolled back.

Many connectors for PostgreSQL provide support for this notification system (including libpq, JDBC, Npgsql, psycopg and node.js) so it can be used by external applications.

PostgreSQL can act as an effective, persistent [[publish–subscribe pattern|"pub/sub" server]] or job server by combining LISTEN with FOR UPDATE SKIP LOCKED.<ref name="postgres-jobserver">{{cite web |last1=Chartier |first1=Colin |title=System design hack: Postgres is a great pub/sub & job server |url=https://layerci.com/blog/postgres-is-the-answer/ |website=LayerCI blog |date=November 8, 2019 |access-date=November 24, 2019}}</ref><ref name="release-9.5">{{cite web |title=Release 9.5 |url=https://www.postgresql.org/docs/9.5/release-9-5.html |website=postgresql.org|date=February 11, 2021 }}</ref><ref name="ringer-skip-locked">{{cite web |last1=Ringer |first1=Craig |title=What is SKIP LOCKED for in PostgreSQL 9.5? |url=https://www.2ndquadrant.com/en/blog/what-is-select-skip-locked-for-in-postgresql-9-5/ |website=2nd Quadrant |date=April 13, 2016 |access-date=November 24, 2019}}</ref>

=== Rules ===
Rules allow the "query tree" of an incoming query to be rewritten; they are an, automatically invoked, [[Macro (computer science)|macro language]] for SQL. "Query Re-Write Rules" are attached to a table/class and "Re-Write" the incoming DML (select, insert, update, and/or delete) into one or more queries that either replace the original DML statement or execute in addition to it. Query Re-Write occurs after DML statement parsing and before query planning.

The functionality rules provide was, in almost every way, later duplicated with the introduction of newer types of triggers.
The use of triggers is usually preferred over rules as it is easier to reason about trigger behavior and interactions than when equivalent rules are used.

=== Other querying features ===
* [[Database transaction|Transactions]]
* [[Full-text search]]
* Views
** Materialized views<ref name="materialized_views" />
** Updateable views<ref name="updatable_views" />
** Recursive views<ref name="recursive_views" />
* Inner, outer (full, left, and right), and cross [[Join (SQL)|joins]]
* Sub-[[Select (SQL)|selects]]
** Correlated sub-queries<ref name="Introduction and Concepts" />
* [[Regular expression]]s<ref name="Bernier" />
* [[Hierarchical and recursive queries in SQL#Common table expression|Common table expressions]] and writable common table expressions
* Encrypted connections via [[Transport Layer Security]] (TLS); current versions do not use vulnerable SSL, even with that configuration option<ref name="POODLE" />
* Domains
* [[Savepoint]]s
* [[Two-phase commit protocol|Two-phase commit]]
* The Oversized-Attribute Storage Technique (TOAST) is used to transparently store large table attributes (such as big MIME attachments or XML messages) in a separate area, with automatic compression.
* [[Embedded SQL]] is implemented using preprocessor. SQL code is first written embedded into C code. Then code is run through ECPG preprocessor, which replaces SQL with calls to code library. Then code can be compiled using a C compiler. Embedding works also with [[C++]] but it does not recognize all C++ constructs.

=== Concurrency model ===
PostgreSQL server is [[process (computing)|process]]-based (not threaded), and uses one operating system process per database session. Multiple sessions are automatically spread across all available CPUs by the operating system. Many types of queries can also be parallelized across multiple background worker processes, taking advantage of multiple CPUs or cores.<ref>{{Cite web |url=https://lwn.net/Articles/689387/ |title=PostgreSQL 9.6 Beta and PGCon 2016 |last=Berkus |first=Josh |date=June 2, 2016 |website=LWN.net}}</ref> Client applications can use threads and create multiple database connections from each thread.<ref>{{Cite web |url=https://wiki.postgresql.org/wiki/FAQ#How_does_PostgreSQL_use_CPU_resources.3F |title=FAQ – PostgreSQL wiki |website=wiki.postgresql.org |language=en |access-date=April 13, 2017}}</ref>