Re: [Patch] Add WHERE clause support to REFRESH MATERIALIZED VIEW

Hi Dharin,

Attached is an updated patch addressing your review comments and fixing the
concurrency model for the non-concurrent path.

Regarding the items you brought up:
1. Fixed the indnatts vs indnkeyatts issue for generating the ON CONFLICT
target.
2. Removed the incorrect regression test comment regarding subqueries.

Regarding the concurrency gap and safety model:
In my last email, I mentioned planning to use transaction-level advisory
locks to fix the consistency gap. After prototyping it, I had to abandon
that approach. Testing revealed that it falls over at scale, quickly
hitting `max_locks_per_transaction` limits and causing issues with bulk
operations. I worked on this for a while before deciding it wasn't workable.

Instead, I went a different direction. The non-concurrent partial refresh
now uses a different two-step strategy:

1. It first executes a `SELECT FROM mv WHERE ... FOR UPDATE` to lock
existing rows matching the predicate. This serializes concurrent partial
refreshes on overlapping rows while allowing non-overlapping refreshes to
proceed in parallel.
2. It then executes a single CTE that evaluates the underlying query,
upserts the results into the matview, and deletes rows that no longer match
the predicate via an anti-join.

In my testing, this approach had similar performance to the original
implementation, but hasn't exhibited the same correctness issues.

Thanks,
Adam Brusselback