Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.98.2) (envelope-from ) id 1xR8uQ-00000000ifw-1OHW for pgsql-bugs@arkaria.postgresql.org; Wed, 11 Nov 2026 14:02:42 +0000 Received: from localhost ([127.0.0.1]:36498 helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.98.2) (envelope-from ) id 1xR8uO-0000000BPbv-40qk for pgsql-bugs@arkaria.postgresql.org; Wed, 11 Nov 2026 14:02:41 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]:38302) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.98.2) (envelope-from ) id 1xR8Xe-0000000BAG6-26eS for pgsql-bugs@lists.postgresql.org; Wed, 11 Nov 2026 13:39:10 +0000 Received: from mahout.postgresql.org ([2001:4800:3e1:1::227]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.98.2) (envelope-from ) id 1xR8Xc-00000000NZY-15vk for pgsql-bugs@lists.postgresql.org; Wed, 11 Nov 2026 13:39:09 +0000 DKIM-Signature: v=1; a=rsa-sha256; q=dns/txt; c=relaxed/relaxed; d=postgresql.org; s=20171124; h=Message-ID:Date:Reply-To:Cc:From:To:Subject: Content-Transfer-Encoding:MIME-Version:Content-Type:Sender:Content-ID: Content-Description:In-Reply-To:References; bh=aAiUHaOAoiW++VCRDzPVnGfMdvcXDxI2QqX8RpwCga0=; b=cb+PwtkIwQ+uwxrpGwystsKPf+ /P1bV2qJepX3FdMieu2t7adXt321BmuR4lqahGerY+TjgVTIuphM7RkEr6cGGVZhbMOUCIuN6IxHw JLTQtZBCV4+2f0RAt/Z6RqJnA0XtO9Lk8a4xKsTZyD+gQByBNyus1P6fES8G8Lb4qALrDP5Bqjy1d 2/IdRZbepUCyLEJBLyoIk5BPHfWWvve9nlqP+vzddHdAIaR7ufRV4wqOb1bHWZZ4VKtX8f+9AumZ0 K2ThcHr0mkgvis/LI2fJFUX3dVoj3UYOrEEY7rH6STaL48mGR5M2dCizMtocht72dlfSsxkGmJVvO E8l/K8xA==; Received: from wrigleys.postgresql.org ([2a02:16a8:dc51::60]) by mahout.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.98.2) (envelope-from ) id 1xR8XY-000000012ue-0MXa for pgsql-bugs@lists.postgresql.org; Wed, 11 Nov 2026 13:39:07 +0000 Received: from localhost ([127.0.0.1] helo=wrigleys.postgresql.org) by wrigleys.postgresql.org with esmtp (Exim 4.98.2) (envelope-from ) id 1xR8XX-00000002ixO-0R17 for pgsql-bugs@lists.postgresql.org; Wed, 11 Nov 2026 13:39:03 +0000 Content-Type: text/plain; charset="utf-8" MIME-Version: 1.0 Content-Transfer-Encoding: quoted-printable Subject: BUG #19880: hstore(record) expression index returns wrong rows after TimeZone change To: pgsql-bugs@lists.postgresql.org From: PG Bug reporting form Cc: imchifan@163.com Reply-To: imchifan@163.com, pgsql-bugs@lists.postgresql.org Date: Wed, 11 Nov 2026 13:39:00 +0000 Message-ID: <19880-3b53510cc9b8da14@postgresql.org> X-Auto-Response-Suppress: All Auto-Submitted: auto-generated List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk 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: =20 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 =3D 'UTC'; SELECT extract(epoch FROM (populate_record(NULL::hstore_row, '"ts"=3D>"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 =3D 'Europe/Moscow'; SELECT extract(epoch FROM (populate_record(NULL::hstore_row, '"ts"=3D>"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 =3D on; SET enable_indexscan =3D off; SET enable_bitmapscan =3D off; SELECT count(*) AS seq_count FROM hstore_idx WHERE hstore(ROW(ts)) -> 'f1' =3D '2026-01-01 03:00:00+03'; SET enable_indexscan =3D on; SET enable_bitmapscan =3D on; SET enable_seqscan =3D off; SELECT count(*) AS index_count FROM hstore_idx WHERE hstore(ROW(ts)) -> 'f1' =3D '2026-01-01 03:00:00+03'; EXPLAIN (COSTS OFF) SELECT count(*) FROM hstore_idx WHERE hstore(ROW(ts)) -> 'f1' =3D '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) =3D '2026-01-01 03:00:00+03'::text) -> Bitmap Index Scan on hstore_idx_expr Index Cond: ((hstore(ROW(ts)) -> 'f1'::text) =3D '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.