Managing Subscriptions and Data Restoration with PostgreSQL Triggers

PostgreSQL Triggers

 Introduction:

In the world of database management, efficient handling of data is a critical aspect. One common scenario is managing subscriptions and ensuring data restoration for deleted records. In this technical blog, we will delve into the process of achieving this using PostgreSQL triggers. We will explore the concepts of triggers, their types, and how they can be applied to ensure seamless data management.

1. Understanding the Scenario:

    In the realm of database management, one common challenge revolves around maintaining and restoring data integrity when dealing with subscriptions and deleted records. Consider a scenario where an application manages APIs and their corresponding subscriptions. As APIs are created, users subscribe to them to receive updates and notifications. However, situations may arise where APIs are deleted due to updates, changes in business requirements, or other reasons. When APIs are deleted, their associated subscriptions should be removed as well to maintain data consistency. 

    However, what if an API that was previously deleted needs to be restored? In this case, the corresponding subscriptions should be restored as well. This presents a unique challenge: How can we ensure that subscriptions are automatically managed and restored when APIs are deleted and subsequently restored? This is where PostgreSQL triggers come into play.

Introducing Triggers:

PostgreSQL, a powerful open-source relational database management system, provides a mechanism known as triggers. Triggers are special stored procedures that are automatically executed in response to specific events occurring in a database. These events can include data modifications such as inserts, updates, or deletes. By utilizing triggers, developers can automate certain actions and enforce specific rules without manual intervention.

In this scenario, we aim to create triggers that address two main objectives:

1. Subscription Deletion Trigger: Automatically captures subscription details before they are deleted and stores them in a separate table. This ensures that data about the deleted subscriptions is retained for future reference or restoration.

2. API Restoration Trigger: When a previously deleted API is restored, this trigger automatically restores the corresponding subscriptions from the data stored in the previous step. This maintains data consistency and user subscriptions when APIs are reinstated.

In the following sections, we'll explore the fundamental concepts behind PostgreSQL triggers, dive into the implementation details of the triggers, discuss their types, and understand how they can be employed to ensure data integrity and streamline database management.

2. Basic Concepts:

Before delving into the implementation of triggers for managing subscriptions and data restoration, it's important to understand some fundamental concepts related to PostgreSQL triggers and their role in database management.

1. What is a Trigger?

   A trigger is a database object in PostgreSQL that is associated with a table and is automatically executed in response to certain events, such as data modifications. Triggers enable you to define custom actions that should occur before or after certain database operations, without requiring manual intervention.

2. Types of Triggers:

   PostgreSQL supports two main types of triggers based on when they are executed:

   - BEFORE Triggers: These triggers are executed before the triggering event occurs. They are often used to validate data or modify values before they are inserted, updated, or deleted in the table.

   - AFTER Triggers: These triggers are executed after the triggering event has occurred. They are commonly used for auditing, logging, or performing additional actions after data modifications.

3. Event Types:

   Triggers are associated with specific events that occur on a table. These events include:

   - INSERT: Triggered when a new row is inserted into the table.

   - UPDATE: Triggered when an existing row is updated.

   - DELETE: Triggered when a row is deleted from the table.

   Triggers can be set to fire either before or after these events.

4. Trigger Functions:

   A trigger function is a piece of code written in a specific procedural language (such as PL/pgSQL) that is executed when the trigger fires. This function encapsulates the logic you want to perform in response to the triggering event.

5. Data Integrity and Automation:

   Triggers play a vital role in maintaining data integrity and automating specific actions within the database. They allow developers to enforce business rules, validate data, track changes, and manage complex workflows without manual intervention.

6. Performance Considerations:

   While triggers provide powerful automation capabilities, it's important to be mindful of their impact on database performance. Poorly designed triggers or complex logic can lead to slower query execution.

In the upcoming sections of this blog, we will apply these concepts to our scenario of managing subscriptions and data restoration. We will implement triggers that automate the restoration of subscriptions when APIs are restored, ensuring data consistency and reducing manual effort. This practical application will showcase the power of triggers in enhancing database management workflows.

3. Creating the Data Structures:

In this section, we'll explore the process of creating the necessary data structures for managing subscriptions and implementing triggers. The data structures include the API, subscription, and deleted_subscriptions tables, along with their respective columns.

The subscription Table:

The subscription table is responsible for storing subscription details associated with APIs. The primary key consists of sub_application_id and sub_api_id.

CREATE TABLE public.subscription (
    sub_application_id text NOT NULL,
    sub_api_id text NOT NULL,
    tenant_id text,
    user_id text,
    sub_created_time text,
    sub_updated_time text,
    PRIMARY KEY (sub_application_id, sub_api_id),
    FOREIGN KEY (sub_api_id) REFERENCES public.api (api_id)
        ON UPDATE NO ACTION 

        ON DELETE CASCADE
);

The deleted_subscriptions Table:

The deleted_subscriptions table is designed to temporarily store subscription details when they are deleted. This table is important for data restoration purposes.

CREATE TABLE public.deleted_subscriptions (

    deleted_subscription_id serial PRIMARY KEY,

    api_id text NOT NULL,

    sub_application_id text NOT NULL,

    sub_api_id text NOT NULL,

    tenant_id text,

    user_id text,

    sub_created_time text,

    sub_updated_time text,

    deleted_timestamp timestamp DEFAULT CURRENT_TIMESTAMP

);

In the above table structures:

The api_id column is used to link subscriptions with APIs.

The FOREIGN KEY constraint in the subscription table ensures referential integrity, connecting subscriptions to their respective APIs.

The ON DELETE CASCADE option in the foreign key constraint ensures that when an API is deleted, its associated subscriptions are also deleted from the subscription table.

The deleted_subscriptions table is introduced to temporarily store subscription details before they are deleted from the subscription table. This allows us to later restore subscriptions when needed.

In the next sections of the blog, we'll delve into the trigger functions that automate the process of capturing subscription details before deletion and restoring them when APIs are restored. We'll examine both the before_subscription_delete trigger for capturing deleted subscriptions and the after_api_insert trigger for restoring subscriptions when APIs are restored. These triggers will demonstrate the power of PostgreSQL triggers in achieving efficient data management.

4. Subscription Deletion Trigger:

In this section, we will explore the implementation of the before_subscription_delete trigger function. This trigger function is designed to automatically capture subscription details before they are deleted from the subscription table and store them in the deleted_subscriptions table. This process ensures that subscription information is retained for potential restoration.

1. The before_api_delete Trigger Function:

The before_api_delete trigger function is executed before a api is deleted. Its primary purpose is to insert the subscription details into the deleted_subscriptions table before the deletion occurs.

CREATE OR REPLACE FUNCTION public.before_api_delete()

    RETURNS trigger

    LANGUAGE plpgsql

    AS $$

    BEGIN

        INSERT INTO public.deleted_subscriptions (api_id, sub_application_id, sub_api_id, tenant_id, user_id, sub_created_time, sub_updated_time)

        SELECT OLD.api_id, sub_application_id, sub_api_id, tenant_id, user_id, sub_created_time, sub_updated_time

        FROM public.subscription

        WHERE sub_api_id = OLD.api_id;

        RETURN OLD;

    END;

    $$;

2. Associating the Trigger with the api Table:

After creating the trigger function, we need to associate it with the subscription table so that it is automatically executed whenever a subscription is deleted.

CREATE TRIGGER before_api_delete_trigger

    BEFORE DELETE

    ON public.api

    FOR EACH ROW

    EXECUTE FUNCTION public.before_api_delete();

Understanding the Process:

When a deletion operation is performed on the api table, the before_api_delete_trigger trigger is automatically triggered. This trigger invokes the before_api_delete function, which captures the subscription details using the OLD record reference (before the deletion) and inserts them into the deleted_subscriptions table. This step ensures that subscription information is preserved before it is removed from the subscription table.

5. API Restoration Trigger:

In this section, we will dive into the implementation of the after_api_insert trigger function. This trigger function is designed to automatically restore subscription details from the deleted_subscriptions table when a previously deleted API is restored. Additionally, the trigger removes the corresponding records from the deleted_subscriptions table, ensuring that the data restoration process is seamless.

1. The after_api_insert Trigger Function:

The after_api_insert trigger function is executed after a new API is inserted into the api table. Its primary purpose is to restore subscription details from the deleted_subscriptions table and remove the corresponding records from the table.

CREATE OR REPLACE FUNCTION public.after_api_update()

    RETURNS trigger

    LANGUAGE plpgsql

    AS $$

    BEGIN

        -- Insert matching records from deleted_subscriptions into subscription

        INSERT INTO public.subscription (sub_application_id, sub_api_id, tenant_id, user_id, sub_created_time, sub_updated_time)

        SELECT sub_application_id, sub_api_id, tenant_id, user_id, sub_created_time, sub_updated_time

        FROM public.deleted_subscriptions

        WHERE api_id = NEW.api_id;

        DELETE FROM public.deleted_subscriptions

    WHERE api_id = NEW.api_id;

        RETURN NEW;

    END;

    $$;

2. Associating the Trigger with the API Table:

After creating the trigger function, we need to associate it with the api table so that it is automatically executed whenever a new API is inserted.

CREATE TRIGGER after_api_update_trigger

    AFTER UPDATE

    ON public.api

    FOR EACH ROW

    EXECUTE FUNCTION public.after_api_update();

Understanding the Process:

When a new API is inserted into the api table, the after_api_insert_trigger trigger is automatically fired. This trigger invokes the after_api_insert function, which performs two main actions:

Restoration: The trigger function selects subscription details from the deleted_subscriptions table using the NEW.api_id (newly inserted API) as the condition. It then inserts these subscription details back into the subscription table, effectively restoring them.

Cleanup: After restoring the subscriptions, the trigger function deletes the corresponding records from the deleted_subscriptions table to complete the restoration process.

By executing these actions within the trigger function, the system is capable of automatically restoring subscription data when a previously deleted API is reinstated. This process enhances data consistency and reduces manual effort.

In the final section of this blog, we will summarize the concepts we've covered and emphasize the benefits of using triggers for managing subscriptions, data restoration, and overall database management.

6. Exploring Trigger Types:

In the context of PostgreSQL, triggers are an essential tool for automating database actions and maintaining data integrity. Triggers are associated with specific events on tables and execute trigger functions in response to those events. PostgreSQL supports two main types of triggers: BEFORE triggers and AFTER triggers. Let's explore these trigger types in detail.

1. BEFORE Triggers:

A BEFORE trigger is executed before the triggering event occurs on the table. It allows you to intervene in the process and potentially modify the data that will be affected by the event. BEFORE triggers are often used for tasks such as data validation or modification before an INSERT, UPDATE, or DELETE operation takes place.

For example, you might use a BEFORE trigger to validate that certain conditions are met before inserting new data into a table. If the conditions are not satisfied, the trigger can raise an error or modify the data to meet the requirements.

2. AFTER Triggers:

An AFTER trigger is executed after the triggering event has occurred on the table. It's commonly used for actions that need to be performed after the data has been modified. This could include logging changes, updating related data, or sending notifications.

For instance, an AFTER trigger can be used to log the changes made to a specific table in an audit log. This ensures that a record of the modification is maintained for tracking and auditing purposes.

In this comprehensive technical blog, we delved into the realm of PostgreSQL database management, triggers, and data integrity. We embarked on a journey that covered the creation of tables, the utilization of triggers, and the management of subscriptions and data restoration. Through this journey, we not only learned the technical aspects but also gained a deeper understanding of how to leverage PostgreSQL's features to automate processes and maintain data accuracy.

Prepared by: Malinda Gamage