From: Andreas Karlsson <andreas@proxel.se>
To: Michael Paquier <michael@paquier.xyz>
Cc: PostgreSQL Developers <pgsql-hackers@lists.postgresql.org>
Subject: Re: Add missing CREATE TABLE IF NOT EXISTS table_name AS EXECUTE query;
Date: Sun, 17 Feb 2019 15:31:05 +0100
Message-ID: <a349376e-a135-e4a4-818e-41e4c605c80a@proxel.se> (raw)
In-Reply-To: <20190215081451.GD2240@paquier.xyz>
References: <2ddcc188-e37c-a0be-32bf-a56b07c3559e@proxel.se>
<20190206114912.GE14980@paquier.xyz>
<2c6f2b5b-a673-fd1c-ad9c-1c7676cee7d6@proxel.se>
<20190207013135.GD4074@paquier.xyz>
<20190211125359.GB3981@paquier.xyz>
<20190215081451.GD2240@paquier.xyz>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-hackers@postgresql.org
Cc: andreas@proxel.se, michael@paquier.xyz, pgsql-hackers@lists.postgresql.org
Subject: Re: Add missing CREATE TABLE IF NOT EXISTS table_name AS EXECUTE query;
In-Reply-To: <a349376e-a135-e4a4-818e-41e4c605c80a@proxel.se>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox