<listitem>

<para>

<literal>BEFORE ROW</literal> triggerscannot change which partition

is the final destination for a new row.

<literal>BEFORE ROW</literal> triggerson <literal>INSERT</literal>

cannot change which partitionis the final destination for a new row.

</para>

</listitem>

{

ExecForceStoreHeapTuple(newtuple,newslot, false);

if (trigger->tgisclone&&

!ExecPartitionCheck(relinfo,newslot,estate, false))

ereport(ERROR,

(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),

errmsg("moving row to another partition during a BEFORE trigger is not supported"),

errdetail("Before executing trigger \"%s\", the row was to be in partition \"%s.%s\".",

trigger->tgname,

get_namespace_name(RelationGetNamespace(relinfo->ri_RelationDesc)),

RelationGetRelationName(relinfo->ri_RelationDesc))));

ERROR: moving row to another partition during a BEFORE FOR EACH ROW trigger is not supported

DETAIL: Before executing trigger "t", the row was to be in partition "public.parted_1_1".

update parted set c = c || 'v3'; -- fail

ERROR:moving row to anotherpartitionduring a BEFORE trigger is not supported

DETAIL:Before executing trigger "t",the rowwas to be in partition "public.parted_1_1".

ERROR:nopartitionof relation "parted" found for row

DETAIL:Partition key ofthefailingrowcontains (a) = (2).

create or replace function parted_trigfunc() returns trigger language plpgsql as $$

begin

new.b = new.b + 1;

ERROR: moving row to another partition during a BEFORE FOR EACH ROW trigger is not supported

DETAIL: Before executing trigger "t", the row was to be in partition "public.parted_1_1".

update parted set c = c || 'v5'; -- fail

ERROR:moving row to anotherpartitionduring a BEFORE trigger is not supported

DETAIL:Before executing trigger "t",the rowwas to be in partition "public.parted_1_1".

ERROR:nopartitionof relation "parted_1" found for row

DETAIL:Partition key ofthefailingrowcontains (b) = (2).

create or replace function parted_trigfunc() returns trigger language plpgsql as $$

begin

new.c = new.c || 'and so';

new.c = new.c || 'did '|| TG_OP;

return new;

end;

$$;

insert into parted values (1, 1, 'uno uno'); -- works

update parted set c = c || ' v6'; -- works

select tableoid::regclass, * from parted;

tableoid | a | b |c

------------+---+---+--------------------------

parted_1_1 | 1 | 1 | uno uno v1 v6and so

parted_1_1 | 1 | 1 | uno unoand so v6and so

tableoid | a | b | c

------------+---+---+----------------------------------

parted_1_1 | 1 | 1 | uno uno v1 v6did UPDATE

parted_1_1 | 1 | 1 | uno unodid INSERT v6did UPDATE

(2 rows)

-- update itself moves tuple to new partition; trigger still works

truncate table parted;

create table parted_2 partition of parted for values in (2);

insert into parted values (1, 1, 'uno uno v5');

update parted set a = 2;

select tableoid::regclass, * from parted;

tableoid | a | b | c

----------+---+---+---------------------------------------------

parted_2 | 2 | 1 | uno uno v5 did INSERT did UPDATE did INSERT

(1 row)

-- both trigger and update change the partition

create or replace function parted_trigfunc2() returns trigger language plpgsql as $$

begin

new.a = new.a + 1;

return new;

end;

$$;

create trigger t2 before update on parted

for each row execute function parted_trigfunc2();

truncate table parted;

insert into parted values (1, 1, 'uno uno v6');

create table parted_3 partition of parted for values in (3);

update parted set a = a + 1;

select tableoid::regclass, * from parted;

tableoid | a | b | c

----------+---+---+---------------------------------------------

parted_3 | 3 | 1 | uno uno v6 did INSERT did UPDATE did INSERT

(1 row)

-- there's no partition for a=0, but this update works anyway because

-- the trigger causes the tuple to be routed to another partition

update parted set a = 0;

select tableoid::regclass, * from parted;

tableoid | a | b | c

------------+---+---+-------------------------------------------------------------------

parted_1_1 | 1 | 1 | uno uno v6 did INSERT did UPDATE did INSERT did UPDATE did INSERT

(1 row)

drop table parted;

create table parted (a int, b int, c text) partition by list ((a + b));

create or replace function parted_trigfunc() returns trigger language plpgsql as $$

update partedset c= c||'v5';-- fail

create or replacefunctionparted_trigfunc() returns trigger language plpgsqlas $$

new.c=new.c||'and so';

new.c=new.c||'did'|| TG_OP;

return new;

end;

$$;

insert into partedvalues (1,1,'uno uno');-- works

update partedset c= c||' v6';-- works

select tableoid::regclass,*from parted;

truncate table parted;

createtableparted_2 partition of parted forvaluesin (2);

insert into partedvalues (1,1,'uno uno v5');

update partedset a=2;

select tableoid::regclass,*from parted;

create or replacefunctionparted_trigfunc2() returns trigger language plpgsqlas $$

new.a=new.a+1;

return new;

end;

$$;

createtriggert2 beforeupdateon parted

for each row execute function parted_trigfunc2();

truncate table parted;

insert into partedvalues (1,1,'uno uno v6');

createtableparted_3 partition of parted forvaluesin (3);

update partedset a= a+1;

select tableoid::regclass,*from parted;

update partedset a=0;

select tableoid::regclass,*from parted;

droptable parted;

createtableparted (aint, bint, ctext) partition by list ((a+ b));

create or replacefunctionparted_trigfunc() returns trigger language plpgsqlas $$