Re: Foreign keys and partitioned tables

Robert Haas wrote:
> On Sun, Dec 31, 2017 at 2:43 PM, Alvaro Herrera
> <alvherre(at)2ndquadrant(dot)com> wrote:
> > This patch removes all the ONLY markers from queries in ri_triggers.c.
> > That makes the queries work for the new use case, but I haven't figured
> > if it breaks things for other use cases. I suppose not, since regular
> > inheritance isn't supposed to allow foreign keys in the first place, but
> > I haven't dug any further.
>
> 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.

I think you're thinking of this problem: if I insert a row in
partitioned table F, and simultaneously remove the referenced row from
table P, it is possible that we fail to reject the insertion in some
corner-case scenario. I suppose it's not completely far-fetched, if P
is partitioned. I don't see any way in which it could be a problem if
only F is partitioned.

For the record: in the patch I'm about to push, I did not implement
foreign key references to partitioned tables. So it should be safe.
More thought may be needed to implement the other direction. Offhand, I
don't see a problem, but I may well be wrong.

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