Re: AW: Why is materialized view creation a "security-restricted operation"?

On 10/6/26 1:20 AM, Färber, Franz-Josef (StMUK) wrote:
> Are you sure the extension pgtt would work in my case?
>
> https://github.com/darold/pgtt#how-the-extension-really-works says:
>
> "the first access to the table using a SELECT, UPDATE or DELETE statement will produce the creation of a temporary table"
>
> Well, my original point was: Creation of a temporary table is prohibited due to "security-restricted operation".

I tried your MATERIALIZED VIEW with pgtt and ran into the temporary
table issue. I tried also against the 'template' table behind the
temporary tables and got a little further but the behavior was unstable.
I suspect a bug that allowed me to get that far.

Questions:

1) Why the MATERIALIZED VIEW?

2) Can you provide an overview in pseudo code, including the part in the
function, of the process you are trying create?

>
>
> Regards,
> Franz-Josef Färber
>
>
> -----Ursprüngliche Nachricht-----
> Von: Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com>
> Gesendet: Freitag, 2. Oktober 2026 16:55
> An: Ron Johnson <ronljohnsonjr(at)gmail(dot)com>; pgsql-general <pgsql-general(at)postgresql(dot)org>
> Betreff: Re: Why is materialized view creation a "security-restricted operation"?
>
> On 10/2/26 3:19 AM, Ron Johnson wrote:
>> On Thu, Oct 1, 2026 at 8:35 PM David G. Johnston
>> <david(dot)g(dot)johnston(at)gmail(dot)com <mailto:david(dot)g(dot)johnston(at)gmail(dot)com>> wrote:
>>
>> On Thu, Oct 1, 2026 at 4:56 PM Ron Johnson <ronljohnsonjr(at)gmail(dot)com
>> <mailto:ronljohnsonjr(at)gmail(dot)com>> wrote:
>>
>> A GLOBAL TEMP table (where the DBA runs the CREATE GLOBAL TEMP
>> TABLE once (so that CREATE TEMP TABLE some_table  everywhere
>> that my_func() is called) would also solve OP's problem.
>>
>>
>> Per the create table docs:
>>
>> "Optionally, GLOBAL or LOCAL can be written before TEMPORARY or
>> TEMP. This presently makes no difference in PostgreSQL and is
>> deprecated; see Compatibility below."
>>
>> And I'm praying for "since future versions of PostgreSQLmight adopt a
>> more standard-compliant interpretation of their meaning" in the
>> Compatibility section you referenced.
>
> As an extension there is:
>
> https://github.com/darold/pgtt
>
>>
>> --
>> Death to <Redacted>, and butter sauce.
>> Don't boil me, I'm still alive.
>> <Redacted> lobster!
>
>