2

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):

DISCARD PLANS;

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?

Thanks

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.