Postgres Pro
Downloads
Home   >   mailing lists

Thread: temp table problem

temp table problem

From
"Abbas"
Date:
Hi,
I have come across a problem. When you try to access a temp table
created via SPI_EXEC, you get a table not found error.
 SPI_EXEC("CREATE TEMP TABLE my_temp_table(first_name text, last_name
text)", UTILITY); SPI_EXEC("REVOKE ALL ON TABLE my_temp_table FROM PUBLIC", UTILITY);

The second statement generates a table not found error, although the
first statement was successful.

After initdb the system has no temp namespace to hold
temp objects and hence the search path does not contain 
any temp namespace either.
On first call to create a temp table the system first creates
a temp namespace. At this point the system calls recomputeNamespacePath
thinking that it would update search path and include the temp namespace
in it, but that does not happen beccause of override search path stack.
Hence subsquent calls to say insert into the temp table fail.

Any suggestions on how to tackle this problem?

Regards
Abbas
EnterpriseDB   http://www.enterprisedb.com

Re: temp table problem

From
Tom Lane
Date:
"Abbas" <abbas.butt@enterprisedb.com> writes:
> I have come across a problem. When you try to access a temp table
> created via SPI_EXEC, you get a table not found error.

>   SPI_EXEC("CREATE TEMP TABLE my_temp_table(first_name text, last_name
> text)", UTILITY);
>   SPI_EXEC("REVOKE ALL ON TABLE my_temp_table FROM PUBLIC", UTILITY);

> The second statement generates a table not found error, although the
> first statement was successful.

Works for me ...
   ret = SPI_execute("CREATE TEMP TABLE my_temp_table(first_name text, last_name text)", false, 0);
   if (ret != SPI_OK_UTILITY)       elog(ERROR, "SPI_execute(CREATE) returned %d", ret);
   ret = SPI_execute("REVOKE ALL ON TABLE my_temp_table FROM PUBLIC", false, 0);
   if (ret != SPI_OK_UTILITY)       elog(ERROR, "SPI_execute(REVOKE) returned %d", ret);

What PG version are you testing?  Maybe you need to show a complete
test case, instead of leaving us to guess at details?
        regards, tom lane

Re: temp table problem

From
"Robert Haas"
Date:
I can't help suspecting that the two statements in question were run
in different sessions (or at least different transactions?).

...Robert

On Fri, Jul 18, 2008 at 9:11 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> "Abbas" <abbas.butt@enterprisedb.com> writes:
>> I have come across a problem. When you try to access a temp table
>> created via SPI_EXEC, you get a table not found error.
>
>>   SPI_EXEC("CREATE TEMP TABLE my_temp_table(first_name text, last_name
>> text)", UTILITY);
>>   SPI_EXEC("REVOKE ALL ON TABLE my_temp_table FROM PUBLIC", UTILITY);
>
>> The second statement generates a table not found error, although the
>> first statement was successful.
>
> Works for me ...
>
>    ret = SPI_execute("CREATE TEMP TABLE my_temp_table(first_name text, last_name text)", false, 0);
>
>    if (ret != SPI_OK_UTILITY)
>        elog(ERROR, "SPI_execute(CREATE) returned %d", ret);
>
>    ret = SPI_execute("REVOKE ALL ON TABLE my_temp_table FROM PUBLIC", false, 0);
>
>    if (ret != SPI_OK_UTILITY)
>        elog(ERROR, "SPI_execute(REVOKE) returned %d", ret);
>
> What PG version are you testing?  Maybe you need to show a complete
> test case, instead of leaving us to guess at details?
>
>                        regards, tom lane
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers
>

Re: temp table problem

From
"Heikki Linnakangas"
Date:
Tom Lane wrote:
> What PG version are you testing?  Maybe you need to show a complete
> test case, instead of leaving us to guess at details?

I think that example is bogus. Let's forget that one, and look at the
attached script.

The underlying problem is that when we do GetOverrideSearchPath() in
CreateCachedPlan, the memorized search path doesn't include pg_temp, if
the temp namespace wasn't initialized for the backend yet. When we later
need to revalidate the plan, pg_temp still isn't searched, even if it
now exists.

(On 8.3 and CVS HEAD)

--
   Heikki Linnakangas
   EnterpriseDB   http://www.enterprisedb.com
DROP TABLE IF EXISTS ambiguous_table;
CREATE TABLE ambiguous_table(which text);
INSERT INTO ambiguous_table VALUES ('permanent');
CREATE OR REPLACE FUNCTION ss () RETURNS text LANGUAGE plpgsql AS $$
BEGIN
  RETURN which FROM ambiguous_table;
END;
$$;

-- Returns 'permanent'
SELECT ss();

-- Replace the table in 'public' schema with a temp table
DROP TABLE ambiguous_table;
CREATE TEMP TABLE ambiguous_table(which text);
INSERT INTO ambiguous_table VALUES ('temp');

-- Should return 'temp', but fails if pg_temp wasn't initialized
-- before executing the function the first time.
SELECT ss();

Re: temp table problem

From
Tom Lane
Date:
"Heikki Linnakangas" <heikki@enterprisedb.com> writes:
> The underlying problem is that when we do GetOverrideSearchPath() in 
> CreateCachedPlan, the memorized search path doesn't include pg_temp, if 
> the temp namespace wasn't initialized for the backend yet. When we later 
> need to revalidate the plan, pg_temp still isn't searched, even if it 
> now exists.

So what's the problem?  The cached plan couldn't have referred to a temp
table.
        regards, tom lane