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

Search Discussions

  • Tom Lane at Jan 24, 2007 at 5:30 pm

    Jeff Trout 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...

    regards, tom lane
  • Jeff Trout at Jan 24, 2007 at 6:59 pm

    On Jan 24, 2007, at 12:24 PM, Tom Lane wrote:

    Jeff Trout <threshar@threshar.is-a-geek.com> 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?
  • Tom Lane at Jan 24, 2007 at 7:04 pm

    Jeff Trout writes:
    On Jan 24, 2007, at 12:24 PM, Tom Lane wrote:
    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.
    The really curious thing is that it does't complain when restoring
    from the dump - or are those error supressed?
    Yeah, pg_dump sets check_function_bodies = off. It more or less has to
    because it can't understand the function contents well enough to know
    what objects would need to be loaded first (and in fact there might be
    no solution --- consider two mutually recursive functions ...)

    regards, tom lane

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-bugs @
categoriespostgresql
postedJan 24, '07 at 3:46p
activeJan 24, '07 at 7:04p
posts4
users2
websitepostgresql.org
irc#postgresql

2 users in discussion

Jeff Trout: 2 posts Tom Lane: 2 posts

People

Translate

site design / logo © 2022 Grokbase