PolarDB:Logon and logoff triggers

Overview

Logon and logoff triggers are event triggers and can trigger specified functions or stored procedures when you log on to and log off from the database. Logon means that the Postgres backend process starts when you connect to the database. Logoff means that the Postgres backend process is about to exit when you initiate the logoff command.

Syntax

CREATE EVENT TRIGGER trigger_name event_login_or_logoff
            EXECUTE FUNCTION_or_PROCEDURE func_name '(' ')'

event_login_or_logoff:
         AFTER LOGON ON DATABASE
        | BEFORE LOGOFF ON DATABASE

Precautions

• If you use third-party tools such as connection pools and connection proxies, the logon and logoff triggers may not be triggered when the client is disconnected or connected because they are triggered only when the backend process starts or exits.
• Like other event triggers, if an event (such as logon) has multiple triggers and one of the triggers fails, the actions of all triggers are terminated and the transaction is rolled back.
• When the logoff trigger is triggered and an error occurs, the error details are generated in the log. The error details may only exist in the log but not received by the client because the client has exited. When the logon trigger is triggered and an error occurs, the error details are generated in the log because the client is not ready. PolarDB tries to send the following warning message to the client: "event trigger occur error after user login. For more information, see log".
• PolarDB is compatible with the following Oracle PL variables for the logon and logoff triggers:
  • polar_login_user: the username that is used to log on to the database. The value is of the TEXT data type.
  • polar_database_name: the name of the database that you log on to. The value is of the TEXT data type.
  • polar_instance_num: the number of clusters that you log on to. The value is 1 and of the INT data type.
  • polar_client_ip: the client IP address that is used to log on to the database. The value is of the TEXT data type.

Examples

• Prepare data for the test.

  CREATE TABLE users_log (
    id       serial,
    user_name VARCHAR2(64),
    database_name VARCHAR2(64),
    event     VARCHAR2(64),
    client_ip VARCHAR2(64),
    tag       VARCHAR2(64),
    instance_num int
  );
  
  CREATE function sample_event_trigger return event_trigger is begin
  INSERT into polar_loginout.users_log (user_name,database_name,event,client_ip, tag, instance_num)
  values (polar_login_user,polar_database_name,tg_event,polar_client_ip,tg_tag,polar_instance_num);
  end;
                      

• Create a logon trigger.

  CREATE EVENT TRIGGER hr.logon_trigger AFTER LOGON
    ON DATABASE execute function public.sample_event_trigger();

• Create a logoff trigger.

  CREATE EVENT TRIGGER hr.logoff_trigger BEFORE LOGOFF
    ON DATABASE execute function public.sample_event_trigger();

• Delete the logon trigger.

  DROP event trigger logon_trigger;

• Delete the logoff trigger.

  DROP event trigger logoff_trigger;