Delete operation in PostgreSQL for bitemporal tables

Abstract

The development of applications and computer systems demands the management of historical, current, and future data, which contrasts with the classic databases where only current data is managed. The SQL: 2011 standard proposes application time, system time, and bitemporal tables for the management of temporal data, however, to date very few Database Management Systems (DBMS) offer support for temporary data. In this paper, we propose a modification of the native PostgreSQL DELETE operation for bitemporal tables that allows it to be aligned with the corresponding operation of the SQL: 2011 standard. The hooks offered by PostgreSQL allow interrupting and modifying the execution phases of an SQL query, on this base, it is established an architecture that allows defining the corresponding algorithm for the new delete operation. The architecture for bitemporal tables is supported by modifications of a previous PostgreSQL extension for the management of system time tables.