Thread: to_char not IMMUTABLE?

I had a problem when upgrading a database from 8.1.4 to 8.2.1:
Sorry, the error messages are in german.

pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 1531; 1259 3477393 INDEX idx_inspektionen_dat_inspektion 
pg_restore: [archiver (db)] could not execute query: FEHLER:  Funktionen im Indexausdruck muessen als IMMUTABLE
markiertsein   Command was: CREATE INDEX idx_inspektionen_dat_inspektion ON inspektionen USING btree
(to_char(dat_inspektion,'yyyy'::text));
 
WARNING: errors ignored on restore: 1

to_char(timestamp, 'yyyy') should be constant and marked immutable, or am I wrong here? Or is it not marked immutable
becauseof possible changes on date_format?
 

Regards,Mario Weilguni

On Fri, Jan 12, 2007 at 11:55:07AM +0100, Mario Weilguni wrote:
> to_char(timestamp, 'yyyy') should be constant and marked immutable,
> or am I wrong here? Or is it not marked immutable because of possible
> changes on date_format?

AIUI, to_char is not immutable because it can be effected by external
variables, like LC_TIME.

As it is though, I'm not sure why you're using to_char here, surely
extract or date_truc would be more appropriate?

Have a nice day,
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to litigate.

Am Freitag, 12. Januar 2007 14:48 schrieb Martijn van Oosterhout:
> On Fri, Jan 12, 2007 at 11:55:07AM +0100, Mario Weilguni wrote:
> > to_char(timestamp, 'yyyy') should be constant and marked immutable,
> > or am I wrong here? Or is it not marked immutable because of possible
> > changes on date_format?
>
> AIUI, to_char is not immutable because it can be effected by external
> variables, like LC_TIME.
>
> As it is though, I'm not sure why you're using to_char here, surely
> extract or date_truc would be more appropriate?

Thanks for the info. Changing this to use extract is no real problem, I was 
just curious if this is intendend behaviour.

Best regards,
Mario Weilguni

Mario Weilguni <mweilguni@sime.com> writes:
> Thanks for the info. Changing this to use extract is no real problem, I was 
> just curious if this is intendend behaviour.

From the CVS logs:

2006-11-28 14:18  tgl
* src/include/catalog/: pg_proc.h (REL7_3_STABLE), pg_proc.h(REL7_4_STABLE), pg_proc.h (REL8_1_STABLE),
pg_proc.h(REL8_0_STABLE):Mark to_number() and the numeric-type variants ofto_char() as stable, not immutable, because
theirresults depend onlc_numeric; this is a longstanding oversight.  We cannot forceinitdb for this in the back
branches,but we can at least providecorrect catalog entries for future installations.
 

2006-11-28 14:18  tgl
* src/include/catalog/pg_proc.h: Mark to_char(timestamp withouttimezone) as stable, not immutable, since its result now
dependsonthe lc_messages setting, as noted by Bruce.  Also, mark to_number()and the numeric-type variants of to_char()
asstable, because theirresults depend on lc_numeric; this is a longstanding oversight. Also, mark to_date() and
to_char(interval)as stable; althoughthese appear not to depend on any GUC variables as of CVS HEAD,that seems a
propertyunlikely to survive future improvements.    Itseems best to mark all the formatting functions stable and be
donewithit.  catversion not bumped, because this does not seemcritical enough to force a post-RC1 initdb, and anyway we
cannotdoso in the back branches.
 
        regards, tom lane

Mario Weilguni wrote:
>
> I had a problem when upgrading a database from 8.1.4 to 8.2.1:
> Sorry, the error messages are in german.
>
> pg_restore: [archiver (db)] Error while PROCESSING TOC:
> pg_restore: [archiver (db)] Error from TOC entry 1531; 1259
> 3477393 INDEX idx_inspektionen_dat_inspektion
> pg_restore: [archiver (db)] could not execute query: FEHLER:
> Funktionen im Indexausdruck muessen als IMMUTABLE markiert sein
>     Command was: CREATE INDEX idx_inspektionen_dat_inspektion
> ON inspektionen USING btree (to_char(dat_inspektion, 'yyyy'::text));
> WARNING: errors ignored on restore: 1
>
> to_char(timestamp, 'yyyy') should be constant and marked
> immutable, or am I wrong here? Or is it not marked immutable
> because of possible changes on date_format?

At some point, the configuration parameter lc_time should have
an influence on the output of to_char(timestamp, text), although
this behaviour is not yet implemented.

I guess that is why the function is STABLE ind not IMMUTABLE.

Maybe you can use date_part('YEAR', dat_inspektion)::bpchar

Yours,
Laurenz Albe