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

From: Michael Paquier <michael(at)paquier(dot)xyz>
To: Bharath Rupireddy <bharath(dot)rupireddyforpostgres(at)gmail(dot)com>
Cc: "Hou, Zhijie" <houzj(dot)fnst(at)cn(dot)fujitsu(dot)com>, PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: Fail Fast In CTAS/CMV If Relation Already Exists To Avoid Unnecessary Rewrite, Planning Costs
Date: 2020-12-22 08:37:17
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers

On Mon, Dec 21, 2020 at 12:01:38PM +0530, Bharath Rupireddy wrote:
> On Fri, Dec 18, 2020 at 8:15 AM Bharath Rupireddy
>> I tried to make it consistent by issuing NOTICE (not an error) even
>> for EXPLAIN/EXPLAIN ANALYZE IF NOT EXISTS case. If issue notice and
>> exit from the ExplainOneUtility, we could output an empty plan to the
>> user because, by now ExplainResultDesc would have been called at the
>> start of the explain via PortalStart(). I didn't find a clean way of
>> coding if we are not okay to show notice and empty plan to the user.
>> Any suggestions on achieving above?

I was looking at your patch today, and I actually found the conclusion
to output an empty plan while issuing a NOTICE to be quite intuitive
if the caller uses IF NOT EXISTS with EXPLAIN.

> Attaching v3 patch that also contains test cases. Please review it further.

Thanks for adding some test cases! Some of them were exact
duplicates, so it is possible to reduce the number of queries without
impacting the coverage. I have also chosen a query that forces an
error within the planner.

Please see the attached. IF NOT EXISTS implies that CTAS or CREATE
MATVIEW will never ERROR if the relation already exists, with or
without EXPLAIN, EXECUTE or WITH NO DATA, so that gets us a consistent
behavior across all the patterns.

Note: I'd like to think that we could choose a better name for

Attachment Content-Type Size
v4-0001-Fail-Fast-In-CTAS-CMV-If-Relation-Already-Exists.patch text/x-diff 10.9 KB

In response to


Browse pgsql-hackers by date

  From Date Subject
Next Message Bharath Rupireddy 2020-12-22 08:45:56 Re: Parallel Inserts in CREATE TABLE AS
Previous Message Justin Pryzby 2020-12-22 08:32:05 Re: Allow CLUSTER, VACUUM FULL and REINDEX to change tablespace on the fly