Re: temp table same name real table

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.