This topic describes the BEFORE row-level triggers in the PolarDB for PostgreSQL(Compatible with Oracle) partition tables, and their compatibility settings.

When you update data across partitions, PostgreSQL and Oracle use different trigger behaviors.

  • PostgreSQL: The triggers of multiple partition tables are fired.
  • Oracle: The relevant triggers are fired only once.

By default, PolarDB for PostgreSQL(Compatible with Oracle) is compatible with the trigger behavior of Oracle.

Note
  • AFTER row-level triggers are not compatible with the trigger behavior of Oracle.
  • Statement-level triggers are used in the same way as standard tables.

Parameter settings

You can control the compatibility of BEFORE row-level triggers by using polar_compatible_oracle_trigger.

  • polar_compatible_oracle_trigger = off

    This setting indicates that PolarDB for PostgreSQL(Compatible with Oracle) uses the trigger behavior of PostgreSQL. If data is updated across partitions, the triggers of multiple tables are fired.

    To set polar_compatible_oracle_trigger to off, execute the following statement:

    SET polar_compatible_oracle_trigger = off;

    The following code block provides an example on how to use this setting:

    create trigger t before insert or update or delete on parted
  for each row execute function trigger_function();
insert into parted values (1, 1, 'uno uno v1');
NOTICE:  parted_1: BEFORE ROW INSERT
update parted set a = 2;
NOTICE:  parted_1: BEFORE ROW UPDATE
NOTICE:  parted_1: BEFORE ROW DELETE
NOTICE:  parted_2: BEFORE ROW INSERT
delete from parted;
NOTICE:  parted_2: BEFORE ROW DELETE
  • polar_compatible_oracle_trigger = on

    This setting indicates that PolarDB for PostgreSQL(Compatible with Oracle) uses the trigger behavior of Oracle. If data is updated across partitions, the relevant triggers are fired only once.

    To set polar_compatible_oracle_trigger to on, execute the following statement:

    SET polar_compatible_oracle_trigger = on;

    The following code block provides an example on how to use this setting:

    insert into parted values (1, 1, 'uno uno v1');
NOTICE:  parted_1: BEFORE ROW INSERT
update parted set a = 2;
NOTICE:  parted_1: BEFORE ROW UPDATE
delete from parted;
NOTICE:  parted_2: BEFORE ROW DELETE