I have found what is causing the crash described in Bug 1500. Now I
would like to fix it, but need opinions about what is the correct behaviour.
The bug can be easily duplicated when formatting interval in to_char()
using 'Mon' or 'Month' in the format string.
select to_char(now() - '20011001'::date, 'YYYYMonDD');
(server process crash follows)
1. The formatting function used is dch_date()
(src/backend/utils/adt/formatting.c) and it works on struct pg_tm.
2. The interval2tm() (src/backend/utils/adt/timestamp.c) is used to
convert the interval into pg_tm struct.
2a. If the Interval parameter has month != 0, then month and year are
filled in pg_tm
2b. If not -> they are set to 0 and only days, hours, minutes, seconds
are filled (this is the case when the bug appears).
3. dch_date() expects the struct pg_tm to have valid 1-based month index
and directly references the months/months_full arrays using (tm->month -
1) as index to get the short/full name of the month.
4. SIGSEGV in the server process
This could be easily by not allowing the bad array indexing, but it
raises a bigger problem: How is supposed the to_char() function to
format interval datatype? What is the correct output?
1) Try to fill the missing data (years, months) using the days (but how
many days are in one month? hardcode 30/31? how many days in 1 year
then...) and fix the formatting function to ignore string based
formatting for intervals
2) Fail the entire statement (do not support interval formatting with
Also the general to_char() Interval formatting seems broken anyway.
Note that the following (and similar) works now, but the result doesn't
seem to be correct:
test=> select to_char(now() - '20011001'::date, 'YYYYDD');
test=> select now() - '20011001'::date;
1271 days 12:48:18.1216260046
So this bug actually brings the issue of interval to_char() formatting.