REINDEX

title: REINDEX(7)

summary: rebuild indexes

text:
# REINDEX(7)

## NAME
REINDEX - rebuild indexes

## SYNOPSIS

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

    CONCURRENTLY [ boolean ]

    TABLESPACE new_tablespace

    VERBOSE [ boolean ]
```

## 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. This form of REINDEX cannot be executed inside a transaction block when used with a partitioned index.

TABLE

Recreate all indexes of the specified table. If the table has a secondary “TOAST” table, that is reindexed as well. This form of REINDEX cannot be executed inside a transaction block when used with a partitioned table.

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, except system catalogs. Indexes on system catalogs are not 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. Their parameter is optional, and it 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.

TABLESPACE

Specifies that indexes will be rebuilt on a new tablespace.

VERBOSE

Prints a progress report as each index is reindexed at INFO level.

boolean

Specifies whether the selected option should be turned on or off. You can write TRUE, ON, or 1 to enable the option, and FALSE, OFF, or 0 to disable it. The boolean value can also be omitted, in which case TRUE is assumed.

new_tablespace

The tablespace where indexes will be rebuilt.

## 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 particular, the query planner tries to take an ACCESS SHARE lock on every index of the table, regardless of the query, and so REINDEX blocks virtually any queries except for some prepared queries whose plan has been cached and which don't use this very 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.

While REINDEX is running, the search_path is temporarily changed to pg_catalog, pg_temp.

Reindexing a single index or table requires having the MAINTAIN privilege on the table. Note that while REINDEX on a partitioned index or table requires having the MAINTAIN privilege on the partitioned table, such commands skip the privilege checks when processing the individual partitions. Reindexing a schema or database requires being the owner of that schema or database or having privileges of the pg_maintain role. Note specifically that it's thus possible for non-superusers to rebuild indexes of tables owned by other users. However, as a special exception, REINDEX DATABASE, REINDEX SCHEMA, and REINDEX SYSTEM will skip indexes on shared catalogs unless the user has the MAINTAIN privilege on the catalog.

Reindexing partitioned indexes or partitioned tables is supported with REINDEX INDEX or REINDEX TABLE, respectively. Each partition of the specified partitioned relation is reindexed in a separate transaction. Those commands cannot be used inside a transaction block when working on a partitioned table or index.

When using the TABLESPACE clause with REINDEX on a partitioned index or table, only the tablespace references of the leaf partitions are updated. As partitioned indexes are not updated, it is recommended to separately use ALTER TABLE ONLY on them so as any new partitions attached inherit the new tablespace. On failure, it may not have moved all the indexes to the new tablespace. Re-running the command will rebuild all the leaf partitions and move previously-unprocessed indexes to the new tablespace.

If SCHEMA, DATABASE or SYSTEM is used with TABLESPACE, system relations are skipped and a single WARNING will be generated. Indexes on TOAST tables are rebuilt, but not moved to the new tablespace.

### 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.)

Each backend running REINDEX will report its progress in the pg_stat_progress_create_index view. See Section 27.4.4 for details.

## 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), Section 27.4.4


title: RELEASE_SAVEPOINT(7)

summary: release a previously defined savepoint

text:
# RELEASE_SAVEPOINT(7)

## NAME
RELEASE_SAVEPOINT - release a previously defined savepoint

## SYNOPSIS

```
RELEASE [ SAVEPOINT ] savepoint_name
```

## DESCRIPTION
RELEASE SAVEPOINT releases the named savepoint and all active savepoints that were created after the named savepoint, and frees their resources. All changes made since the creation of the savepoint that didn't already get rolled back are merged into the transaction or savepoint that was active when the named savepoint was created. Changes made after RELEASE SAVEPOINT will also be part of this active transaction or savepoint.

## PARAMETERS
savepoint_name

The name of the savepoint to release.

## 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; to do that, use ROLLBACK TO SAVEPOINT (ROLLBACK_TO_SAVEPOINT(7)).

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 release 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.

A more complex example with multiple nested subtransactions:

```
BEGIN;

    INSERT INTO table1 VALUES (1);

    SAVEPOINT sp1;

    INSERT INTO table1 VALUES (2);

    SAVEPOINT sp2;

    INSERT INTO table1 VALUES (3);

    RELEASE SAVEPOINT sp2;

    INSERT INTO table1 VALUES (4))); -- generates an error
```

In this example, the application requests the release of the savepoint sp2, which inserted 3. This changes the insert's transaction context to sp1. When the statement attempting to insert value 4 generates an error, the insertion of 2 and 4 are lost because they are in the same, now-rolled back savepoint, and value 3 is in the same transaction context. The application can now only choose one of these two commands, since all other commands will be ignored:

```
ROLLBACK;
ROLLBACK TO SAVEPOINT sp1;
```

Choosing ROLLBACK will abort everything, including value 1, whereas ROLLBACK TO SAVEPOINT sp1 will retain value 1 and allow the transaction to continue.

## 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 | MAINTAIN }

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

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

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

    FROM role_specification [, ...]

    [ GRANTED BY 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 [, ...]

    [ GRANTED BY 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 [, ...]

    [ GRANTED BY role_specification ]

    [ CASCADE | RESTRICT ]
REVOKE [ GRANT OPTION FOR ]

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

    ON DATABASE database_name [, ...]

    FROM role_specification [, ...]

    [ GRANTED BY role_specification ]

    [ CASCADE | RESTRICT ]
REVOKE [ GRANT OPTION FOR ]

    { USAGE | ALL [ PRIVILEGES ] }

    ON DOMAIN domain_name [, ...]

    FROM role_specification [, ...]

    [ GRANTED BY role_specification ]

    [ CASCADE | RESTRICT ]
REVOKE [ GRANT OPTION FOR ]

    { USAGE | ALL [ PRIVILEGES ] }

    ON FOREIGN DATA WRAPPER fdw_name [, ...]

    FROM role_specification [, ...]

    [ GRANTED BY role_specification ]

    [ CASCADE | RESTRICT ]
REVOKE [ GRANT OPTION FOR ]

    { USAGE | ALL [ PRIVILEGES ] }

    ON FOREIGN SERVER server_name [, ...]

    FROM role_specification [, ...]

    [ GRANTED BY 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 [, ...]

    [ GRANTED BY role_specification ]

    [ CASCADE | RESTRICT ]
REVOKE [ GRANT OPTION FOR ]

    { USAGE | ALL [ PRIVILEGES ] }

    ON LANGUAGE lang_name [, ...]

    FROM role_specification [, ...]

    [ GRANTED BY role_specification ]

    [ CASCADE | RESTRICT ]
REVOKE [ GRANT OPTION FOR ]

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

    ON LARGE OBJECT loid [, ...]

    FROM role_specification [, ...]

    [ GRANTED BY role_specification ]

    [ CASCADE | RESTRICT ]
REVOKE [ GRANT OPTION FOR ]

    { { SET | ALTER SYSTEM } [, ...] | ALL [ PRIVILEGES ] }

    ON PARAMETER configuration_parameter [, ...]

    FROM role_specification [, ...]

    [ GRANTED BY role_specification ]

    [ CASCADE | RESTRICT ]
REVOKE [ GRANT OPTION FOR ]

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

    ON SCHEMA schema_name [, ...]

    FROM role_specification [, ...]

    [ GRANTED BY role_specification ]

    [ CASCADE | RESTRICT ]
REVOKE [ GRANT OPTION FOR ]

    { CREATE | ALL [ PRIVILEGES ] }

    ON TABLESPACE tablespace_name [, ...]

    FROM role_specification [, ...]

    [ GRANTED BY role_specification ]

    [ CASCADE | RESTRICT ]
REVOKE [ GRANT OPTION FOR ]

    { USAGE | ALL [ PRIVILEGES ] }

    ON TYPE type_name [, ...]

    FROM role_specification [, ...]

    [ GRANTED BY role_specification ]

    [ CASCADE | RESTRICT ]
REVOKE [ { ADMIN | INHERIT | SET } OPTION FOR ]

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

    [ GRANTED BY role_specification ]

    [ CASCADE | RESTRICT ]
where role_specification can be:

    [ GROUP ] role_name

  | PUBLIC

  | CURRENT_ROLE

  | 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 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. Note that, in releases prior to PostgreSQL 16, dependent privileges were not tracked for grants of role membership, and thus CASCADE had no effect for role membership. This is no longer the case. Note also that this form of the command does not allow the noise word GROUP in role_specification.

Just as ADMIN OPTION can be removed from an existing role grant, it is also possible to revoke INHERIT OPTION or SET OPTION. This is equivalent to setting the value of the corresponding option to FALSE.

## 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 roles do not have owners, in the case of a GRANT of role membership, the command is performed as though it were issued by the bootstrap superuser.) 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.8 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 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 (not aborted) 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 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 and then start a new subtransaction at the same transaction level. 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 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 to rollback to a savepoint. Use RELEASE SAVEPOINT 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 objects that reside in schemas (tables, functions, etc.) 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