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