The following bug has been logged online:

Bug reference: 2487
Logged by: Pedro J. Romero
Email address: multiacademia (AT) eresmas (DOT) com
PostgreSQL version: 8.0.1
Operating system: Linux
Description: Immutable functions results
Details:

Sorry if this not a bug, but I think so:

Look at this, I think this must be self explanatory, after two or three
readings:

select * from tablea;
fielda
------
16
(1 row)

select * from childtablea;
fielda|data_a
------|----------
16| 101
16| 127
16| 4315
(3 rows)

select fielda, otherfunction('childtablea', 'fielda', fielda) from tablea;

--otherfunction is not marked as immutable, it cannot
--So I cannot use it for index

fielda|otherfunction
------|-----------------
16|101-127-4315
(1 row)

select fielda, immutablefunction_a(fielda) from tablea;

fielda|immutablefunction_a
------|-------------------
16|101-127-4315
(1 row)


select otherfunction('childtablea', 'fielda', fielda),
immutablefunction_a(fielda), otherfunction('childtablea', 'fielda',
fielda)=immutablefunction_a(fielda) as are_the_same from tablea;

otherfunction|immutablefunction_a|are_the_same
-------------|-------------------|------------
101-127-4315 |101-127-4315 |t

--===========================================
--===========================================


--Same schema, same data, same database, other tables

select * from tableb;
fieldb
------
16
(1 row)

select * from childtableb;
fieldb|data_b
------|----------
16| 101
16| 127
16| 4315
(3 rows)

select fieldb,otherfunction('childtableb', 'fieldb', fieldb) from tableb;

fieldb|otherfunction
------|-----------------
16|101-127-4315
(1 row)

select fieldb, immutablefunction_b(fieldb) from tableb;

fieldb|immutablefunction
------|-----------------
16|101-127-4315
(1 row)


select otherfunction('childtableb', 'fieldb', fieldb),
immutablefunction_b(fieldb), otherfunction('childtableb', 'detailb',
fieldb)=immutablefunction_b(fieldb) as are_the_same from tableb;

otherfunction|immutablefunction_b|are_the_same
-------------|-------------------|------------
101-127-4315 |101-127-4315 |t

--===========================================
--===========================================

Ok, until this....

But....

vacuum full tablea;

vacuum full tableb;

select fielda, fieldb, otherfunction('childtablea', 'fielda', fielda) from
tablea left join tableb on otherfunction('childtablea', 'fielda',
fielda)=otherfunction('childtableb', 'fieldb', fieldb);

fielda|fieldb|otherfunction
------|------|-------------
16| 16|101-127-4315
(0 rows)


select fielda, fieldb from tablea left join tableb on
immutablefunction_a(fielda)=immutablefunction_b(fi eldb);

fielda|fieldb
------|------
(0 rows)

vacuum full tablea;

vacuum full tableb;

select fielda, fieldb from tablea left join tableb on
immutablefunction_a(fielda)=immutablefunction_b(fi eldb);

fielda|fieldb
------|------
(0 rows)



Why? If I cannot use the immutable function, performance is really bad.
Using an index, speed is several times greater.

Is this a bug? Or I'm missing something about the immutable functions
characteristics?

Thank you very much.

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

"Pedro J. Romero" <multiacademia (AT) eresmas (DOT) com> writes:
This report is useless, since you have not shown us either function.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

Dear Tom:

Sorry, but I think you can see the problem without the definition of the
function. If after this message you continue thinking you need the source of
the function I will send it to the list. I don't want to extend so much the
message.

There is a master table (tablea) and a detail table (childtablea). With
"otherfunction" I want to retrieve in a string all the values related with a
row in the master table, separated by a "-".

select otherfunction('childtablea', 'fielda', fielda) from tablea;

So, otherfunction takes three parameters, the first is the name of a detail
table (childtablea), the second is the name of a foreign key in that table
(fielda) which references a primary key (also called fielda) in a master
table (tablea), and the third is the value of the foreign key I'm looking
for.

"immutablefunction_a" and "immutablefunction_b" are both wrappers functions
for "otherfunction", because I can't create a functional index over a
function with static arguments.... (This is true, isn't it?????)

So, the definition of immutablefunction_a is:

create function immutablefunction_a(int) returns text as '
begin
return otherfunction('childtablea','fielda', $1);
end;' language plpgsql immutable;

create function immutablefunction_b(int) returns text as '
begin
return otherfunction('childtableb','fieldb', $1);
end;' language plpgsql immutable;

So, the "bug" is that the when the result for immutablefunction_a(10) is,
for example, "10-20-30" and the result for immutablefunction_b(30) is
"10-20-30", and if this is used in the join clause, records are not
joined....

I hope you can understand me, I know is a little complicated schema....

Thank you very much.

P.D.: Tom, is the first time I speak to you, and I want to express my
congratulations for the work you and your folks are making. I'm very pleased
with the product.

-----Mensaje original-----
De: Tom Lane [mailto:tgl (AT) sss (DOT) pgh.pa.us]
Enviado el: jueves, 22 de junio de 2006 16:55
Para: Pedro J. Romero
CC: pgsql-bugs (AT) postgresql (DOT) org
Asunto: Re: [BUGS] BUG #2487: Immutable functions results

"Pedro J. Romero" <multiacademia (AT) eresmas (DOT) com> writes:
This report is useless, since you have not shown us either function.

regards, tom lane




---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings