LOCK

title: LOCK(7)

summary: lock a table

text:
# LOCK(7)

## NAME
LOCK - lock a table

## SYNOPSIS

```
LOCK [ TABLE ] [ ONLY ] name [ * ] [, ...] [ IN lockmode MODE ] [ NOWAIT ]
where lockmode is one of:

    ACCESS SHARE | ROW SHARE | ROW EXCLUSIVE | SHARE UPDATE EXCLUSIVE

    | SHARE | SHARE ROW EXCLUSIVE | EXCLUSIVE | ACCESS EXCLUSIVE
```

## DESCRIPTION
LOCK TABLE obtains a table-level lock, waiting if necessary for any conflicting locks to be released. If NOWAIT is specified, LOCK TABLE does not wait to acquire the desired lock: if it cannot be acquired immediately, the command is aborted and an error is emitted. Once obtained, the lock is held for the remainder of the current transaction. (There is no UNLOCK TABLE command; locks are always released at transaction end.)

When a view is locked, all relations appearing in the view definition query are also locked recursively with the same lock mode.

When acquiring locks automatically for commands that reference tables, PostgreSQL always uses the least restrictive lock mode possible. LOCK TABLE provides for cases when you might need more restrictive locking. For example, suppose an application runs a transaction at the READ COMMITTED isolation level and needs to ensure that data in a table remains stable for the duration of the transaction. To achieve this you could obtain SHARE lock mode over the table before querying. This will prevent concurrent data changes and ensure subsequent reads of the table see a stable view of committed data, because SHARE lock mode conflicts with the ROW EXCLUSIVE lock acquired by writers, and your LOCK TABLE name IN SHARE MODE statement will wait until any concurrent holders of ROW EXCLUSIVE mode locks commit or roll back. Thus, once you obtain the lock, there are no uncommitted writes outstanding; furthermore none can begin until you release the lock.

To achieve a similar effect when running a transaction at the REPEATABLE READ or SERIALIZABLE isolation level, you have to execute the LOCK TABLE statement before executing any SELECT or data modification statement. A REPEATABLE READ or SERIALIZABLE transaction's view of data will be frozen when its first SELECT or data modification statement begins. A LOCK TABLE later in the transaction will still prevent concurrent writes — but it won't ensure that what the transaction reads corresponds to the latest committed values.

If a transaction of this sort is going to change the data in the table, then it should use SHARE ROW EXCLUSIVE lock mode instead of SHARE mode. This ensures that only one transaction of this type runs at a time. Without this, a deadlock is possible: two transactions might both acquire SHARE mode, and then be unable to also acquire ROW EXCLUSIVE mode to actually perform their updates. (Note that a transaction's own locks never conflict, so a transaction can acquire ROW EXCLUSIVE mode when it holds SHARE mode — but not if anyone else holds SHARE mode.) To avoid deadlocks, make sure all transactions acquire locks on the same objects in the same order, and if multiple lock modes are involved for a single object, then transactions should always acquire the most restrictive mode first.

More information about the lock modes and locking strategies can be found in Section 13.3.

## PARAMETERS
name

The name (optionally schema-qualified) of an existing table to lock. If ONLY is specified before the table name, only that table is locked. If ONLY is not specified, the table and all its descendant tables (if any) are locked. Optionally, * can be specified after the table name to explicitly indicate that descendant tables are included. The command LOCK TABLE a, b; is equivalent to LOCK TABLE a; LOCK TABLE b;. The tables are locked one-by-one in the order specified in the LOCK TABLE command.

lockmode

The lock mode specifies which locks this lock conflicts with. Lock modes are described in Section 13.3. If no lock mode is specified, then ACCESS EXCLUSIVE, the most restrictive mode, is used.

NOWAIT

Specifies that LOCK TABLE should not wait for any conflicting locks to be released: if the specified lock(s) cannot be acquired immediately without waiting, the transaction is aborted.

## NOTES
LOCK TABLE ... IN ACCESS SHARE MODE requires SELECT privileges on the target table. LOCK TABLE ... IN ROW EXCLUSIVE MODE requires INSERT, UPDATE, DELETE, or TRUNCATE privileges on the target table. All other forms of LOCK require table-level UPDATE, DELETE, or TRUNCATE privileges.

The user performing the lock on the view must have the corresponding privilege on the view. In addition the view's owner must have the relevant privileges on the underlying base relations, but the user performing the lock does not need any permissions on the underlying base relations.

LOCK TABLE is useless outside a transaction block: the lock would remain held only to the completion of the statement. Therefore PostgreSQL reports an error if LOCK is used outside a transaction block. Use BEGIN(7) and COMMIT(7) (or ROLLBACK(7)) to define a transaction block.

LOCK TABLE only deals with table-level locks, and so the mode names involving ROW are all misnomers. These mode names should generally be read as indicating the intention of the user to acquire row-level locks within the locked table. Also, ROW EXCLUSIVE mode is a shareable table lock. Keep in mind that all the lock modes have identical semantics so far as LOCK TABLE is concerned, differing only in the rules about which modes conflict with which. For information on how to acquire an actual row-level lock, see Section 13.3.2 and The Locking Clause in the SELECT(7) documentation.

## EXAMPLES
Obtain a SHARE lock on a primary key table when going to perform inserts into a foreign key table:

```
BEGIN WORK;
LOCK TABLE films IN SHARE MODE;
SELECT id FROM films

    WHERE name = 'Star Wars: Episode I - The Phantom Menace';
-- Do ROLLBACK if record was not returned
INSERT INTO films_user_comments VALUES

    (_id_, 'GREAT! I was waiting for it for so long!');
COMMIT WORK;
```

Take a SHARE ROW EXCLUSIVE lock on a primary key table when going to perform a delete operation:

```
BEGIN WORK;
LOCK TABLE films IN SHARE ROW EXCLUSIVE MODE;
DELETE FROM films_user_comments WHERE id IN

    (SELECT id FROM films WHERE rating < 5);
DELETE FROM films WHERE rating < 5;
COMMIT WORK;
```

## COMPATIBILITY
There is no LOCK TABLE in the SQL standard, which instead uses SET TRANSACTION to specify concurrency levels on transactions. PostgreSQL supports that too; see SET TRANSACTION (SET_TRANSACTION(7)) for details.

Except for ACCESS SHARE, ACCESS EXCLUSIVE, and SHARE UPDATE EXCLUSIVE lock modes, the PostgreSQL lock modes and the LOCK TABLE syntax are compatible with those present in Oracle.


MOVE

title: MOVE(7)

summary: position a cursor

text:
# MOVE(7)

## NAME
MOVE - position a cursor

## SYNOPSIS

```
MOVE [ direction ] [ FROM | IN ] cursor_name
where direction can be one of:

    NEXT

    PRIOR

    FIRST

    LAST

    ABSOLUTE count

    RELATIVE count

    count

    ALL

    FORWARD

    FORWARD count

    FORWARD ALL

    BACKWARD

    BACKWARD count

    BACKWARD ALL
```

## DESCRIPTION
MOVE repositions a cursor without retrieving any data. MOVE works exactly like the FETCH command, except it only positions the cursor and does not return rows.

The parameters for the MOVE command are identical to those of the FETCH command; refer to FETCH(7) for details on syntax and usage.

## OUTPUTS
On successful completion, a MOVE command returns a command tag of the form

```
MOVE count
```

The count is the number of rows that a FETCH command with the same parameters would have returned (possibly zero).

## EXAMPLES

```
BEGIN WORK;
DECLARE liahona CURSOR FOR SELECT * FROM films;
-- Skip the first 5 rows:
MOVE FORWARD 5 IN liahona;
MOVE 5
-- Fetch the 6th row from the cursor liahona:
FETCH 1 FROM liahona;

 code  | title  | did | date_prod  |  kind  |  len
-------+--------+-----+------------+--------+-------

 P_303 | 48 Hrs | 103 | 1982-10-22 | Action | 01:37
(1 row)
-- Close the cursor liahona and end the transaction:
CLOSE liahona;
COMMIT WORK;
```

## COMPATIBILITY
There is no MOVE statement in the SQL standard.

## SEE ALSO
CLOSE(7), DECLARE(7), FETCH(7)


NOTIFY

title: NOTIFY(7)

summary: generate a notification

text:
# NOTIFY(7)

## NAME
NOTIFY - generate a notification

## SYNOPSIS

```
NOTIFY channel [ , payload ]
```

## DESCRIPTION
The NOTIFY command sends a notification event together with an optional “payload” string to each client application that has previously executed LISTEN channel for the specified channel name in the current database. Notifications are visible to all users.

NOTIFY provides a simple interprocess communication mechanism for a collection of processes accessing the same PostgreSQL database. A payload string can be sent along with the notification, and higher-level mechanisms for passing structured data can be built by using tables in the database to pass additional data from notifier to listener(s).

The information passed to the client for a notification event includes the notification channel name, the notifying session's server process PID, and the payload string, which is an empty string if it has not been specified.

It is up to the database designer to define the channel names that will be used in a given database and what each one means. Commonly, the channel name is the same as the name of some table in the database, and the notify event essentially means, “I changed this table, take a look at it to see what's new”. But no such association is enforced by the NOTIFY and LISTEN commands. For example, a database designer could use several different channel names to signal different sorts of changes to a single table. Alternatively, the payload string could be used to differentiate various cases.

When NOTIFY is used to signal the occurrence of changes to a particular table, a useful programming technique is to put the NOTIFY in a statement trigger that is triggered by table updates. In this way, notification happens automatically when the table is changed, and the application programmer cannot accidentally forget to do it.

NOTIFY interacts with SQL transactions in some important ways. Firstly, if a NOTIFY is executed inside a transaction, the notify events are not delivered until and unless the transaction is committed. This is appropriate, since if the transaction is aborted, all the commands within it have had no effect, including NOTIFY. But it can be disconcerting if one is expecting the notification events to be delivered immediately. Secondly, if a listening session receives a notification signal while it is within a transaction, the notification event will not be delivered to its connected client until just after the transaction is completed (either committed or aborted). Again, the reasoning is that if a notification were delivered within a transaction that was later aborted, one would want the notification to be undone somehow — but the server cannot “take back” a notification once it has sent it to the client. So notification events are only delivered between transactions. The upshot of this is that applications using NOTIFY for real-time signaling should try to keep their transactions short.

If the same channel name is signaled multiple times with identical payload strings within the same transaction, only one instance of the notification event is delivered to listeners. On the other hand, notifications with distinct payload strings will always be delivered as distinct notifications. Similarly, notifications from different transactions will never get folded into one notification. Except for dropping later instances of duplicate notifications, NOTIFY guarantees that notifications from the same transaction get delivered in the order they were sent. It is also guaranteed that messages from different transactions are delivered in the order in which the transactions committed.

It is common for a client that executes NOTIFY to be listening on the same notification channel itself. In that case it will get back a notification event, just like all the other listening sessions. Depending on the application logic, this could result in useless work, for example, reading a database table to find the same updates that that session just wrote out. It is possible to avoid such extra work by noticing whether the notifying session's server process PID (supplied in the notification event message) is the same as one's own session's PID (available from libpq). When they are the same, the notification event is one's own work bouncing back, and can be ignored.

## PARAMETERS
channel

Name of the notification channel to be signaled (any identifier).

payload

The “payload” string to be communicated along with the notification. This must be specified as a simple string literal. In the default configuration it must be shorter than 8000 bytes. (If binary data or large amounts of information need to be communicated, it's best to put it in a database table and send the key of the record.)

## NOTES
There is a queue that holds notifications that have been sent but not yet processed by all listening sessions. If this queue becomes full, transactions calling NOTIFY will fail at commit. The queue is quite large (8GB in a standard installation) and should be sufficiently sized for almost every use case. However, no cleanup can take place if a session executes LISTEN and then enters a transaction for a very long time. Once the queue is half full you will see warnings in the log file pointing you to the session that is preventing cleanup. In this case you should make sure that this session ends its current transaction so that cleanup can proceed.

The function pg_notification_queue_usage returns the fraction of the queue that is currently occupied by pending notifications. See Section 9.26 for more information.

A transaction that has executed NOTIFY cannot be prepared for two-phase commit.

### pg_notify
To send a notification you can also use the function pg_notify(text, text). The function takes the channel name as the first argument and the payload as the second. The function is much easier to use than the NOTIFY command if you need to work with non-constant channel names and payloads.

## EXAMPLES
Configure and execute a listen/notify sequence from psql:

```
LISTEN virtual;
NOTIFY virtual;
Asynchronous notification "virtual" received from server process with PID 8448.
NOTIFY virtual, 'This is the payload';
Asynchronous notification "virtual" with payload "This is the payload" received from server process with PID 8448.
LISTEN foo;
SELECT pg_notify('fo' || 'o', 'pay' || 'load');
Asynchronous notification "foo" with payload "payload" received from server process with PID 14728.
```

## COMPATIBILITY
There is no NOTIFY statement in the SQL standard.

## SEE ALSO
LISTEN(7), UNLISTEN(7)


PREPARE

title: PREPARE(7)

summary: prepare a statement for execution

text:
# PREPARE(7)

## NAME
PREPARE - prepare a statement for execution

## SYNOPSIS

```
PREPARE name [ ( data_type [, ...] ) ] AS statement
```

## DESCRIPTION
PREPARE creates a prepared statement. A prepared statement is a server-side object that can be used to optimize performance. When the PREPARE statement is executed, the specified statement is parsed, analyzed, and rewritten. When an EXECUTE command is subsequently issued, the prepared statement is planned and executed. This division of labor avoids repetitive parse analysis work, while allowing the execution plan to depend on the specific parameter values supplied.

Prepared statements can take parameters: values that are substituted into the statement when it is executed. When creating the prepared statement, refer to parameters by position, using $1, $2, etc. A corresponding list of parameter data types can optionally be specified. When a parameter's data type is not specified or is declared as unknown, the type is inferred from the context in which the parameter is first referenced (if possible). When executing the statement, specify the actual values for these parameters in the EXECUTE statement. Refer to EXECUTE(7) for more information about that.

Prepared statements only last for the duration of the current database session. When the session ends, the prepared statement is forgotten, so it must be recreated before being used again. This also means that a single prepared statement cannot be used by multiple simultaneous database clients; however, each client can create their own prepared statement to use. Prepared statements can be manually cleaned up using the DEALLOCATE(7) command.

Prepared statements potentially have the largest performance advantage when a single session is being used to execute a large number of similar statements. The performance difference will be particularly significant if the statements are complex to plan or rewrite, e.g., if the query involves a join of many tables or requires the application of several rules. If the statement is relatively simple to plan and rewrite but relatively expensive to execute, the performance advantage of prepared statements will be less noticeable.

## PARAMETERS
name

An arbitrary name given to this particular prepared statement. It must be unique within a single session and is subsequently used to execute or deallocate a previously prepared statement.

data_type

The data type of a parameter to the prepared statement. If the data type of a particular parameter is unspecified or is specified as unknown, it will be inferred from the context in which the parameter is first referenced. To refer to the parameters in the prepared statement itself, use $1, $2, etc.

statement

Any SELECT, INSERT, UPDATE, DELETE, or VALUES statement.

## NOTES
A prepared statement can be executed with either a generic plan or a custom plan. A generic plan is the same across all executions, while a custom plan is generated for a specific execution using the parameter values given in that call. Use of a generic plan avoids planning overhead, but in some situations a custom plan will be much more efficient to execute because the planner can make use of knowledge of the parameter values. (Of course, if the prepared statement has no parameters, then this is moot and a generic plan is always used.)

By default (that is, when plan_cache_mode is set to auto), the server will automatically choose whether to use a generic or custom plan for a prepared statement that has parameters. The current rule for this is that the first five executions are done with custom plans and the average estimated cost of those plans is calculated. Then a generic plan is created and its estimated cost is compared to the average custom-plan cost. Subsequent executions use the generic plan if its cost is not so much higher than the average custom-plan cost as to make repeated replanning seem preferable.

This heuristic can be overridden, forcing the server to use either generic or custom plans, by setting plan_cache_mode to force_generic_plan or force_custom_plan respectively. This setting is primarily useful if the generic plan's cost estimate is badly off for some reason, allowing it to be chosen even though its actual cost is much more than that of a custom plan.

To examine the query plan PostgreSQL is using for a prepared statement, use EXPLAIN(7), for example

```
EXPLAIN EXECUTE name(parameter_values);
```

If a generic plan is in use, it will contain parameter symbols $n, while a custom plan will have the supplied parameter values substituted into it.

For more information on query planning and the statistics collected by PostgreSQL for that purpose, see the ANALYZE(7) documentation.

Although the main point of a prepared statement is to avoid repeated parse analysis and planning of the statement, PostgreSQL will force re-analysis and re-planning of the statement before using it whenever database objects used in the statement have undergone definitional (DDL) changes since the previous use of the prepared statement. Also, if the value of search_path changes from one use to the next, the statement will be re-parsed using the new search_path. (This latter behavior is new as of PostgreSQL 9.3.) These rules make use of a prepared statement semantically almost equivalent to re-submitting the same query text over and over, but with a performance benefit if no object definitions are changed, especially if the best plan remains the same across uses. An example of a case where the semantic equivalence is not perfect is that if the statement refers to a table by an unqualified name, and then a new table of the same name is created in a schema appearing earlier in the search_path, no automatic re-parse will occur since no object used in the statement changed. However, if some other change forces a re-parse, the new table will be referenced in subsequent uses.

You can see all prepared statements available in the session by querying the pg_prepared_statements system view.

## EXAMPLES
Create a prepared statement for an INSERT statement, and then execute it:

```
PREPARE fooplan (int, text, bool, numeric) AS

    INSERT INTO foo VALUES($1, $2, $3, $4);
EXECUTE fooplan(1, 'Hunter Valley', 't', 200.00);
```

Create a prepared statement for a SELECT statement, and then execute it:

```
PREPARE usrrptplan (int) AS

    SELECT * FROM users u, logs l WHERE u.usrid=$1 AND u.usrid=l.usrid

    AND l.date = $2;
EXECUTE usrrptplan(1, current_date);
```

In this example, the data type of the second parameter is not specified, so it is inferred from the context in which $2 is used.

## COMPATIBILITY
The SQL standard includes a PREPARE statement, but it is only for use in embedded SQL. This version of the PREPARE statement also uses a somewhat different syntax.

## SEE ALSO
DEALLOCATE(7), EXECUTE(7)


title: PREPARE_TRANSACTION(7)

summary: prepare the current transaction for two\-phase commit

text:
# PREPARE_TRANSACTION(7)

## NAME
PREPARE_TRANSACTION - prepare the current transaction for two-phase commit

## SYNOPSIS

```
PREPARE TRANSACTION transaction_id
```

## DESCRIPTION
PREPARE TRANSACTION prepares the current transaction for two-phase commit. After this command, the transaction is no longer associated with the current session; instead, its state is fully stored on disk, and there is a very high probability that it can be committed successfully, even if a database crash occurs before the commit is requested.

Once prepared, a transaction can later be committed or rolled back with COMMIT PREPARED (COMMIT_PREPARED(7)) or ROLLBACK PREPARED (ROLLBACK_PREPARED(7)), respectively. Those commands can be issued from any session, not only the one that executed the original transaction.

From the point of view of the issuing session, PREPARE TRANSACTION is not unlike a ROLLBACK command: after executing it, there is no active current transaction, and the effects of the prepared transaction are no longer visible. (The effects will become visible again if the transaction is committed.)

If the PREPARE TRANSACTION command fails for any reason, it becomes a ROLLBACK: the current transaction is canceled.

## PARAMETERS
transaction_id

An arbitrary identifier that later identifies this transaction for COMMIT PREPARED or ROLLBACK PREPARED. The identifier must be written as a string literal, and must be less than 200 bytes long. It must not be the same as the identifier used for any currently prepared transaction.

## NOTES
PREPARE TRANSACTION is not intended for use in applications or interactive sessions. Its purpose is to allow an external transaction manager to perform atomic global transactions across multiple databases or other transactional resources. Unless you're writing a transaction manager, you probably shouldn't be using PREPARE TRANSACTION.

This command must be used inside a transaction block. Use BEGIN(7) to start one.

It is not currently allowed to PREPARE a transaction that has executed any operations involving temporary tables or the session's temporary namespace, created any cursors WITH HOLD, or executed LISTEN, UNLISTEN, or NOTIFY. Those features are too tightly tied to the current session to be useful in a transaction to be prepared.

If the transaction modified any run-time parameters with SET (without the LOCAL option), those effects persist after PREPARE TRANSACTION, and will not be affected by any later COMMIT PREPARED or ROLLBACK PREPARED. Thus, in this one respect PREPARE TRANSACTION acts more like COMMIT than ROLLBACK.

All currently available prepared transactions are listed in the pg_prepared_xacts system view.

Caution

It is unwise to leave transactions in the prepared state for a long time. This will interfere with the ability of VACUUM to reclaim storage, and in extreme cases could cause the database to shut down to prevent transaction ID wraparound (see Section 24.1.5). Keep in mind also that the transaction continues to hold whatever locks it held. The intended usage of the feature is that a prepared transaction will normally be committed or rolled back as soon as an external transaction manager has verified that other databases are also prepared to commit.

If you have not set up an external transaction manager to track prepared transactions and ensure they get closed out promptly, it is best to keep the prepared-transaction feature disabled by setting max_prepared_transactions to zero. This will prevent accidental creation of prepared transactions that might then be forgotten and eventually cause problems.

## EXAMPLES
Prepare the current transaction for two-phase commit, using foobar as the transaction identifier:

```
PREPARE TRANSACTION 'foobar';
```

## COMPATIBILITY
PREPARE TRANSACTION is a PostgreSQL extension. It is intended for use by external transaction management systems, some of which are covered by standards (such as X/Open XA), but the SQL side of those systems is not standardized.

## SEE ALSO
COMMIT PREPARED (COMMIT_PREPARED(7)), ROLLBACK PREPARED (ROLLBACK_PREPARED(7))


title: REASSIGN_OWNED(7)

text:
# REASSIGN_OWNED(7)

## NAME
REASSIGN_OWNED - change the ownership of database objects owned by a database role

## SYNOPSIS

```
REASSIGN OWNED BY { old_role | CURRENT_USER | SESSION_USER } [, ...]

               TO { new_role | CURRENT_USER | SESSION_USER }
```

## DESCRIPTION
REASSIGN OWNED instructs the system to change the ownership of database objects owned by any of the old_roles to new_role.

## PARAMETERS
old_role

The name of a role. The ownership of all the objects within the current database, and of all shared objects (databases, tablespaces), owned by this role will be reassigned to new_role.

new_role

The name of the role that will be made the new owner of the affected objects.

## NOTES
REASSIGN OWNED is often used to prepare for the removal of one or more roles. Because REASSIGN OWNED does not affect objects within other databases, it is usually necessary to execute this command in each database that contains objects owned by a role that is to be removed.

REASSIGN OWNED requires membership on both the source role(s) and the target role.

The DROP OWNED (DROP_OWNED(7)) command is an alternative that simply drops all the database objects owned by one or more roles.

The REASSIGN OWNED command does not affect any privileges granted to the old_roles on objects that are not owned by them. Likewise, it does not affect default privileges created with ALTER DEFAULT PRIVILEGES. Use DROP OWNED to revoke such privileges.

See Section 21.4 for more discussion.

## COMPATIBILITY
The REASSIGN OWNED command is a PostgreSQL extension.

## SEE ALSO
DROP OWNED (DROP_OWNED(7)), DROP ROLE (DROP_ROLE(7)), ALTER DATABASE (ALTER_DATABASE(7))


title: REFRESH_MATERIALIZED_VIEW(7)

text:
# REFRESH_MATERIALIZED_VIEW(7)

## NAME
REFRESH_MATERIALIZED_VIEW - replace the contents of a materialized view

## SYNOPSIS

```
REFRESH MATERIALIZED VIEW [ CONCURRENTLY ] name

    [ WITH [ NO ] DATA ]
```

## DESCRIPTION
REFRESH MATERIALIZED VIEW completely replaces the contents of a materialized view. To execute this command you must be the owner of the materialized view. The old contents are discarded. If WITH DATA is specified (or defaults) the backing query is executed to provide the new data, and the materialized view is left in a scannable state. If WITH NO DATA is specified no new data is generated and the materialized view is left in an unscannable state.

CONCURRENTLY and WITH NO DATA may not be specified together.

## PARAMETERS
CONCURRENTLY

Refresh the materialized view without locking out concurrent selects on the materialized view. Without this option a refresh which affects a lot of rows will tend to use fewer resources and complete more quickly, but could block other connections which are trying to read from the materialized view. This option may be faster in cases where a small number of rows are affected. This option is only allowed if there is at least one UNIQUE index on the materialized view which uses only column names and includes all rows; that is, it must not be an expression index or include a WHERE clause.

This option may not be used when the materialized view is not already populated.

Even with this option only one REFRESH at a time may run against any one materialized view.

name

The name (optionally schema-qualified) of the materialized view to refresh.

## NOTES
If there is an ORDER BY clause in the materialized view's defining query, the original contents of the materialized view will be ordered that way; but REFRESH MATERIALIZED VIEW does not guarantee to preserve that ordering.

## EXAMPLES
This command will replace the contents of the materialized view called order_summary using the query from the materialized view's definition, and leave it in a scannable state:

```
REFRESH MATERIALIZED VIEW order_summary;
```

This command will free storage associated with the materialized view annual_statistics_basis and leave it in an unscannable state:

```
REFRESH MATERIALIZED VIEW annual_statistics_basis WITH NO DATA;
```

## COMPATIBILITY
REFRESH MATERIALIZED VIEW is a PostgreSQL extension.

## SEE ALSO
CREATE MATERIALIZED VIEW (CREATE_MATERIALIZED_VIEW(7)), ALTER MATERIALIZED VIEW (ALTER_MATERIALIZED_VIEW(7)), DROP MATERIALIZED VIEW (DROP_MATERIALIZED_VIEW(7))


REINDEX

title: REINDEX(7)

summary: rebuild indexes

text:
# REINDEX(7)

## NAME
REINDEX - rebuild indexes

## SYNOPSIS

```
REINDEX [ ( option [, ...] ) ] { INDEX | TABLE | SCHEMA | DATABASE | SYSTEM } [ CONCURRENTLY ] name
where option can be one of:

    VERBOSE
```

## DESCRIPTION
REINDEX rebuilds an index using the data stored in the index's table, replacing the old copy of the index. There are several scenarios in which to use REINDEX:

•An index has become corrupted, and no longer contains valid data. Although in theory this should never happen, in practice indexes can become corrupted due to software bugs or hardware failures. REINDEX provides a recovery method.

•An index has become “bloated”, that is it contains many empty or nearly-empty pages. This can occur with B-tree indexes in PostgreSQL under certain uncommon access patterns. REINDEX provides a way to reduce the space consumption of the index by writing a new version of the index without the dead pages. See Section 24.2 for more information.

•You have altered a storage parameter (such as fillfactor) for an index, and wish to ensure that the change has taken full effect.

•If an index build fails with the CONCURRENTLY option, this index is left as “invalid”. Such indexes are useless but it can be convenient to use REINDEX to rebuild them. Note that only REINDEX INDEX is able to perform a concurrent build on an invalid index.

## PARAMETERS
INDEX

Recreate the specified index.

TABLE

Recreate all indexes of the specified table. If the table has a secondary “TOAST” table, that is reindexed as well.

SCHEMA

Recreate all indexes of the specified schema. If a table of this schema has a secondary “TOAST” table, that is reindexed as well. Indexes on shared system catalogs are also processed. This form of REINDEX cannot be executed inside a transaction block.

DATABASE

Recreate all indexes within the current database. Indexes on shared system catalogs are also processed. This form of REINDEX cannot be executed inside a transaction block.

SYSTEM

Recreate all indexes on system catalogs within the current database. Indexes on shared system catalogs are included. Indexes on user tables are not processed. This form of REINDEX cannot be executed inside a transaction block.

name

The name of the specific index, table, or database to be reindexed. Index and table names can be schema-qualified. Presently, REINDEX DATABASE and REINDEX SYSTEM can only reindex the current database, so their parameter must match the current database's name.

CONCURRENTLY

When this option is used, PostgreSQL will rebuild the index without taking any locks that prevent concurrent inserts, updates, or deletes on the table; whereas a standard index rebuild locks out writes (but not reads) on the table until it's done. There are several caveats to be aware of when using this option — see Rebuilding Indexes Concurrently below. For temporary tables, REINDEX is always non-concurrent, as no other session can access them, and non-concurrent reindex is cheaper.

VERBOSE

Prints a progress report as each index is reindexed.

## NOTES
If you suspect corruption of an index on a user table, you can simply rebuild that index, or all indexes on the table, using REINDEX INDEX or REINDEX TABLE.

Things are more difficult if you need to recover from corruption of an index on a system table. In this case it's important for the system to not have used any of the suspect indexes itself. (Indeed, in this sort of scenario you might find that server processes are crashing immediately at start-up, due to reliance on the corrupted indexes.) To recover safely, the server must be started with the -P option, which prevents it from using indexes for system catalog lookups.

One way to do this is to shut down the server and start a single-user PostgreSQL server with the -P option included on its command line. Then, REINDEX DATABASE, REINDEX SYSTEM, REINDEX TABLE, or REINDEX INDEX can be issued, depending on how much you want to reconstruct. If in doubt, use REINDEX SYSTEM to select reconstruction of all system indexes in the database. Then quit the single-user server session and restart the regular server. See the postgres(1) reference page for more information about how to interact with the single-user server interface.

Alternatively, a regular server session can be started with -P included in its command line options. The method for doing this varies across clients, but in all libpq-based clients, it is possible to set the PGOPTIONS environment variable to -P before starting the client. Note that while this method does not require locking out other clients, it might still be wise to prevent other users from connecting to the damaged database until repairs have been completed.

REINDEX is similar to a drop and recreate of the index in that the index contents are rebuilt from scratch. However, the locking considerations are rather different. REINDEX locks out writes but not reads of the index's parent table. It also takes an ACCESS EXCLUSIVE lock on the specific index being processed, which will block reads that attempt to use that index. In contrast, DROP INDEX momentarily takes an ACCESS EXCLUSIVE lock on the parent table, blocking both writes and reads. The subsequent CREATE INDEX locks out writes but not reads; since the index is not there, no read will attempt to use it, meaning that there will be no blocking but reads might be forced into expensive sequential scans.

Reindexing a single index or table requires being the owner of that index or table. Reindexing a schema or database requires being the owner of that schema or database. Note specifically that it's thus possible for non-superusers to rebuild indexes of tables owned by other users. However, as a special exception, when REINDEX DATABASE, REINDEX SCHEMA or REINDEX SYSTEM is issued by a non-superuser, indexes on shared catalogs will be skipped unless the user owns the catalog (which typically won't be the case). Of course, superusers can always reindex anything.

Reindexing partitioned tables or partitioned indexes is not supported. Each individual partition can be reindexed separately instead.

### Rebuilding Indexes Concurrently
Rebuilding an index can interfere with regular operation of a database. Normally PostgreSQL locks the table whose index is rebuilt against writes and performs the entire index build with a single scan of the table. Other transactions can still read the table, but if they try to insert, update, or delete rows in the table they will block until the index rebuild is finished. This could have a severe effect if the system is a live production database. Very large tables can take many hours to be indexed, and even for smaller tables, an index rebuild can lock out writers for periods that are unacceptably long for a production system.

PostgreSQL supports rebuilding indexes with minimum locking of writes. This method is invoked by specifying the CONCURRENTLY option of REINDEX. When this option is used, PostgreSQL must perform two scans of the table for each index that needs to be rebuilt and wait for termination of all existing transactions that could potentially use the index. This method requires more total work than a standard index rebuild and takes significantly longer to complete as it needs to wait for unfinished transactions that might modify the index. However, since it allows normal operations to continue while the index is being rebuilt, this method is useful for rebuilding indexes in a production environment. Of course, the extra CPU, memory and I/O load imposed by the index rebuild may slow down other operations.

The following steps occur in a concurrent reindex. Each step is run in a separate transaction. If there are multiple indexes to be rebuilt, then each step loops through all the indexes before moving to the next step.

1.A new transient index definition is added to the catalog pg_index. This definition will be used to replace the old index. A SHARE UPDATE EXCLUSIVE lock at session level is taken on the indexes being reindexed as well as their associated tables to prevent any schema modification while processing.

2.A first pass to build the index is done for each new index. Once the index is built, its flag pg_index.indisready is switched to “true” to make it ready for inserts, making it visible to other sessions once the transaction that performed the build is finished. This step is done in a separate transaction for each index.

3.Then a second pass is performed to add tuples that were added while the first pass was running. This step is also done in a separate transaction for each index.

4.All the constraints that refer to the index are changed to refer to the new index definition, and the names of the indexes are changed. At this point, pg_index.indisvalid is switched to “true” for the new index and to “false” for the old, and a cache invalidation is done causing all sessions that referenced the old index to be invalidated.

5.The old indexes have pg_index.indisready switched to “false” to prevent any new tuple insertions, after waiting for running queries that might reference the old index to complete.

6.The old indexes are dropped. The SHARE UPDATE EXCLUSIVE session locks for the indexes and the table are released.

If a problem arises while rebuilding the indexes, such as a uniqueness violation in a unique index, the REINDEX command will fail but leave behind an “invalid” new index in addition to the pre-existing one. This index will be ignored for querying purposes because it might be incomplete; however it will still consume update overhead. The psql \d command will report such an index as INVALID:

```
postgres=# \d tab

       Table "public.tab"

 Column |  Type   | Modifiers
--------+---------+-----------

 col    | integer |
Indexes:

    "idx" btree (col)

    "idx_ccnew" btree (col) INVALID
```

If the index marked INVALID is suffixed _ccnew, then it corresponds to the transient index created during the concurrent operation, and the recommended recovery method is to drop it using DROP INDEX, then attempt REINDEX CONCURRENTLY again. If the invalid index is instead suffixed _ccold, it corresponds to the original index which could not be dropped; the recommended recovery method is to just drop said index, since the rebuild proper has been successful. A nonzero number may be appended to the suffix of the invalid index names to keep them unique, like _ccnew1, _ccold2, etc.

Regular index builds permit other regular index builds on the same table to occur simultaneously, but only one concurrent index build can occur on a table at a time. In both cases, no other types of schema modification on the table are allowed meanwhile. Another difference is that a regular REINDEX TABLE or REINDEX INDEX command can be performed within a transaction block, but REINDEX CONCURRENTLY cannot.

Like any long-running transaction, REINDEX on a table can affect which tuples can be removed by concurrent VACUUM on any other table.

REINDEX SYSTEM does not support CONCURRENTLY since system catalogs cannot be reindexed concurrently.

Furthermore, indexes for exclusion constraints cannot be reindexed concurrently. If such an index is named directly in this command, an error is raised. If a table or database with exclusion constraint indexes is reindexed concurrently, those indexes will be skipped. (It is possible to reindex such indexes without the CONCURRENTLY option.)

## EXAMPLES
Rebuild a single index:

```
REINDEX INDEX my_index;
```

Rebuild all the indexes on the table my_table:

```
REINDEX TABLE my_table;
```

Rebuild all indexes in a particular database, without trusting the system indexes to be valid already:

```
$ export PGOPTIONS="-P"
$ psql broken_db
...
broken_db=> REINDEX DATABASE broken_db;
broken_db=> \q
```

Rebuild indexes for a table, without blocking read and write operations on involved relations while reindexing is in progress:

```
REINDEX TABLE CONCURRENTLY my_broken_table;
```

## COMPATIBILITY
There is no REINDEX command in the SQL standard.

## SEE ALSO
CREATE INDEX (CREATE_INDEX(7)), DROP INDEX (DROP_INDEX(7)), reindexdb(1)


title: RELEASE_SAVEPOINT(7)

summary: destroy a previously defined savepoint

text:
# RELEASE_SAVEPOINT(7)

## NAME
RELEASE_SAVEPOINT - destroy a previously defined savepoint

## SYNOPSIS

```
RELEASE [ SAVEPOINT ] savepoint_name
```

## DESCRIPTION
RELEASE SAVEPOINT destroys a savepoint previously defined in the current transaction.

Destroying a savepoint makes it unavailable as a rollback point, but it has no other user visible behavior. It does not undo the effects of commands executed after the savepoint was established. (To do that, see ROLLBACK TO SAVEPOINT (ROLLBACK_TO_SAVEPOINT(7)).) Destroying a savepoint when it is no longer needed allows the system to reclaim some resources earlier than transaction end.

RELEASE SAVEPOINT also destroys all savepoints that were established after the named savepoint was established.

## PARAMETERS
savepoint_name

The name of the savepoint to destroy.

## NOTES
Specifying a savepoint name that was not previously defined is an error.

It is not possible to release a savepoint when the transaction is in an aborted state.

If multiple savepoints have the same name, only the most recently defined unreleased one is released. Repeated commands will release progressively older savepoints.

## EXAMPLES
To establish and later destroy a savepoint:

```
BEGIN;

    INSERT INTO table1 VALUES (3);

    SAVEPOINT my_savepoint;

    INSERT INTO table1 VALUES (4);

    RELEASE SAVEPOINT my_savepoint;
COMMIT;
```

The above transaction will insert both 3 and 4.

## COMPATIBILITY
This command conforms to the SQL standard. The standard specifies that the key word SAVEPOINT is mandatory, but PostgreSQL allows it to be omitted.

## SEE ALSO
BEGIN(7), COMMIT(7), ROLLBACK(7), ROLLBACK TO SAVEPOINT (ROLLBACK_TO_SAVEPOINT(7)), SAVEPOINT(7)


RESET

title: RESET(7)

summary: restore the value of a run\-time parameter to the default value

text:
# RESET(7)

## NAME
RESET - restore the value of a run-time parameter to the default value

## SYNOPSIS

```
RESET configuration_parameter
RESET ALL
```

## DESCRIPTION
RESET restores run-time parameters to their default values. RESET is an alternative spelling for

```
SET configuration_parameter TO DEFAULT
```

Refer to SET(7) for details.

The default value is defined as the value that the parameter would have had, if no SET had ever been issued for it in the current session. The actual source of this value might be a compiled-in default, the configuration file, command-line options, or per-database or per-user default settings. This is subtly different from defining it as “the value that the parameter had at session start”, because if the value came from the configuration file, it will be reset to whatever is specified by the configuration file now. See Chapter 19 for details.

The transactional behavior of RESET is the same as SET: its effects will be undone by transaction rollback.

## PARAMETERS
configuration_parameter

Name of a settable run-time parameter. Available parameters are documented in Chapter 19 and on the SET(7) reference page.

ALL

Resets all settable run-time parameters to default values.

## EXAMPLES
Set the timezone configuration variable to its default value:

```
RESET timezone;
```

## COMPATIBILITY
RESET is a PostgreSQL extension.

## SEE ALSO
SET(7), SHOW(7)


REVOKE

title: REVOKE(7)

summary: remove access privileges

text:
# REVOKE(7)

## NAME
REVOKE - remove access privileges

## SYNOPSIS

```
REVOKE [ GRANT OPTION FOR ]

    { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER }

    [, ...] | ALL [ PRIVILEGES ] }

    ON { [ TABLE ] table_name [, ...]

         | ALL TABLES IN SCHEMA schema_name [, ...] }

    FROM role_specification [, ...]

    [ CASCADE | RESTRICT ]
REVOKE [ GRANT OPTION FOR ]

    { { SELECT | INSERT | UPDATE | REFERENCES } ( column_name [, ...] )

    [, ...] | ALL [ PRIVILEGES ] ( column_name [, ...] ) }

    ON [ TABLE ] table_name [, ...]

    FROM role_specification [, ...]

    [ CASCADE | RESTRICT ]
REVOKE [ GRANT OPTION FOR ]

    { { USAGE | SELECT | UPDATE }

    [, ...] | ALL [ PRIVILEGES ] }

    ON { SEQUENCE sequence_name [, ...]

         | ALL SEQUENCES IN SCHEMA schema_name [, ...] }

    FROM role_specification [, ...]

    [ CASCADE | RESTRICT ]
REVOKE [ GRANT OPTION FOR ]

    { { CREATE | CONNECT | TEMPORARY | TEMP } [, ...] | ALL [ PRIVILEGES ] }

    ON DATABASE database_name [, ...]

    FROM role_specification [, ...]

    [ CASCADE | RESTRICT ]
REVOKE [ GRANT OPTION FOR ]

    { USAGE | ALL [ PRIVILEGES ] }

    ON DOMAIN domain_name [, ...]

    FROM role_specification [, ...]

    [ CASCADE | RESTRICT ]
REVOKE [ GRANT OPTION FOR ]

    { USAGE | ALL [ PRIVILEGES ] }

    ON FOREIGN DATA WRAPPER fdw_name [, ...]

    FROM role_specification [, ...]

    [ CASCADE | RESTRICT ]
REVOKE [ GRANT OPTION FOR ]

    { USAGE | ALL [ PRIVILEGES ] }

    ON FOREIGN SERVER server_name [, ...]

    FROM role_specification [, ...]

    [ CASCADE | RESTRICT ]
REVOKE [ GRANT OPTION FOR ]

    { EXECUTE | ALL [ PRIVILEGES ] }

    ON { { FUNCTION | PROCEDURE | ROUTINE } function_name [ ( [ [ argmode ] [ arg_name ] arg_type [, ...] ] ) ] [, ...]

         | ALL { FUNCTIONS | PROCEDURES | ROUTINES } IN SCHEMA schema_name [, ...] }

    FROM role_specification [, ...]

    [ CASCADE | RESTRICT ]
REVOKE [ GRANT OPTION FOR ]

    { USAGE | ALL [ PRIVILEGES ] }

    ON LANGUAGE lang_name [, ...]

    FROM role_specification [, ...]

    [ CASCADE | RESTRICT ]
REVOKE [ GRANT OPTION FOR ]

    { { SELECT | UPDATE } [, ...] | ALL [ PRIVILEGES ] }

    ON LARGE OBJECT loid [, ...]

    FROM role_specification [, ...]

    [ CASCADE | RESTRICT ]
REVOKE [ GRANT OPTION FOR ]

    { { CREATE | USAGE } [, ...] | ALL [ PRIVILEGES ] }

    ON SCHEMA schema_name [, ...]

    FROM role_specification [, ...]

    [ CASCADE | RESTRICT ]
REVOKE [ GRANT OPTION FOR ]

    { CREATE | ALL [ PRIVILEGES ] }

    ON TABLESPACE tablespace_name [, ...]

    FROM role_specification [, ...]

    [ CASCADE | RESTRICT ]
REVOKE [ GRANT OPTION FOR ]

    { USAGE | ALL [ PRIVILEGES ] }

    ON TYPE type_name [, ...]

    FROM role_specification [, ...]

    [ CASCADE | RESTRICT ]
REVOKE [ ADMIN OPTION FOR ]

    role_name [, ...] FROM role_specification [, ...]

    [ GRANTED BY role_specification ]

    [ CASCADE | RESTRICT ]
where role_specification can be:

    [ GROUP ] role_name

  | PUBLIC

  | CURRENT_USER

  | SESSION_USER
```

## DESCRIPTION
The REVOKE command revokes previously granted privileges from one or more roles. The key word PUBLIC refers to the implicitly defined group of all roles.

See the description of the GRANT(7) command for the meaning of the privilege types.

Note that any particular role will have the sum of privileges granted directly to it, privileges granted to any role it is presently a member of, and privileges granted to PUBLIC. Thus, for example, revoking SELECT privilege from PUBLIC does not necessarily mean that all roles have lost SELECT privilege on the object: those who have it granted directly or via another role will still have it. Similarly, revoking SELECT from a user might not prevent that user from using SELECT if PUBLIC or another membership role still has SELECT rights.

If GRANT OPTION FOR is specified, only the grant option for the privilege is revoked, not the privilege itself. Otherwise, both the privilege and the grant option are revoked.

If a user holds a privilege with grant option and has granted it to other users then the privileges held by those other users are called dependent privileges. If the privilege or the grant option held by the first user is being revoked and dependent privileges exist, those dependent privileges are also revoked if CASCADE is specified; if it is not, the revoke action will fail. This recursive revocation only affects privileges that were granted through a chain of users that is traceable to the user that is the subject of this REVOKE command. Thus, the affected users might effectively keep the privilege if it was also granted through other users.

When revoking privileges on a table, the corresponding column privileges (if any) are automatically revoked on each column of the table, as well. On the other hand, if a role has been granted privileges on a table, then revoking the same privileges from individual columns will have no effect.

When revoking membership in a role, GRANT OPTION is instead called ADMIN OPTION, but the behavior is similar. This form of the command also allows a GRANTED BY option, but that option is currently ignored (except for checking the existence of the named role). Note also that this form of the command does not allow the noise word GROUP in role_specification.

## NOTES
A user can only revoke privileges that were granted directly by that user. If, for example, user A has granted a privilege with grant option to user B, and user B has in turn granted it to user C, then user A cannot revoke the privilege directly from C. Instead, user A could revoke the grant option from user B and use the CASCADE option so that the privilege is in turn revoked from user C. For another example, if both A and B have granted the same privilege to C, A can revoke their own grant but not B's grant, so C will still effectively have the privilege.

When a non-owner of an object attempts to REVOKE privileges on the object, the command will fail outright if the user has no privileges whatsoever on the object. As long as some privilege is available, the command will proceed, but it will revoke only those privileges for which the user has grant options. The REVOKE ALL PRIVILEGES forms will issue a warning message if no grant options are held, while the other forms will issue a warning if grant options for any of the privileges specifically named in the command are not held. (In principle these statements apply to the object owner as well, but since the owner is always treated as holding all grant options, the cases can never occur.)

If a superuser chooses to issue a GRANT or REVOKE command, the command is performed as though it were issued by the owner of the affected object. Since all privileges ultimately come from the object owner (possibly indirectly via chains of grant options), it is possible for a superuser to revoke all privileges, but this might require use of CASCADE as stated above.

REVOKE can also be done by a role that is not the owner of the affected object, but is a member of the role that owns the object, or is a member of a role that holds privileges WITH GRANT OPTION on the object. In this case the command is performed as though it were issued by the containing role that actually owns the object or holds the privileges WITH GRANT OPTION. For example, if table t1 is owned by role g1, of which role u1 is a member, then u1 can revoke privileges on t1 that are recorded as being granted by g1. This would include grants made by u1 as well as by other members of role g1.

If the role executing REVOKE holds privileges indirectly via more than one role membership path, it is unspecified which containing role will be used to perform the command. In such cases it is best practice to use SET ROLE to become the specific role you want to do the REVOKE as. Failure to do so might lead to revoking privileges other than the ones you intended, or not revoking anything at all.

See Section 5.7 for more information about specific privilege types, as well as how to inspect objects' privileges.

## EXAMPLES
Revoke insert privilege for the public on table films:

```
REVOKE INSERT ON films FROM PUBLIC;
```

Revoke all privileges from user manuel on view kinds:

```
REVOKE ALL PRIVILEGES ON kinds FROM manuel;
```

Note that this actually means “revoke all privileges that I granted”.

Revoke membership in role admins from user joe:

```
REVOKE admins FROM joe;
```

## COMPATIBILITY
The compatibility notes of the GRANT(7) command apply analogously to REVOKE. The keyword RESTRICT or CASCADE is required according to the standard, but PostgreSQL assumes RESTRICT by default.

## SEE ALSO
GRANT(7), ALTER DEFAULT PRIVILEGES (ALTER_DEFAULT_PRIVILEGES(7))


ROLLBACK

title: ROLLBACK(7)

summary: abort the current transaction

text:
# ROLLBACK(7)

## NAME
ROLLBACK - abort the current transaction

## SYNOPSIS

```
ROLLBACK [ WORK | TRANSACTION ] [ AND [ NO ] CHAIN ]
```

## DESCRIPTION
ROLLBACK rolls back the current transaction and causes all the updates made by the transaction to be discarded.

## PARAMETERS
WORK

TRANSACTION

Optional key words. They have no effect.

AND CHAIN

If AND CHAIN is specified, a new transaction is immediately started with the same transaction characteristics (see SET TRANSACTION (SET_TRANSACTION(7))) as the just finished one. Otherwise, no new transaction is started.

## NOTES
Use COMMIT(7) to successfully terminate a transaction.

Issuing ROLLBACK outside of a transaction block emits a warning and otherwise has no effect. ROLLBACK AND CHAIN outside of a transaction block is an error.

## EXAMPLES
To abort all changes:

```
ROLLBACK;
```

## COMPATIBILITY
The command ROLLBACK conforms to the SQL standard. The form ROLLBACK TRANSACTION is a PostgreSQL extension.

## SEE ALSO
BEGIN(7), COMMIT(7), ROLLBACK TO SAVEPOINT (ROLLBACK_TO_SAVEPOINT(7))


title: ROLLBACK_PREPARED(7)

summary: cancel a transaction that was earlier prepared for two\-phase commit

text:
# ROLLBACK_PREPARED(7)

## NAME
ROLLBACK_PREPARED - cancel a transaction that was earlier prepared for two-phase commit

## SYNOPSIS

```
ROLLBACK PREPARED transaction_id
```

## DESCRIPTION
ROLLBACK PREPARED rolls back a transaction that is in prepared state.

## PARAMETERS
transaction_id

The transaction identifier of the transaction that is to be rolled back.

## NOTES
To roll back 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 session that executed the transaction.

This command cannot be executed inside a transaction block. The prepared transaction is rolled back immediately.

All currently available prepared transactions are listed in the pg_prepared_xacts system view.

## EXAMPLES
Roll back the transaction identified by the transaction identifier foobar:

```
ROLLBACK PREPARED 'foobar';
```

## COMPATIBILITY
ROLLBACK PREPARED is a PostgreSQL extension. It is intended for use by external transaction management systems, some of which are covered by standards (such as X/Open XA), but the SQL side of those systems is not standardized.

## SEE ALSO
PREPARE TRANSACTION (PREPARE_TRANSACTION(7)), COMMIT PREPARED (COMMIT_PREPARED(7))


title: ROLLBACK_TO_SAVEPOINT(7)

summary: roll back to a savepoint

text:
# ROLLBACK_TO_SAVEPOINT(7)

## NAME
ROLLBACK_TO_SAVEPOINT - roll back to a savepoint

## SYNOPSIS

```
ROLLBACK [ WORK | TRANSACTION ] TO [ SAVEPOINT ] savepoint_name
```

## DESCRIPTION
Roll back all commands that were executed after the savepoint was established. The savepoint remains valid and can be rolled back to again later, if needed.

ROLLBACK TO SAVEPOINT implicitly destroys all savepoints that were established after the named savepoint.

## PARAMETERS
savepoint_name

The savepoint to roll back to.

## NOTES
Use RELEASE SAVEPOINT (RELEASE_SAVEPOINT(7)) to destroy a savepoint without discarding the effects of commands executed after it was established.

Specifying a savepoint name that has not been established is an error.

Cursors have somewhat non-transactional behavior with respect to savepoints. Any cursor that is opened inside a savepoint will be closed when the savepoint is rolled back. If a previously opened cursor is affected by a FETCH or MOVE command inside a savepoint that is later rolled back, the cursor remains at the position that FETCH left it pointing to (that is, the cursor motion caused by FETCH is not rolled back). Closing a cursor is not undone by rolling back, either. However, other side-effects caused by the cursor's query (such as side-effects of volatile functions called by the query) are rolled back if they occur during a savepoint that is later rolled back. A cursor whose execution causes a transaction to abort is put in a cannot-execute state, so while the transaction can be restored using ROLLBACK TO SAVEPOINT, the cursor can no longer be used.

## EXAMPLES
To undo the effects of the commands executed after my_savepoint was established:

```
ROLLBACK TO SAVEPOINT my_savepoint;
```

Cursor positions are not affected by savepoint rollback:

```
BEGIN;
DECLARE foo CURSOR FOR SELECT 1 UNION SELECT 2;
SAVEPOINT foo;
FETCH 1 FROM foo;

 ?column?
----------

        1
ROLLBACK TO SAVEPOINT foo;
FETCH 1 FROM foo;

 ?column?
----------

        2
COMMIT;
```

## COMPATIBILITY
The SQL standard specifies that the key word SAVEPOINT is mandatory, but PostgreSQL and Oracle allow it to be omitted. SQL allows only WORK, not TRANSACTION, as a noise word after ROLLBACK. Also, SQL has an optional clause AND [ NO ] CHAIN which is not currently supported by PostgreSQL. Otherwise, this command conforms to the SQL standard.

## SEE ALSO
BEGIN(7), COMMIT(7), RELEASE SAVEPOINT (RELEASE_SAVEPOINT(7)), ROLLBACK(7), SAVEPOINT(7)


SAVEPOINT

title: SAVEPOINT(7)

text:
# SAVEPOINT(7)

## NAME
SAVEPOINT - define a new savepoint within the current transaction

## SYNOPSIS

```
SAVEPOINT savepoint_name
```

## DESCRIPTION
SAVEPOINT establishes a new savepoint within the current transaction.

A savepoint is a special mark inside a transaction that allows all commands that are executed after it was established to be rolled back, restoring the transaction state to what it was at the time of the savepoint.

## PARAMETERS
savepoint_name

The name to give to the new savepoint. If savepoints with the same name already exist, they will be inaccessible until newer identically-named savepoints are released.

## NOTES
Use ROLLBACK TO SAVEPOINT (ROLLBACK_TO_SAVEPOINT(7)) to rollback to a savepoint. Use RELEASE SAVEPOINT (RELEASE_SAVEPOINT(7)) to destroy a savepoint, keeping the effects of commands executed after it was established.

Savepoints can only be established when inside a transaction block. There can be multiple savepoints defined within a transaction.

## EXAMPLES
To establish a savepoint and later undo the effects of all commands executed after it was established:

```
BEGIN;

    INSERT INTO table1 VALUES (1);

    SAVEPOINT my_savepoint;

    INSERT INTO table1 VALUES (2);

    ROLLBACK TO SAVEPOINT my_savepoint;

    INSERT INTO table1 VALUES (3);
COMMIT;
```

The above transaction will insert the values 1 and 3, but not 2.

To establish and later destroy a savepoint:

```
BEGIN;

    INSERT INTO table1 VALUES (3);

    SAVEPOINT my_savepoint;

    INSERT INTO table1 VALUES (4);

    RELEASE SAVEPOINT my_savepoint;
COMMIT;
```

The above transaction will insert both 3 and 4.

To use a single savepoint name:

```
BEGIN;

    INSERT INTO table1 VALUES (1);

    SAVEPOINT my_savepoint;

    INSERT INTO table1 VALUES (2);

    SAVEPOINT my_savepoint;

    INSERT INTO table1 VALUES (3);

    -- rollback to the second savepoint

    ROLLBACK TO SAVEPOINT my_savepoint;

    SELECT * FROM table1;               -- shows rows 1 and 2

    -- release the second savepoint

    RELEASE SAVEPOINT my_savepoint;

    -- rollback to the first savepoint

    ROLLBACK TO SAVEPOINT my_savepoint;

    SELECT * FROM table1;               -- shows only row 1
COMMIT;
```

The above transaction shows row 3 being rolled back first, then row 2.

## COMPATIBILITY
SQL requires a savepoint to be destroyed automatically when another savepoint with the same name is established. In PostgreSQL, the old savepoint is kept, though only the more recent one will be used when rolling back or releasing. (Releasing the newer savepoint with RELEASE SAVEPOINT will cause the older one to again become accessible to ROLLBACK TO SAVEPOINT and RELEASE SAVEPOINT.) Otherwise, SAVEPOINT is fully SQL conforming.

## SEE ALSO
BEGIN(7), COMMIT(7), RELEASE SAVEPOINT (RELEASE_SAVEPOINT(7)), ROLLBACK(7), ROLLBACK TO SAVEPOINT (ROLLBACK_TO_SAVEPOINT(7))


title: SECURITY_LABEL(7)

text:
# SECURITY_LABEL(7)

## NAME
SECURITY_LABEL - define or change a security label applied to an object

## SYNOPSIS

```
SECURITY LABEL [ FOR provider ] ON
{

  TABLE object_name |

  COLUMN table_name.column_name |

  AGGREGATE aggregate_name ( aggregate_signature ) |

  DATABASE object_name |

  DOMAIN object_name |

  EVENT TRIGGER object_name |

  FOREIGN TABLE object_name |

  FUNCTION function_name [ ( [ [ argmode ] [ argname ] argtype [, ...] ] ) ] |

  LARGE OBJECT large_object_oid |

  MATERIALIZED VIEW object_name |

  [ PROCEDURAL ] LANGUAGE object_name |

  PROCEDURE procedure_name [ ( [ [ argmode ] [ argname ] argtype [, ...] ] ) ] |

  PUBLICATION object_name |

  ROLE object_name |

  ROUTINE routine_name [ ( [ [ argmode ] [ argname ] argtype [, ...] ] ) ] |

  SCHEMA object_name |

  SEQUENCE object_name |

  SUBSCRIPTION object_name |

  TABLESPACE object_name |

  TYPE object_name |

  VIEW object_name
} IS { string_literal | NULL }
where aggregate_signature is:
* |
[ argmode ] [ argname ] argtype [ , ... ] |
[ [ argmode ] [ argname ] argtype [ , ... ] ] ORDER BY [ argmode ] [ argname ] argtype [ , ... ]
```

## DESCRIPTION
SECURITY LABEL applies a security label to a database object. An arbitrary number of security labels, one per label provider, can be associated with a given database object. Label providers are loadable modules which register themselves by using the function register_label_provider.

Note

register_label_provider is not an SQL function; it can only be called from C code loaded into the backend.

The label provider determines whether a given label is valid and whether it is permissible to assign that label to a given object. The meaning of a given label is likewise at the discretion of the label provider. PostgreSQL places no restrictions on whether or how a label provider must interpret security labels; it merely provides a mechanism for storing them. In practice, this facility is intended to allow integration with label-based mandatory access control (MAC) systems such as SELinux. Such systems make all access control decisions based on object labels, rather than traditional discretionary access control (DAC) concepts such as users and groups.

You must own the database object to use SECURITY LABEL.

## PARAMETERS
object_name

table_name.column_name

aggregate_name

function_name

procedure_name

routine_name

The name of the object to be labeled. Names of tables, aggregates, domains, foreign tables, functions, procedures, routines, sequences, types, and views can be schema-qualified.

provider

The name of the provider with which this label is to be associated. The named provider must be loaded and must consent to the proposed labeling operation. If exactly one provider is loaded, the provider name may be omitted for brevity.

argmode

The mode of a function, procedure, or aggregate argument: IN, OUT, INOUT, or VARIADIC. If omitted, the default is IN. Note that SECURITY LABEL does not actually pay any attention to OUT arguments, since only the input arguments are needed to determine the function's identity. So it is sufficient to list the IN, INOUT, and VARIADIC arguments.

argname

The name of a function, procedure, or aggregate argument. Note that SECURITY LABEL does not actually pay any attention to argument names, since only the argument data types are needed to determine the function's identity.

argtype

The data type of a function, procedure, or aggregate argument.

large_object_oid

The OID of the large object.

PROCEDURAL

This is a noise word.

string_literal

The new setting of the security label, written as a string literal.

NULL

Write NULL to drop the security label.

## EXAMPLES
The following example shows how the security label of a table could be set or changed:

```
SECURITY LABEL FOR selinux ON TABLE mytable IS 'system_u:object_r:sepgsql_table_t:s0';
```

To remove the label:

```
SECURITY LABEL FOR selinux ON TABLE mytable IS NULL;
```

## COMPATIBILITY
There is no SECURITY LABEL command in the SQL standard.

## SEE ALSO
sepgsql, src/test/modules/dummy_seclabel