BUG #4421: convert_to() should be immutable

andipeer@gmx.net wrote:

PostgreSQL version: 8.3.3
Operating system: Linux
Description: convert_to() should be immutable
Details:

The function convert_to(string text, dest_encoding name) is not allowed to
be used in a index expression, because it is not marked as "IMMUTABLE".
According to the documentation, a function is immutable if it does not
modify the database, and for the same arguments, it returns always the same
results. I think that all of these conditions can be applied to the
convert_to()-function, therefore it should be marked as "IMMUTABLE".

You can change the way a conversion is done with CREATE/DROP CONVERSION.
That's why it can't be IMMUTABLE.

(I doubt any sane person would actually do that, but that's another debate)

--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com

Heikki Linnakangas <heikki.linnakangas@enterprisedb.com> writes:

andipeer@gmx.net wrote:

The function convert_to(string text, dest_encoding name) is not allowed to
be used in a index expression, because it is not marked as "IMMUTABLE".

You can change the way a conversion is done with CREATE/DROP CONVERSION.
That's why it can't be IMMUTABLE.

The other reason is that it depends on the database encoding. I suppose
you could make an argument that that's fixed for as long as IMMUTABLE
needs to think about --- but we'd have to remember to undo the marking
if database encoding ever becomes less fixed.

Just out of curiosity, what's the use-case for this function in an index
anyway?

regards, tom lane

Tom Lane wrote:

Heikki Linnakangas <heikki.linnakangas@enterprisedb.com> writes:

andipeer@gmx.net wrote:

The function convert_to(string text, dest_encoding name) is not allowed to
be used in a index expression, because it is not marked as "IMMUTABLE".

You can change the way a conversion is done with CREATE/DROP CONVERSION.
That's why it can't be IMMUTABLE.

The other reason is that it depends on the database encoding. I suppose
you could make an argument that that's fixed for as long as IMMUTABLE
needs to think about --- but we'd have to remember to undo the marking
if database encoding ever becomes less fixed.

Just out of curiosity, what's the use-case for this function in an index
anyway?

regards, tom lane

Thank you all for the responses!
Well, the use case is a strange one... I would like to use a varchar()
column for storing a variable-length vector of integers. The numbers are
represented by the codepoints. Therefore, I need to sort them as binary
data, not as characters. I would often need to get all the vectors that
lie in between to vectors, therefore I need the "binary" index.
And the code should be as database independent as possible, therefore I
cannot use an array or another data type that may not be supported by
other DBMS.

Regards,
Andreas Peer

Andreas Peer wrote:

Well, the use case is a strange one... I would like to use a varchar()
column for storing a variable-length vector of integers. The numbers are
represented by the codepoints. Therefore, I need to sort them as binary
data, not as characters. I would often need to get all the vectors that
lie in between to vectors, therefore I need the "binary" index.
And the code should be as database independent as possible, therefore I
cannot use an array or another data type that may not be supported by
other DBMS.

What makes you think messing around with encoding conversions is going
to be portable. Arrays are at least in the SQL standard.

Andreas Peer <andipeer@gmx.net> writes:

Well, the use case is a strange one... I would like to use a varchar()
column for storing a variable-length vector of integers. The numbers are
represented by the codepoints. Therefore, I need to sort them as binary
data, not as characters. I would often need to get all the vectors that
lie in between to vectors, therefore I need the "binary" index.

Use bytea maybe?

And the code should be as database independent as possible, therefore I
cannot use an array or another data type that may not be supported by
other DBMS.

I can't imagine how you'd think that a functional index on convert_to()
would be the most portable solution ...

regards, tom lane

Tom Lane schrieb:

Andreas Peer <andipeer@gmx.net> writes:

Well, the use case is a strange one... I would like to use a varchar()
column for storing a variable-length vector of integers. The numbers are
represented by the codepoints. Therefore, I need to sort them as binary
data, not as characters. I would often need to get all the vectors that
lie in between to vectors, therefore I need the "binary" index.

Use bytea maybe?

Than I cannot access the single characters anymore (or I just use bytes,
but that limits a number to 256...)

And the code should be as database independent as possible, therefore I
cannot use an array or another data type that may not be supported by
other DBMS.

I can't imagine how you'd think that a functional index on convert_to()
would be the most portable solution ...

nearly every DBMS has a function for converting character strings to
binary strings, I would just have to change the function
name/parameters, but the rest of the query could be the same

regards, tom lane

Regards,
Andreas Peer