Re: Unnecessary checks for new rows by some RI trigger functions?

Antonin Houska <ah(at)cybertec(dot)at> writes:
> Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> It's quite easy to demonstrate that the second part of Antonin's
>> proposed patch (ie, don't check for new rows in the FK table during
>> ri_restrict) is wrong:

> When I was running this example, the other session got blocked until the first
> (serializable) transaction committed. To achieve this ERROR (or FK violated
> due to my patch proposal) I had to run the other session's INSERT before the
> first session's DELETE.

Oh, right, I copied and pasted out of my terminal windows in the wrong
order :-(. The INSERT indeed must happen before the DELETE (and the
TABLE or SELECT before that, so as to establish the serializable
transaction's snapshot before the insertion). Sorry about that.

Not sure what I think about your new proposed patch. What problem
do you think it solves? Also, don't think I believe this:

+ * crosscheck_snapshot is actually used only for UPDATE / DELETE
+ * queries.

The commands we're issuing here are SELECT FOR UPDATE^H^H^HSHARE,
and those should chase up to the newest row version and do a
crosscheck just as UPDATE / DELETE would do. If they don't, there's
a hazard of mis-enforcing the FK constraint in the face of
concurrent updates.

regards, tom lane