CREATE UNDO

CREATE UNDO — define a new undo segment

CREATE UNDO [ IF NOT EXISTS ] name
    [ WITH ( storage_parameter = value [, ... ] ) ]
    [ TABLESPACE tablespace_name ]

CREATE UNDO constructs a new undo segment in the current database. The undo segment name must be distinct from the name of any existing undo segments in the current database. undo segments are primarily used to hold old version data that has been modified in tables and indexes.

To create a undo segment, the invoking user must have the CREATE privilege for the current database. (Of course, superusers bypass this check.)

• IF NOT EXISTS

  Do not throw an error if a relation with the same name already exists. A notice is issued in this case. Note that there is no guarantee that the existing undo is anything like the one that would have been created.

• name

  The name of the undo segment to be created. No schema name can be included here; the undo segment is always created in the schema pg_catalog.

• storage_parameter

  The name of an undo-specific storage parameter. See Undo Storage Parameters for details.

• tablespace_name

  The tablespace in which to create the undo segment. If not specified, default_tablespace is consulted.

The optional WITH clause specifies storage parameters for the undo segment. Accepted parameters include:

• minpages

  Minimum number of pages in the undo segment. The default is 128.

• maxpages

  Maximum number of pages in the undo segment. The default is unlimited. It is just a hint, in fact, Redrock Postgres provides a fully automated mechanism, known as automatic undo segment management mode, for managing undo segments and space.

Create an undo segment:

CREATE UNDO undo_13;

To create an undo segment and have it reside in the tablespace undospace:

CREATE UNDO undo_14 TABLESPACE undospace;

CREATE UNDO is a Redrock Postgres language extension. There are no provisions for undo segments in the SQL standard.