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

On Wed, Feb 20, 2019 at 9:27 AM Antonin Houska <ah@cybertec.at> wrote:
> However I find it confusing that the trigger functions pass detectNewRows=true
> even if they only execute SELECT statement.

I don't quite see what those two things have to do with each other,
but I might be missing something.  I stuck in a debugging elog() to
see where ri_Check_Pk_Match() gets called and it fired for the
following query in the regression tests:

update pp set f1=f1+1;

That agrees with my intuition, which is that the logic here has
something to do with allowing an update that changes a referenced row
but also changes some other row in the same table so that the
reference remains valid -- it's just now a reference to some other
row.  Unfortunately, as you probably also noticed, making the change
you propose here doesn't make any tests fail in either the main
regression tests or the isolation tests.

I suspect that this is a defect in the tests rather than a sign that
the code doesn't need to be changed, though.  I'd try a statement like
the above in a multi-statement transaction with a higher-than-default
isolation level, probably REPEATABLE READ, and maybe some concurrent
activity in another session.  Sorry, I'm hand-waving here; I don't
know exactly what's going on.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company