BUG #19644: byteaout, float8out and float4out are marked IMMUTABLE but depend on GUCs

The following bug has been logged on the website:

Bug reference: 19644
Logged by: 放空
Email address: jj-zhang25(at)mails(dot)tsinghua(dot)edu(dot)cn
PostgreSQL version: 18.6
Operating system: MacOS
Description:

Summary
=======

Three output functions are marked IMMUTABLE in pg_proc although their
results
depend on a GUC:

byteaout(bytea) depends on bytea_output
float8out(double precision) depends on extra_float_digits
float4out(real) depends on extra_float_digits

Because they are marked IMMUTABLE, casts of these types to text are accepted
in
expression indexes, in generated columns, in unique indexes, in CHECK
constraints, and in partition keys. Changing the corresponding GUC -- a
plain
session-level SET, with no DDL -- then leaves those materialised values
inconsistent with the current setting.

The comparable output functions whose results also depend on a GUC are
marked
STABLE and are correctly rejected at DDL time, which is what makes these
three
stand out:

SELECT p.oid::regprocedure AS function, p.provolatile::text
FROM pg_proc p
WHERE p.proname IN ('byteaout','float8out','float4out','date_out',
'interval_out','cash_out','numeric_out','timestamptz_out')
ORDER BY p.provolatile::text DESC, 1;

function | provolatile
-------------------------------------------+-------------
cash_out(money) | s
date_out(date) | s
timestamptz_out(timestamp with time zone) | s
interval_out(interval) | s
byteaout(bytea) | i
float4out(real) | i
float8out(double precision) | i
numeric_out(numeric) | i

(numeric_out is correctly IMMUTABLE; its output does not depend on any GUC.)

I am reporting the three together because they appear to share one fix --
the
volatility marking -- and because the consequences below are reached through
whichever of the three is used. Five distinct consequences follow, in
increasing
order of severity.

1. Expression index returns wrong results
=========================================

SET bytea_output='hex';
CREATE TABLE b(id int primary key, v bytea);
CREATE INDEX ix ON b ((v::text));
INSERT INTO b(id,v) VALUES (1,'\x41ff'),(2,'\x42fe');

SET bytea_output='escape'; -- session setting, no DDL

SELECT id FROM b WHERE v::text = 'A\377';
id
----
(0 rows)

The default plan uses the index:

EXPLAIN (COSTS OFF) SELECT id FROM b WHERE v::text = 'A\377';
Bitmap Heap Scan on b
Recheck Cond: ((v)::text = 'A\377'::text)
-> Bitmap Index Scan on ix
Index Cond: ((v)::text = 'A\377'::text)

Forcing a sequential scan gives the correct answer:

SET enable_indexscan=off; SET enable_bitmapscan=off;
SELECT id FROM b WHERE v::text = 'A\377';
id
----
1
(1 row)

The row does match the predicate:

SELECT v::text AS actual, v::text = 'A\377' AS eq,
md5(v::text) AS md5_actual, md5('A\377') AS md5_literal
FROM b WHERE id=1;
actual | eq | md5_actual | md5_literal
--------+----+----------------------------------+----------------------------------
A\377 | t | 6b279243ff65734b53ffb78c7a20ce5d |
6b279243ff65734b53ffb78c7a20ce5d

REINDEX restores agreement, confirming the index content is the stale part.
VACUUM FULL does not.

2. Generated column contradicts its own definition
==================================================

SET bytea_output='hex';
CREATE TABLE g2(id int primary key, v bytea,
gv text GENERATED ALWAYS AS (v::text) STORED);
INSERT INTO g2(id,v) VALUES (1,'\x41ff');
SET bytea_output='escape';
INSERT INTO g2(id,v) VALUES (2,'\x41ff');

SELECT id, v, gv, v::text FROM g2 ORDER BY id;
id | v | gv | text
----+-------+--------+-------
1 | A\377 | \x41ff | A\377
2 | A\377 | A\377 | A\377

Both rows hold the identical bytea value, yet their generated columns
differ,
and row 1's stored value differs from what its own definition yields in any
current session.

3. Unique index admits two rows holding the same value
======================================================

bytea:

SET bytea_output='hex';
CREATE TABLE u(id int primary key, v bytea);
CREATE UNIQUE INDEX ux ON u ((v::text));
INSERT INTO u VALUES (1,'\x41ff');
SET bytea_output='escape';
INSERT INTO u VALUES (2,'\x41ff'); -- accepted

SELECT id, v, length(v) FROM u ORDER BY id;
id | v | length
----+-------+--------
1 | A\377 | 2
2 | A\377 | 2

SELECT v, count(*) FROM u GROUP BY v;
v | count
-------+-------
A\377 | 2

float8, where the two rows demonstrably hold the same double precision value
rather than merely similar-looking ones:

SET extra_float_digits=0;
CREATE TABLE fu(id int primary key, v float8);
CREATE UNIQUE INDEX fux ON fu ((v::text));
INSERT INTO fu VALUES (1, pi());
SET extra_float_digits=3;
INSERT INTO fu VALUES (2, pi()); -- accepted

SELECT id, v, (v = pi()) AS is_pi FROM fu ORDER BY id;
id | v | is_pi
----+-------------------+-------
1 | 3.141592653589793 | t
2 | 3.141592653589793 | t

SELECT count(*) FROM fu WHERE v = pi();
2

The index still enforces the constraint against new rows, so it is
simultaneously enforcing and violated.

4. CHECK constraint violated by stored data
===========================================

SET bytea_output='hex';
CREATE TABLE h1(id int primary key, v bytea,
CONSTRAINT ckb CHECK (v::text <> '\x41ff'));
INSERT INTO h1 VALUES (1,'\x42fe');

SET bytea_output='escape';
INSERT INTO h1 VALUES (2,'\x41ff'); -- accepted: v::text is 'A\377'
here

SET bytea_output='hex';
SELECT id, v::text, (v::text <> '\x41ff') AS check_holds FROM h1 ORDER BY
id;
id | text | check_holds
----+--------+-------------
1 | \x42fe | t
2 | \x41ff | f

INSERT INTO h1 VALUES (3,'\x41ff');
ERROR: new row for relation "h1" violates check constraint "ckb"
DETAIL: Failing row contains (3, \x41ff).

The table cannot be restored from its own dump:

$ pg_dump -t h1 ... | psql -d target
ERROR: new row for relation "h1" violates check constraint "ckb"
DETAIL: Failing row contains (2, \x41ff).
target=# SELECT count(*) FROM h1;
0

5. Partition constraint violated by stored data
===============================================

CREATE TABLE ip(id int, v float8) PARTITION BY LIST ((v::text));
SET extra_float_digits=0;
CREATE TABLE ipart PARTITION OF ip FOR VALUES IN ('3.14159265358979');
INSERT INTO ip VALUES (1, pi()); -- routed into ipart

SET extra_float_digits=3;
SELECT id, v::text AS key_now,
(v::text = '3.14159265358979') AS constraint_holds FROM ipart;
id | key_now | constraint_holds
----+-------------------+------------------
1 | 3.141592653589793 | f

The row sits in a partition whose constraint it no longer satisfies. It is
invisible to predicate queries on the parent while still counted by an
unqualified scan:

SELECT count(*) FROM ip; -- 1
SELECT count(*) FROM ip WHERE v::text = '3.141592653589793'; -- 0

Inserting the identical value now fails outright:

INSERT INTO ip VALUES (2, pi());
ERROR: no partition of relation "ip" found for row
DETAIL: Partition key of the failing row contains ((v::text)) =
(3.141592653589793).

The same value is therefore insertable or not depending only on a session
GUC,
and the table cannot be restored from its own dump:

$ pg_dump -t ip -t ipart ... | psql -d target
ERROR: new row for relation "ipart" violates partition constraint
DETAIL: Failing row contains (1, 3.141592653589793).
target=# SELECT count(*) FROM ip;
0

Also affected: hash and BRIN indexes
====================================

The staleness is not specific to btree:

SET bytea_output='hex';
CREATE TABLE k1(id int primary key, v bytea);
CREATE INDEX kx ON k1 USING hash ((v::text)); -- and again USING brin
INSERT INTO k1 VALUES (1,'\x41ff'),(2,'\x42fe');

SET bytea_output='escape';
SET enable_seqscan=off;
SELECT count(*) FROM k1 WHERE v::text='A\377'; -- 0
SET enable_indexscan=off; SET enable_bitmapscan=off;
SELECT count(*) FROM k1 WHERE v::text='A\377'; -- 1

Note on pg_dump
===============

pg_dump does not emit a SET for bytea_output or extra_float_digits, so a
restore
uses the client default. The value under which the original index entries or
generated column values were produced is not recorded anywhere, which is why
the
restores above fail rather than reproducing the source state.

What PostgreSQL gets right
==========================

Equivalent probes against genuinely non-immutable operations are correctly
refused, which is why this looks like a marking oversight rather than a
design
decision:

ALTER TABLE t ADD COLUMN d date GENERATED ALWAYS AS (CAST(ts AS date))
STORED;
ERROR: generation expression is not immutable

CREATE INDEX ON t (CAST(ts AS date));
ERROR: functions in index expression must be marked IMMUTABLE

-- to_char with locale-dependent patterns, cash_out, interval_out:
likewise refused

Changes that genuinely alter equality or ordering are also handled
correctly,
including re-validating constraints and rebuilding dependent indexes when a
column's collation changes to a nondeterministic one.

Suggested fix
=============

Mark byteaout, float8out and float4out (and the corresponding casts to text)
STABLE rather than IMMUTABLE, matching date_out, interval_out and cash_out.
Every consequence above then becomes a DDL-time error, as it already is for
the
other GUC-dependent output functions.

I recognise this would reject expressions that some existing schemas may
use, so
a narrower alternative would be to make the text output of these types
independent of the GUC in index and generated-column contexts. That leaves
the
volatility markings inaccurate for other callers, so the first option seems
preferable, but the compatibility trade-off is a judgement for the project.

Prior discussion
================

I searched the mailing list archives, the TODO list and the FAQ and found no
prior report of this. If it overlaps something I missed, I would be glad to
be
pointed at it.