PURGE_PROJECTION

Permanently removes deleted data from physical storage so disk space can be reused. You can purge historical data up to and including the Ancient History Mark epoch.

PURGE_PROJECTION can use significant disk space while purging the data.

See PURGE for details about purge operations.

Syntax

PURGE_PROJECTION ( '[[database.]schema.]projection' )

Parameters

[database.]schema

Specifies a schema, by default public. If schema is any schema other than public, you must supply the schema name. For example:

myschema.thisDbObject

If you specify a database, it must be the current database.
projection The projection to purge.

Privileges

  • Table owner
  • USAGE privilege on schema

Examples

The following example purges all historical data in projection tbl_p that precedes the Ancient History Mark epoch.

=> CREATE TABLE tbl (x int, y int);
CREATE TABLE
=> INSERT INTO tbl VALUES(1,2);
 OUTPUT
--------
      1
(1 row)

=> INSERT INTO tbl VALUES(3,4);
 OUTPUT
--------
      1
(1 row)

dbadmin=> COMMIT;
COMMIT
=> CREATE PROJECTION tbl_p AS SELECT x FROM tbl UNSEGMENTED ALL NODES; 
WARNING 4468: Projection <public.tbl_p> is not available for query processing. 
Execute the select start_refresh() function to copy data into this projection. 
The projection must have a sufficient number of buddy projections and all nodes must be up before starting a refresh 
CREATE PROJECTION 
=> SELECT START_REFRESH(); 
             START_REFRESH 
---------------------------------------- 
 Starting refresh background process. 
=> DELETE FROM tbl WHERE x=1;
 OUTPUT
--------
      1
(1 row)

=> COMMIT;
COMMIT
=> SELECT MAKE_AHM_NOW();
         MAKE_AHM_NOW
-------------------------------
 AHM set (New AHM Epoch: 9066)
(1 row)

=> SELECT PURGE_PROJECTION ('tbl_p'); 
 PURGE_PROJECTION 
------------------- 
 Projection purged 
(1 row)

See Also