pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedBUG #19880: hstore(record) expression index returns wrong rows after TimeZone change
2+ messages / 2 participants
[nested] [flat]
* BUG #19880: hstore(record) expression index returns wrong rows after TimeZone change
@ 2026-11-11 13:39 PG Bug reporting form <noreply@postgresql.org>
2026-11-11 15:18 ` Re: BUG #19880: hstore(record) expression index returns wrong rows after TimeZone change Fujii Masao <masao.fujii@gmail.com>
0 siblings, 1 reply; 2+ messages in thread
From: PG Bug reporting form @ 2026-11-11 13:39 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org; +Cc: imchifan@163.com
The following bug has been logged on the website:
Bug reference: 19880
Logged by: Qifan Liu
Email address: imchifan@163.com
PostgreSQL version: 18.6
Operating system: Linux/amd64
Description:
PostgreSQL 18.6 on Linux/amd64 marks hstore(record) and
populate_record(anyelement,hstore) IMMUTABLE even though their results can
depend on TimeZone. With an expression index built under UTC, changing
TimeZone to Europe/Moscow makes a forced index scan return zero rows while
sequential evaluation of the same predicate returns one row. The same hstore
input to populate_record also produces epochs three hours apart.
An IMMUTABLE function must return the same result for the same arguments, so
both converter calls should be independent of TimeZone and indexed
evaluation should agree with sequential evaluation.
How to repeat:
\set ON_ERROR_STOP on
DROP TABLE IF EXISTS hstore_idx;
DROP TYPE IF EXISTS hstore_row;
DROP EXTENSION IF EXISTS hstore;
CREATE EXTENSION hstore;
CREATE TYPE hstore_row AS (ts timestamptz);
SELECT p.oid::regprocedure, p.provolatile
FROM pg_proc AS p
WHERE p.oid IN ('hstore(record)'::regprocedure,
'populate_record(anyelement,hstore)'::regprocedure)
ORDER BY 1::text;
SET TimeZone = 'UTC';
SELECT extract(epoch FROM
(populate_record(NULL::hstore_row,
'"ts"=>"2026-01-01 00:00:00"'::hstore)).ts)
AS populate_epoch_at_utc;
CREATE TABLE hstore_idx(ts timestamptz);
CREATE INDEX hstore_idx_expr
ON hstore_idx ((hstore(ROW(ts)) -> 'f1'));
INSERT INTO hstore_idx VALUES ('2026-01-01 00:00+00');
SELECT hstore(ROW(ts)) -> 'f1' AS output_at_utc
FROM hstore_idx;
SET TimeZone = 'Europe/Moscow';
SELECT extract(epoch FROM
(populate_record(NULL::hstore_row,
'"ts"=>"2026-01-01 00:00:00"'::hstore)).ts)
AS populate_epoch_at_moscow;
SELECT hstore(ROW(ts)) -> 'f1' AS output_at_moscow
FROM hstore_idx;
SET enable_seqscan = on;
SET enable_indexscan = off;
SET enable_bitmapscan = off;
SELECT count(*) AS seq_count
FROM hstore_idx
WHERE hstore(ROW(ts)) -> 'f1' = '2026-01-01 03:00:00+03';
SET enable_indexscan = on;
SET enable_bitmapscan = on;
SET enable_seqscan = off;
SELECT count(*) AS index_count
FROM hstore_idx
WHERE hstore(ROW(ts)) -> 'f1' = '2026-01-01 03:00:00+03';
EXPLAIN (COSTS OFF)
SELECT count(*)
FROM hstore_idx
WHERE hstore(ROW(ts)) -> 'f1' = '2026-01-01 03:00:00+03';
RESET enable_seqscan;
RESET enable_indexscan;
RESET enable_bitmapscan;
RESET TimeZone;
DROP TABLE hstore_idx;
DROP TYPE hstore_row;
DROP EXTENSION hstore;
The relevant output was:
oid | provolatile
------------------------------------+-------------
hstore(record) | i
populate_record(anyelement,hstore) | i
(2 rows)
populate_epoch_at_utc
-----------------------
1767225600.000000
(1 row)
output_at_utc
------------------------
2026-01-01 00:00:00+00
(1 row)
populate_epoch_at_moscow
--------------------------
1767214800.000000
(1 row)
output_at_moscow
------------------------
2026-01-01 03:00:00+03
(1 row)
seq_count
-----------
1
(1 row)
index_count
-------------
0
(1 row)
QUERY PLAN
---------------------------------------------------------
Aggregate
-> Bitmap Heap Scan on hstore_idx
Recheck Cond: ((hstore(ROW(ts)) -> 'f1'::text) = '2026-01-01
03:00:00+03'::text)
-> Bitmap Index Scan on hstore_idx_expr
Index Cond: ((hstore(ROW(ts)) -> 'f1'::text) = '2026-01-01
03:00:00+03'::text)
(5 rows)
The issue was reproduced on PostgreSQL 18.6 on Linux/amd64 with the hstore
extension and otherwise session-local planner and TimeZone settings shown
above. The catalog volatility value i permits these setting-dependent
converters in persistent expressions, allowing an expression index to retain
text generated under an earlier TimeZone. This occurs when hstore record
converters or an expression index over hstore(record) are used across
TimeZone changes.
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: BUG #19880: hstore(record) expression index returns wrong rows after TimeZone change
2026-11-11 13:39 BUG #19880: hstore(record) expression index returns wrong rows after TimeZone change PG Bug reporting form <noreply@postgresql.org>
@ 2026-11-11 15:18 ` Fujii Masao <masao.fujii@gmail.com>
0 siblings, 0 replies; 2+ messages in thread
From: Fujii Masao @ 2026-11-11 15:18 UTC (permalink / raw)
To: imchifan@163.com; pgsql-bugs@lists.postgresql.org
On Wed, Nov 11, 2026 at 11:00 PM PG Bug reporting form
<noreply@postgresql.org> wrote:
> PostgreSQL 18.6 on Linux/amd64 marks hstore(record) and
> populate_record(anyelement,hstore) IMMUTABLE even though their results can
> depend on TimeZone.
This issue is already being discussed in [1].
Regards,
[1] https://www.postgresql.org/message-id/C79ADC9A-5DFE-4B5C-8C78-012802D31531%40localhost.localdomain
--
Fujii Masao
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2026-11-11 15:18 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-11-11 13:39 BUG #19880: hstore(record) expression index returns wrong rows after TimeZone change PG Bug reporting form <noreply@postgresql.org>
2026-11-11 15:18 ` Fujii Masao <masao.fujii@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox