TSContainerPurge function

The TSContainerPurge function deletes time series data through a specified timestamp from one or more containers.

Syntax

TSContainerPurge(
           control_file  lvarchar,
           location      lvarchar default 'client',
           flags         integer default 0);
returns lvarchar

control_file

The name of the text file that contains information about which elements to delete from which containers. The file must have one or more lines in the following format:

container_name|instance_id|end_range|

container_name: The name of the container from which to delete elements.
instance_id: The unique identifier of a time series instance. An instance is a row in a table that includes a TimeSeries column.
end_range: The ending time of the deletion range. For a regular time series, the index of the last timestamp to delete. For irregular time series, the last timestamp to delete.

location (Optional)

The location of the control file. Can be either of the following values:

’client’: Default. The control file is on the client computer.
’server’: The control file is on the same computer as the database server.

flags (Optional)

Determines delete behavior. Can be either of the following values:

0: Elements that match the delete criteria are deleted only if all elements on a page match the criteria. The resulting empty pages are freed.
1: Elements on pages where all the elements match the delete criteria are deleted and the pages are freed. Remaining elements that match the delete criteria are set to NULL.

Usage

Use the TSContainerPurge function to remove old data from containers. The TSContainerPurge function deletes pages where all elements have a timestamp that is equal to or older than the specified ending time in the specified containers for the specified time series instances. The resulting empty pages are freed.

You can use the TSContainerPurge function to remove data from row that use a different TimeSeries subtype than the subtype specified in the container definition. However, the TimeSeries subtype that is named in the container definition must exist.

You can create a control file by unloading the results of a SELECT statement that defines the delete criteria into a file. Use the following TimeSeries functions in the SELECT statement to populate the control file with the container names, instance IDs, and, for regular time series, the indexes of the end range for reach instance:

GetContainerName function
InstanceId function
GetIndex function (regular time series only)

If you intend to delete a large amount of data at one time, running multiple TSContainerPurge functions to delete data from different containers might be faster than running a single TSContainerPurge function.

Returns

An LVARCHAR string that describes how many containers were affected, how many pages were freed, and how many elements were deleted. For example:

"containers(4) deleted_pages(2043) deleted_slots(260300)"

Example 1: Delete regular time series data from multiple containers

The following statement creates a control file named regular_purge.unl to delete elements from 10 regular time series instances in all containers that store those instances:

UNLOAD TO 'regular_purge.unl' 
       SELECT GetContainerName(readings),InstanceId(readings), 
              GetIndex(readings,'2011-10-01 23:45:00.00000'::datetime year to 
                       fraction(5))::varchar(25) 
       FROM sm 
       WHERE meter_id IN ('met0','met1','met11','met4','met5','met6',
                          'met61','met7','met8','met9');

The resulting control file has the following contents:

sm0|1|7871| 
sm0|8|7295| 
sm0|13|6911| 
sm1|2|7775| 
sm1|9|7199| 
sm1|14|6815| 
sm2|3|7679| 
sm2|10|7103| 
sm3|7|7391| 
sm3|12|7007|

The 10 time series instances that are specified in the WHERE clause are stored in the four different containers, which are listed in the first column. The second column lists the ID for each time series instance. The third column lists the element index number that corresponds to the timestamp 2011-10-01 23:45:00.00000.

The following statement deletes all elements at and before 2011-10-01 23:45:00.00000 for the 10 time series instances:

EXECUTE FUNCTION TSContainerPurge('regular_purge.unl',1);

Any deleted elements that remain are marked as NULL.

Example 2: Delete elements from a specific container

The following statement creates a control file named regular_purge2.unl to delete elements from all time series instances in the container named sm0:

UNLOAD TO regular_purge2.unl 
       SELECT GetContainerName(readings),InstanceId(readings),
              GetIndex(readings,'2011-10-01 23:00:00.00000'::datetime 
                       year to fraction(5))::varchar(25)
       FROM sm
       WHERE GetContainerName(readings) = 'sm0';

The resulting control file has entries for a single container:

sm0|1|7871| 
sm0|8|7295| 
sm0|13|6911|

Example 3: Deleting irregular time series data

The following statement creates a control file named irregular_purge.unl to delete elements from four irregular time series instances:

UNLOAD TO irregular_purge.unl 
       SELECT GetContainerName(readings),InstanceId(readings),
                   '2011-10-01 23:00:00.00000'::varchar(25)
       FROM sm
       WHERE meter_id IN ('met12','met2','met3','met62');

The resulting control file includes the ending timestamp instead of the element index number, for example:

sm4|4|2011-10-01 23:45:00.00000| 
sm4|6|2011-10-01 23:45:00.00000| 
sm5|5|2011-10-01 23:45:00.00000| 
sm5|11|2011-10-01 23:45:00.00000|