I have several records in my database which have encrypted fields. I
want to find all the ones that match a certain format but do NOT match
another.

My problem is that the 'cc_encrypt' function is being executed for every
matching row in the table instead of just once. The function was
defined as STABLE and I tried IMMUTABLE as well. That doesn't seem to
be helping.

This format causes the function to execute too many times:

SELECT COUNT(*) AS result
FROM credit_card
WHERE card_number_enc = cc_encrypt('4111-1111-1111-1111', 'pwd')
AND card_number_enc != cc_encrypt('4111111111111111', 'pwd');

So, the second cc_encrypt is being executed for every row matching the
first cc_encrypt condition. My expectation is that both functions would
be executed ONCE the result would be used in the query like this:

SELECT COUNT(*) AS result
FROM credit_card
WHERE card_number_enc = <RESULT>
AND card_number_enc != <RESULT>;

To fix the "bug", I can rewrite my query like this and the functions
will only be executed once each as expected:

SELECT COUNT(*) AS result
FROM credit_card
WHERE card_number_enc = cc_encrypt('4111-1111-1111-1111', 'pwd')
AND card_number_enc NOT IN (
SELECT cc_encrypt('4111111111111111', 'pwd')
);

I don't understand what's happening here. Any help? Maybe the EXPLAIN
tells something?

# EXPLAIN SELECT COUNT(*) AS result
# FROM credit_card
# WHERE card_number_enc = cc_encrypt('4111-1111-1111-1111', 'pwd')
# AND card_number_enc != cc_encrypt('4111111111111111', 'pwd');
QUERY PLAN

------------------------------------------------------------------------------------------------------------------
Aggregate (cost=196.96..196.97 rows=1 width=0)
-> Bitmap Heap Scan on credit_card (cost=4.87..196.76 rows=79 width=0)
Recheck Cond: (card_number_enc =
credit_card_encrypt('4111-1111-1111-1111'::text, 'password'::text))
Filter: (card_number_enc <>
credit_card_encrypt('4111111111111111'::text, 'password'::text))
-> Bitmap Index Scan on credit_card_idx_card_number_enc
(cost=0.00..4.85 rows=79 width=0)
Index Cond: (card_number_enc =
credit_card_encrypt('4111-1111-1111-1111'::text, 'password'::text))
(6 rows)

Oddly, when I use 'EXPLAIN', I see my debug logging "RAISE NOTICE"
statements showing that the function was only executed once each. When
I don't use EXPLAIN, it's back to showing that the second function was
executed for each matching record of the first.

# SELECT version();
version

----------------------------------------------------------------------------------------------------------
PostgreSQL 8.2.4 on x86_64-redhat-linux-gnu, compiled by GCC gcc (GCC)
4.1.1 20070105 (Red Hat 4.1.1-52)
(1 row)

-- Dante

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

"D. Dante Lorenso" <dante@lorenso.com> writes:
> This format causes the function to execute too many times:

> SELECT COUNT(*) AS result
> FROM credit_card
> WHERE card_number_enc = cc_encrypt('4111-1111-1111-1111', 'pwd')
> AND card_number_enc != cc_encrypt('4111111111111111', 'pwd');

If the function is marked immutable that query should certainly evaluate
it only twice.

I notice that the EXPLAINs show it as credit_card_encrypt() not
cc_encrypt() --- maybe you got confused about which function you
were adjusting the properties of?

regards, tom lane

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Tom Lane wrote:
> "D. Dante Lorenso" <dante@lorenso.com> writes:
>> This format causes the function to execute too many times:
>
>> SELECT COUNT(*) AS result
>> FROM credit_card
>> WHERE card_number_enc = cc_encrypt('4111-1111-1111-1111', 'pwd')
>> AND card_number_enc != cc_encrypt('4111111111111111', 'pwd');
>
> If the function is marked immutable that query should certainly evaluate
> it only twice.
>
> I notice that the EXPLAINs show it as credit_card_encrypt() not
> cc_encrypt() --- maybe you got confused about which function you
> were adjusting the properties of?

Sorry I was trying to shorten the function name to help with email
wrapping. It was credit_card_encrypt. I didn't do reps in all places.

So, that not being the problem, any ideas? Is it an 8.2.4 thing?

-- Dante

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Tom Lane wrote:
> "D. Dante Lorenso" <dante@lorenso.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




--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

"D. Dante Lorenso" <dante@lorenso.com> writes:
> Tom Lane wrote:
>> 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.

So far as I can tell, it's a problem with having declared the function
STABLE. You want IMMUTABLE if you're hoping to have this usage folded
to a constant.

regards, tom lane

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general