[BUGS] Strange time zone +00:53:28
Hi! Recently, we switched from Pg 8.3 to Pg 9.1, using Pg9's pg_upgrade to migrate our cluster. The cluster itself is running on a Debian 6 machine with timezone Europe/Berlin, which means UTC+02 currently. Under Pg 8.3, we used the timestamp 0001-01-01 00:00:00+01 as an easy-to-remember marker for 'dunno, but predates any usual business dates' for fields of type timestamp with time zone. With Pg 9.1, these timestamps now appear as 0001-12-31 23:53:28+00:53:28 BC (using a current psql shell in both cases). If I try to reset the timestamp, eg by update testtable set ts='0001-01-01'; the result is 0001-01-01 00:00:00+00:53:28 Specifying the time explicitly update testtable set ts='0001-01-01 00:00:00+01:00:00'; results in 0001-12-31 23:53:28+00:53:28 BC again, hence I'd suspect that somehow the textual representation of such timestamps is broken?! Any ideas? Is this a known bug (I didn't find), maybe fixed in 9.2? Karsten -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] Strange time zone +00:53:28
On pią, maj 10, 2013 at 10:34:02 +0200, Karsten Düsterloh wrote: Under Pg 8.3, we used the timestamp 0001-01-01 00:00:00+01 as an easy-to-remember marker for 'dunno, but predates any usual business dates' for fields of type timestamp with time zone. Why didn't you use -infinity for this? Any ideas? Is this a known bug (I didn't find), maybe fixed in 9.2? It's not a bug. Berlin timezone that far away has such not-round offset. In time zone database you can find: # Zone NAMEGMTOFF RULES FORMAT [UNTIL] ZoneEurope/Berlin 0:53:28 - LMT 1893 Apr 1:00C-Eur CE%sT 1945 May 24 2:00 1:00 SovietZone CE%sT 1946 1:00Germany CE%sT 1980 1:00EU CE%sT Best regards, depesz -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] Strange time zone +00:53:28
hubert depesz lubaczewski wrote: Under Pg 8.3, we used the timestamp 0001-01-01 00:00:00+01 as an easy-to-remember marker for 'dunno, but predates any usual business dates' for fields of type timestamp with time zone. Why didn't you use -infinity for this? Back when that value was chosen, we had some legacy software around which did not handle that value well. Definitely worth reevaluating. OTOH, Delphi4 + Win98 + ODBC is definitely choking on '0001-12-31 23:53:28+00:53:28 BC'. ;-) It's not a bug. Berlin timezone that far away has such not-round offset. In time zone database you can find: # Zone NAMEGMTOFF RULES FORMAT [UNTIL] ZoneEurope/Berlin 0:53:28 - LMT 1893 Apr Thanks! That does explain the number as such (I somehow only found the Sicily stuff mentioned further down in the time zone database file), why it's popping up now and that's just a display problem. The question remains, though, where this special value comes from … Karsten -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] Strange time zone +00:53:28
Karsten Düsterloh pg-bugs...@tal.de wrote hubert depesz lubaczewski wrote: ZoneEurope/Berlin 0:53:28 - LMT 1893 Apr The question remains, though, where this special value comes from ? That was probably the local time at Berlin, with 12:00 the moment when the sun was highest. With the advent of the railways, time started to get unified. Railways in Prussia used Berlin time from 1848 up to 1893, when the time was changed by law: http://de.wikipedia.org/wiki/Gesetz_betreffend_die_Einf%C3%BChrung_einer_einheitlichen_Zeitbestimmung http://zeitzonen.net/html/geschichte.html Best regards, Curd
Re: [BUGS] Strange time zone +00:53:28
=?ISO-8859-1?Q?Karsten_D=FCsterloh?= pg-bugs...@tal.de writes: Under Pg 8.3, we used the timestamp 0001-01-01 00:00:00+01 as an easy-to-remember marker for 'dunno, but predates any usual business dates' for fields of type timestamp with time zone. Have you considered using '-infinity'? With Pg 9.1, these timestamps now appear as 0001-12-31 23:53:28+00:53:28 BC This is not a bug. I refer you to the IANA timezone database's entry for Europe/Berlin: # Zone NAMEGMTOFF RULES FORMAT [UNTIL] ZoneEurope/Berlin 0:53:28 - LMT 1893 Apr 1:00C-Eur CE%sT 1945 May 24 2:00 1:00 SovietZone CE%sT 1946 1:00Germany CE%sT 1980 1:00EU CE%sT which says that timekeeping before April 1893 was done according to local mean solar time, 53:28 east of Greenwich; so a timestamp specified as midnight GMT+1 comes out as 23:53:28 local time. Now, I agree that it's somewhat debatable to extend that rule clear back to 1 AD; but it's more sensible than believing that local time would ever have been taken as exactly GMT+1 before the days of standardized timezones. The only reason 8.3 and before didn't do what you're seeing is they were incapable of applying timezone rules outside the range of 32-bit time_t (ie, back to about 1901). We fixed that code to be 64-bit, and now it does what the timezone definition says. If you're inextricably wedded to using '0001-01-01 00:00:00+01', you might consider building yourself a custom timezone database that has an entry defined the way you want. But personally I'd recommend changing to something less randomly chosen. regards, tom lane -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] Inconsistency between TO_CHAR() and TO_NUMBER()
Patryk Kordylewski p...@fooby.de writes:
SET lc_numeric TO 'de_DE.UTF-8';
SET
SELECT
TO_CHAR(123456789.123, 'FM99G999G999G999G999G999G999D000'),
TO_NUMBER(TO_CHAR(123456789.123, 'FM99G999G999G999G999G999G999D000'),
'FM99G999G999G999G999G999G999D000');
to_char | to_number
-+---
123.456.789,123 | 123.456
(1 row)
I looked into this, and find that the reason it misbehaves is that
NUM_numpart_from_char() will treat a '.' as being a decimal point
*without any regard to locale considerations*. So even if we have
a locale-dependent format string and a locale that says '.' is a
thousands separator, it does the wrong thing.
It's a bit surprising nobody's complained of this before.
I propose the attached patch. I'm slightly worried though about whether
this might break any existing applications that are (incorrectly)
depending on a D format specifier being able to match '.' regardless of
locale. Perhaps we should only apply this to HEAD and not back-patch?
regards, tom lane
diff --git a/src/backend/utils/adt/formatting.c b/src/backend/utils/adt/formatting.c
index db5dfca51d477d3e9b33b8d2c264495b3b2ec433..81e3329ef60ce4f835fedba50208b8d0f4b19d63 100644
*** a/src/backend/utils/adt/formatting.c
--- b/src/backend/utils/adt/formatting.c
*** NUM_numpart_from_char(NUMProc *Np, int i
*** 4131,4137
#endif
/*
! * read digit
*/
if (isdigit((unsigned char) *Np-inout_p))
{
--- 4131,4137
#endif
/*
! * read digit or decimal point
*/
if (isdigit((unsigned char) *Np-inout_p))
{
*** NUM_numpart_from_char(NUMProc *Np, int i
*** 4151,4190
#ifdef DEBUG_TO_FROM_CHAR
elog(DEBUG_elog_output, Read digit (%c), *Np-inout_p);
#endif
-
- /*
- * read decimal point
- */
}
else if (IS_DECIMAL(Np-Num) Np-read_dec == FALSE)
{
#ifdef DEBUG_TO_FROM_CHAR
! elog(DEBUG_elog_output, Try read decimal point (%c), *Np-inout_p);
#endif
! if (*Np-inout_p == '.')
{
*Np-number_p = '.';
Np-number_p++;
Np-read_dec = TRUE;
isread = TRUE;
}
- else
- {
- int x = strlen(Np-decimal);
-
- #ifdef DEBUG_TO_FROM_CHAR
- elog(DEBUG_elog_output, Try read locale point (%c),
- *Np-inout_p);
- #endif
- if (x AMOUNT_TEST(x) strncmp(Np-inout_p, Np-decimal, x) == 0)
- {
- Np-inout_p += x - 1;
- *Np-number_p = '.';
- Np-number_p++;
- Np-read_dec = TRUE;
- isread = TRUE;
- }
- }
}
if (OVERLOAD_TEST)
--- 4151,4178
#ifdef DEBUG_TO_FROM_CHAR
elog(DEBUG_elog_output, Read digit (%c), *Np-inout_p);
#endif
}
else if (IS_DECIMAL(Np-Num) Np-read_dec == FALSE)
{
+ /*
+ * We need not test IS_LDECIMAL(Np-Num) explicitly here, because
+ * Np-decimal is always just . if we don't have a D format token.
+ * So we just unconditionally match to Np-decimal.
+ */
+ int x = strlen(Np-decimal);
+
#ifdef DEBUG_TO_FROM_CHAR
! elog(DEBUG_elog_output, Try read decimal point (%c),
! *Np-inout_p);
#endif
! if (x AMOUNT_TEST(x) strncmp(Np-inout_p, Np-decimal, x) == 0)
{
+ Np-inout_p += x - 1;
*Np-number_p = '.';
Np-number_p++;
Np-read_dec = TRUE;
isread = TRUE;
}
}
if (OVERLOAD_TEST)
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] Inconsistency between TO_CHAR() and TO_NUMBER()
Tom Lane wrote on 10.05.2013 17:49: I looked into this, and find that the reason it misbehaves is that NUM_numpart_from_char() will treat a '.' as being a decimal point *without any regard to locale considerations*. So even if we have a locale-dependent format string and a locale that says '.' is a thousands separator, it does the wrong thing. It's a bit surprising nobody's complained of this before. I propose the attached patch. I'm slightly worried though about whether this might break any existing applications that are (incorrectly) depending on a D format specifier being able to match '.' regardless of locale. Perhaps we should only apply this to HEAD and not back-patch? The manual claims that 'D' is locale dependent (whereas '.' is not), so _theoretically_ a back patch would make sense I guess. -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #8143: Backend segmentation fault in pg_trgm
That was quick. Applied the 91715e82932665 commit directly against the 9.2.4 pgdg source, fix works great. Test data and the original breaking production queries run fine for me. Thank you very much! -joel On May 9, 2013, at 6:19 PM, Tom Lane wrote: jrol...@rjobrien.com writes: We've come across a specific query and query plan that causes a repeatable segmentation fault on the postgresql backend. Ah, I see it: gistrescan() is trying to preserve the per-scankey fn_extra values to allow caching, but what it's doing does not work if more than one scankey refers to the same consistentFn, ie, the same index column. A bit surprising we've not seen this before, because I think that code has been like that for awhile. Will fix, thanks for the report! regards, tom lane -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] Inconsistency between TO_CHAR() and TO_NUMBER()
On 10-05-2013 13:09, Thomas Kellerer wrote: Tom Lane wrote on 10.05.2013 17:49: I looked into this, and find that the reason it misbehaves is that NUM_numpart_from_char() will treat a '.' as being a decimal point *without any regard to locale considerations*. So even if we have a locale-dependent format string and a locale that says '.' is a thousands separator, it does the wrong thing. It's a bit surprising nobody's complained of this before. I propose the attached patch. I'm slightly worried though about whether this might break any existing applications that are (incorrectly) depending on a D format specifier being able to match '.' regardless of locale. Perhaps we should only apply this to HEAD and not back-patch? +1 only in HEAD. That's because (a) it doesn't crash, (b) it doesn't always produce the wrong answer (only in some specific situation) and (c) it has been like that for years without a complain. For those reasons, it is better to continue with this wrong behavior in back branches than prevent important security updates to be applied (without applying a patch to preserve the wrong answer). This argument is only valid for legacy closed-source apps but seems to have more weight than the bug scenario. The manual claims that 'D' is locale dependent (whereas '.' is not), so _theoretically_ a back patch would make sense I guess. I would consider a documentation bug in back branches because fix it means break apps. -- Euler Taveira de Oliveira - Timbira http://www.timbira.com.br/ PostgreSQL: Consultoria, Desenvolvimento, Suporte 24x7 e Treinamento -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
