I can imagine removal of the SPI from the current implementation (and constructing the plans "manually"), but note that the queries I use in my patch are no longer that trivial. So the SPI makes sense to me because it ensures regular query planning.
As an intermediate step, in the case where we have one row, it should be simple enough to extract that row manually, and do an SPI call with fixed values rather than the join to the ephemeral table, yes?
As for the tuplestore, I'm not sure the startup cost is a problem: if you're concerned about the 1-row case, the row should usually be stored in memory.
> and once that is done, we could see about step #2.
As I said during my review of your patch last year, I think the RI semantics has too much in common with that of triggers. I'd need more info to imagine such a change.
As a general outline, I think that DML would iterate over the 2 sets of potentially relevant RI definitions rather than iterating over the triggers.
The similarities between RI and general triggers are obvious, which explains why they went that route initially, but they're also a crutch, but since all RI operations boil down to either an iteration over a tuplestore to do lookups in an index (when checking for referenced rows), or a hash join of the transient data against the un-indexed table when checking for referencing rows, and people who know this stuff far better than me seem to think that SPI overhead is best avoided when possible. I'm looking forward to having more time to spend on this.
