NameBEGIN -- start a transaction block Synopsis BEGIN [ WORK | 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 BEGIN initiates a transaction block, that is, all statements after a BEGIN command will be executed in a single transaction until an explicit COMMIT or ROLLBACK is given. By default (without BEG

NameDROP FOREIGN DATA WRAPPER -- remove a foreign-data wrapper Synopsis DROP FOREIGN DATA WRAPPER [ IF EXISTS ] name [ CASCADE | RESTRICT ] Description DROP FOREIGN DATA WRAPPER removes an existing foreign-data wrapper. To execute this command, the current user must be the owner of the foreign-data wrapper. Parameters IF EXISTS Do not throw an error if the foreign-data wrapper does not exist. A notice is issued in this case. name The name of an existing foreign-data wrapper. C

NameCOMMIT PREPARED -- commit a transaction that was earlier prepared for two-phase commit Synopsis COMMIT PREPARED transaction_id Description COMMIT PREPARED commits a transaction that is in prepared state. Parameters transaction_id The transaction identifier of the transaction that is to be committed. Notes To commit a prepared transaction, you must be either the same user that executed the transaction originally, or a superuser. But you do not have to be in the same sessi

The view pg_shadow exists for backwards compatibility: it emulates a catalog that existed in PostgreSQL before version 8.1. It shows properties of all roles that are marked as rolcanlogin in pg_authid. The name stems from the fact that this table should not be readable by the public since it contains passwords. pg_user is a publicly readable view on pg_shadow that blanks out the password field. Table 50-77. pg_shadow Columns Name Type References Description usename name pg_authid.rolname User

PostgreSQL provides the standard SQL type boolean; see Table 8-19. The boolean type can have several states: "true", "false", and a third state, "unknown", which is represented by the SQL null value. Table 8-19. Boolean Data Type Name Storage Size Description boolean 1 byte state of true or false Valid literal values for the "true" state are: TRUE 't' 'true' 'y' 'yes' 'on' '1' For the "false" state, the following values can be used: FALSE 'f' 'false' 'n' 'no' 'off' '0' Leading or traili

Suppose we have a table similar to this: CREATE TABLE test1 ( id integer, content varchar ); and the application issues many queries of the form: SELECT content FROM test1 WHERE id = constant; With no advance preparation, the system would have to scan the entire test1 table, row by row, to find all matching entries. If there are many rows in test1 and only a few rows (perhaps zero or one) that would be returned by such a query, this is clearly an inefficient method. But if the syste

The catalog pg_seclabel stores security labels on database objects. Security labels can be manipulated with the SECURITY LABEL command. For an easier way to view security labels, see Section 50.74. See also pg_shseclabel, which performs a similar function for security labels of database objects that are shared across a database cluster. Table 50-42. pg_seclabel Columns Name Type References Description objoid oid any OID column The OID of the object this security label pertains to classoid oid

NameCREATE ACCESS METHOD -- define a new access method Synopsis CREATE ACCESS METHOD name TYPE access_method_type HANDLER handler_function Description CREATE ACCESS METHOD creates a new access method. The access method name must be unique within the database. Only superusers can define new access methods. Parameters name The name of the access method to be created. access_method_type This clause specifies the type of access method to define. Only INDEX is supported at p

NameDEALLOCATE -- deallocate a prepared statement Synopsis DEALLOCATE [ PREPARE ] { name | ALL } Description DEALLOCATE is used to deallocate a previously prepared SQL statement. If you do not explicitly deallocate a prepared statement, it is deallocated when the session ends. For more information on prepared statements, see PREPARE. Parameters PREPARE This key word is ignored. name The name of the prepared statement to deallocate. ALL Deallocate all prepared statements.

Among all relational operators the most difficult one to process and optimize is the join. The number of possible query plans grows exponentially with the number of joins in the query. Further optimization effort is caused by the support of a variety of join methods (e.g., nested loop, hash join, merge join in PostgreSQL) to process individual joins and a diversity of indexes (e.g., B-tree, hash, GiST and GIN in PostgreSQL) as access paths for relations. The normal PostgreSQL query optimizer pe