Re: PostgreSQL executing my function too many times during query

["D. Dante Lorenso" <dante@xxxxxxxxxxx>]

Tom Lane wrote:
> "D. Dante Lorenso" <dante@xxxxxxxxxxx> writes:
> > So, that not being the problem, any ideas?  Is it an 8.2.4 thing?
>
> I can't reproduce any such problem in 8.2 branch tip, and a desultory
> scan of the CVS history back to 8.2.4 doesn't turn up any obviously
> related patches.  Please provide a self-contained test case for what
> you're seeing.

I think this is a problem with the BYTEA type.  I've created a new 
database and reproduced the problem rather easily.  I've run this test 
on both 8.2.4 and 8.3.1.  Here is my test:

---------- 8< -------------------- 8< ----------
> createdb -U dante functest
> createlang -U dante -d functest plpgsql
> psql -U dante functest

##
## create simple table ... most important is the bytea column
##

CREATE TABLE "public"."demo" (
  "rec_num" SERIAL,
  "data_enc_col" BYTEA NOT NULL,
  CONSTRAINT "demo_pkey" PRIMARY KEY("rec_num")
) WITHOUT OIDS;

##
## we need a simple function that will raise a notice on execution
##

CREATE OR REPLACE FUNCTION "public"."data_enc" (in_text text) RETURNS 
bytea AS
$body$
DECLARE
  my_value BYTEA;
BEGIN
  -- decode text into BYTEA type
  SELECT DECODE(in_text, 'escape')
  INTO my_value;

  -- log that we are called
  RAISE NOTICE 'func data_enc called: %', in_text;

  -- done
  return my_value;
END;
$body$
LANGUAGE 'plpgsql' STABLE RETURNS NULL ON NULL INPUT SECURITY INVOKER;

##
## insert 5 sample values that are all the same
##

functest=# INSERT INTO demo(data_enc_col) VALUES (data_enc('dante'));
NOTICE:  func data_enc called: dante
INSERT 0 1
functest=# INSERT INTO demo(data_enc_col) VALUES (data_enc('dante'));
NOTICE:  func data_enc called: dante
INSERT 0 1
functest=# INSERT INTO demo(data_enc_col) VALUES (data_enc('dante'));
NOTICE:  func data_enc called: dante
INSERT 0 1
functest=# INSERT INTO demo(data_enc_col) VALUES (data_enc('dante'));
NOTICE:  func data_enc called: dante
INSERT 0 1
functest=# INSERT INTO demo(data_enc_col) VALUES (data_enc('dante'));
NOTICE:  func data_enc called: dante
INSERT 0 1

##
## now show that the function runs more than once despite being STABLE
##

functest=# SELECT * FROM demo WHERE data_enc_col = data_enc('dante');
NOTICE:  func data_enc called: dante
NOTICE:  func data_enc called: dante
NOTICE:  func data_enc called: dante
NOTICE:  func data_enc called: dante
NOTICE:  func data_enc called: dante
NOTICE:  func data_enc called: dante
 rec_num | data_enc_col
---------+--------------
       1 | dante
       2 | dante
       3 | dante
       4 | dante
       5 | dante
(5 rows)

##
## test the query again but this time, use a subselect as a "fix"
##

functest=# SELECT * FROM demo WHERE data_enc_col IN (SELECT 
data_enc('dante'));
NOTICE:  func data_enc called: dante
 rec_num | data_enc_col
---------+--------------
       1 | dante
       2 | dante
       3 | dante
       4 | dante
       5 | dante
(5 rows)

---------- 8< -------------------- 8< ----------

What you want to see is how the NOTICE is generated 6 times in the first 
select but only 1 time in the second select (using the subselect 
syntax).  This function has been defined as STABLE and IMMUTABLE and 
neither seem to help.  I've tested this "bug" on 8.2.4 and 8.3.1.

Is this a bug, or do I need to improve my understanding of how this is 
supposed to work?

-- Dante