Обсуждение: BUG #4421: convert_to() should be immutable
The following bug has been logged online: Bug reference: 4421 Logged by: Email address: andipeer@gmx.net 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".
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