Re: Transition relations: correlating OLD TABLE and NEW TABLE

This is a possible workaround. But even if a table has a primary key, it
seems like there's some inefficiency in doing things this way: the old and
new row versions start out linked together (for instance this information
is available in a FOR EACH ROW trigger), but we're throwing away that
information by splitting them into two separate relations, forcing us to
have to join them back up again. Wouldn't it make sense to expose a
transition relation where the correspondence between old and new versions
is never discarded in the first place? Or is there something I'm missing?

Also, there are cases where it may not be desired to have a primary key, as
the index maintenance and constraint checking are not free and not always
necessary. And if one wants to implement a general change data capture
setup, it would nice to be able accomodate such tables without having to
alter them.

I'd be happy to try to work out an implementation of REFERENCING CHANGE
TABLE if there's support for the idea. Or is there some problem with this,
or some better way of achieving the goal?

- Brent

On Fri, Jul 6, 2018 at 9:36 PM, David G. Johnston <
david(dot)g(dot)johnston(at)gmail(dot)com> wrote:

> On Friday, July 6, 2018, Brent Kerby <blkerby(at)gmail(dot)com> wrote:
>
>> Of course if the table has a primary key, then this can be used, but I'm
>> wondering how to handle this in the general case where a primary key might
>> not exist.
>>
>
> Personally, I would consider the lack of a PK a rare and special
> case...I'd handle the proposed situation by adding a big serial column to
> the table.
>
> David J.
>
>