Re: temp table same name real table

Moving this over to -hackers and the commitfest

https://www.postgresql.org/message-id/CAKFQuwaZjsaQ9KdDKjcwgGo3axg2t0BdBVYJiHf%3DH9nbvsxQbQ%40mail.gmail.com

David J.

On Thu, Oct 1, 2020 at 5:14 PM David G. Johnston <david(dot)g(dot)johnston(at)gmail(dot)com>
wrote:

> On Wed, Sep 30, 2020 at 7:41 AM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>
>> If you really really need to do this, I'd counsel using EXECUTE to
>> ensure no caching happens. But I concur with Michael that it's
>> fundamentally a bad idea.
>>
>
> Agreed, though the documentation seems a bit loose here. The fact that
> the temp table hides the permanent one is a side-effect of pg_temp being
> placed first in the default search_path. If it is explicitly placed last
> the permanent table would be found again.
>
> Adding a reminder that search_path searching happens only during new plan
> creation (even if we don't generally cover caching implementation in
> detail, though I didn't look around for this one) seems like a good value.
>
> I propose the following:
>
> diff --git a/doc/src/sgml/ref/create_table.sgml
> b/doc/src/sgml/ref/create_table.sgml
> index 087cad184c..a400334092 100644
> --- a/doc/src/sgml/ref/create_table.sgml
> +++ b/doc/src/sgml/ref/create_table.sgml
> @@ -171,8 +171,9 @@ WITH ( MODULUS <replaceable
> class="parameter">numeric_literal</replaceable>, REM
> If specified, the table is created as a temporary table.
> Temporary tables are automatically dropped at the end of a
> session, or optionally at the end of the current transaction
> - (see <literal>ON COMMIT</literal> below). Existing permanent
> - tables with the same name are not visible to the current session
> + (see <literal>ON COMMIT</literal> below). The default
> + search_path includes the temporary schema first and so identically
> + named existing permanent tables are not chosen for new plans
> while the temporary table exists, unless they are referenced
> with schema-qualified names. Any indexes created on a temporary
> table are automatically temporary as well.
>
> David J.
>
>