Thread: temp table same name real table

temp table same name real table

From

"v.brusa@joinsnc.com"

Date:

30 September 2020, 16:27:17

Hi all,
 I have this strange behavior when I use temp table with same name of a permanent table in a function. 
 Postgres version is:
PostgreSQL 11.3 (Debian 11.3-1.pgdg90+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 6.3.0-18+deb9u1) 6.3.0 20170516, 64-bit
 I use a query panel from pgAdmin

 drop table if exists taba; 
create table taba (cola text); 
insert into taba(cola) values ('val_permanent'); 

create or replace function test_temp_table(_is_temp boolean, scan refcursor) RETURNS refcursor AS
 $BODY$ 
BEGIN
if _is_temp then
      create temp table taba on commit drop as select * from public.taba with no data; 
      insert into taba(cola) values ('val_temp'); 
else

end if; 

 open scan for
select * from taba; 

return scan; 

END; 
 $BODY$ 
LANGUAGE plpgsql VOLATILE; 


select test_temp_table(true,'scan'); fetch all from scan; -- return data in temp table taba that is 'val_temp' 

select test_temp_table(false,'scan'); fetch all from scan; -- return data in permanent table taba that is 'val_permanent' 

select test_temp_table(true,'scan'); fetch all from scan; -- return data in permanent table taba that is 'val_permanent' and dont see temporary table 


If I force a change in search_path the code works correctly. 


drop table if exists taba; 
create table taba (cola text); 
insert into taba(cola) values ('val_permanent'); 

create or replace function test_temp_table(_is_temp boolean, scan refcursor) RETURNS refcursor AS
 $BODY$ 
BEGIN

if _is_temp then
      set search_path=pg_temp,public; 
      create temp table taba on commit drop as select * from public.taba with no data; 
      insert into taba(cola) values ('val_temp'); 
else
      set search_path=public; 
end if; 

 open scan for
      select * from taba; 

return scan; 

END; 
 $BODY$ 
LANGUAGE plpgsql VOLATILE; 


select test_temp_table(true,'scan'); fetch all from scan; -- return data in temp table taba that is 'val_temp' 

select test_temp_table(false,'scan'); fetch all from scan; -- return data in permanent table taba that is 'val_permanent' 

select test_temp_table(true,'scan'); fetch all from scan; -- return data in temp table taba that is 'val_temp' 


 it would seem that when function references permanent table first time in next calling (select * from taba;) temp table is not and is not true postgres documentation (Existing permanent tables with the same name are not visible to the current session while the temporary table exists, unless they are referenced with schema-qualified names) 

 any idea?

 thanks

 Vittorio Brusa Zappellini

Re: temp table same name real table

From

Michael Lewis

Date:

30 September 2020, 17:34:48

On Wed, Sep 30, 2020, 7:27 AM v.brusa@joinsnc.com <v.brusa@joinsnc.com> wrote:

Hi all,
I have this strange behavior when I use temp table with same name of a permanent table in a function.

IMO, you are asking for strange behavior when you overload a table name.

I expect that one of the people who work down deep down in the code will say it is a caching thing that saves system table lookups. It also may be required to ensure consistent behavior when you use the same table name (non specified schema) then you should always get the same table.

Still, it might be simple to use exceptions and try to read the pg_temp table and if it does not exist, try the public schema.

Or, just schema qualify the fetch call with a string that is computed for which schema you want to use.

Re: temp table same name real table

From

Tom Lane

Date:

30 September 2020, 17:41:29

Michael Lewis <mlewis@entrata.com> writes:
> IMO, you are asking for strange behavior when you overload a table name.

> I expect that one of the people who work down deep down in the code will
> say it is a caching thing that saves system table lookups.

Yeah, I think that on the second call, plpgsql caches a query plan that
references the permanent table, and then it sees no reason to reconsider
that plan on the third call.  (Change of search_path is one of the
conditions that would prompt throwing away the cached plan, which
fits that part of the report.  But creating a temp table that happens
to shadow a real table is not one.)

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.

            regards, tom lane

Re: temp table same name real table

From

"David G. Johnston"

Date:

02 October 2020, 03:14:03

On Wed, Sep 30, 2020 at 7:41 AM Tom Lane <tgl@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.

Re: temp table same name real table

From

"David G. Johnston"

Date:

21 October 2020, 18:30:35

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.johnston@gmail.com> wrote:

On Wed, Sep 30, 2020 at 7:41 AM Tom Lane <tgl@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.