Re: Foreign keys and partitioned tables

Robert Haas wrote:

> I suspect that this leads to bugs under concurrency, something to do
> with crosscheck_snapshot, but I couldn't say exactly what the problem
> is off the top of my head. My hope is that partitioning might be
> immune on the strength of knowing that any given tuple could only be
> present in one particular partition, but that might be wishful
> thinking.

Speaking of crosscheck_snapshot, I just noticed that the case of FKs
with repeatable read or serializable snapshot seems not to be covered by
tests at all, judging from the coverage report:

2635 : /*
2636 : * In READ COMMITTED mode, we just need to use an up-to-date regular
2637 : * snapshot, and we will see all rows that could be interesting. But in
2638 : * transaction-snapshot mode, we can't change the transaction snapshot. If
2639 : * the caller passes detectNewRows == false then it's okay to do the query
2640 : * with the transaction snapshot; otherwise we use a current snapshot, and
2641 : * tell the executor to error out if it finds any rows under the current
2642 : * snapshot that wouldn't be visible per the transaction snapshot. Note
2643 : * that SPI_execute_snapshot will register the snapshots, so we don't need
2644 : * to bother here.
2645 : */
2646 3026 : if (IsolationUsesXactSnapshot() && detectNewRows)
2647 : {
2648 0 : CommandCounterIncrement(); /* be sure all my own work is visible */
2649 0 : test_snapshot = GetLatestSnapshot();
2650 0 : crosscheck_snapshot = GetTransactionSnapshot();
2651 : }
2652 : else
2653 : {
2654 : /* the default SPI behavior is okay */
2655 3026 : test_snapshot = InvalidSnapshot;
2656 3026 : crosscheck_snapshot = InvalidSnapshot;
2657 : }
https://coverage.postgresql.org/src/backend/utils/adt/ri_triggers.c.gcov.html

--
Álvaro Herrera https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services