Remote exception handling in a Postgres trigger?

Question

We are currently moving from Oracle to Postgres. In Oracle we have a trigger creating statement like this:

create or replace TRIGGER action2md                                         
    AFTER INSERT OR UPDATE OR DELETE
    ON action                                                               
    DECLARE 
    Mutating_table EXCEPTION;
    PRAGMA EXCEPTION_INIT (Mutating_table, -4091);
BEGIN
    UPDATE global_tables_md                                                 
    SET moddate = SYSTIMESTAMP
    WHERE table_name='action';                                              
EXCEPTION
    WHEN Mutating_table THEN
        NULL;
END;

If needed you can read more about remote exception handling in Oracle triggers here.

We are using ora2pg to help migrate the database and it changed the trigger creation statement to the following:

DROP TRIGGER IF EXISTS action2md ON action CASCADE;
CREATE OR REPLACE FUNCTION trigger_fct_action2md() RETURNS trigger AS $BODY$
DECLARE
    Mutating_table EXCEPTION;
    PRAGMA EXCEPTION_INIT (Mutating_table, -4091);
BEGIN
    UPDATE global_tables_md
    SET moddate = CURRENT_TIMESTAMP
    WHERE table_name='action';                                              
IF TG_OP = 'DELETE' THEN
    RETURN OLD;
ELSE
    RETURN NEW;
END IF;

EXCEPTION
    WHEN Mutating_table THEN
        NULL;
END
$BODY$
 LANGUAGE 'plpgsql';

CREATE TRIGGER action2md
    AFTER INSERT OR UPDATE OR DELETE ON action FOR EACH STATEMENT
    EXECUTE PROCEDURE trigger_fct_action2md();

This results in the error [42704] ERROR: type "exception" does not exist.

It seems to me that the problem has to do with the DECLARE block. Do you have any insight on how we can create an equivalent trigger in Postgres?

score 3 · Accepted Answer · edited May 23 '17 at 12:08

3

It seems that ora2pg cannot handle Oracle trigger pragmas and exceptions, so it is literally adding the same code in the PG script, which has no meaning in PostgreSQL.

A mutating table is an exception triggered by Oracle when a trigger causes itself to update and thus fire a new trigger (or something like that). This trigger seems to be catering for that situation, catching the exception and doing nothing. You have to remove all of the DECLARE and EXCEPTION sections in the PostgreSQL function, because they're meaningless.

In PostgreSQL, triggers can cause subsequent (nested) triggers, and it is the responsibility of the developer to deal with them. This answer illustrates an example of how to deal with nested triggers in PostgreSQL: https://stackoverflow.com/a/14262289/3886053.

edited May 23 '17 at 12:08

Community

1
1

answered May 10 '16 at 01:45

Ezequiel Tolnay

4,302
1
19
28

Could you tell me what the block IF TG_OP = 'DELETE' THEN RETURN OLD; ELSE RETURN NEW; END IF; signifies in the ora2pg trigger function? – irrational May 10 '16 at 18:05
@irrational The OLD and NEW records are defined as needed on DELETE, UPDATE and INSERT triggers. There is no NEW record defined on DELETE triggers, so the function must return OLD for them, and NEW otherwise. On BEFORE triggers, DELETE deletes the row if OLD is not null, otherwise it updates or inserts the row with the values in NEW (which you can modify inside the trigger). – Ezequiel Tolnay May 10 '16 at 23:24

Remote exception handling in a Postgres trigger?

1 Answers1