Function returns wrong data after datatype change - Mailing list pgsql-bugs

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 <jeff@jefftrout.com>
http://www.dellsmartexitin.com/
http://www.stuarthamm.net/