If I don't specify time zone either in config or using "SET TimeZone"
first, "SET TimeZone" inside a transaction isn't rolled back. System
time zone is actually Europe/Moscow. Here's an example:

fduch@~=# SELECT current_setting('TimeZone'), now();
current_setting | now
-----------------+-------------------------------
unknown | 2005-02-14 15:03:41.972955+03
(1 row)

fduch@~=# BEGIN ; SET TimeZone TO 'America/New_York'; SELECT current_setting('TimeZone'), now();
BEGIN
SET
current_setting | now
------------------+-------------------------------
America/New_York | 2005-02-14 07:03:46.982974-05
(1 row)

fduch@~*=# ROLLBACK ; SELECT current_setting('TimeZone'), now();
ROLLBACK
current_setting | now
------------------+------------------------------
America/New_York | 2005-02-14 07:03:51.34979-05
(1 row)


However, with explicit initial time zone, it get rolled back okay:

fduch@~=# SET TimeZone TO 'Europe/Moscow'; SELECT current_setting('TimeZone'), now();
SET
current_setting | now
-----------------+-------------------------------
Europe/Moscow | 2005-02-14 15:03:55.552894+03
(1 row)

fduch@~=# BEGIN ; SET TimeZone TO 'America/New_York'; SELECT current_setting('TimeZone'), now();
BEGIN
SET
current_setting | now
------------------+-------------------------------
America/New_York | 2005-02-14 07:03:59.891249-05
(1 row)

fduch@~*=# ROLLBACK ; SELECT current_setting('TimeZone'), now();
ROLLBACK
current_setting | now
-----------------+------------------------------
Europe/Moscow | 2005-02-14 15:04:06.67614+03
(1 row)

fduch@~=# SELECT version();
version
------------------------------------------------------------------------------------------------
PostgreSQL 7.4.6 on i386-portbld-freebsd5.3, compiled by GCC cc (GCC) 3.4.2 [FreeBSD] 20040728
(1 row)

Comments?


--
Fduch M. Pravking

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo (AT) postgresql (DOT) org)

"Alexander M. Pravking" <fduch (AT) antar (DOT) bryansk.ru> writes:
This should work OK in 8.0. Pre-8.0 it's fundamentally unfixable:
if we cannot see a TZ environment setting, we have no idea what zone the
system timezone library has as default, or how to get it to revert to that.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match