[BUGS] BUG #2678: Create or replace function with OUT args
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 downLOG: 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
[BUGS] BUG #2678: Create or replace function with OUT args
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 downLOG: 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) ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[BUGS] Function returns wrong data after datatype change
I just ran across this, and I do not think it is entirely a PG bug or even something that the backend can detect and handle. The problem stems from swapping a table definition from under a function. I had a rather large table that had a number of double precision (dp) fields, and in a battle to make it smaller, thus fit more in ram, I changed it to float4 (real). I did not do it with alter table .. type .. I made a new table, insert into newtbl select * from oldtbl; then switched the names. When trying to induce this error if I reloaded the function I use to induce it PG does complain about a datatype mismatch. However, one thing that happens is you can successfully pg_dump the new db (with the altered table) and load it and that function will not complain. Here's a self contained example. createdb broken1 psql broken1 create table brokendp ( cik int, trade_date timestamp, open_price double precision, high_price double precision, low_price double precision, close_price double precision, volume bigint, id int ); insert into brokendp values (803016, '19940103', 0, 9.375, 9.375, 9.375, 200, 9644195); insert into brokendp values (12345, '19950101', 1.12, 2.23, 3.34, 4.45, 1000, 1234567); create or replace function getBrokenDP(int) returns double precision as $$ select close_price from brokendp where cik = $1 order by trade_date asc limit 1 $$ language 'sql'; select '803', getbrokendp(803016); select '123', getbrokendp(12345); create table newbrokendp ( cik int, trade_date timestamp, open_price real, high_price real, low_price real, close_price real, volume bigint, id int ); -- -- I do not htink there is anything we can do about -- this from a PG perspective. -- insert into newbrokendp select * from brokendp; alter table brokendp rename to oldbrokendp; alter table newbrokendp rename to brokendp; select 'switch'; select '803', getbrokendp(803016); select '123', getbrokendp(12345); commit; \q pg_dump broken1 > broken1.sql createdb broken2 psql -f broken1.sql broken2 You'll see the numbers go radically different (ie 9.375 changing to 5.39500333695425e-315) and when you restore the backup, the getBrokenDP function will not make a datatype complaint, so this error will go on for a long time before it creeps up somewhere. -- Jeff Trout <[EMAIL PROTECTED]> http://www.dellsmartexitin.com/ http://www.stuarthamm.net/ ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [BUGS] Function returns wrong data after datatype change
On Jan 24, 2007, at 12:24 PM, Tom Lane wrote: Jeff Trout <[EMAIL PROTECTED]> writes: I just ran across this, and I do not think it is entirely a PG bug or even something that the backend can detect and handle. The problem stems from swapping a table definition from under a function. Hmm. This should yield an error (SQL function not returning the type it claims to), and we probably should plug the hole by invoking check_sql_fn_retval every time not just at creation. I thought you were about to complain about plpgsql, which has much worse problems due to plan caching... The really curious thing is that it does't complain when restoring from the dump - or are those error supressed? -- Jeff Trout <[EMAIL PROTECTED]> http://www.dellsmartexitin.com/ http://www.stuarthamm.net/ ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
