Re: Transition Tables doesn´t have OID

On Sun, Dec 2, 2018 at 7:38 AM Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com> wrote:
>
> On 12/1/18 8:51 AM, PegoraroF10 wrote:
> > ok
> > then, help me to find alternatives to it.
> > As I sad, sometimes whe change our PK, so using OID would be a smart way to
> > have a join between old and new transition tables and we would like to use
> > transition tables because each statement is a lot faster than each row for
> > auditing purposes.
> >
> > So, whats the alternative ? One trigger for each row just for changing PK
> > values (will occur few times) and another trigger for each statement to do
> > the logging ?
>
> If you don't want to change the structure of your tables that would be
> seem to be the way to go. It will require some thought to make sure the
> 'for each row' and 'for each statement' don't interfere with each other.

I also contemplated this question when hacking on transition tables.
We know that the new and old tuplestores are ordered in the same way
(as an implementation detail), but there is no way to take advantage
of that knowledge in SQL, where relations are unordered sets. There
is a syntax WITH ORDINALITY that SQL uses to deal with the fact that
set-returning functions' results might really be be ordered, and you
might not want to lose that information. Suppose we allowed WITH
ORDINALITY for transition tables, so that the 'capture' order of rows
could be exposed, and we promised that old and new ORDINALTITY numbers
will line up, and then we made the ORDINALITY column a pathkey of the
scan. Now you could join old and new tables by the ORDINALITY column,
and get a merge join without any sorting. That's... pretty weird
though, and the syntax would be outside the SQL spec, and the
semantics might be questionable.

--
Thomas Munro
http://www.enterprisedb.com