Re: Fail Fast In CTAS/CMV If Relation Already Exists To Avoid Unnecessary Rewrite, Planning Costs - Mailing list pgsql-hackers

On Mon, Dec 14, 2020 at 11:52 AM Michael Paquier <michael@paquier.xyz> wrote:
> On Mon, Dec 14, 2020 at 03:15:12PM +0900, Michael Paquier wrote:
> > Please note that this case fails with your patch, but the presence of
> > IF NOT EXISTS should ensure that we don't fail and issue a NOTICE
> > instead, no?

Thanks for the use case. The provided use case (or for that matter any
use case with explain analyze ctas if-not-exists) fails if the
relation already exists. It happens on the master branch, please have
a look at tests [1]. You are right in saying that whether it is
explain/explain analyze ctas if there is if-not-exists we should issue
notice instead of error as with plain ctas.

Do we want to fix this behaviour for explain/explain analyze ctat with
if-not-exists cases? Thoughts?

If yes, we could change the code in ExplainOneUtility() such that we
check relation existence before rewrite/planning, issue notice and
return. Then. the user sees a notice and an empty plan as we are
returning from ExplainOneUtility(). Is it okay to show a warning and
an empty plan to the user? Thoughts?

>> Taking this case specifically (OK, I am playing with
> > the rules a bit to insert data into the relation itself, still), this
> > query may finish by adding tuples to the table whose creation should
> > have been bypassed but the query got executed and inserted tuples.

IIUC, with the use case provided, the tuples will not be inserted as
the query later fails (and the txn gets aborted) if the relation
exists.

> > That's one example of behavior that may be confusing.  There may be
> > others, but it seems to me that it may be simpler to execute or even
> > plan the query at all if the relation already exists.
>
> Er..  Sorry. I meant here to *not* execute or even *not* plan the
> query at all if the relation already exists.

+1 to not plan and execute the query at all if the relation which
ctas/cmv is trying to create already exists.

[1] -
postgres=#   explain analyze
postgres-#      create table if not exists aa as
postgres-#        with insert_query as
postgres-#          (insert into aa values (1) returning a1)
postgres-#        select * from insert_query;
ERROR:  relation "aa" already exists

postgres=#  explain analyze
postgres-#      create table aa as
postgres-#        with insert_query as
postgres-#          (insert into aa values (1) returning a1)
postgres-#        select * from insert_query;
ERROR:  relation "aa" already exists

postgres=#  explain
postgres-#      create table aa as
postgres-#        with insert_query as
postgres-#          (insert into aa values (1) returning a1)
postgres-#        select * from insert_query;
                         QUERY PLAN
------------------------------------------------------------
 CTE Scan on insert_query  (cost=0.01..0.03 rows=1 width=4)
   CTE insert_query
     ->  Insert on aa  (cost=0.00..0.01 rows=1 width=4)
           ->  Result  (cost=0.00..0.01 rows=1 width=4)

postgres=#   explain
postgres-#       create table if not exists aa as
postgres-#        with insert_query as
postgres-#          (insert into aa values (1) returning a1)
postgres-#        select * from insert_query;
                         QUERY PLAN
------------------------------------------------------------
 CTE Scan on insert_query  (cost=0.01..0.03 rows=1 width=4)
   CTE insert_query
     ->  Insert on aa  (cost=0.00..0.01 rows=1 width=4)
           ->  Result  (cost=0.00..0.01 rows=1 width=4)

postgres=#   create table aa as
postgres-#        with insert_query as
postgres-#          (insert into aa values (1) returning a1)
postgres-#        select * from insert_query;
ERROR:  relation "aa" already exists

postgres=#   create table if not exists aa as
postgres-#        with insert_query as
postgres-#          (insert into aa values (1) returning a1)
postgres-#        select * from insert_query;
NOTICE:  relation "aa" already exists, skipping
CREATE TABLE AS

With Regards,
Bharath Rupireddy.
EnterpriseDB: http://www.enterprisedb.com