Re: Feature: temporary materialized views - Mailing list pgsql-hackers

From Andreas Karlsson
Subject Re: Feature: temporary materialized views
Date
Msg-id 4f747607-29f6-d865-e5f6-75fe11b8d5ff@proxel.se
Whole thread Raw
In response to Re: Feature: temporary materialized views  (Andreas Karlsson <andreas@proxel.se>)
List pgsql-hackers
On 2/5/19 6:56 PM, Andreas Karlsson wrote:
> On 2/5/19 12:36 PM, Michael Paquier wrote:> - skipData is visibly always 
> false.
>  > We may want to keep skipData to have an assertion at the beginning of
>  > inforel_startup for sanity purposes though.
> This is not true in this version of the patch. The following two cases 
> would crash if we add such an assertion:
> 
> EXPLAIN ANALYZE CREATE TABLE foo AS SELECT 1 WITH NO DATA;
> 
> and
> 
> PREPARE s AS SELECT 1;
> CREATE TABLE bar AS EXECUTE s WITH NO DATA;
> 
> since they both still run the setup and tear down steps of the executor.
> 
> I guess that I could fix that for the second case as soon as I 
> understand how much of the portal stuff can be skipped in 
> ExecuteQuery(). But I am not sure what we should do with EXPLAIN ANALYZE 
> ... NO DATA. It feels like a contraction to me. Should we just raise an 
> error? Or should we try to preserve the current behavior where you see 
> something like the below?

In general I do not like how EXPLAIN CREATE TABLE AS uses a separate 
code path than CREATE TABLE AS, because we get weird but mostly harmless 
edge cases like the below and that I do not think that EXPLAIN ANALYZE 
CREATE MATERIALIZED VIEW sets the security context properly.

I am not sure if any of this is worth fixing, but it certainly 
contributed to why I thought that it was hard to design a good API.

postgres=# EXPLAIN ANALYZE CREATE TABLE IF NOT EXISTS bar AS SELECT 1;
                                      QUERY PLAN 

------------------------------------------------------------------------------------
  Result  (cost=0.00..0.01 rows=1 width=4) (actual time=0.001..0.002 
rows=1 loops=1)
  Planning Time: 0.030 ms
  Execution Time: 12.245 ms
(3 rows)

Time: 18.223 ms
postgres=# EXPLAIN ANALYZE CREATE TABLE IF NOT EXISTS bar AS SELECT 1;
ERROR:  relation "bar" already exists
Time: 2.129 ms

Andreas


pgsql-hackers by date:

Previous
From: Justin Pryzby
Date:
Subject: Re: pg11.1: dsa_area could not attach to segment
Next
From: Andreas Karlsson
Date:
Subject: Add missing CREATE TABLE IF NOT EXISTS table_name AS EXECUTE query;