Re: Fail Fast In CTAS/CMV If Relation Already Exists To Avoid Unnecessary Rewrite, Planning Costs

On Wed, Dec 23, 2020 at 6:01 PM Michael Paquier <michael(at)paquier(dot)xyz> wrote:
> On Tue, Dec 22, 2020 at 03:12:15PM +0530, Bharath Rupireddy wrote:
> > On Tue, Dec 22, 2020 at 2:07 PM Michael Paquier <michael(at)paquier(dot)xyz> wrote:
> >> Note: I'd like to think that we could choose a better name for
> >> CheckRelExistenceInCTAS().
> >
> > I changed it to IsCTASRelCreationAllowed() and attached a v5 patch.
> > Please let me know if this is okay.
>
> After thinking about that, using "CTAS" while other routines in the
> same area use "CreateTableAs" looks inconsistent to me. So I have
> come up with CreateTableAsRelExists() as name.

I think CreateTableAsRelExists() can return true if the relation
already exists and false otherwise, to keep in sync with the function
name. I updated this and attached v7 patch.

> As the same time, I have looked at the git history to note 9bd27b7
> where we had better not give an empty output for non-text formats. So
> I'd like to think that it makes sense to use ExplainDummyGroup() if
> the relation exists with IF NOT EXISTS, keeping some consistency.
>
> What do you think?

+1. Shall we add some test cases(with xml, yaml, json formats as is
currently being done in explain.sql) to cover that? We can have the
explain_filter() function to remove the unstable parts in the output,
it looks something like below. If yes, please let me know I can add
them to matview and select_into.

postgres=# select explain_filter('explain(analyze, format xml) create
table if not exists t1 as select 1;');
NOTICE: relation "t1" already exists, skipping
explain_filter
-------------------------------------------------------
<explain xmlns="http://www.postgresql.org/N/explain">+
<CREATE-TABLE-AS /> +
</explain>
(1 row)

postgres=# select explain_filter('explain(analyze, format yaml)
create table if not exists t1 as select 1;');
NOTICE: relation "t1" already exists, skipping
explain_filter
---------------------
- "CREATE TABLE AS"
(1 row)

postgres=# select explain_filter('explain(analyze, format json)
create table if not exists t1 as select 1;');
NOTICE: relation "t1" already exists, skipping
explain_filter
---------------------
[ +
"CREATE TABLE AS"+
]
(1 row)

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