Обсуждение: BUG #17687: Session timezone change does not play well with prepared statements
BUG #17687: Session timezone change does not play well with prepared statements
От
PG Bug reporting form
Дата:
The following bug has been logged on the website: Bug reference: 17687 Logged by: Bart Lengkeek Email address: bart@lengkeek.net PostgreSQL version: 15.1 Operating system: Linux (Ubuntu 20.04.5 LTS) Description: When a statement is prepared using one session timezone and executed using another, it returns the wrong timestamp. Example using psql: ===================================================== psql (15.1 (Ubuntu 15.1-1.pgdg20.04+1)) postgres=# SET TIMEZONE TO 'Europe/Berlin'; SET postgres=# PREPARE test AS SELECT '2021-03-15 14:10:03'::timestamptz; PREPARE postgres=# SET TIMEZONE TO 'America/Chicago'; SET postgres=# EXECUTE test; timestamptz ------------------------ 2021-03-15 08:10:03-05 (1 row) postgres=# SELECT '2021-03-15 14:10:03'::timestamptz; timestamptz ------------------------ 2021-03-15 14:10:03-05 (1 row) ===================================================== The "EXECUTE test" should return the same as the unprepared execution of the same statement, "2021-03-15 14:10:03-05" instead of "2021-03-15 08:10:03-05". The same behavior happens for protocol level prepared statements. That's actually how I found out about this strange behavior. It also applies to server version 13.9. Thnx for looking into this.
PG Bug reporting form <noreply@postgresql.org> writes: > When a statement is prepared using one session timezone and executed using > another, it returns the wrong timestamp. > Example using psql: > postgres=# SET TIMEZONE TO 'Europe/Berlin'; > SET > postgres=# PREPARE test AS > SELECT '2021-03-15 14:10:03'::timestamptz; > PREPARE > postgres=# SET TIMEZONE TO 'America/Chicago'; > SET > postgres=# EXECUTE test; > timestamptz > ------------------------ > 2021-03-15 08:10:03-05 > (1 row) That is not a bug, it's just how timestamptz works. You'd get the same behavior if you stored the value into a table and then read it out under another timezone setting. (Internally, the reason is that the timestamptz literal is reduced to a Const on sight, and stored that way in the prepared statement.) regards, tom lane