[Maia-users] "invalid input syntax for type timestamp" - postgresql backend

[Robert LeBlanc]

Marc G. Fournier wrote:
> Damn, I think I did fix this one and didn't post it :(  Posting this to 
> the list at the same time ... not sure why they are trying to format with 
> the string like that, but the %Y is always 4 digit, %m always 2, etc ...
> 
> Try this, in amavisd-maia, maia_store_mail function, around line 3608 ... :
> 
> #    my($received_date) = strftime("%04Y-%02m-%02d %02H:%02M:%02S",localtime);
>      my($received_date) = strftime("%Y-%m-%d %H:%M:%S",localtime);

Thanks, Marc.  If this is the fix for this PostgreSQL issue, though, I'm
left with more questions than answers.

The error messages that PostgreSQL users are reporting seem to occur in
maia_store_mail() when oversized mail items are processed (which
explains why it doesn't happen on /every/ mail item, and why people with
larger size limits set don't encounter this as often).

The error itself, though:

  invalid input syntax for type timestamp: "4Y-2m-2d 2H:2M:2S"

suggests that $received_date is getting set literally to "4Y-2m-2d
2H:2M:2S" rather than the actual timestamp that strftime() should be
producing.  When PostgreSQL tries to insert this value into
newest_oversized_date (or oldest_oversized_date) in the maia_stats
table, it complains and the error is the result.

What's disturbing about this solution is that it implies a problem with
certain versions of the POSIX module, suggesting that the strftime()
subroutine behaves differently under certain implementations.  Running
with v1.07 (Perl 5.8.3) or v1.08 (Perl 5.8.6) of the POSIX module, I see
no difference between "%04Y-%02m-%02d %02H:%02M:%02S" and "%Y-%m-%d
%H:%M:%S"--they both produce identical formatted strings.  But for the
error to have occurred, the strftime() subroutine on your machine had to
have interpreted those two format strings differently somehow.  The
attached script is a little test that compares the output of both format
strings and displays your Perl and POSIX module versions; I'd be very
interested to see the output.

If this turns out to be a Perl/POSIX version issue, then this has
implications for MySQL users as well.  Thus far this has only reared its
ugly head among PostgreSQL users, but clearly trying to store "4Y-2m-2d
2H:2M:2S" as a timestamp in MySQL should fail as well, triggering a
similar error.  Yet there have been no reports of this error or anything
like it from MySQL users.

Going forward, we can stick to "%Y-%m-%d %H:%M:%S", on the assumption
that this is more broadly compatible across different POSIX
implementations (and I agree that specifying field sizes in this case is
redundant), but I'm still left wondering why this only seems to have hit
the PostgreSQL users.  Puzzling...

-- 
Robert LeBlanc <rjl at renaissoft.com>
Renaissoft, Inc.
Maia Mailguard <http://www.maiamailguard.com/>

-------------- next part --------------
A non-text attachment was scrubbed...
Name: datetest.pl
Type: application/x-perl
Size: 513 bytes
Desc: not available
Url : http://www.renaissoft.com/pipermail/maia-users/attachments/20060914/f6043ae5/attachment.bin 
-------------- next part --------------
A non-text attachment was scrubbed...
Name: signature.asc
Type: application/pgp-signature
Size: 189 bytes
Desc: OpenPGP digital signature
Url : http://www.renaissoft.com/pipermail/maia-users/attachments/20060914/f6043ae5/attachment-0001.bin