Dangers of mislabelled immutable functions
I want to index the textual representations of a table's rows.
You can cast a row to text by using the name of the table where you would
usually use the name of a column, like "table_name::text". But this is not
immutable and so can't be used in an expression index.
I can easily make a pl/perl function which does the same thing as the text
cast, and label it as immutable and use it in an index. But are the
dangers of doing this?
I know that configuration changes to the date and time formats could change
the way that the data is shown in ways that would cause the rows inserted
under a different configuration to be missed. That doesn't bother me.
I know that if I drop a table column, the contents of the column will still
be in the index. That doesn't bother me either, as all the indexes I plan
to use are lossy and so will do a recheck anyway.
What other problems am I setting myself up for by doing this?
Cheers,
Jeff
Jeff Janes wrote:
I want to index the textual representations of a table's rows.
You can cast a row to text by using the name of the table where you would usually use the name of a
column, like "table_name::text". But this is not immutable and so can't be used in an expression
index.I can easily make a pl/perl function which does the same thing as the text cast, and label it as
immutable and use it in an index. But are the dangers of doing this?I know that configuration changes to the date and time formats could change the way that the data is
shown in ways that would cause the rows inserted under a different configuration to be missed. That
doesn't bother me.
That's exactly the danger: your index will become corrupted if the function turns out not to
be immutable after all. So if you know that such configuration changes will not happen,
you can safely do that.
But how do you keep people from running "SET DateStyle=..."?
I know that if I drop a table column, the contents of the column will still be in the index. That
doesn't bother me either, as all the indexes I plan to use are lossy and so will do a recheck anyway.
I don't understand.
If you drop a column, all indexes that depend on that column will be dropped as well.
Yours,
Laurenz Albe
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general