Re: problem with RETURNING and update row movement

On Thu, Sep 24, 2020 at 7:30 PM Etsuro Fujita <etsuro(dot)fujita(at)gmail(dot)com> wrote:
> On Thu, Sep 24, 2020 at 2:47 PM Amit Langote <amitlangote09(at)gmail(dot)com> wrote:
> > On Thu, Sep 24, 2020 at 4:25 AM Etsuro Fujita <etsuro(dot)fujita(at)gmail(dot)com> wrote:
> > > Yeah, but for the other issue, I started thinking that we should just
> > > forbid referencing xmin/xmax/cmin/cmax in 12, 13, and HEAD...
> >
> > When the command is being performed on a partitioned table you mean?
>
> Yes. One concern about that is triggers: IIUC, triggers on a
> partition as-is can or can not reference xmin/xmax/cmin/cmax depending
> on whether a dedicated tuple slot for the partition is used or not.
> We should do something about this if we go in that direction?

Maybe I'm missing something, but assuming that we're talking about
prohibiting system attribute access in the RETURNING clause, how does
that affect what triggers can or cannot do? AFAICS, only AFTER
row-level triggers may sensibly access system attributes and
whether/how they can do so has not much to do with the slot that
ExecInsert() gets the new tuple in. It seems that the AFTER trigger
infrastructure remembers an affected tuple's ctid and fetches it just
before calling trigger function by asking the result relation's (e.g.,
a partition's) access method.

To illustrate, with HEAD:

create table foo (a int, b int) partition by range (a);
create table foo1 partition of foo for values from (1) to (2);

create or replace function report_system_info () returns trigger
language plpgsql as $$
begin
raise notice 'ctid: %', new.ctid;
raise notice 'xmin: %', new.xmin;
raise notice 'xmax: %', new.xmax;
raise notice 'cmin: %', new.cmin;
raise notice 'cmax: %', new.cmax;
raise notice 'tableoid: %', new.tableoid;
return NULL;
end; $$;

create trigger foo_after_trig after insert on foo for each row execute
function report_system_info();

begin;

insert into foo values (1);
NOTICE: ctid: (0,1)
NOTICE: xmin: 532
NOTICE: xmax: 0
NOTICE: cmin: 0
NOTICE: cmax: 0
NOTICE: tableoid: 16387

insert into foo values (1);
NOTICE: ctid: (0,2)
NOTICE: xmin: 532
NOTICE: xmax: 0
NOTICE: cmin: 1
NOTICE: cmax: 1
NOTICE: tableoid: 16387

insert into foo values (1);
NOTICE: ctid: (0,3)
NOTICE: xmin: 532
NOTICE: xmax: 0
NOTICE: cmin: 2
NOTICE: cmax: 2
NOTICE: tableoid: 16387

--
Amit Langote
EnterpriseDB: http://www.enterprisedb.com