Hi have a set of functions that take a row of a table (let's call it bookings) as input parameters. Some of these functions call other functions, and they are used to calculate a materialized view.
So far so good.
The I decided that the table column "booking_date" should not be a timestamp, but instead a date:
ALTER TABLE booking ALTER booking_date TYPE DATE;
This booking table had views over it that I previously dropped (also triggers, but those I kept as it had no effect).
Then, when I try to recreate the materialized view (that uses those above mentioned functions), I get the following error message:
Message : ERROR: date out of range for timestamp Where: PL/pgSQL function calcfee(booking) while storing call arguments into local variables
But if I execute a second time, everything works as expected.
Then I read about Plan Caching, and it starts to make sense - the functions are evaluated at session start (in this case, flyway running migrations), and since the underlying table has changed, the cache should be invalidate.
Hence, I tried (as described here):
To no result.
However, this brute-force approach did the trick:
CREATE OR REPLACE FUNCTION public.recreate_functions() RETURNS void AS $BODY$ declare f record; BEGIN -- query postgres for 'user functions definitions' FOR f IN select pg_get_functiondef(pp.oid) as fdef from pg_proc pp inner join pg_namespace pn on (pp.pronamespace = pn.oid) inner join pg_language pl on (pp.prolang = pl.oid) where pl.lanname NOT IN ('c','internal') and pn.nspname NOT LIKE 'pg_%' and pn.nspname <> 'information_schema' LOOP EXECUTE f.fdef; END LOOP; END; $BODY$ LANGUAGE plpgsql VOLATILE COST 100; -- DISCARD PLANS; SELECT recreate_functions();
Why is that? What am I missing here?