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.g.johns...@gmail.com> wrote: > On Wed, Sep 30, 2020 at 7:41 AM Tom Lane <t...@sss.pgh.pa.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. > >
