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/