Thread: PL/PgSQL Create/Drop Table Issue

I believe I've found a problem in creating and dropping tables within
PL/PgSQL functions. If you create a table, do some work with it, then
drop it from within a PL/PgSQL function, it will work correctly the
first time through. On subsequent executions of the function, however,
attempting to insert data into the table will result in an error. The
error report follows as does a script which demonstrates the issue clearly.

====  START OF ERROR OUTPUT  ====

psql:./test_temp_table.sql:33: WARNING:  Error occurred while executing
PL/pgSQL function test_temp_table
psql:./test_temp_table.sql:33: WARNING:  line 5 at SQL statement
psql:./test_temp_table.sql:33: ERROR:  pg_class_aclcheck: relation
3326289 not found

====   END OF ERROR OUTPUT   ====


====     START OF SCRIPT     ====

CREATE TABLE my_permanent_table (
     key_value serial NOT NULL PRIMARY KEY,
     statement text NOT NULL);

CREATE OR REPLACE FUNCTION test_temp_table()
RETURNS boolean AS '
     BEGIN
    -- NOTE: regular and temporary tables both affected the same

         CREATE TEMPORARY TABLE my_temp_table (
             statement text NOT NULL);

         INSERT INTO my_temp_table (statement)
             VALUES (''We can''''t play this game anymore...'');

         INSERT INTO my_temp_table (statement)
             VALUES (''...but can we still be friends?'');

         INSERT INTO my_permanent_table (statement)
             SELECT statement
             FROM my_temp_table;

         DROP TABLE my_temp_table;

         RETURN true;
     END;
' LANGUAGE 'plpgsql';

SELECT test_temp_table();

SELECT key_value,
     statement
FROM my_permanent_table;

SELECT test_temp_table();

====      END OF SCRIPT      ====


====  VERSION/PLATFORM INFO  ====

PostgreSQL 7.3.2 on powerpc-apple-darwin6.3, compiled by GCC gcc (GCC)
3.1 20020420 (prerelease)

Also tested on Debian Linux 3.0.x on Intel x86 with same result.

=================================


Thanks for looking into this.

--
Database Administrator, vilaj.com, LLC
<http://www.vilaj.com/>

See FAQ item about this --- use EXECUTE plpgsql command.

---------------------------------------------------------------------------

Database Administrator wrote:
> I believe I've found a problem in creating and dropping tables within
> PL/PgSQL functions. If you create a table, do some work with it, then
> drop it from within a PL/PgSQL function, it will work correctly the
> first time through. On subsequent executions of the function, however,
> attempting to insert data into the table will result in an error. The
> error report follows as does a script which demonstrates the issue clearly.
>
> ====  START OF ERROR OUTPUT  ====
>
> psql:./test_temp_table.sql:33: WARNING:  Error occurred while executing
> PL/pgSQL function test_temp_table
> psql:./test_temp_table.sql:33: WARNING:  line 5 at SQL statement
> psql:./test_temp_table.sql:33: ERROR:  pg_class_aclcheck: relation
> 3326289 not found
>
> ====   END OF ERROR OUTPUT   ====
>
>
> ====     START OF SCRIPT     ====
>
> CREATE TABLE my_permanent_table (
>      key_value serial NOT NULL PRIMARY KEY,
>      statement text NOT NULL);
>
> CREATE OR REPLACE FUNCTION test_temp_table()
> RETURNS boolean AS '
>      BEGIN
>     -- NOTE: regular and temporary tables both affected the same
>
>          CREATE TEMPORARY TABLE my_temp_table (
>              statement text NOT NULL);
>
>          INSERT INTO my_temp_table (statement)
>              VALUES (''We can''''t play this game anymore...'');
>
>          INSERT INTO my_temp_table (statement)
>              VALUES (''...but can we still be friends?'');
>
>          INSERT INTO my_permanent_table (statement)
>              SELECT statement
>              FROM my_temp_table;
>
>          DROP TABLE my_temp_table;
>
>          RETURN true;
>      END;
> ' LANGUAGE 'plpgsql';
>
> SELECT test_temp_table();
>
> SELECT key_value,
>      statement
> FROM my_permanent_table;
>
> SELECT test_temp_table();
>
> ====      END OF SCRIPT      ====
>
>
> ====  VERSION/PLATFORM INFO  ====
>
> PostgreSQL 7.3.2 on powerpc-apple-darwin6.3, compiled by GCC gcc (GCC)
> 3.1 20020420 (prerelease)
>
> Also tested on Debian Linux 3.0.x on Intel x86 with same result.
>
> =================================
>
>
> Thanks for looking into this.
>
> --
> Database Administrator, vilaj.com, LLC
> <http://www.vilaj.com/>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

On Friday 09 May 2003 16:26, Database Administrator wrote:
> I believe I've found a problem in creating and dropping tables within
> PL/PgSQL functions. If you create a table, do some work with it, then
> drop it from within a PL/PgSQL function, it will work correctly the
> first time through. On subsequent executions of the function, however,
> attempting to insert data into the table will result in an error. The
> error report follows as does a script which demonstrates the issue clearly.

See:
http://www.postgresql.org/docs/faqs/FAQ.html#4.26

Ian Barwick
barwick@gmx.net