On 12/16/2015 01:26 AM, Simon Riggs 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.

There's a concept of "correlation maps", described in a paper [1] presented on VLDB 2009. It essentially talks about deriving conditions between attributes of the same table, but I guess it might be modified to track the correlations through foreign keys.

Interestingly enough, the data for the paper paper were collected on PostgreSQL, but the correlation maps were implemented in an application layer on top of the database.

[1] http://www.vldb.org/pvldb/2/vldb09-199.pdf


Tomas Vondra                  http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:

Reply via email to