Clarification on materialized view restriction needed
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
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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/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@leadboat.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 athttp://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
On Wed, Aug 28, 2013 at 1:40 AM, Ashutosh Bapat
<ashutosh.bapat@enterprisedb.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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers