>Well, you want to use a materialized view for something that it cannot be used >for, for security reasons. We tried to explain that in this thread. Yes, you can see it this way: One way would be: Using Temp Tables. This way is blocked due to security reasons. Discussion finished.
Another point of view is: 0) The security problem is not inherent for what I was trying to archieve; but due to lack of expressiveness one is forced into this dead end. 1) Either: Postgres' COPY stmt lacks the ability to omit the physical table, something like SELECT * FROM COPY FROM ... 2) or: Postgres lacks table variables. > Just because the features you are stacking don't work the way you'd like them > to, it's not a shortcoming of orthogonality or modularity. Well this is my very definition of orthogonality / modularity, This is what I'm out for: Language features should be generally combinable, with very few limits. > You could write a procedure that truncates the table and populates it with a > query, using a temporary table if you want. Calling that procedure is just > as convenient as calling REFRESH MATERIALIZED VIEW. Yes, that's what I meant in my last post. But this leads to: I need to adjust all callings points, notify users of the previously-MATVIEW to adjust, and so on. > Are you searching for a usable solution for a problem? Then that should do. I am searching for both * A solution that does not feel like a workaround. (Well I WILL be able to live with the function solution. Somehow.) * Some common ground that there is some lack of modularity / orthogonality / expressiveness in Postgres. Addressing the problem is the first step. Thank You. Regards, Franz-Josef Färber -----Ursprüngliche Nachricht----- Von: Laurenz Albe <[email protected]> Gesendet: Dienstag, 13. Oktober 2026 15:48 An: Färber, Franz-Josef (StMUK) <[email protected]>; Adrian Klaver <[email protected]>; [email protected] Cc: Haupt, Matthias (StMUK) <[email protected]> Betreff: Re: AW: Why is materialized view creation a "security-restricted operation"? On Tue, 2026-10-13 at 11:36 +0000, Färber, Franz-Josef (StMUK) wrote: > > > [rant about PostgreSQL security restrictions] > > No no no, not about security restrictions. The rant is about lacking > orthogonality. > About often NOT being able to combine language features. Doesn't > anybody share that view? We COULD do better, I think. Well, you want to use a materialized view for something that it cannot be used for, for security reasons. We tried to explain that in this thread. > > If the security restrictions of materialized views prevent you from > > using them, why don't you use a normal table instead, TRUNCATE it and > > INSERT into it? > > The MAT VIEW is used from "outside". From other views, or from a client that > connects to the database. > > * I can replace it with a view; I didn't do that for performance reasons. > * I can replace it with a persistent table, a stored procedure, and custom > caching > logic. That'll work. But this is what a MAT VIEW is for, I thought. You could write a procedure that truncates the table and populates it with a query, using a temporary table if you want. Calling that procedure is just as convenient as calling REFRESH MATERIALIZED VIEW. Are you searching for a usable solution for a problem? Then that should do. Yours, Laurenz Albe
