BUG #19880: hstore(record) expression index returns wrong rows after TimeZone change

The following bug has been logged on the website:

Bug reference: 19880
Logged by: Qifan Liu
Email address: imchifan(at)163(dot)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.