Subject: pg_restore and materialized view deps

From: Wells Oliver <[email-masked]>
Date: Fri, 6 Sep 2024 17:41:52 -0700

Not the end of the world but I notice when running pg_restore that I
encounter a few errors where materialized views cannot be refreshed because
they query other materialized views not yet refreshed.

Is this a fixable thing or just something one must live with, and refresh
them after the restore?

-- 
Wells Oliver
[email-masked] <[email-masked]>

---

From: Tom Lane <[email-masked]>
Date: Fri, 06 Sep 2024 21:33:01 -0400

> they query other materialized views not yet refreshed.

Oh?  There are dependencies that are supposed to prevent that.
Can you provide a self-contained example of a schema where
that doesn't work?

			regards, tom lane

---

From: Wells Oliver <[email-masked]>
Date: Fri, 6 Sep 2024 18:41:22 -0700

Ah, you know, a little deeper: it's an issue with a mat view unable to
refresh because it uses a function, and within that function, there is a
call to a mat view not yet materialized.

---

From: Tom Lane <[email-masked]>
Date: Fri, 06 Sep 2024 22:12:34 -0400

> call to a mat view not yet materialized.

Oh yes, pg_dump can't really see dependencies inside function bodies.

... Unless ... can you write the function as a new-style SQL
function (BEGIN ATOMIC instead of a string literal)?
Then I think it might work.

			regards, tom lane