Re: Add missing CREATE TABLE IF NOT EXISTS table_name AS EXECUTE query;

On 15/02/2019 09.14, Michael Paquier wrote:
> On Mon, Feb 11, 2019 at 09:53:59PM +0900, Michael Paquier wrote:
>> Let's wait a bit more than the beginning of this week. I forgot about
>> this week's minor release, and it is too late to do something about
>> this report now, so we will have to wait unt
> OK, committed down to 9.5.

Thanks!

> Another thing I have noticed is the following, which is kind of funky
> (just rinse and repeat once):
> =# EXPLAIN ANALYZE CREATE TABLE IF NOT EXISTS ac AS SELECT 1;
> ERROR: 42P07: relation "ac" already exists
> LOCATION: heap_create_with_catalog, heap.c:1111
>
> The issue here is that we check for IF NOT EXISTS at the high level of
> ExecCreateTableAs, however EXPLAIN takes the lower path of
> create_ctas_internal() which enforces if_not_exists to false when
> building the CreateStmt object to create the relation. This brings
> out a more interesting issue: how should an EXPLAIN behave in this
> case? It has nothing to output as the relation already exists.

Yeah, noticed the same thing myself while refactoring the CTAS code, but
I guess the output could be like the current output for "EXPLAIN ANALYZE
<CTAS> WITH NO DATA;", i.e. a top plan with "(never executed)" (see
below for an example).

# EXPLAIN ANALYZE CREATE TABLE ac AS SELECT 1 WITH NO DATA;
QUERY PLAN
-----------------------------------------------------------
Result (cost=0.00..0.01 rows=1 width=4) (never executed)
Planning Time: 0.125 ms
Execution Time: 17.046 ms
(3 rows)

The main thing which bothers me right now about my refactoring is how
different the code paths for CTAS and EXPLAIN ANALYZE CTAS are, which
leads to weirdness like this. I wonder if we cannot make them share more
code e.g. by having ExplainOneUtility() call into some function in
createas.c.

Maybe I should give it a shot and then start a new thread for the
refactoring once I have looked more into this.

Andreas