Re: Dangers of mislabelled immutable functions - Mailing list pgsql-general

From Albe Laurenz
Subject Re: Dangers of mislabelled immutable functions
Date
Msg-id A737B7A37273E048B164557ADEF4A58B50F946E3@ntex2010i.host.magwien.gv.at
Whole thread Raw
In response to Dangers of mislabelled immutable functions  (Jeff Janes <jeff.janes@gmail.com>)
List pgsql-general
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

pgsql-general by date:

Previous
From: Igor Sosa Mayor
Date:
Subject: Problem with pl/python procedure connecting to the internet
Next
From: John R Pierce
Date:
Subject: Re: Problem with pl/python procedure connecting to the internet