Re: Delta Materialized View Refreshes?

Hi folks,

I've shared a new patch against 11.0, which seems to work as expected.
(Message ID 5100C2B3-641B-4A35-86D0-12ED2E618101(at)QQdd(dot)eu(dot))

While playing with it, it is actually quite easy to get it confused. And so
I wonder — is it actually what we want?

For example, if I refresh including a WHERE that filters /out/ some content
presently in the MV, but filters /in/ some new content relating to those
same rows, then we predictably get a fail. Using the following example MV
MV, 'testview', AS SELECT test.type, test.message, count(1) AS count FROM
test GROUP BY test.type, test.message, then a refresh materialized view
concurrently testview where type = 'main' and count>2 hits:

ERROR: duplicate key value violates unique constraint
"testview_type_message_idx"
DETAIL: Key (type, message)=(main, hello world) already exists.
CONTEXT: SQL statement "INSERT INTO public.testview SELECT (diff.newdata).*
FROM pg_temp_3.pg_temp_16390_2 diff WHERE tid IS NULL"

The message can probably be cleaned up. But the root cause is clearly in the
fact that REFRESH ... WHERE really needs to be used quite carefully.

I mused about restricting the WHERE clause Vars to allow reference only to
columns that are part of the MV's UNIQUE index. It seems it would prevent
the issue arising in my simple example, but is it always necessary? And
would it be overly restrictive? (For example: would it prevent people
issuing delta refreshes and including clauses that make the refresh
performant — because perhaps it helps the planner see a short cut to speedy
execution?)

On a different topic, in implementing it, I noticed that there is
rudimentary code-level support for incremental refreshes (see
Open/CloseMatViewIncrementalMaintenance() and
MatViewIncrementalMaintenanceIsEnabled()), but the facility is not
hook-able. There's another discussion (Flexible permissions for REFRESH
MATERIALIZED VIEW), and I wonder if a more interesting feature would be to
either allow the incremental refresh barriers to be hooked by extensions, or
even to offer a fine-grained permission that allows direct manipulation of
data in the MV's underlying table.

Far as I can see, allowing extensions to hook the incremental refresh APIs
would be trivial. Exposing the same via a fine-grained permission would
certainly be much harder but it might enable advanced delta-refresh
strategies to emerge that are written in high level languages such as
PL/pgSQL or Java (etc.) — that is certainly desirable.

denty.

--
Sent from: http://www.postgresql-archive.org/PostgreSQL-hackers-f1928748.html