ALTER UNDO

ALTER UNDO — change the definition of an undo segment

ALTER UNDO [ IF EXISTS ] name RENAME TO new_name
ALTER UNDO [ IF EXISTS ] name SET TABLESPACE tablespace_name
ALTER UNDO name OWNER TO { new_owner | CURRENT_USER | SESSION_USER }
ALTER UNDO [ IF EXISTS ] name SET ( storage_parameter = value [, ... ] )
ALTER UNDO [ IF EXISTS ] name RESET ( storage_parameter [, ... ] )

ALTER UNDO changes the definition of an existing undo segment. There are several subforms described below. Note that the lock level required may differ for each subform. An ACCESS EXCLUSIVE lock is held unless explicitly noted. When multiple subcommands are listed, the lock held will be the strictest one required from any subcommand.

• RENAME

  The RENAME form changes the name of the undo segment. There is no effect on the stored data.

  Renaming an index acquires a SHARE UPDATE EXCLUSIVE lock.

• SET TABLESPACE

  This form changes the undo segment’s tablespace to the specified tablespace and moves the data file(s) associated with the undo segment to the new tablespace. To change the tablespace of an undo segment, you must own the undo segment and have CREATE privilege on the new tablespace.

• OWNER TO

  This form changes the undo segment’s owner to the specified owner. To alter the owner, you must also be a direct or indirect member of the new owning role. (Note that superusers have all these privileges automatically.)

• SET ( storage_parameter=value [, ... ] )

  This form changes one or more index-method-specific storage parameters for the undo segment. See CREATE UNDO for details on the available parameters. Note that the undo contents will not be modified immediately by this command; depending on the parameter you might need to clean the undo segment with VACUUM to get the desired effects.

• RESET ( storage_parameter [, ... ] )

  This form resets one or more undo-specific storage parameters to their defaults. As with SET, a VACUUM might be needed to clean the undo segment entirely.

• IF EXISTS

  Do not throw an error if the undo segment does not exist. A notice is issued in this case.

• name

  The name (possibly schema-qualified) of an existing undo segment to alter.

• new_name

  The new name for the undo segment.

• tablespace_name

  The tablespace to which the undo segment will be moved.

• new_owner

  The new owner of the undo segment.

• storage_parameter

  The name of an undo-specific storage parameter.

• value

  The new value for an undo-specific storage parameter. This might be a number or a word depending on the parameter.

To rename an existing undo segment:

ALTER UNDO undo_abc RENAME TO undo_test;

To move an undo segment to a different tablespace:

ALTER UNDO undo_15 SET TABLESPACE undospace;

To change an undo segment’s minimum pages:

ALTER UNDO undo_15 SET (minpages = 131072);

Change the owner of undo segment undo_15:

ALTER UNDO undo_15 OWNER TO mary;

ALTER UNDO is a Redrock Postgres extension.