Every table has several system columns that are implicitly defined by the system. Therefore, these names cannot be used as names of user-defined columns. (Note that these restrictions are separate from whether the name is a key word or not; quoting a name will not allow you to escape these restrictions.) You do not really need to be concerned about these columns; just know they exist. oid The object identifier (object ID) of a row. This column is only present if the table was created using WI

NameCREATE RULE -- define a new rewrite rule Synopsis CREATE [ OR REPLACE ] RULE name AS ON event TO table_name [ WHERE condition ] DO [ ALSO | INSTEAD ] { NOTHING | command | ( command ; command ... ) } where event can be one of: SELECT | INSERT | UPDATE | DELETE Description CREATE RULE defines a new rule applying to a specified table or view. CREATE OR REPLACE RULE will either create a new rule, or replace an existing rule of the same name for the same table. The Postg

NameSET TRANSACTION -- set the characteristics of the current transaction Synopsis SET TRANSACTION transaction_mode [, ...] SET TRANSACTION SNAPSHOT snapshot_id SET SESSION CHARACTERISTICS AS TRANSACTION transaction_mode [, ...] where transaction_mode is one of: ISOLATION LEVEL { SERIALIZABLE | REPEATABLE READ | READ COMMITTED | READ UNCOMMITTED } READ WRITE | READ ONLY [ NOT ] DEFERRABLE Description The SET TRANSACTION command sets the characteristics of the current tra

Transactions are a fundamental concept of all database systems. The essential point of a transaction is that it bundles multiple steps into a single, all-or-nothing operation. The intermediate states between the steps are not visible to other concurrent transactions, and if some failure occurs that prevents the transaction from completing, then none of the steps affect the database at all. For example, consider a bank database that contains balances for various customer accounts, as well as tot

An alternative to the built-in standby mode described in the previous sections is to use a restore_command that polls the archive location. This was the only option available in versions 8.4 and below. In this setup, set standby_mode off, because you are implementing the polling required for standby operation yourself. See the pg_standby module for a reference implementation of this. Note that in this mode, the server will apply WAL one file at a time, so if you use the standby server for queri

The executor takes the plan created by the planner/optimizer and recursively processes it to extract the required set of rows. This is essentially a demand-pull pipeline mechanism. Each time a plan node is called, it must deliver one more row, or report that it is done delivering rows. To provide a concrete example, assume that the top node is a MergeJoin node. Before any merge can be done two rows have to be fetched (one from each subplan). So the executor recursively calls itself to process t

An alternative backup strategy is to directly copy the files that PostgreSQL uses to store the data in the database; Section 18.2 explains where these files are located. You can use whatever method you prefer for doing file system backups; for example: tar -cf backup.tar /usr/local/pgsql/data There are two restrictions, however, which make this method impractical, or at least inferior to the pg_dump method: The database server must be shut down in order to get a usable backup. Half-way measu

Namepg_isready -- check the connection status of a PostgreSQL server Synopsis pg_isready [connection-option...] [option...] Description pg_isready is a utility for checking the connection status of a PostgreSQL database server. The exit status specifies the result of the connection check. Options -d dbname--dbname=dbname Specifies the name of the database to connect to. If this parameter contains an = sign or starts with a valid URI prefix (postgresql:// or postgres://), it is tr

Namepgbench -- run a benchmark test on PostgreSQL Synopsis pgbench -i [option...] [dbname] pgbench [option...] [dbname] Description pgbench is a simple program for running benchmark tests on PostgreSQL. It runs the same sequence of SQL commands over and over, possibly in multiple concurrent database sessions, and then calculates the average transaction rate (transactions per second). By default, pgbench tests a scenario that is loosely based on TPC-B, involving five SELECT, UPDATE, and

The current limitations of PostgreSQL's text search features are: The length of each lexeme must be less than 2K bytes The length of a tsvector (lexemes + positions) must be less than 1 megabyte The number of lexemes must be less than 264 Position values in tsvector must be greater than 0 and no more than 16,383 The match distance in a <N> (FOLLOWED BY) tsquery operator cannot be more than 16,384 No more than 256 positions per lexeme The number of nodes (lexemes + operators)