Thread

1. Want function to be called only once in query

   Steve Pritchard <steve.pritchard@bto.org> — 2021-09-16T08:51:31Z

   I have a PL/pgSQL function that I want to call within a query, but the
   function is fairly expensive to execute so I only want it executed once
   within the query. However the planner seems to reorganize my query so that
   it calls the function for every row.
   
   We were previously on Pg 9.6 and this wasn't a problem then. But now that
   we have upgraded to Pg 13, the behaviour has changed.
   
   I thought that marking the function as STABLE would mean that the function
   would only be called once within a query, but this doesn't seem to be the
   case. (Note: the function isn't IMMUTABLE). I've also tried increasing the
   cost of the function, but this doesn't make any difference.
   
   From looking at previous posts I discovered that putting "offset 0" on the
   function call in a "with" clause means that it only gets called once
   (because then the Common Table Expression isn't combined with the rest of
   the query).
   
   This does work, however it seems rather a kludge (and might not work in
   future versions of PostgreSQL).
   
   There must be a "proper" way to get the planner to call a function only
   once.
   
   Postgres version: PostgreSQL 13.3 on x86_64-pc-linux-gnu, compiled by gcc
   (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit
   
   Here's a simple test case that demonstrates the issue:
   
   create or replace function test_caching(v integer)
     returns text
   as
   $BODY$
   begin
     raise NOTICE 'In test_caching(%) function', v;
     return 'Test';
   end
   $BODY$
   LANGUAGE plpgsql STABLE
   COST 500;
   
   select n, test_caching(7) from generate_series(1, 10) n;
   -- test_caching(...) is called 10 times
   
   with tc as (
     select test_caching(7)
   )
   select n, tc.test_caching
   from tc
   cross join generate_series(1, 10) n;
   -- test_caching(...) is called 10 times
   -- (in Pg 9.6, test_caching(...) is only called once)
   
   with tc as (
     select test_caching(7) offset 0
   )
   select n, tc.test_caching
   from tc
   cross join generate_series(1, 10) n;
   -- test_caching(...) is called once
   -- works, but a kludge
   
   Steve
   -- 
   Steve Pritchard
   Database Developer
   
   British Trust for Ornithology, The Nunnery, Thetford, Norfolk IP24 2PU, UK
   Tel: +44 (0)1842 750050, fax: +44 (0)1842 750030
   Registered Charity No 216652 (England & Wales) No SC039193 (Scotland)
   Company Limited by Guarantee No 357284 (England & Wales)
   

2. Re: Want function to be called only once in query

   Jim Mlodgenski <jimmy76@gmail.com> — 2021-09-16T10:56:05Z

   On Thu, Sep 16, 2021 at 4:51 AM Steve Pritchard <steve.pritchard@bto.org> wrote:
   >
   > I have a PL/pgSQL function that I want to call within a query, but the function is fairly expensive to execute so I only want it executed once within the query. However the planner seems to reorganize my query so that it calls the function for every row.
   >
   > We were previously on Pg 9.6 and this wasn't a problem then. But now that we have upgraded to Pg 13, the behaviour has changed.
   >
   
   The behavior for planning a CTE changed in PG12.
   
   > There must be a "proper" way to get the planner to call a function only once.
   >
   Add the MATERIALIZED keyword to the WITH statement
   
   
   
   

3. Re: Want function to be called only once in query

   Steve Pritchard <steve.pritchard@bto.org> — 2021-09-16T11:07:53Z

   > Add the MATERIALIZED keyword to the WITH statement
   
   Many thanks Jim, that's just what I needed - that does the trick.
   
   It's hard to keep abreast of these SQL changes. Thank goodness for mailing
   lists!
   
   Steve
   
   On Thu, 16 Sept 2021 at 11:56, Jim Mlodgenski <jimmy76@gmail.com> wrote:
   
   > On Thu, Sep 16, 2021 at 4:51 AM Steve Pritchard <steve.pritchard@bto.org>
   > wrote:
   > >
   > > I have a PL/pgSQL function that I want to call within a query, but the
   > function is fairly expensive to execute so I only want it executed once
   > within the query. However the planner seems to reorganize my query so that
   > it calls the function for every row.
   > >
   > > We were previously on Pg 9.6 and this wasn't a problem then. But now
   > that we have upgraded to Pg 13, the behaviour has changed.
   > >
   >
   > The behavior for planning a CTE changed in PG12.
   >
   > > There must be a "proper" way to get the planner to call a function only
   > once.
   > >
   > Add the MATERIALIZED keyword to the WITH statement
   >
   
   
   -- 
   Steve Pritchard
   Database Developer
   
   British Trust for Ornithology, The Nunnery, Thetford, Norfolk IP24 2PU, UK
   Tel: +44 (0)1842 750050, fax: +44 (0)1842 750030
   Registered Charity No 216652 (England & Wales) No SC039193 (Scotland)
   Company Limited by Guarantee No 357284 (England & Wales)