Re: PostgreSQL executing my function too many times during query

Tom Lane wrote:
> "D. Dante Lorenso" <dante(at)lorenso(dot)com> 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