Re: Transition relations: correlating OLD TABLE and NEW TABLE
On Saturday, July 7, 2018, Brent Kerby wrote: > > 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. > Btree uniqueness enforcement is worth the price. > 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? > I do see the value in basically saying, "I have a unique index but I want to write a generic function that can handle being installed on any table and, without dynamic sql, can be presented with a full outer join relation of all inserts, updates, and deletes." Natural join won't work. We can limit the feature to just transition tables or create a new join type that would require left and right to be the same relation and the syste, would figure out and join on the PK columns (or planner error if there are none). Keep in mind that part of this discussion involves deciding where we are OK with being non-standard. David J.
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.g.johns...@gmail.com> wrote: > On Friday, July 6, 2018, Brent Kerby 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. > >
Re: Transition relations: correlating OLD TABLE and NEW TABLE
On Friday, July 6, 2018, Brent Kerby 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.
