to_char not IMMUTABLE?

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/

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 of
to_char() as stable, not immutable, because their results depend on
lc_numeric; this is a longstanding oversight. We cannot force
initdb for this in the back branches, but we can at least provide
correct catalog entries for future installations.

2006-11-28 14:18 tgl

* src/include/catalog/pg_proc.h: Mark to_char(timestamp without
timezone) as stable, not immutable, since its result now depends on
the lc_messages setting, as noted by Bruce. Also, mark to_number()
and the numeric-type variants of to_char() as stable, because their
results depend on lc_numeric; this is a longstanding oversight.
Also, mark to_date() and to_char(interval) as stable; although
these appear not to depend on any GUC variables as of CVS HEAD,
that seems a property unlikely to survive future improvements. It
seems best to mark all the formatting functions stable and be done
with it. catversion not bumped, because this does not seem
critical enough to force a post-RC1 initdb, and anyway we cannot do
so 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