The pgrowlocks module provides a function to show row locking information for a specified table. F.28.1. Overview pgrowlocks(text) returns setof record The parameter is the name of a table. The result is a set of records, with one row for each locked row within the table. The output columns are shown in Table F-21. Table F-21. pgrowlocks Output Columns Name Type Description locked_row tid Tuple ID (TID) of locked row locker xid Transaction ID of locker, or multixact ID if multitransaction

Mathematical operators are provided for many PostgreSQL types. For types without standard mathematical conventions (e.g., date/time types) we describe the actual behavior in subsequent sections. Table 9-4 shows the available mathematical operators. Table 9-4. Mathematical Operators Operator Description Example Result + addition 2 + 3 5 - subtraction 2 - 3 -1 * multiplication 2 * 3 6 / division (integer division truncates the result) 4 / 2 2 % modulo (remainder) 5 % 4 1 ^ exponentiation (assoc

Value expressions are used in a variety of contexts, such as in the target list of the SELECT command, as new column values in INSERT or UPDATE, or in search conditions in a number of commands. The result of a value expression is sometimes called a scalar, to distinguish it from the result of a table expression (which is a table). Value expressions are therefore also called scalar expressions (or even simply expressions). The expression syntax allows the calculation of values from primitive par

NameCREATE TYPE -- define a new data type Synopsis CREATE TYPE name AS ( [ attribute_name data_type [ COLLATE collation ] [, ... ] ] ) CREATE TYPE name AS ENUM ( [ 'label' [, ... ] ] ) CREATE TYPE name AS RANGE ( SUBTYPE = subtype [ , SUBTYPE_OPCLASS = subtype_operator_class ] [ , COLLATION = collation ] [ , CANONICAL = canonical_function ] [ , SUBTYPE_DIFF = subtype_diff_function ] ) CREATE TYPE name ( INPUT = input_function, OUTPUT = output_functio

At all times, PostgreSQL maintains a write ahead log (WAL) in the pg_xlog/ subdirectory of the cluster's data directory. The log records every change made to the database's data files. This log exists primarily for crash-safety purposes: if the system crashes, the database can be restored to consistency by "replaying" the log entries made since the last checkpoint. However, the existence of the log makes it possible to use a third strategy for backing up databases: we can combine a file-system-

Create vs. insert Insertion into a GIN index can be slow due to the likelihood of many keys being inserted for each item. So, for bulk insertions into a table it is advisable to drop the GIN index and recreate it after finishing bulk insertion. As of PostgreSQL 8.4, this advice is less necessary since delayed indexing is used (see Section 63.4.1 for details). But for very large updates it may still be best to drop and recreate the index. maintenance_work_mem Build time for a GIN index is ve

PostgreSQL's statistics collector is a subsystem that supports collection and reporting of information about server activity. Presently, the collector can count accesses to tables and indexes in both disk-block and individual-row terms. It also tracks the total number of rows in each table, and information about vacuum and analyze actions for each table. It can also count calls to user-defined functions and the total time spent in each one. PostgreSQL also supports reporting dynamic information

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

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

It is possible to use SSH to encrypt the network connection between clients and a PostgreSQL server. Done properly, this provides an adequately secure network connection, even for non-SSL-capable clients. First make sure that an SSH server is running properly on the same machine as the PostgreSQL server and that you can log in using ssh as some user. Then you can establish a secure tunnel with a command like this from the client machine: ssh -L 63333:localhost:5432 joe@foo.com The first numbe