The following bug has been logged online:
Bug reference: 2678
Logged by: Jeff Trout
Email address: [EMAIL PROTECTED]
PostgreSQL version: 8.1.4
Operating system: OSX 10.4.8 (Also occurs on FC4 w/kernel 2.6.16
Description: Create or replace function with OUT args
Details:
it seems there may be some bug with cache coherency when replacing
functions
that use OUT arguments. This happens on Linux (2.6.16 ) - same
version of
PG. (I didn't test across the pg restart as others are using that
system).
I've created and replaced hundreds of "normal" functions. so I think
it has
to do with OUT params.
Here's a transcript:
skittlebrau:/tmp postgres$ cat broken2.sql
create or replace function cachebroken(p_id int, out o_val1 int, out
o_val2
int)
as $$
BEGIN
o_val1 := 12;
o_val2 := 45;
END
$$
language 'plpgsql';
skittlebrau:/tmp postgres$ psql indie
Welcome to psql 8.1.4, the PostgreSQL interactive terminal.
Type: \copyright for distribution terms
\h for help with SQL commands
\? for help with psql commands
\g or terminate with semicolon to execute query
\q to quit
indie=# \i broken2.sql
CREATE FUNCTION
indie=# \i broken2.sql
CREATE FUNCTION
indie=# \i broken2.sql
CREATE FUNCTION
indie=# \i broken2.sql
CREATE FUNCTION
indie=# \q
skittlebrau:/tmp postgres$ psql indie
Welcome to psql 8.1.4, the PostgreSQL interactive terminal.
Type: \copyright for distribution terms
\h for help with SQL commands
\? for help with psql commands
\g or terminate with semicolon to execute query
\q to quit
indie=# \i broken2.sql
psql:broken2.sql:10: ERROR: missing cache data for cache id 27
indie=# \i broken2.sql
psql:broken2.sql:10: ERROR: missing cache data for cache id 27
indie=# \q
skittlebrau:/tmp postgres$ pg_ctl -D /usr/local/pgsql/data/ -m fast
restart
waiting for postmaster to shut down....LOG: logger shutting down
done
postmaster stopped
postmaster starting
skittlebrau:/tmp postgres$ psql indie
Welcome to psql 8.1.4, the PostgreSQL interactive terminal.
Type: \copyright for distribution terms
\h for help with SQL commands
\? for help with psql commands
\g or terminate with semicolon to execute query
\q to quit
indie=# \i broken2.sql
psql:broken2.sql:10: ERROR: missing cache data for cache id 27
indie=# drop function cachebroken (int);
DROP FUNCTION
indie=# \i broken2.sql
CREATE FUNCTION
indie=# \q
Occurs on: indie=# select version();
version
------------------------------------------------------------------------
----
------------------------------------------------------------------
PostgreSQL 8.1.4 on powerpc-apple-darwin8.8.0, compiled by GCC
powerpc-apple-darwin8-gcc-4.0.0 (GCC) 4.0.0 (Apple Computer, Inc. build
5026)
(1 row)
indie=# select version();
version
------------------------------------------------------------------------
----
-----------------------
PostgreSQL 8.1.4 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 4.0.1
20050727 (Red Hat 4.0.1-5)
(1 row)
a couple of the guys on irc have confirmed it on HEAD as well.
--
Jeff Trout <[EMAIL PROTECTED]>
http://www.dellsmartexitin.com/
http://www.stuarthamm.net/
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
