Lists: | pgsql-hackers |
---|
From: | Ashutosh Bapat <ashutosh(dot)bapat(at)enterprisedb(dot)com> |
---|---|
To: | pgsql-hackers <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Clarification on materialized view restriction needed |
Date: | 2013-08-27 08:27:49 |
Message-ID: | CAFjFpRcz3qKQFQo3RynfPinXdOp_42Tz+xCqBQdAoe061bMRSw@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-hackers |
Hi All,
I want to create a materialized view as the output of a plpgsql function
returning a set of rows. But that function creates temporary tables and
thus can not be used for creating materialized view as per the
documentation at
http://www.postgresql.org/docs/9.3/static/sql-creatematerializedview.html.
"This query will run within a security-restricted operation; in particular,
calls to functions that themselves create temporary tables will fail."
I tried to understand what is "security-restricted operation", and didn't
find any definition of this term or any listing as to "these are
security-restricted operations ...". I am wondering what are other
restrictions on the queries whose results can be used to create
materialized views.
--
Best Wishes,
Ashutosh Bapat
EntepriseDB Corporation
The Postgres Database Company
From: | Noah Misch <noah(at)leadboat(dot)com> |
---|---|
To: | Ashutosh Bapat <ashutosh(dot)bapat(at)enterprisedb(dot)com> |
Cc: | pgsql-hackers <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: Clarification on materialized view restriction needed |
Date: | 2013-08-28 04:42:57 |
Message-ID: | 20130828044257.GA38772@tornado.leadboat.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-hackers |
On Tue, Aug 27, 2013 at 01:57:49PM +0530, Ashutosh Bapat wrote:
> I want to create a materialized view as the output of a plpgsql function
> returning a set of rows. But that function creates temporary tables and
> thus can not be used for creating materialized view as per the
> documentation at
> http://www.postgresql.org/docs/9.3/static/sql-creatematerializedview.html.
> "This query will run within a security-restricted operation; in particular,
> calls to functions that themselves create temporary tables will fail."
>
> I tried to understand what is "security-restricted operation", and didn't
> find any definition of this term or any listing as to "these are
> security-restricted operations ...". I am wondering what are other
> restrictions on the queries whose results can be used to create
> materialized views.
The semantics of a security-restricted operation remain undocumented. You can
witness the list of restrictions by searching for callers of
InSecurityRestrictedOperation(). Here is the current list for core code:
- CREATE TEMP TABLE
- SET ROLE
- SET SESSION AUTHORIZATION
- CLOSE
- PREPARE
- DEALLOCATE
- LISTEN
- UNLISTEN
- DISCARD
--
Noah Misch
EnterpriseDB http://www.enterprisedb.com
From: | Ashutosh Bapat <ashutosh(dot)bapat(at)enterprisedb(dot)com> |
---|---|
To: | Noah Misch <noah(at)leadboat(dot)com> |
Cc: | pgsql-hackers <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: Clarification on materialized view restriction needed |
Date: | 2013-08-28 05:40:56 |
Message-ID: | CAFjFpRcHkEXcr_EOML9KjKo++42vibfUuzQ=GrCcCCA9LtRVqQ@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-hackers |
I would be good, if this set gets documented, lest users will be confused.
Can you point me to relevant sections of document? I can add this
documentation.
On Wed, Aug 28, 2013 at 10:12 AM, Noah Misch <noah(at)leadboat(dot)com> wrote:
> On Tue, Aug 27, 2013 at 01:57:49PM +0530, Ashutosh Bapat wrote:
> > I want to create a materialized view as the output of a plpgsql function
> > returning a set of rows. But that function creates temporary tables and
> > thus can not be used for creating materialized view as per the
> > documentation at
> >
> http://www.postgresql.org/docs/9.3/static/sql-creatematerializedview.html.
> > "This query will run within a security-restricted operation; in
> particular,
> > calls to functions that themselves create temporary tables will fail."
> >
> > I tried to understand what is "security-restricted operation", and didn't
> > find any definition of this term or any listing as to "these are
> > security-restricted operations ...". I am wondering what are other
> > restrictions on the queries whose results can be used to create
> > materialized views.
>
> The semantics of a security-restricted operation remain undocumented. You
> can
> witness the list of restrictions by searching for callers of
> InSecurityRestrictedOperation(). Here is the current list for core code:
>
> - CREATE TEMP TABLE
> - SET ROLE
> - SET SESSION AUTHORIZATION
> - CLOSE
> - PREPARE
> - DEALLOCATE
> - LISTEN
> - UNLISTEN
> - DISCARD
>
> --
> Noah Misch
> EnterpriseDB http://www.enterprisedb.com
>
--
Best Wishes,
Ashutosh Bapat
EntepriseDB Corporation
The Postgres Database Company
From: | Robert Haas <robertmhaas(at)gmail(dot)com> |
---|---|
To: | Ashutosh Bapat <ashutosh(dot)bapat(at)enterprisedb(dot)com> |
Cc: | Noah Misch <noah(at)leadboat(dot)com>, pgsql-hackers <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: Clarification on materialized view restriction needed |
Date: | 2013-08-28 23:58:04 |
Message-ID: | CA+Tgmoa0jWZ1g8uoO+vkVpJW8MNYp00AZkS-T11-U1hjBB+XrA@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-hackers |
On Wed, Aug 28, 2013 at 1:40 AM, Ashutosh Bapat
<ashutosh(dot)bapat(at)enterprisedb(dot)com> wrote:
> I would be good, if this set gets documented, lest users will be confused.
> Can you point me to relevant sections of document? I can add this
> documentation.
I think it's your job to look at the documentation and determine where
this would best fit, not Noah's to go decide that for you.
...Robert