temp table same name real table
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
droptableifexists taba;
createtable taba (cola text);
insertinto taba(cola) values ('val_permanent');
createorreplacefunction test_temp_table(_is_temp
boolean, scan refcursor) RETURNS refcursor AS
$BODY$
BEGIN
if _is_temp then
createtemptable taba oncommitdropasselect * from public.taba withnodata;
insertinto taba(cola) values ('val_temp');
else
endif;
open scan for
select * from taba;
return scan;
END;
$BODY$
LANGUAGE plpgsql VOLATILE;
select test_temp_table(true,'scan'); fetchallfrom scan;
-- return data in temp table taba that is 'val_temp'
select test_temp_table(false,'scan'); fetchallfrom scan;
-- return data in permanent table taba that is 'val_permanent'
select test_temp_table(true,'scan'); fetchallfrom 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.
droptableifexists taba;
createtable taba (cola text);
insertinto taba(cola) values ('val_permanent');
createorreplacefunction test_temp_table(_is_temp
boolean, scan refcursor) RETURNS refcursor AS
$BODY$
BEGIN
if _is_temp then
set search_path=pg_temp,public;
createtemptable taba oncommitdropasselect * from public.taba withnodata;
insertinto taba(cola) values ('val_temp');
else
set search_path=public;
endif;
open scan for
select * from taba;
return scan;
END;
$BODY$
LANGUAGE plpgsql VOLATILE;
select test_temp_table(true,'scan'); fetchallfrom scan;
-- return data in temp table taba that is 'val_temp'
select test_temp_table(false,'scan'); fetchallfrom scan;
-- return data in permanent table taba that is 'val_permanent'
select test_temp_table(true,'scan'); fetchallfrom scan;
-- return data in temp table taba that is 'val_temp'
it would seem that whenfunctionreferences permanent
tablefirsttimeinnext calling (select * from taba;)
temptableisnotandisnottrue postgres documentation (Existing permanent
tableswith the same name are not visible to the currentsessionwhile the
temporarytableexists, unless they are referenced withschema-qualified
names)
any idea?
thanks
Vittorio Brusa Zappellini
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.
Show quoted text
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
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.
Moving this over to -hackers and the commitfest
/messages/by-id/CAKFQuwaZjsaQ9KdDKjcwgGo3axg2t0BdBVYJiHf=H9nbvsxQbQ@mail.gmail.com
David J.
On Thu, Oct 1, 2020 at 5:14 PM David G. Johnston <david.g.johnston@gmail.com>
wrote:
Show quoted text
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.