On 16 December 2015 at 13:26, Simon Riggs <si...@2ndquadrant.com> wrote:
> There is an interesting real world case where we might get some use of > these thoughts. > > If we have Orders and OrderItems (FK->Orders) > and we also know (and can Assert) Order.order_date <= OrderItems.ship_date > then a restriction on Orders.order_date > X => OrderItem.ship_date > X > when the two tables are joined on OrderId > and also a restriction on OrderItems.ship_date >= X => Orders.order_date < > X when the two tables are joined on OrderId > > Such an assertion could be checked during the FK check, so would not be > expensive to maintain. > > One for the future, at least, since we don't have any way of expressing or > enforcing that just yet. > > That does sound interesting, but it's important to remember that referenced tables are not updated in real time in that same way that indexes are. This was the reason the INNER JOIN removals had problems, we simply can't determine at planner time that the trigger queue for the foreign key will be empty during execution, so can't be certain that the foreign key will be "true". I'm just mentioning this as I wouldn't want someone to run off thinking this was a fantastic idea without being aware of the above, and waste time making the same mistakes as I did last year. -- David Rowley http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services
