DKIM2-Signature: i=1; m=1; t=1791322682; d=aklaver.com;
	mf=PGFkcmlhbi5rbGF2ZXJAYWtsYXZlci5jb20+;
	rt=PHBnc3FsLWdlbmVyYWxAcG9zdGdyZXNxbC5vcmc+;
	s=fm1:rsa-sha256:v/xJ7ZNeQqZZjfbtdB8x0pBeLkeDf1E+fRSKmJV4K2oW2zs
	M3NAIegWLNqKS5ELT96zrzE8ZqKI+lH+EToDk8Vy67QbWnAS6nASfgkbOSZ2AsA4
	voSfMZTpIdqdrhX7lCVHAjeFt0DbSNBllnyD61O9qZpAHRNneqQsEkMXqknkrEXS
	NId0ZcRtmBBzWb1ijyb3bhAON+zmRJc+HdNJTuSAwO0p08aS8okmT+eOTn9Qw8eV
	4xNPi/8v2AQwJl1JFLLkywBxC0QPEvvX8Gx5CUG/yvMUdosgcM1JqK6z2H0OGeaG
	HybSTntVPjBr9DhUjC2mPs/4FZtkXzq3GTXMwUw==;
Message-Instance: m=1;
 h=sha256:n7tTlDHFpK8h21QKzVpSXplprfOh5Oe+FUz1Q4jg48w=:QDDngG8JJNA0rlgASvTB5MJDghw3Bvf+eR9ud8aNrac=;
Feedback-ID: i76984098:Fastmail
Message-ID: <0f484041-00b6-454b-9def-e14a955d7c69@aklaver.com>
Date: Tue, 6 Oct 2026 14:38:00 -0700
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: AW: Why is materialized view creation a "security-restricted
 operation"?
To: =?UTF-8?Q?F=C3=A4rber=2C_Franz-Josef_=28StMUK=29?=
 <Franz-Josef.Faerber@stmuk.bayern.de>,
 "pgsql-general@postgresql.org" <pgsql-general@postgresql.org>
References: <77826d7580a9488ca28eb4bcfe94aa37@stmuk.bayern.de>
 <61488cb9-5b84-449a-9886-e0d3b9671aa6@aklaver.com>
 <CANzqJaCa34mEGBsTKZZQz7ZwDR3jd+Q0=s2_7ayw2hHFwTMXjw@mail.gmail.com>
 <CAKFQuwZwX-L-c+=DS=_+kox_+p9AobWRWL7Fot48uAOt2t_X1Q@mail.gmail.com>
 <CANzqJaBnOOer7V+AFk1uY86+uGfFj5EUCJLA0LvYyv9KCTksxg@mail.gmail.com>
 <f6d2fca1-1ee4-4d97-8219-100dd91d8496@aklaver.com>
 <886a7a1fb54a4abdbb89897a6854edd7@stmuk.bayern.de>
Content-Language: en-US
From: Adrian Klaver <adrian.klaver@aklaver.com>
In-Reply-To: <886a7a1fb54a4abdbb89897a6854edd7@stmuk.bayern.de>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit
Archived-At: 
 <https://www.postgresql.org/message-id/0f484041-00b6-454b-9def-e14a955d7c69%40aklaver.com>
Precedence: bulk


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.klaver@aklaver.com>
> Gesendet: Freitag, 2. Oktober 2026 16:55
> An: Ron Johnson <ronljohnsonjr@gmail.com>; pgsql-general <pgsql-general@postgresql.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.g.johnston@gmail.com <mailto:david.g.johnston@gmail.com>> wrote:
>>
>>      On Thu, Oct 1, 2026 at 4:56 PM Ron Johnson <ronljohnsonjr@gmail.com
>>      <mailto:ronljohnsonjr@gmail.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!
> 
>