Configure Oracle compatibility for BEFORE row-level trigger on partition tables - PolarDB

PolarDB for PostgreSQL (Compatible with Oracle) supports BEFORE row-level triggers on partitioned tables, with a compatibility setting that controls whether they behave like PostgreSQL or Oracle when a DML statement moves rows across partitions.

How it works

A row-level trigger fires once for each row affected by an INSERT, UPDATE, or DELETE statement.

When an UPDATE moves a row from one partition to another — because the new column value falls outside the current partition's range — the database internally deletes the row from the source partition and inserts it into the target partition. This means two row-level triggers are involved: one on the source partition and one on the target partition.

PostgreSQL fires both triggers. Oracle fires only the trigger defined on the parent table, once.

By default, PolarDB for PostgreSQL (Compatible with Oracle) uses Oracle-compatible behavior.

Note

The Oracle-compatible behavior applies only to BEFORE row-level triggers. AFTER row-level triggers always use PostgreSQL behavior. Statement-level triggers work the same way as on standard (non-partitioned) tables.

Configure trigger compatibility

Use the polar_compatible_oracle_trigger parameter to switch between behaviors.

Value	Behavior
`on` (default)	Oracle behavior: the BEFORE row-level trigger fires once, regardless of how many partitions are affected.
`off`	PostgreSQL behavior: the BEFORE row-level trigger fires once per affected partition.

Oracle behavior (default)

With polar_compatible_oracle_trigger = on, a cross-partition update fires the BEFORE row-level trigger only once.

SET polar_compatible_oracle_trigger = on;

The following example uses a partitioned table parted with two partitions: parted_1 (for rows where a = 1) and parted_2 (for rows where a = 2). The trigger is defined on the parent table parted.

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

The UPDATE moves the row from parted_1 to parted_2, but the trigger fires only once (parted_1: BEFORE ROW UPDATE), consistent with Oracle behavior.

PostgreSQL behavior

With polar_compatible_oracle_trigger = off, a cross-partition update fires the BEFORE row-level trigger once per affected partition.

SET polar_compatible_oracle_trigger = off;

Using the same parted table:

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

The UPDATE fires three trigger events: a DELETE on parted_1 and an INSERT on parted_2, plus the UPDATE on parted_1 — reflecting the internal delete-then-insert that PostgreSQL performs when moving a row across partitions.