Thread: BUG #1059: Second Call of a PGSQL-function fails

The following bug has been logged online:

Bug reference:      1059
Logged by:          Wilhelm

Email address:      wilhelm.pakulla@gmx.de

PostgreSQL version: 7.4

Operating system:   Linux

Description:        Second Call of a PGSQL-function fails

Details:

-- The Source:

-- Init Stuff
DROP FUNCTION plpgsql_call_handler () CASCADE;
CREATE FUNCTION plpgsql_call_handler () RETURNS LANGUAGE_HANDLER AS
'$libdir/plpgsql' LANGUAGE C;

CREATE TRUSTED PROCEDURAL LANGUAGE plpgsql
    HANDLER plpgsql_call_handler;

-- The function

CREATE FUNCTION f (INTEGER) RETURNS INTEGER
AS '
BEGIN
  CREATE TABLE test ( x INTEGER );

  -- Without this insert, everything works well...
  INSERT INTO test VALUES (1);

  DROP TABLE test CASCADE;

  RETURN 0;
END;
' LANGUAGE 'plpgsql';

-- That works.
SELECT f(1);

-- Second Call fails.
SELECT f(1);

-- Thanks in advance, Wilhelm

On Wednesday 21 January 2004 21:24, PostgreSQL Bugs List wrote:
> Bug reference:      1059
> Logged by:          Wilhelm
> Email address:      wilhelm.pakulla@gmx.de
>
> PostgreSQL version: 7.4
> Operating system:   Linux
> Description:        Second Call of a PGSQL-function fails

Wilhelm - this is a known behaviour (I'm not sure it can be called a bug).

Because plpgsql is a compiled language, it converts references to tables and
other objects to their internal OID number. If you drop a table then recreate
it, it will get a new OID and your function will no longer be able to find
it.

There are two ways to deal with this:
1. Don't use plpgsql for these functions, use SQL or TCL/Perl/some other
interpreted language
2. Use EXECUTE to build a query from a string.

You should find plenty on this in the mailing list archives, usually in
connection to TEMPorary tables.

HTH

PS - If you think the documentation needs clarifying, suggestions on wording
are always appreciated, preferably on pgsql-docs mailing list.

--
  Richard Huxton
  Archonet Ltd

We have an FAQ for this:

    <H4><A name="4.26">4.26</A>) Why can't I reliably create/drop
    temporary tables in PL/PgSQL functions?</H4>

It says temporary tables, but it is valid for real tables too when you
creating/dropping them in the function.

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

PostgreSQL Bugs List wrote:
>
> The following bug has been logged online:
>
> Bug reference:      1059
> Logged by:          Wilhelm
>
> Email address:      wilhelm.pakulla@gmx.de
>
> PostgreSQL version: 7.4
>
> Operating system:   Linux
>
> Description:        Second Call of a PGSQL-function fails
>
> Details:
>
> -- The Source:
>
> -- Init Stuff
> DROP FUNCTION plpgsql_call_handler () CASCADE;
> CREATE FUNCTION plpgsql_call_handler () RETURNS LANGUAGE_HANDLER AS
> '$libdir/plpgsql' LANGUAGE C;
>
> CREATE TRUSTED PROCEDURAL LANGUAGE plpgsql
>     HANDLER plpgsql_call_handler;
>
> -- The function
>
> CREATE FUNCTION f (INTEGER) RETURNS INTEGER
> AS '
> BEGIN
>   CREATE TABLE test ( x INTEGER );
>
>   -- Without this insert, everything works well...
>   INSERT INTO test VALUES (1);
>
>   DROP TABLE test CASCADE;
>
>   RETURN 0;
> END;
> ' LANGUAGE 'plpgsql';
>
> -- That works.
> SELECT f(1);
>
> -- Second Call fails.
> SELECT f(1);
>
> -- Thanks in advance, Wilhelm
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>

--
  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