Re: problem with timezone parsing

On Wed, Feb 18, 2009 at 08:36:25AM +0900, James Henstridge wrote:

This code:

> > def convert_ts_with_odd_tz(string_value, cursor):
> >        try:
> >                return dbapi.DATETIME(string_value, cursor)
> >        except dbapi.DataError:
> >                _log.error('unable to parse [%s] as <timestamp with time zone>', string_value)
> >                if regex.match('(\+|-)\d\d:\d\d:\d\d', string_value[-9:]) is not None:
> >                        # parsing doesn't succeed even if seconds
> >                        # are ":00" so truncate in any case
> >                        _log.debug('time zone with seconds detected (true local time ?)')
> >                        adjusted_string_value = string_value[:-3]
> >                        _log.warning('truncating to [%s] and trying again', adjusted_string_value)
> >                        _log.warning('value will be off by %s seconds', string_value[-2:])
> >                        return dbapi.DATETIME(adjusted_string_value, cursor)
> >                raise
> >
> > DT_W_ODD_TZ = psycopg2.extensions.new_type(dbapi.DATETIME.values, 'DT_W_ODD_TZ', convert_ts_with_odd_tz)
> > psycopg2.extensions.register_type(DT_W_ODD_TZ)

does not quite work well enough. Due to the
"new_type(dbapi.DATETIME.values" it will register the new
type for all OIDs in that set. Now, the new caster first of
all refers all input to the old one so it should work just
fine one might think. However, it does not - it fails for,
say, intervals (the OID of which are in the set) which is
rather strange. If I remove the new type cast or narrow down
the OID range for it things work normally for those input
types.

The narrowed down code goes like this:

# ----------------------------------------------------------------------
# PostgreSQL -> Python
# ----------------------------------------------------------------------

# We need this because some places once used time "zones"
# with true local time, IOW having seconds in the UTC offset.
# The Python datetime zone code cannot handle that, however,
# which makes psycopg2 fail when loading timestamps with such
# time zones from the backend ...
# So we (almost silently) drop the seconds and try again.
def convert_ts_with_odd_tz(string_value, cursor):
	try:
		return dbapi.DATETIME(string_value, cursor)
	except (dbapi.DataError,), exc:
		_log.error('unable to parse [%s]', string_value)

		if exc.message != "unable to parse time":
			raise

		_log.debug('unable to parse as <timestamp with time zone>')

		if regex.match('(\+|-)\d\d:\d\d:\d\d', string_value[-9:]) is None:
			raise

		# parsing doesn't succeed even if seconds
		# are ":00" so truncate in any case
		_log.debug('time zone with seconds detected (true local time ?): %s', string_value[-9:])
		truncated_string_value = string_value[:-3]
		_log.warning('truncating to [%s] and trying again', truncated_string_value)
		_log.warning('value will be off by %s seconds', string_value[-2:])
		return dbapi.DATETIME(truncated_string_value, cursor)


TIMESTAMPTZ_OID = 1184		# taken from PostgreSQL headers
if TIMESTAMPTZ_OID not in dbapi.DATETIME.values:
	raise ImportError('TIMESTAMPTZ_OID <1184> not in psycopg2.DATETIME.values [%s]' % dbapi.DATETIME.values)

#DT_W_ODD_TZ = psycopg2.extensions.new_type(dbapi.DATETIME.values, 'DT_W_ODD_TZ', convert_ts_with_odd_tz)
DT_W_ODD_TZ = psycopg2.extensions.new_type((TIMESTAMPTZ_OID,), 'DT_W_ODD_TZ', convert_ts_with_odd_tz)
psycopg2.extensions.register_type(DT_W_ODD_TZ)



(note that psycopg2 is imported as dbapi and re as regex)

This reduces the coverage of the new caster to TIMESTAMPTZ.
The drawback, however, is that that OID is now hardcoded
(problems with which will show up early due to the check).

Frederico said, that using DATETIME.values is safe because
the OIDs are taken from my PostgreSQL headers. But what
happens if locally I have PG 8.1 installed while the remote
server runs PG 8.3 (and assuming the OIDs happen to not be
identical) ?

Karsten
-- 
GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD  4537 78B9 A9F9 E407 1346